CODEWITHSUNDEEP
sql-serversql-server-2008
Amit Rai Sharma
Amit Rai Sharma

Reputation: 4225

Different output when executing statement directly and from stored procedure?

Sql Server 2008 is behaving in a strange way. When I execute the stored procedure the out put is in a different order than when I execute the statements directly for the same parameters. I am not sure what I am doing wrong. Please help!!!

Here is a simple query structure and explain what it does.

Top 10 Query1
Union all
Top 10 Query2
Order by name

a. When u run it in a proc : From Query 1 it fetches top 10 , then from query 2 it fetches top 10 and then finally it does the order

b. When you open the query : From Query 1 it applies the order and then fetches top 10, and from Query 2 it also applies the order and then fetches top 10

This is strange that it's doing 2 different things with the same query.

Output from Procedure
Name                                          Cost Price
A2 Bag Stickerss DO NOT STOCKTAKES               24
aaaaaa                                           5
aaaaaa                                           7.5


Output from Query
Name                                          Cost Price
A2 Bag Stickerss DO NOT STOCKTAKES               24
A2 Bag Stickerss DO NOT STOCKTAKES               27
aaaaaa                                           5
aaaaaa                                           7.5
aaaaaa                                           9

Upvotes: 1

Views: 2437

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

TOP without ORDER BY is not deterministic.

It just means "Select any 10 records". So you are selecting an arbitrary set of 10 results from query 1 and an arbitrary set of 10 records from query 2 then ordering these 20 records by name.

Which TOP 10 you end up with depends on the plan chosen (which may well be different in the stored procedure) You would need to add an order by (on a set of columns with no ties) to each query to make it deterministic.

Your current query is like

SELECT TOP 10 * 
FROM master..spt_values
UNION ALL
SELECT TOP 10 * 
FROM master..spt_values
ORDER BY name

Plan 1

You see that SQL Server just adds a TOP iterator to both branches of the plan to limit the output of both queries then these feed into the Union and the sort by name happens after that. SQL Server chose a clustered index scan for this so the results will likely be the TOP 10 in clustered index order type,number,name (though this shouldn't be relied upon either, without a specified order by to indicate what the TOP refers to any set of 10 rows would be valid. It would be perfectly valid for it to use the advanced scanning feature here and give you an arbitrary 10 rows that it knows to be in cache as they have just been read by an other query's scan.)

To rewrite the query with TOP...ORDER BY specified for each element you could use CTEs as below.

;WITH Query1 AS
(
SELECT TOP 10 * 
FROM master..spt_values
ORDER BY name,number,type
), Query2 AS
(
SELECT TOP 10 * 
FROM master..spt_values
ORDER BY number,type,name
)

SELECT * 
FROM Query1
UNION ALL
SELECT * 
FROM Query2
ORDER BY name

Plan 2

Upvotes: 4

Related Questions