SELECT TOP 1 1

Hello!

I want to check if a given phone number exists in the table. In SQL Server 2000 (or MSDE) which one of these two queries should we use (which is better/faster) or is it the same thing?
#1 SELECT TOP 1 1 FROM Table WHERE Number='XXX'
#2 SELECT TOP 1 Number FROM Table WHERE Number='XXX'

Thank you in advance,
Pedro

Assuming you're running an Index Scan , it should be the same

Yes, dineshasanka, I just want to check if the number exists in the table.

No, Jack, I'm not using any indexes.

I've been told that if you use query #1 in VB to load, e.g., a RdoResultSet, SQL Server creates a temporary table and then a cursor. If you use query #2, it is faster because no temporary table is created. Is this true/does it make sense?

Thank you once more!

As Frank mentioned:


if exists(SELECT * FROM Table WHERE Number='XXX') return 1
else return 0

quote:Originally posted by mmarovic

As Frank mentioned:


if exists(SELECT * FROM Table WHERE Number='XXX') return 1
else return 0

I'd recommend using a constant instead of *

if exists(SELECT 1 FROM Table WHERE Number='XXX') return 1
else return 0

Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

There is no difference in execution plans between implementations mentioned.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> The difference happens while parsing a query. The parser always expands a * into the actual column list. So it needs to retrieve this column list from somewhere. <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />And they do call it the "Star Expansion"[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Roji. P. Thomas</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> The difference happens while parsing a query. The parser always expands a * into the actual column list. So it needs to retrieve this column list from somewhere. <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />And they do call it the "Star Expansion"[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />... soon to be renamed to "Disa-star expansion" ?<br /><br />Sorry - couldn't help it [}<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

There is no computation as EXISTS merely checks for existence of the record. So it doesnt matter if you use "SELECT * FROM .." or "SELECT 1 FROM .."

From BOL:

When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

***********************
Dinakar Nethi
SQL Server MVP
***********************

quote:Originally posted by FrankKalis
The difference happens while parsing a query. The parser always expands a * into the actual column list. So it needs to retrieve this column list from somewhere.

In mssql server 2000 and probably 7 parser doesn't check for object existence.

BOL - Create procedure topic:

quote:
Referencing Objects
SQL Server allows the creation of stored procedures that reference objects that do not yet exist. At creation time, only syntax checking is done. The stored procedure is compiled to generate an execution plan when executed, if a valid plan does not already exist in the cache. Only during compilation are all objects referenced in the stored procedure resolved. Thus, a syntactically correct stored procedure that references objects which do not exist can be created successfully, but will fail at run time because referenced objects do not exist. For more information, see Deferred Name Resolution and Compilation.

Deferred Name Resolution and Compatibility Level
SQL Server allows Transact-SQL stored procedures to refer to tables that do not exist at creation time. This ability is called deferred name resolution. If, however, the Transact-SQL stored procedure refers to a table defined within the stored procedure, a warning is issued at creation time if the compatibility level setting (set by executing sp_dbcmptlevel) is 65. An error message is returned at run time if the table referenced does not exist. For more information, see sp_dbcmptlevel and Deferred Name Resolution and Compilation.

Deferred Name Resolution and Compilation
When a stored procedure is created, the statements in the procedure are parsed for syntactical accuracy. If a syntactical error is encountered in the procedure definition, an error is returned and the stored procedure is not created. If the statements are syntactically correct, the text of the stored procedure is stored in the syscomments system table.

When a stored procedure is executed for the first time, the query processor reads the text of the stored procedure from the syscomments system table of the procedure and checks that the names of the objects used by the procedure are present. This process is called deferred name resolution because objects referenced by the stored procedure need not exist when the stored procedure is created, but only when it is executed.

In the resolution stage, Microsoft® SQL Server™ 2000 also performs other validation activities (for example, checking the compatibility of a column data type with variables). If the objects referenced by the stored procedure are missing when the stored procedure is executed, the stored procedure stops executing when it gets to the statement that references the missing object. In this case, or if other errors are found in the resolution stage, an error is returned.

So parsing doesn't expand star to column names because it doesn't even check existence of database object at that time. That's what BOL says. Now, follows speculation, I haven't found proof for my theory yet:

Star (*) might be expanded during resolution stage. However, for parsing finite automates are usually used. FA are not aware of context, so if * is expanded to column names at that time they wouldn't be aware of difference between regular 'select *' and 'select *' inside 'exists' operator. On the other hand I expect that resolution phase uses context aware algorithm, so it can distinguish and handle properly two cases mentioned. Resolution is kind of compilation. If I remember well (20 years past since I studied computer science mathematic - Formal languages theory) and the same 'grammer' is applied, for translation is used 'push automate' or something with similar name. PAs important characteristic is that they are context aware.

I believe star expansion myth comes from sql server 6.5 version, where stored procedures where precompiled, so it might be true that parser was responsible for expanding * token then.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />I believe star expansion myth comes from sql server 6.5 version, where stored procedures where precompiled, so it might be true that parser was responsible for expanding * token then.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />No, I'm afraid, that's not a myth. Well, at least not, if you tend to believe that the dev lead of the query optimization team, Conor Cunningham, knows what he is talking about. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />When the parser faces a * it doesn't have enough context to know that 1, * or whatever doesn't make any difference. And because of that it expands the * into the actual column list. This is still true in current versions of SQL Server. The thread confirming that was in the private MVP newsgroups in December 2005. I'm afraid, I can't just copy and paste the answer as I guessing it is under MVP NDA.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well, in that case either BOL is wrong or Conor Cunningham. Once again BOL says database objects are parsed before storing and checked the syntax. Object references are resolved during compilation, which means before stored procedure without exec plan in cache is executed.<br />The contradiction may come from loosely use of the term parsing. Parsing before procedure is stored, according to BOL, doesn't check referenced objects. It might be that microsoft applied another 'parsing' at the moment execution plan is built. <br /><br />The sentence:<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />When the parser faces a * it doesn't have enough context to know that 1, * or whatever doesn't make any difference. And because of that it expands the * into the actual column list. <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />doesn't make a sense to me. Proper implementation (IMO) would be:<br /><br />1. Parsing phase: '*' token is recognized as 'star' token. Period. It is obviously true for parsing that preceds storing the procedure.<br />2. Compilation (translation) phase: Context is known at this moment so 'star' token is not expanded in case it is used in context of exists operator.<br /><br />If it there is another different parsing before compilation that includes star resolution, I don't think it is very smart implementation.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />No, I'm afraid, that's not a myth. Well, at least not, if you tend to believe that the dev lead of the query optimization team, Conor Cunningham, knows what he is talking about. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Infact I had raised this question in the private NG, following a discussion in a public news group, and it was confirmed by Conor that Star Expansion and Shrinking occurs when you use SELECT * in EXISTS. <br /><br />I am not sure whether it actually happens at the parsing stage.<br /><br />Somehow I had lost the thread. Frank, Do you have the link to the public NG posting discussed there?<br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />This is the link you've posted in your question:<a target="_blank" href=http://tinyurl.com/ake27>http://tinyurl.com/ake27</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well. Thanks Frank. So here is the statement by Conor for brevity.<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />It is functionally equivalent from the end plan perspective. <br /><br />For compilation time, it may be ever-so-slightly faster to use "SELECT 1 <br />FROM T" because it does not need to expand the * into a column list and then <br />prune it away. Obviously it would matter more on tables with more columns. <br /><br />Select '1' is also fine. I use SELECT 1 since it's the smallest thing I can <br />do in an exists subquery - fewer allocations <img src='/community/emoticons/emotion-5.gif' alt='' />. <br /><br />Hope that helps. <br /><br />Conor Cunningham <br />SQL Server Query Optimization Development Lead <br />Microsoft <br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br /><br />Roji. P. Thomas<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

Obviously, there must be some parsing going on, because if you do

WHERE EXISTS (SELECT invalid_column_name FROM valid_table_name)

... then you do get an error on the invalid column name.

Other than that, you can use the name of a nullable column, and EXISTS doesn't care whether there's a null or a fixed value on that column - it checks for the row, not for the column.

quote:Originally posted by Adriaan

Obviously, there must be some parsing going on, because if you do

WHERE EXISTS (SELECT invalid_column_name FROM valid_table_name)

... then you do get an error on the invalid column name.

Do you know that parser doesnt care about Invalid column names or table names?

Try the following.

SET PARSEONLY ON

SELECT * FROM Customers C WHERE EXISTS(SELECT XYZ FROM Orders O WHERE O.CustomerID = C.CustomerID)

SELECT * FROM Customers C WHERE EXISTS(SELECT CompanyName FROM Orders O WHERE O.CustomerID = C.CustomerID)

SET PARSEONLY OFF

Both the queries will be parsed successfully parsed because of the feature called Deferred Name Resolution.

If you execute the above queries the first one will fail with an

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'XYZ'.

error. But the second query will succeed even when there was no column named CompanyName in Orders. This is called fancy scoping.




Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

Why don't they add a requirement to the SQL standard that as soon as there is more than one (instance of a) table in the query, then you must predicate columns with the table name or an alias?

There should not be such a thing as fancy scoping, so that people are forced to resolve ambiguities themselves.