INSERT INTO Issue in SQL 2012
-
I have a query that executed OK in SQL 2008.
INSERT INTO @Authors (AuthorName)
SELECT AuthorName
from tblAuthors
ORDER BY AuthorName
However in SQL 2012, the Authors are not inserted by the Order By clause unless I add a Top to the SELECT clause.
Any ideas?
Thanks.
-
michaelh-731356 (5/31/2012)
I have a query that executed OK in SQL 2008.INSERT INTO @Authors (AuthorName)
SELECT AuthorName
from tblAuthors
ORDER BY AuthorName
However in SQL 2012, the Authors are not inserted by the Order By clause unless I add a Top to the SELECT clause.
Any ideas?
Thanks.
Remove the order by. It is not going to gain you anything other than a hit to performance when inserting. Tables in sql are by definition an unordered collection of data. If you want your data sorted there is exactly ONE way to make sure it is ordered. That is to add an order by clause when you select the data from the table. The essence of what you are doing adding an order by to the insert is trying to tell sql how to store it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (6/1/2012)
Tables in sql are by definition an unordered collection of data.Heh... I've heard that. 😀 However, while that is the general mantra of database experts all over the world, there are times where an ordered collection is necessary and the "inserted order" needs to be reflected by something like an IDENTITY column.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
michaelh-731356 (5/31/2012)
I have a query that executed OK in SQL 2008.INSERT INTO @Authors (AuthorName)
SELECT AuthorName
from tblAuthors
ORDER BY AuthorName
However in SQL 2012, the Authors are not inserted by the Order By clause unless I add a Top to the SELECT clause.
Any ideas?
Thanks.
I don't know if they made a change or not in 2k12 but, according to your post, it would appear that they have. I hate it when they make such subtle and seemingly unnecessary changes like this.
If what you say is indeed true, then you may have to add TOP to the SELECT to do the ORDER BY during the INSERT. You'll find that lots of people will warn you away from using TOP 100 PERCENT and I agree. Do a search on the internet for the details if you're interested.
TOP can take a BIGINT as an operand. It's not likely that you'll ever need to do an insert of something bigger than what an INT can hold so if you use a nice round number, like 3 billion, I'm pretty sure it'll cover the bases for you.
SELECT TOP 3000000000
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Couple points...
Order by on an insert has never been guaranteed to do anything other than control the order of the identity column if present, top or no top. Select without an order by has never been guaranteed to return rows in any particular order.
Since the behaviour has never been guaranteed and if it ever did happen was as a side effect of how the optimiser and query processor worked, it wouldn't have been an outright change that was consciously made, but any change to the optimiser and/or query processor could easily have had a side effect of changing the effective order.
Either order by on the select that fetches data, or add an identity column to the table, order by on the insert and then order by the identity column on the select.
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 -
I DID make the mistake of assuming that the only reason why you might want to do an "ordered insert" was because you wanted that insert order to be reflected in an IDENTITY column. The question is, then, do you have an IDENTITY column on the @Authors you're inserting into?
Another question would be, are you getting an error without the TOP or not?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For the record, SQL 2008
DECLARE @WeirdTable TABLE (ID INT IDENTITY,
Name VARCHAR(150) NOT NULL PRIMARY KEY, -- so cluster
CreateDate DATETIME,
Filler CHAR(100) DEFAULT ''
)
INSERT INTO @WeirdTable (Name, CreateDate)
SELECT name, create_date FROM msdb.sys.tables AS t
ORDER BY create_date
SELECT * FROM @WeirdTable AS wt
The identity values correspond to the order of the create_date column, the data is returned in the order of the name because that is the clustered index key and the query plan is a simple clustered index scan so there's no reason for the order to change.
I'll fire up a 2012 box later (if I still have one that works) and compare
Edit: 2012 RC0 exactly the same behaviour. (I don't have an RTM instance installed)
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 -
Jeff Moden (6/2/2012)
Sean Lange (6/1/2012)
Tables in sql are by definition an unordered collection of data.Heh... I've heard that. 😀 However, while that is the general mantra of database experts all over the world, there are times where an ordered collection is necessary and the "inserted order" needs to be reflected by something like an IDENTITY column.
I suppose that could be. I have never come up with a reason why the order of an identity makes any difference, it is after all a "magic number" assigned to a row. I also assumed that since this is inserting and ordering by author name that the order of the identity (if present) would not matter. Now had it been ordered by DateAdded or something along those lines it would seem more likely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
GilaMonster (6/2/2012)
Order by on an insert has never been guaranteed to do anything other than control the order of the identity column if presentNot correct.
Order by actually guarantees the order of records as they are inserted into a table.
Simple test:
DROP TABLE [dbo].[Test]CREATE TABLE [dbo].[Test](
[ID] [int],
)
CREATE CLUSTERED INDEX UC_Test ON dbo.Test(ID) WITH FILLFACTOR = 80
/*
Now we pre-populate the index with some data:
*/
INSERT INTO [dbo].[Test] (ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N
INSERT INTO [dbo].[Test] (ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N DESC
And here goes the actual test.
To be executed repeatedly:
DECLARE @initTime datetime
SET @initTime = GETDATE()
INSERT INTO [dbo].[Test]
(ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N
PRINT 'Order By N : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
SET @initTime = GETDATE()
INSERT INTO [dbo].[Test]
(ID)
SELECT N
FROM dbo.Tally AS T
ORDER BY N DESC
PRINT 'Order By N DESC : ' + CONVERT(varchar(20), GETDATE () - @InitTime, 114)
By the difference in execution time you may tell ORDER BY makes some difference.
By looking into the execution plans you may see how exactly it affects the query execution.
Forcing an order of inserted rows by ORDER BY to be in line with CLUSTERED key/index definition may cut execution time by 2 or more times.
Especially on a table with multiple indexes.
_____________
Code for TallyGenerator -
I'm not going to get into a huge debate because as the previous thread proved, you're willing to twist my words and ignore portions of what I say just to prove me 'wrong'
Order by on an insert does not guarantee the order of the rows in the table (well, if there's a heap and it's empty before the insert maybe). For a cluster, the key order affects the logical order of data in the table, not the order the rows were inserted in.
Now, on to your test...
The first test, the data could be pulled from the table in the same order that the destination clustered index wanted, so SQL didn't have to put a sort in the plan itself. The second test, the data was pulled from the Tally table in an order that does not match the destination clustered index, so the optimiser had to put a sort in the plan (check the sort, it's ASC, it's the scan on Tally that's reverse)
Hence what I said earlier still holds. An order by on the insert does not guarantee the order of that the rows will be inserted in the table, it will however affect the identity values if you have them. All putting an order by into the plan does is potentially make the insert slower. SQL will honour the order by you specified (in 2008 at least) then will re-order data for the clustered index insert.
For further proof, check this variation, there are two sorts, one on the newID, then a second to get the rows into the clustered index order for the actual insert.
INSERT INTO [dbo].[Test] (ID)SELECT N
FROM dbo.Tally AS T
ORDER BY newID() DESC
If you go and put an order by that does match the clustered index key, all you're doing is removing the need for SQL to put an implicit sort into the plan.
Edit: I ran the tests in 2008, don't currently have a working SQL 2012 installation.
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 -
Sergiy (8/20/2012)
Order by actually guarantees the order of records as they are inserted into a table.Not correct. There was a missed optimization pre-2012 which meant that a unnecessary sort often appeared in query plans where this sort of pointless ORDER BY was included. In SQL Server 2012 (and remember this is the 2012 forum), the query plans for your two test queries are identical:
-
The plans are identical for "Query 2" and "Query 3" in SQL 2008 as well.
Did you run those queries repeatedly, as I asked?
So we can see the plans for "Query 4" and "Query 5"?
_____________
Code for TallyGenerator -
GilaMonster (8/21/2012)
Order by on an insert does not guarantee the order of the rows in the tableNothing actually can guarantee the order of the rows in the table. Not even a clustered index with fillfactor = 100%.
But it's not the only target people could chase with the statement, would you agree?
Hence what I said earlier still holds. An order by on the insert does not guarantee the order of that the rows will be inserted in the table, it will however affect the identity values if you have them.
It's not quite what you said.
What you said was this:
Order by on an insert has never been guaranteed to do anything other than control the order of the identity column if present, top or no top.
There are many other things you can fit into "do anything" apart from "guarantee the order of that the rows ".
_____________
Code for TallyGenerator -
Sergiy (8/21/2012)
The plans are identical for "Query 2" and "Query 3" in SQL 2008 as well.No they're not. On SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2:
Or, if the server is able to use parallelism:
Did you run those queries repeatedly, as I asked?
Certainly. The plans are always as I have shown.
So we can see the plans for "Query 4" and "Query 5"?
You're looking at them, as I have explained.
-
Sergiy (8/21/2012)
GilaMonster (8/21/2012)
Order by on an insert does not guarantee the order of the rows in the tableNothing actually can guarantee the order of the rows in the table. Not even a clustered index with fillfactor = 100%.
But it's not the only target people could chase with the statement, would you agree?
Hence what I said earlier still holds. An order by on the insert does not guarantee the order of that the rows will be inserted in the table, it will however affect the identity values if you have them.
It's not quite what you said.
What you said was this:
Order by on an insert has never been guaranteed to do anything other than control the order of the identity column if present, top or no top.
There are many other things you can fit into "do anything" apart from "guarantee the order of that the rows ".
Anyone can read anything they want into any statement they want to. Doesn't make them right, but they can do it.
I've had plenty of people argue with statements I never made, based on what they decided I "must really mean", completely ignoring the actual words I really used.
That seems to be what you're doing here.
Based on that, I'll just pipe in with, "How dare you speak about those people that way! They've suffered in ways you can't possibly imagine! You are an evil, evil person!"
Doesn't make any sense, given any actual context, but since we're chasing imaginary targets on statements now.
This is fun, isn't it? 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply