Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why use Select Top 100 Percent?

I understand that prior to SQL Server 2005, you could "trick" SQL Server to allow use of an order by in a view definition, by also include TOP 100 PERCENT in the SELECT clause. But I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT ... within dynamic SQL statements (used in ADO in ASP.NET apps, etc). Is there any reason for this? Isn't the result the same as not including the TOP 100 PERCENT?

like image 600
Ed Schembor Avatar asked Oct 26 '09 02:10

Ed Schembor


People also ask

What does select top 100 percent do?

TOP (100) PERCENT is completely meaningless in recent versions of SQL Server, and it (along with the corresponding ORDER BY, in the case of a view definition or derived table) is ignored by the query processor. You're correct that once upon a time, it could be used as a trick, but even then it wasn't reliable.

What does Top 100 mean in SQL?

So if we see TOP (100) PERCENT in code, we suspect that the developer has attempted to create an ordered view and we check if that's important before going any further. Chances are that the query that uses that view (or the client application) need to be modified instead.

What does select top mean?

The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

How do you use top percentage in SQL?

Example - Using TOP PERCENT keyword For example: SELECT TOP(10) PERCENT employee_id, last_name, first_name FROM employees WHERE last_name = 'Anderson' ORDER BY employee_id; This SQL Server SELECT TOP example would select the first 10% of the records from the full result set.


Video Answer


1 Answers

It was used for "intermediate materialization (Google search)"

Good article: Adam Machanic: Exploring the secrets of intermediate materialization

He even raised an MS Connect so it can be done in a cleaner fashion

My view is "not inherently bad", but don't use it unless 100% sure. The problem is, it works only at the time you do it and probably not later (patch level, schema, index, row counts etc)...

Worked example

This may fail because you don't know in which order things are evaluated

SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1 AND CAST(foo AS int) > 100 

And this may also fail because

SELECT foo FROM     (SELECT foo From MyTable WHERE ISNUMERIC (foo) = 1) bar WHERE     CAST(foo AS int) > 100 

However, this did not in SQL Server 2000. The inner query is evaluated and spooled:

SELECT foo FROM     (SELECT TOP 100 PERCENT foo From MyTable WHERE ISNUMERIC (foo) = 1 ORDER BY foo) bar WHERE     CAST(foo AS int) > 100 

Note, this still works in SQL Server 2005

SELECT TOP 2000000000 ... ORDER BY... 
like image 147
gbn Avatar answered Sep 19 '22 15:09

gbn