INSERT INTO SELECT WITH ORDER BY

Question

INSERT INTO SELECT WITH ORDER BY

Viewing 10 posts - 31 through 39 (of 39 total)

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

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

Eugene Elutin (6/24/2010)
Hi Joe, since when are you back to a dusty Texas? Or how you managed to get internet connection in your Borneo hut?

That's uncalled for.

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

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

Paul White NZ (6/24/2010)
SET NOCOUNT ON; GO CREATE TABLE dbo.Example ( row_id INTEGER IDENTITY NOT NULL PRIMARY KEY CLUSTERED, data AS CONVERT(VARCHAR(11), row_id) PERSISTED NOT NULL UNIQUE ); GO INSERT dbo.Example (data) DEFAULT VALUES; GO 10 -- Comes back in data order SELECT E.row_id, E.data FROM dbo.Example E; GO -- Comes back in row_id order SELECT E.row_id, E.data FROM dbo.Example E
ORDER BY E.row_id;

Without running it, I would venture a guess that the first is doing a scan of the unique nonclustered index and the second a scan of the cluster.

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

GilaMonster (6/24/2010)

Without running it, I would venture a guess that the first is doing a scan of the unique nonclustered index and the second a scan of the cluster.

And you would guess right 😉

Clear Sky SQL
My Blog[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Thanks, Paul!

That is the key! Adding another index to the table made me happy and explained everything!

As soon as second (non-clustered) index added, SQL may deside to use it to retrieve data from table in uncoditional query and that exactly what happens! Cool.

😀

_____________________________________________
"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[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 5

GilaMonster (6/24/2010)
Without running it, I would venture a guess that the first is doing a scan of the unique nonclustered index and the second a scan of the cluster.

Absolutely.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 6

Random fact for the day:

On a table clustered on an integer IDENTITY column, SQL Server Enterprise can use linear interpolation to find the row data on the page from a key value rather than doing a binary search based entries in the offset table.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

GilaMonster (6/24/2010)
Eugene Elutin (6/24/2010)
Hi Joe, since when are you back to a dusty Texas? Or how you managed to get internet connection in your Borneo hut?
That's uncalled for.

I was curious as I didn't understand the Borneo thing, so I did a quick google search.

I think he refers to this:

Frankie - 30 Oct 2005 04:36 GMT
Just theorizing here that "Joe Celko" is really more than one person...
actually a common alias used by all the MVPs to say what they Really Think
whenever they see some totally stupid question. The real Joe Celko who
served on the ANSI committee retired years ago to a small village in Borneo
and is completely oblivious to any of this. He's actually quite a nice guy
and plays Santa year 'round for the natives who aren't the wiser.

Taken from here: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-programming/38454/Joe-Celko-Theory

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Paul White NZ (6/24/2010)
Random fact for the day:
On a table clustered on an integer IDENTITY column, SQL Server Enterprise can use linear interpolation to find the row data on the page from a key value rather than doing a binary search based entries in the offset table.
Paul

This is really interesting, Paul. Have you got any sources to point us to?

-- Gianluca Sartori

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

GilaMonster (6/24/2010)
Eugene Elutin (6/24/2010)
Hi Joe, since when are you back to a dusty Texas? Or how you managed to get internet connection in your Borneo hut?
That's uncalled for.

I would like to unreservedly apologise for a "dusty Texas" remark. Texas is a beautiful state indeed.

But for Borneo - it is not easy to get internet connection in remote areas of it. In cities quite ok plus there are some cafes of cause.:-D

Should I appologise for arguing with the expert about Identity?

Ok, I will:

I solemnly swear to never user Identity again and persuade all SQL developers not to use too, otherwise such developers will be damned to hell and will not be allowed to code in SQL anymore.

One problem only: who is going to pay for the projects to remove it from everywhere? I am more than happy to help with it (my contract rate is ,...:-D)

BIT is bad datype? Of cause! I think we shouldn't stop there.

Don't use anything else than NUMERIC(38) to store numeric data or Y/N & 0/1flags. BIT, TINYINT, SMALLINT etc. are datatypes of the past, reserved for assembly programmers and punch cards lovers of 60's, 70's and 80's (may be 90's as well but not a 21 century for sure). Good SQL programmers never use them as the above datatypes (especially BIT one) are bad, disgusting and simply yacky. Brrrrrr.....

:-D:-D:-D

_____________________________________________
"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[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 10

Gianluca Sartori (6/24/2010)
This is really interesting, Paul. Have you got any sources to point us to?

It came up in discussion with Paul Randal.

There is a technical paper on it:

http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.92.2044

Click on the View or Download link to open it in PDF format.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi