ORDER BY when inserting into table variable
-
Hi,
I am a bit confused why the SQL Server 2012 doesn't behave as same as SQL Server 2008
here is the issue,
i have the following query:
DECLARE @table TABLE
(
num INT
)
INSERT INTO @table
SELECT
Number
FROM
dbo.Number
ORDER BY
Number desc
SELECT * FROM @table
dbo.Number contains one column called Number and has data 1,2,3,4,5..
when i run this query on SQL Server 2008, it will return me the data in this order
Number
5
4
3
2
1
but in SQL Server 2012 the query will return like this
1
2
3
4
5
seems like the order BY Number DESC on the insert statement doesn;t work in SQL Server 2012
-
The only thing that order by on an insert is guaranteed to do is assign the values of an identity column if one exists. Your select has no order by, hence SQL is in no way required to return the data in any particular order.
It's not that one version is correct and the other is not, they're both correct, you're depending on behaviour that is not and never has been guaranteed. If you want an order in your returned results, you must put an order by on the outer-most select statement.
DECLARE @table TABLE(
num INT
)
INSERT INTO @table
SELECT
Number
FROM
dbo.Number
SELECT *
FROM @table
ORDER BY
Number desc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
x_japanfans5312 (6/24/2013)
seems like the order BY Number DESC on the insert statement doesn;t work in SQL Server 2012We don't order INSERTS, we order SELECTS.
Your two queries can be read like this:
Add some rows into a table in a specific order.Give them back to me in any order.
They should read like this:
Add some rows into a table in any order.Give them back to me in a specific order.
-
We don't order INSERTS, we order SELECTS.
Until we have identity and we want to populate it in a specific order...
-
Eugene Elutin (6/28/2013)
We don't order INSERTS, we order SELECTS.
Until we have identity and we want to populate it in a specific order...
Or want to do a clustered insert instead of a table insert + index insert (as far as I remember) -
Luis Cazares (6/28/2013)
Eugene Elutin (6/28/2013)
We don't order INSERTS, we order SELECTS.
Until we have identity and we want to populate it in a specific order...
Or want to do a clustered insert instead of a table insert + index insert (as far as I remember)
Nope.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Luis Cazares (6/28/2013)
Eugene Elutin (6/28/2013)
We don't order INSERTS, we order SELECTS.
Until we have identity and we want to populate it in a specific order...
Or want to do a clustered insert instead of a table insert + index insert (as far as I remember)
No, I don't think that clustered index is relevant here. Even if ORDER BY specified, SQL server may not sort your records for inserting into clustered table.
Check this one:
http://dba.stackexchange.com/questions/7350/efficient-insert-into-a-table-with-clustered-index
I think it's only relevant to specify the order of identity generation and for cases when selecting TOP (N) rows for inserts.
-
Ok, won't argue on this, but will definitively search on why I was told that.
EDIT: I believe they told me to change a non-clustered index (only index on a heap) to a clustered index and then use an order by and a dbcc traceon(610) to have minimally logged inserts. (I'm not sure it's a good practice even if the advice was given by a Microsoft consultant.)
-
Luis Cazares (6/28/2013)
Ok, won't argue on this, but will definitively search on why I was told that.EDIT: I believe they told me to change a non-clustered index (only index on a heap) to a clustered index and then use an order by and a dbcc traceon(610) to have minimally logged inserts. (I'm not sure it's a good practice even if the advice was given by a Microsoft consultant.)
I'm not sure about how it would play in older versions of SQL Server. As for now, mimimal logging for INSERT INTO, according to MS (http://msdn.microsoft.com/en-us/library/ms174335(v=sql.105).aspx) could be achieved by:
Using INSERT INTO…SELECT to Bulk Import Data with Minimal Logging
You can use INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.
Minimal logging for this statement has the following requirements:
•The recovery model of the database is set to simple or bulk-logged.
•The target table is empty or is a nonempty heap.
•The target table is not used in replication.
•The TABLOCK hint is specified for the target table.
Rows that are inserted into a heap as the result of an insert action in a MERGE statement may also be minimally logged.
...
I'm not aware of anything else which can make INSERT INTO "less logged"
-
Eugene,
I found this reference googling dbcc traceon(610)
http://sqlserverplanet.com/data-warehouse/sql-server-2008-minimally-logged-inserts
Again, I'm not saying is a good choice, simply an advice I've got.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply