SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ORDER BY when inserting into table variable


ORDER BY when inserting into table variable

Author
Message
x_japanfans5312
x_japanfans5312
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211338 Visits: 46256
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 recoverability

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


Sean Pearce
Sean Pearce
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3808 Visits: 3436
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
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11674 Visits: 5478

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Luis Cazares
Luis Cazares
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39737 Visits: 19802
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
GilaMonster
GilaMonster
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211338 Visits: 46256
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 recoverability

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


Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11674 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Luis Cazares
Luis Cazares
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39737 Visits: 19802
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11674 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Luis Cazares
Luis Cazares
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39737 Visits: 19802
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search