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


Warning... Order by FILTERS results


Warning... Order by FILTERS results

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64999 Visits: 9671
Check this out :

I was tweaking a working reorganize script for my new client and I wanted to randomize the reorganize sequence because the data distribution is really uneven in the DB. The real intent was to be able to run the code in high production hours if need be. And since I can't use the online option with 2005 Standard, I had to avoid reorganizing 8 big indexes in a row on the same large table... hence locking it for 5 minutes.

I test the concatenation script and everything is fine, I then add the ORDER BY NEWID() and call it a night after I schedule the job to run at 10 PM.

The next day I see that the job ran in less than 3 minutes... which is completely impossible with 95% fragmentation on the DB across all indexes (autoshrink was ON when I got here). I had to run this overnight because I had 3 complaints from users when I tried to run the code during high prod hours.

After a couple minutes of digging I find this out :



GO
DECLARE @X NVARCHAR(MAX)
SET @X = ''
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY name
--PRINT @X
EXEC (@X)
GO --95 rows affected


--This is what I used when I ran in the job :

DECLARE @X NVARCHAR(MAX)
SET @X = ''
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID()
--PRINT @X
EXEC (@X)
GO --1 rows affected

--This is what had happened in my job... only 1 index had been reorganized... hence the ridiculously fast reorganize.

DECLARE @X NVARCHAR(MAX)
SET @X = ''
SELECT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY (SELECT NEWID())
--PRINT @X
EXEC (@X)
GO --95 rows affected

--After moving the NEWID() is its own subquery, the query works again as it should... ugly hack if you ask me, but at least it works!





Did I just stumble on a bug, or did I misunderstand the use of orderby and newid() all those years?

Where can I report this to MS if this is a bug?

Was this fixed in a hotfix or post SP 2 that is not installed on the server (can't seem to find that bug anywhere in the build list)?

Server info : Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64999 Visits: 9671
I went ahead and posted this as a bug on the connect website.

Here's the url if you have anything else to add to this for Microsoft to use to fix the issue :

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=383641
edwardelliott
edwardelliott
Mr or Mrs. 500
Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)

Group: General Forum Members
Points: 511 Visits: 351
I get the same issue here seems very odd - 9.0.4028.

Anyway it looks like you can get all the tables if you add a top X:

SELECT TOP 99.9 PERCENT @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID()


If you say 99.9 percent then it give you all the records, if you say 100 percent it seems to ignore it and just give you the 1 record!


ed
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64999 Visits: 9671
Thanks edward.

That opens up another valid work around using TOP 999999999999 (make sure this number is way over the actual # of records possibly returned).


SELECT TOP (10000000) @X = @X + 'PRINT ''[' + name + ']'';' + CHAR(13) + CHAR(10) FROM msdb.sys.tables ORDER BY NEWID()


I'll post those as possible workarounds... that'll surely guide the MS team on the right tracks.


Keep 'em comming.
sgmunson
sgmunson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16634 Visits: 4634
The SQL Server is just tried your query on is as follows:

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Here's another way around, as using a CTE instead of a temp table failed to produce the desired results in the same way the original query does, which is really interesting when you think about it.


DECLARE @X NVARCHAR(MAX)
SET @X = ''

SELECT [name], NEWID() AS NEW_ID
INTO #TEST
FROM msdb.sys.tables

SELECT @X = @X + 'PRINT ''[' + [name] + ']'';' + CHAR(13) + CHAR(10)
FROM #TEST
ORDER BY NEW_ID
--PRINT @X
EXEC (@X)
GO

DROP TABLE #TEST



Steve
(aka smunson)
SmileSmileSmile

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64999 Visits: 9671
That's so cool, so many ways to screw up with the engine Wink.
sgmunson
sgmunson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16634 Visits: 4634
I just tested a variation, where I moved the ORDER BY clause up into the SELECT INTO query, and it continues to produce a correct result, but apparently, only because SQL Server is forced to instantiate the data via the temp table. Otherwise, it appears the optimizer shortcuts the process. Take a look at the execution plans, as I don't have the ability to do that in my current environment.

Steve
(aka smunson)
SmileSmileSmile

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64999 Visits: 9671
The working plans have an extra compute scalar operation... which seems to be where the data is shot in the variable.

In the failing plan, there's a difference in the compute scalar. Instead of fetching the table names, it fetches 2 columns (probably where it sets the data in the variable)... then it forgets to concatenate anything!
aureolin
aureolin
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1047 Visits: 1056
One more interesting detail... I tried this on SQL 2008, and it works as you described, but with a twist. Each time the procedure runs, it produces a single row result, but the row produced is different each time. While the result appears to be random, after running this many times, it seems that the row selected is actually one of a small set of possible results (though that may be that I didn't run it enough to get the full effect of randomness).

Too weird.
Wink



sgmunson
sgmunson
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16634 Visits: 4634
I'm not sure the OP noticed, but I figured it didn't matter much, as that's the exact same kind of result I was getting. Some one of those records got chosen, and sometimes it was the same one a couple times in a row, but it was often different. I was using SQL 2005.

Steve
(aka smunson)
SmileSmileSmile

aureolin (11/25/2008)
One more interesting detail... I tried this on SQL 2008, and it works as you described, but with a twist. Each time the procedure runs, it produces a single row result, but the row produced is different each time. While the result appears to be random, after running this many times, it seems that the row selected is actually one of a small set of possible results (though that may be that I didn't run it enough to get the full effect of randomness).

Too weird.
Wink


Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
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