Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practice


Best Practice

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
davidr (9/4/2008)
side note: we shouldn't really be doing "select *" to check for existence. If that's a LOONNNNGGGGG row there could be a lot of overhead to no good purpose. It may be more self-documenting to code "select 'exists' from ....."


Hi David,

I a stand-alone SELECT statement, I would agree. But in the context of a [NOT] EXISTS subquey, it really doesn't matter a bit what you put behind the SELECT - whatever you write there, SQL Server will interpret it as checking for existance of a row, and perform the check in the most efficient way.

Personally, I prefer SELECT * as it indicates checking for a row, not checking for a specific value or so. But that's just personal preference. From a performance viewpoint, there is absolutely no difference.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
Christian Buettner (9/4/2008)
I have adjusted my post above to remove the wrong information.


.... that I seem to have overlooked thus far. And now I find myself wondering what it is that you have written.

Sad, isn't it? BigGrin


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
Thanks guys for the feedback.

davidr (9/4/2008)
side note: we shouldn't really be doing "select *" to check for existence. If that's a LOONNNNGGGGG row there could be a lot of overhead to no good purpose. It may be more self-documenting to code "select 'exists' from ....."


Long time ago there was a big discussion on T-SQL forum about using SELECT * in EXIST checks.
Despite I use always SELECT 1 (just habit) it was proven with many tests that beginning from version 2000 there is no difference what to put into SELECT.
When it's for existence check SQL Server does not actually do the SELECT itself. Only FROM and WHERE.
Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 2049
Hugo Kornelis (9/4/2008)

Personally, I prefer SELECT * as it indicates checking for a row, not checking for a specific value or so. But that's just personal preference. From a performance viewpoint, there is absolutely no difference.

I prefer to use SELECT NULL vs. SELECT * or SELECT 'x' or SELECT 1 for my existance tests. Selecting a NULL is a bit more indicative that nothing is being selected.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
dunnjoe
dunnjoe
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1938 Visits: 208
Hi Chris and Hugo,

Thanks for your responses!

Joe
Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 2049
AJ (9/4/2008)
Good question, but I think it is being confused, including by me, with an UPSERT (as the previous posts' referral to MERGE indicates too).

This question needs more information to answer correctly: How is the action dealt with if the INSERT is not performed? An UPDATE instead? Maybe we actually want a unique constraint on the Name column to report an error if a name is attempted to be inserted twice? If we do need to update instead if the record already exists, how do we control concurrency in that case?

In high concurrency designs these and many more questions must be answered first and I completely agree a more deterministic approach would be required.

A big misconception I keep coming across is that there must be a perfect singleton solution for all situations.... but why not perfect the solution for each singleton situation? A welcome side-effect of this is that it keeps us all in jobs!


I agree. I think that the question should have stated "what is the most efficient way..."

Depending upon what is to happen if the value already exists, would also possibly influence the choice of method to be used. In this simple example with only one column, I note the following (performance and locking issues aside):
1. The first option (IF statement) has the capability of returning information back to the UI to inform the user that the value already exists.
2. Option 2 would have to check the ROWCOUNT from the insert to see if the row was inserted and then pass the appropriate information back. Otherwise, the application would [wrongly] assume that the row was added. So we're still coding an IF statement.

And I wouldn't, in this simple case, use the new SQL Server 2008 MERGE statement to update a value to itself.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Tom Garth
Tom Garth
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: 1013 Visits: 1499
A GREAT QoD! I got it wrong, then read how obvious the answer was. It's amazing the things you don't take time to think about. This will change my practice.

I've done SELECT 1 for years out of habit. I never saw SELECT NULL used. That's pretty cool. I might switch.

Thanks for the tidbit Sergiy.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Mauve
Mauve
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1246 Visits: 2049
Tom Garth (9/4/2008)
... I never saw SELECT NULL used. That's pretty cool. I might switch.

I discovered the SELECT NULL syntax by observing the internal SQL statements issued by Oracle when it is checking FK constraints. So if it was good syntax for the engine, then it must be good for us programmers.


Side note: Both DB2 and Oracle, both architected from the same original System R specification, use SQL to run the engine. This was a good thing, in that it forced optimization as well as enhancements to the SQL language.

One of the first things that DB2 encountered was heavy lock contention and deadlocking on its own internal dictionary tables as it was taking page-level locks. The next release (circa 1984) had row-level locking.


(PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.
Ric Sierra
Ric Sierra
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1441 Visits: 342
I'm not completly agree with the answer, because depends of the context:
If you are looking for the best execution plan the answer is #1
If you are looking for less deadlock the answer is #2
But if the target is INSERT a new record, both solutions works.
Alan. T.
Alan. T.
Say Hey Kid
Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)Say Hey Kid (689 reputation)

Group: General Forum Members
Points: 689 Visits: 646
Great question! I have seen many co-workers create queries like this that work really well when all of the data can be copied onto memory on their test but when it get to the big production server it brings the system to a halt locking huge numbers of records and if let run long enough causing a huge rollback.
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