Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 11,716 through 11,730 (of 66,712 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Dave Ballantyne (2/18/2010)
Does anyone else read this response
http://www.sqlservercentral.com/Forums/FindPost867131.aspx
as implying that using a identity defines order ?

In that particular case, with the identity column as primary key clustered, yes, doesn't it?

-- Gianluca Sartori

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

Gianluca Sartori (2/18/2010)
In that particular case, with the identity column as primary key clustered, yes, doesn't it?

Logical order of the index - yes

Physical order of the data - no

Order that the rows will be returned in - no.

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

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

Oh dear, I posted a reply to that identity-order thread before checking here :ermm:

I got the checksum question wrong by the way - fell into the 'single bit error' bear trap 😎

edit: for clarity, I guess

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Paul White (2/18/2010)
Oh dear, I posted a reply to that identity-order thread before checking here :ermm:

It's a great answer and needed to be said on the thread.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Chris Morris-439714 (2/18/2010)
Paul White (2/18/2010)
Oh dear, I posted a reply to that identity-order thread before checking here :ermm:
It's a great answer and needed to be said on the thread.

:blush: Not so sure about the 'great', but thanks anyway!

I suppose I just wanted to show the sequence of events...to avoid any misunderstandings 😉 :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

GilaMonster (2/18/2010)
Gianluca Sartori (2/18/2010)
In that particular case, with the identity column as primary key clustered, yes, doesn't it?
Logical order of the index - yes
Physical order of the data - no
Order that the rows will be returned in - no.

Ok, it's not guaranteed and I would not rely on it, but honestly I have to say I've never seen it behaving differently.

I've been spending some time trying to make it return rows in a different order, but I did not succeed. Does anybody have a script to do that? It would be interesting to see.

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

spaghettidba

SSC Guru

Points: 105732

February 18, 2010 at 4:19 am

#1120781

Duplicate post, deleted

-- Gianluca Sartori

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

Paul White (2/18/2010)
Oh dear, I posted a reply to that identity-order thread before checking here :ermm:

If I'm reading the q correctly, he wants the data in the identity order, but is getting the descriptions sorted alphabetically. Probably cause SQL's doing a SORT/DISTINCT SORT to eliminate duplicate descriptions.

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

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

GilaMonster (2/18/2010)
Paul White (2/18/2010)
Oh dear, I posted a reply to that identity-order thread before checking here :ermm:
If I'm reading the q correctly, he wants the data in the identity order, but is getting the descriptions sorted alphabetically. Probably cause SQL's doing a SORT/DISTINCT SORT to eliminate duplicate descriptions.

The OP does, yes. Lutz and I already posted code to do that reliably. I think the recent debate concerned GT's comment.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Paul White (2/18/2010)
Chris Morris-439714 (2/18/2010)
Paul White (2/18/2010)
Oh dear, I posted a reply to that identity-order thread before checking here :ermm:
It's a great answer and needed to be said on the thread.
:blush: Not so sure about the 'great', but thanks anyway!
I suppose I just wanted to show the sequence of events...to avoid any misunderstandings 😉 :w00t:

It's one of the commonest misunderstandings. Maybe SSC could do with some canned responses for simple stuff like this.

Also, with particular reference to the recent "suspect db" thread, it would make sense for a moderator (are there moderators other than Steve?) to post something like "This thread is now under the auspices (/ choose similar word) of ...", perhaps with a brief mention of credentials - the key point being to reduce the background noise for the hapless OP.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Gianluca Sartori (2/18/2010)
GilaMonster (2/18/2010)
Gianluca Sartori (2/18/2010)
In that particular case, with the identity column as primary key clustered, yes, doesn't it?
Logical order of the index - yes
Physical order of the data - no
Order that the rows will be returned in - no.
Ok, it's not guaranteed and I would not rely on it, but honestly I have to say I've never seen it behaving differently.
I've been spending some time trying to make it return rows in a different order, but I did not succeed. Does anybody have a script to do that? It would be interesting to see.

You need a bigger table to reproduce a different order reliably/convincingly. The easiest way is to get the query to execute a parallel plan - come to think of it, you could maybe fudge that behaviour for a very small table by playing with UPDATE STATISTICS in a fairly daft sort of way. There are other ways (like the merry-go-round thing on Enterprise) but that's hard to repro.

I'll have a go at that statistics hack if you're interested - and if The Thread can tolerate some T-SQL 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Gianluca Sartori (2/18/2010)
I've been spending some time trying to make it return rows in a different order, but I did not succeed. Does anybody have a script to do that? It would be interesting to see.

Run on a multi-core machine and make sure that the tables are big enough that SQL parallels the query.

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 13

GilaMonster (2/18/2010)
Gianluca Sartori (2/18/2010)
I've been spending some time trying to make it return rows in a different order, but I did not succeed. Does anybody have a script to do that? It would be interesting to see.
Run on a multi-core machine and make sure that the tables are big enough that SQL parallels the query.

Other option is to fragment the clustered index and then switch to read-uncommitted or force a table lock (can't recall if it has to be exclusive or if shared is enough)

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

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

GilaMonster (2/18/2010)
Other option is to fragment the clustered index and then switch to read-uncommitted or force a table lock (can't recall if it has to be exclusive or if shared is enough)

A shared table lock is sufficient - the Storage Engine may then choose an allocation-order scan since it knows no-one else can modify the table, assuming the Query Processor specified ordered:false, which should be safe to assume, given the circumstances.

This method also needs a table large enough to invoke an allocation-order scan- I think the threshold is 64 pages, but it might be 64 extents - I always find that figure tricky to remember.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Gianluca Sartori (2/18/2010)
Ok, it's not guaranteed and I would not rely on it, but honestly I have to say I've never seen it behaving differently. I've been spending some time trying to make it return rows in a different order, but I did not succeed. Does anybody have a script to do that? It would be interesting to see.

The query in the thread was something like SELECT DISTINCT Serial FROM dbo.SomeTableNameOrOther.

In that case it's very easy to get the rows out of order: just add an OPTION (HASH GROUP)

It's much harder with a simple query like SELECT column_list FROM dbo.SomeTableNameOrOther, for a couple of reasons:

1. The 'trivial plan' optimization kicks in, and I think I'm right in saying that Trivial Plans never generate a parallel plan.

2. It's hard to see how a parallel plan could cost less than the trivial plan anyway

I can get parallel plans on small tables by using the ROWCOUNT and/or PAGECOUNT arguments to UPDATE STATISTICS (or by forcing a plan with USE PLAN) but these all require non-trivial SELECTs, for example including a DISTINCT.

If anyone has any other ideas how to return rows in other than clustered index order on very small tables, I'm all ears. My best attempt so far is to return them in reverse order from a covering nonclustered index, but that doesn't really seem very fair.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi