top of page

Search Results

2 items found for ""

  • Ordering of Row inserts into a table during INSERT statement

    In SQL Server, if we try to INSERT rows using a SELECT ORDER BY statement as in the SQL mentioned below, then the inserted rows are not necessarily inserted in the same sequence as the rows returned by the SELECT ORDER BY statement. In other words there is no use of having an ORDER BY clause for the SELECT statement when inserting rows and its impossible to control the order of rows inserted. The only control a developer has is over the default ordering when a SELECT statement is used without any ORDER BY clause, for the table in which rows have been inserted. This article explains how we control this default ordering. INSERT INTO table1 (ProductId, Product) SELECT ProductId, Product FROM table2 order by Product; SELECT ProductId, Product from table1; --this will not show rows ordered by Product, but ordered by the primary key or clustered index of table1 or in some unknown order if neither a primary key or a clustered index exists APPROACH 1 (using Primary key or Clustered Index) The solution is to make the column on which you want to have a default order the primary key in the target table. Then if you insert using a SELECT statement the inserted rows in the target table would have the default ordering by that column. Below examples use table variables to illustrate the solution, but the same concepts apply equally well to temp tables or regular tables in SQL Server. DECLARE @table_source TABLE (ProductId INT ,Product VARCHAR(50)); INSERT INTO @table_source (ProductId, Product) VALUES (2, 'Blue Trousers'), (1, 'Red Trousers'), (7, 'Coral Sweater'), (3, 'White Shirt'), (4, 'Auburn Shirt'), (5, 'Blue Shirt'), (6, 'Red Shirt'); SELECT * FROM @table_source; DECLARE @table_target TABLE (ProductId INT ,Product VARCHAR(50) PRIMARY KEY); --the above definition will result in inserted rows to be stored in ascending order of Product --instead of specifying a primary key, you can create a clustered index --for the target table on the column by which you want to sort rows inserted, if the target table is not a table variable -- CREATE CLUSTERED INDEX IX_TargetTable_Product ON tableName (Product); INSERT INTO @table_target (ProductId, Product) SELECT * FROM @table_source; SELECT * FROM @table_target; --this will automatically return rows in ascending order of Product What if we wanted to change the default order to a descending order of Product rather than an ascending order. The solution is the same as above except where the target table is declared/defined. The target table definition statement would be changed to the following. DECLARE @table_target TABLE (ProductId INT ,Product VARCHAR(50) PRIMARY KEY CLUSTERED (Product DESC)); --instead of defining a primary key as above you could create a clustered index and get the same effect, provided the table is not a table variable --(table variable cannot have a clustered index created by the below statement) --CREATE CLUSTERED INDEX IX_TargetTable_Product ON tableName (Product DESC); APPROACH 2 (using a dynamically generated INSERT query) There is another way to control the default order after row inserts and that is by using a dynamically created INSERT query as shown below. Note that the target table into which rows are inserted should not have a primary key or a clustered index else this approach will not work. The data from the source table is ordered using ROW_NUMBER( ) function in order to sequence by Product column. CREATE TABLE #table_source   ( ProductId INT ,Product VARCHAR(50) ); INSERT INTO #table_source  (ProductId, Product) VALUES (2, 'Blue Trousers'), (1, 'Red Trousers'), (7, 'Coral Sweater'), (3, 'White Shirt'), (4, 'Auburn Shirt'), (5, 'Blue Shirt'), (6, 'Red Shirt'); SELECT * FROM #table_source ; --make sure there is no primary key defined in the target table CREATE TABLE #table_target   (ProductId INT ,Product VARCHAR(50)); DECLARE @values NVARCHAR(MAX); --create dynamically the VALUES part of the INSERT query --in the same order in which you want to insert the rows --in the target table, as in statement below SELECT @values= COALESCE( @values + ',(' + CAST(ts.ProductId AS VARCHAR(20)) +',''' + ts.Product +''')' ,' (' + CAST(ts.ProductId AS VARCHAR(20)) +',''' + ts.Product +''')' ) FROM #table_source ts ORDER BY Prod uct; PRINT @values; DECLARE @insertQuery NVARCHAR(MAX); SET @insertQuery = 'INSERT INTO #table_target  (ProductId, Product) VALUES ' + @values; PRINT @insertQuery; EXECUTE sys.sp_executesql @insertQuery; SELECT FROM #table_target ; --rows inserted are ordered automatically DROP TABLE #table_source ; DROP TABLE #table_target ;

  • Script to create an index for a table variable

    Table variables can have a non-clustered or a clustered index. Further, an order can be specified for index column(s). Script to create a UNIQUE clustered index on a table variable (note you can create a UNIQUE or non-unique clustered index for a table variable) DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), UNIQUE CLUSTERED (Product)); --above will create an index in ascending order of Product --to create an index with a descending order of Product --use the script below (note: you can also use multiple columns as in --(Product DESC, ProductId ASC) when defining an index) --DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), -- UNIQUE CLUSTERED (Product DESC)); An aletrnate create script is as below. DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 UNIQUE CLUSTERED (Product DESC)); Script to create a Non-unique Clustered Index DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 CLUSTERED (Product ASC)); Script to create a NON-UNIQUE clustered index on a table variable DECLARE @table_target TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 CLUSTERED(Product)); If you define a primary key for a table variable, then automatically a clustered index is created for the primary key column(s). so this is another way to create a clustered index for a table variable. DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50) PRIMARY KEY); --an alternate script for creating primary key on one or more columns is as below ---DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), PRIMARY KEY (ProductId ASC, Product DESC)); Script to create a non-clustered index on a table variable (note that we do not need a UNIQUE constraint for such an index) DECLARE @table_source TABLE ( ProductId INT ,Product VARCHAR(50), INDEX IX1 NONCLUSTERED (Product)); If you have a primary key (non-clustered index) and also a clustered index on another column then the default order of rows in SELECT statement will be decided by the primary key and not the clustered index. Keep in mind that only 1 clustered index can be created per table and it decides the storage order of the rows for that table.

bottom of page