Order by While inserting

Question

Order by While inserting

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

ScottPletcher (2/15/2012)
I thought it depended on whether SQL thought it was "necessary" or not.
Whether the number of rows, etc., met whatever magic criteria SQL went by -- which are not documented and can change from release to release.

The real key is, "it doesn't matter". The only thing that matters is how to control the numbering of the IDENTITY column during inserts if that's important. For existing tables, it has been demonstrated that an ORDER BY will work if the ORDER BY values are determinent. For SELECT/INTO it has been demonstrated that an ORDER BY will work if the ORDER BY values are determinent and parallalism is prevented using MAXDOP.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

ScottPletcher (2/15/2012)
IIRC, a single insert of many rows will be done in the clustered index order auto-magically.
Really? Interesting. I don't recall that. Would be nice tho.

Yup, does happen. I wouldn't call it 'auto-magically', the optimiser just drops a sort into the plan

CREATE TABLE InsertOrder (

SomeKey UNIQUEIDENTIFIER,

Filler CHAR(5)

)

CREATE CLUSTERED INDEX idx_TestingOrder ON InsertOrder (SomeKey)

go

INSERT INTO InsertOrder

SELECT NEWID(), LEFT(name,5) FROM sys.columns AS c

View the exec plan of that insert and there's a sort just before the clustered index insert that orders the resultset by the NewID.

Exercise for the readers: toss a nonclustered index onto the other column and see how the plan changes (hint: sorts, plural)

Will it happen in every single case? Probably not, I'm sure there are several optimisations for particular cases.

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

ScottPletcher SSC Guru Points: 101103 More actions · Answer 3

I wouldn't call it 'auto-magically',

That's primarily what I meant. I thought it was conditional. I've seen SQL not do it.

To me it's much safer to code your own ORDER BY for that.

I would expect SQL to do sorts for the other indexes for any significant number of rows, for the same reason -- to avoid too many random mods and page splits.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

ScottPletcher (2/15/2012)
I wouldn't call it 'auto-magically',
That's primarily what I meant. I thought it was conditional. I've seen SQL not do it.
To me it's much safer to code your own ORDER BY for that.

I wouldn't agree. If a sort is needed, one will be added. If there's some optimisation of a particular case where a sort isn't needed, then explicitly adding one may make the query less efficient.

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

ScottPletcher SSC Guru Points: 101103 More actions · Answer 5

When did SQL Server start doing these "auto sorts" for clus indexes?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 6

ScottPletcher (2/16/2012)
When did SQL Server start doing these "auto sorts" for clus indexes?

Common sense tells me it was like this since the day clustered indexes were implemented in SQL Server.

Alex Suprun