Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ORDER BY when inserting into table variable Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 2:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 24, 2013 3:06 AM
Points: 10, Visits: 209
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
Post #1466626
Posted Monday, June 24, 2013 3:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1466641
Posted Tuesday, June 25, 2013 4:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:37 AM
Points: 998, Visits: 3,089
x_japanfans5312 (6/24/2013)
seems like the order BY Number DESC on the insert statement doesn;t work in SQL Server 2012

We 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.





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1467061
Posted Friday, June 28, 2013 9:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201

We don't order INSERTS, we order SELECTS.


Until we have identity and we want to populate it in a specific order...



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1468606
Posted Friday, June 28, 2013 9:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 4,049, Visits: 9,211
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 C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468610
Posted Friday, June 28, 2013 10:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1468614
Posted Friday, June 28, 2013 10:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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.




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1468616
Posted Friday, June 28, 2013 10:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 4,049, Visits: 9,211
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 C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468619
Posted Friday, June 28, 2013 10:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 10:09 AM
Points: 2,876, Visits: 5,201
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"


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1468627
Posted Friday, June 28, 2013 11:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 4,049, Visits: 9,211
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468644
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse