August 13, 2009 at 1:00 pm
Justa Developer (8/13/2009)
Grant Fritchey (8/13/2009)
Introductory error handling article[/url]. It includes 2000 and 2005/2008 syntax.Oh, that is lovely, thanks Grant! I'm learning tons and tons!
Pleasure!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2009 at 3:59 pm
Jeff Moden (8/13/2009)
jswong05 (8/13/2009)
True portability is a myth.
True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.
I wouldn't want them to... competition spurs innovation.
Actually - I do want them to. Standards improve interoperability, which spurs competition because it removes vendor lock-in.
August 13, 2009 at 6:00 pm
ta.bu.shi.da.yu (8/13/2009)
Jeff Moden (8/13/2009)
jswong05 (8/13/2009)
True portability is a myth.
True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.
I wouldn't want them to... competition spurs innovation.
Actually - I do want them to. Standards improve interoperability, which spurs competition because it removes vendor lock-in.
Ummm.... no... I've gotta disagree. Another name for "vendor lock-in" is "market share" and that really drives the competition. And how can you spur competition if everyone is following the same droll standards?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2009 at 7:03 pm
Jeff Moden (8/13/2009)
ta.bu.shi.da.yu (8/13/2009)
Jeff Moden (8/13/2009)
jswong05 (8/13/2009)
True portability is a myth.
True, basic compatibility is expected. Nothing is 100% compatible to another thing.:-P Why don't database vendors got together and make one ANSI standard, they don't want to!!!!! They want to cut throat of each other to get ahead in the market.
I wouldn't want them to... competition spurs innovation.
Actually - I do want them to. Standards improve interoperability, which spurs competition because it removes vendor lock-in.
Ummm.... no... I've gotta disagree. Another name for "vendor lock-in" is "market share" and that really drives the competition. And how can you spur competition if everyone is following the same droll standards?
Well, I guess we'll agree to disagree. I could use the same argument for open source software - i.e. Open Source means market share. The only reason that vendor lock-in equals market share is because it makes it hard to switch to a competing product - in other words it *reduces* competition.
I believe that standards help. Microsoft switched from NTLM to Kerberos, implemented Active Directory on the LDAP standard and ditched WINS for DNS. Funnily enough, all of a sudden their market share rose dramatically...
Those droll standards allow people to compete AND gain market share.
Incidentally, I'm not saying that SQL extensions are necessarily a bad thing. But standardizing extensions sure helps reduce vendor lockin. In my company, we have to support Oracle and SQL Server installations in equal measure. That keeps our market share high. But I can tell you, it's a royal pain.
August 13, 2009 at 8:42 pm
ta.bu.shi.da.yu (8/13/2009)
Well, I guess we'll agree to disagree.
Heh... I can do that.
But I can tell you, it's a royal pain.
Now there's something we can both agree on. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2009 at 8:59 pm
rja.carnegie (8/10/2009)
Last I heard, SELECT @variable1 = expression, @variable2 = expression, ... ; is deprecated.
For me, that would really be a bad thing. Do you happen to have an MS reference? I might just be missing it, but I don't see it on the 2k5 or the 2k8 deprecation list...
http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms143729.aspx
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2009 at 1:12 am
Jeff Moden (8/13/2009)
rja.carnegie (8/10/2009)
Last I heard, SELECT @variable1 = expression, @variable2 = expression, ... ; is deprecated.For me, that would really be a bad thing. Do you happen to have an MS reference? I might just be missing it, but I don't see it on the 2k5 or the 2k8 deprecation list...
http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx
I may be mis-remembering it, but I think we established (earlier in this thread) that it is not deprecated; there is simply a cryptic note in Books Online against the SELECT @local_variable entry:
For assigning variables, we recommend that you use SET @local_variable instead of SELECT @local_variable.
I have little insight into the reasons for that statement - though maybe it has something to do with the fact that some operations (like calling XML methods) can only be achieved with SET. Personally, I will continue to prefer SELECT for multiple assignments.
While I'm here, I just want to add my '+1' to supporting 'non-standard' extensions. SQL code portability is indeed a myth, and a costly one at that.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 14, 2009 at 2:00 am
So my question is: Does anyone have strong recommendations (other than readability:-D) regarding LEFT JOIN versus either of the other two? Are there any circumstances in SQL Server where it might outperform them? Does it really take a 3% hit for the filter? (OK, 3 questions)
NOT IN:
SELECT * FROM Sales.Customer WHERE CustomerID NOT IN (SELECT CustomerID from Sales.CustomerAddress)
NOT EXISTS:
SELECT * FROM Sales.Customer C WHERE NOT EXISTS (SELECT 1 FROM Sales.CustomerAddress CA where C.CustomerID = CA.CustomerID)
LEFT JOIN:
SELECT * FROM Sales.Customer C LEFT JOIN Sales.CustomerAddress CA ON C.CustomerID = CA.CustomerID WHERE CA.CustomerID IS NULL
I haven't tested this, but I believe that LEFT JOIN will join all rows and then perform the filter operation where the previous two will stop for the corresponding entry as soon as you have found one. In the example above if a customer has multiple addresses, it is better to go for NOT IN or NOT EXISTS
Cheers,
Prithiviraj Kulasingham
http://preethiviraj.blogspot.com/
August 16, 2009 at 12:32 pm
I need to correct myself here. Surprised no one picked up on this....
GilaMonster (8/10/2009)
Also, try to avoid IN. While checking the existence of some values, then use EXISTS instead of IN. Because IN counts the NULL values also, hence slower than EXISTS. Since EXISTS returns Boolean(Yes/No) but IN returns all values hence result set for IN is heavier than EXISTS.
SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)
SELECT * FROM employee WHERE NOT EXISTS (SELECT emp_no FROM emp_detail)<snip>
The equivalent query using exists is this:
SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM emp_detail where employee.emp_no = emp_detail.emp_no)
The two queries
SELECT * FROM employee WHERE emp_no NOT IN (SELECT emp_no from emp_detail)
and
SELECT * FROM employee WHERE NOT EXISTS (SELECT 1 FROM emp_detail where employee.emp_no = emp_detail.emp_no)
are not logically equivalent in all cases.
If the column returned by the subquery in the IN is declared as NOT NULL (emp_no in this case), then these two queries are equivalent and the execution plans are the same.
If the column returned by the subquery in the IN is declared as nullable (emp_no in this case) , but has no nulls, the queries will return the same results but the EXISTS will run much faster than the IN.
If the column returned by the subquery in the IN is declared as nullable (emp_no in this case), and has one or more null values, the two queries will return completely different results. Specifically, the query using IN will return no rows.
A simple example to show this is:
select * from sys.databases where name not in ('master','model')
select * from sys.databases where name not in ('master','model', null)
Note that this is only the case with NOT IN and NOT EXISTS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2009 at 4:06 pm
GilaMonster (8/16/2009)
I need to correct myself here. Surprised no one picked up on this....
I think I posted something about the query missing table aliases so it wasn't clear which column was being referred to (emp_no may not exist in whatever schema emp_detail resolves into for the executing user). I must admit I rather left it alone since it seemed to want to be a correlated subquery, but wasn't, was missing schema prefixes, and had no table definitions...so I gave it minimal attention.
GilaMonster (8/16/2009)
A simple example to show this is:
select * from sys.databases where name not in ('master','model')select * from sys.databases where name not in ('master','model', null)
Note that this is only the case with NOT IN and NOT EXISTS.
The behaviour of the second statement rather depends on the setting of ANSI_NULLS. If set to ON, we get a constant scan since it can never return rows because of the NULL comparison. If set to OFF (deprecated and not recommended in any case), we get a plan much like the first statement. It may be exactly the same - I haven't checked it.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 17, 2009 at 4:39 am
I dont agree with most of the points listed here.. :w00t:
August 17, 2009 at 6:20 am
jp (8/17/2009)
I dont agree with most of the points listed here.. :w00t:
Which, the original 20 or the comments that followed?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 17, 2009 at 6:57 am
GilaMonster (8/16/2009)
A simple example to show this is:
select * from sys.databases where name not in ('master','model')select * from sys.databases where name not in ('master','model', null)
Note that this is only the case with NOT IN and NOT EXISTS.
The behaviour of the second statement rather depends on the setting of ANSI_NULLS. If set to ON, we get a constant scan since it can never return rows because of the NULL comparison. If set to OFF (deprecated and not recommended in any case), we get a plan much like the first statement. It may be exactly the same - I haven't checked it.
I'm in trouble, I don't understand the non-deprecated behaviour 🙁
IN(...) doesn't seem to mind if a NULL is in there. But NOT (name IN (...) ) apparently also does the same as (name NOT IN (...) ). Can someone walk me through this please? :kiss:
August 17, 2009 at 7:18 am
rja.carnegie (8/17/2009)
IN(...) doesn't seem to mind if a NULL is in there. But NOT (name IN (...) ) apparently also does the same as (name NOT IN (...) ). Can someone walk me through this please? :kiss:
IN is like an OR...any one of the terms matching results in TRUE. But, *all* the IN(...) items have to be NULL to allow the QO to shortcut the expression to a constant scan - since that is the only way that the expression is guaranteed to return UNKNOWN.
NOT IN has to compare all of them (like AND) - this falls with the NULL - since it returns UNKNOWN. So any of the terms being NULL guarantees that the expression will return UNKNOWN (since all values must be evaluated).
Wrapping the IN with NOT() simply turns TRUE into FALSE or vice-versa. UNKNOWN stays as UNKNOWN. The situation is resolved by the IN before NOT has to look at it.
Does that help?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 17, 2009 at 7:42 am
Paul White (8/16/2009)
The behaviour of the second statement rather depends on the setting of ANSI_NULLS.
Considering I never switch it off, anything I post about nulls can be assumed Ansi_nulls on
If set to ON, we get a constant scan since it can never return rows because of the NULL comparison.
The exec plan's a lot more complex than that. The one I got in testing last night (with subquery, not a list) had 4 clustered index scans, one row spool, two nested loop joins and a hash join. (SQL 2008 SP1)
Edit: That was with no indexes present on the 'join' columns. I didn't get around to testing the case where there were indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 196 through 210 (of 244 total)
You must be logged in to reply to this topic. Login to reply