Why is this stored procedure using TOP 100 PERCENT?

Question

I have the following stored procedure inside a third party application inside sql server 2008 R2:-

ALTER PROCEDURE [dbo].[GetContacts] 
AS
BEGIN

---------
SELECT     TOP (100) PERCENT  .....
INTO            [#temp200]
FROM         dbo.Contact 

ORDER BY dbo.Contact.Name

--SELECT * from #temp200

SELECT  top 38   *
FROM         #temp200
ORDER BY Fullname

delete top (38) FROM         #temp200

SELECT  top 38   *
FROM         #temp200
ORDER BY Fullname

delete top (38) FROM         #temp200 

SELECT  top 38   *
FROM         #temp200
ORDER BY Fullname

delete top (38) FROM         #temp200 

SELECT   *
FROM         #temp200
ORDER BY Fullname

now I run this inside sql management studio where I got the following results tabs:-

the first one contains 38 records.
the second one 38 records.
the third one contains 38 records.
the fourth one contains 30 records.

where in this case I got 144 records ,, so not sure what is the purpose of the (SELECT TOP (100)) , as I will get 144 records. now as a test I changed the Select TOP(100) to be Select TOP(35) where in this case I got 2 results; the first on with 38 records while the second one with 17 records and .. so can anyone advice how my above SP is working ?

Are you sure that you don't have `SELECT TOP (100) PERCENT ..... INTO [#temp200] FROM ...`? If yes you create `#temp200` table with all records from `contact`(144?) — Lukasz Szozda, Dec 10 '15 at 15:30
I have never seen anything like that. Is that a poor attempt at paging 38 records per page with a limit of 4 pages? I also don't understand why there is a top (100) if its going to be a top 100 percent. How is possible you would get more records than what you inserted into the table. I suspect we are missing parts of this query. — ewahner, Dec 10 '15 at 15:54
@lad2025 yes you are correct there is a TOP (100) PERCENT .. I modify my question accordingly .. — , Dec 10 '15 at 16:28
Possible duplicate of [Why use Select Top 100 Percent?](http://stackoverflow.com/questions/1622878/why-use-select-top-100-percent) — AHiggins, Dec 10 '15 at 20:45

score 2 · Accepted Answer · answered Dec 10 '15 at 20:55

That guy did not understand that tables do not have order. He tried to insert in an ordered way into the temp tables. This is not possible. The TOP 100 PERCENT trick shuts up the warning about that but does nothing to ensure order.

In earlier SQL Server versions this code might well have worked by coincidence. Since then more optimizations have been added and this code is extremely brittle. Rewrite this if you get the chance. It's a latent time bomb.

Why is this stored procedure using TOP 100 PERCENT?

1 Answers1