October 27, 2009 at 11:05 am
I was working along today and ran into something interesting with SQL Server that I didn't know before. It's not obscure or only limited to specific environments or rarely used features (everyone does a LEFT JOIN, right?), so I think it would have a broad base of interest and is an excellent candidate for a QOD. However, there are some technical pieces that could really burn me, specifically if this optimization is only valid for specific editions or versions, so I'm hesitant to submit the QOD since I haven't done complete research and would probably miss something anyway even if I tried.
So... What do YOU suggest? There really isn't a forum to submit a QOD for review prior to submitting it, and if there was, would that be giving away too much (I don't think so - it's easy enough to cheat on the QOD if that's what you want anyway)? Do I submit it and put on my thick alligator skin, or are there enough QODs already and I just keep it in my gee-whiz file?
October 27, 2009 at 11:21 am
I'd say submit it. Don't worry about the flaming. You have already piqued my curiosity.
October 27, 2009 at 12:25 pm
Chad Crawford
From one who has been battered from pillow to post on some of my QOD's. Do not take it to heart, expand the time you can to research the validity of your correct answer(s) or do not hesitate to post it using a specific SQL Server version ... just simply state "in SQL Server 2008" or whatever. Not to be too pessimistic even that might get you stomped on.
About a vetting of the QOD read this explanation by Steve Jones
http://www.sqlservercentral.com/articles/SQLServerCentral/62764/
be sure to read the entire article as Steve explains how it was done in the past and why it is no longer being performed except by Steve himself.
Do I have a gripe heck yes. Try posting a question that has only 2 answers such as "True" or "False". If you can do that explain it to me. I have e-mailed the Webmaster for SCC on Oct 26th asking why that capability has disappeared, as yet no answer.
Added on 10/28:
Finally figured it out. Lord does it feel bad to admit that it was my own fault .... oh well we all screw up at one time or another
Now if you want some one who is NOT an expert to attempt to vet your question, or admit "I don't know" send it to me via PM and I will attempt to render what assistance I can.
October 27, 2009 at 1:09 pm
I was working along today and ran into something interesting with SQL Server that I didn't know before. It's not obscure or only limited to specific environments or rarely used features (everyone does a LEFT JOIN, right?),
OUTER JOIN default to a mathematical null so some optimizations on the physical generally does not improve performance so if you find something that cannot be repeated by random user in any box then your question is not valid. So before you post any questions about OUTER JOIN I need to know what Microsoft have done on the physical that changed its default define behavoir because things like index column include and index covering are some tricks on the physical. But these are vendor dependent features so I can say are not really valid per ANSI SQL standard.
Kind regards,
Gift Peddie
October 27, 2009 at 1:13 pm
Gift Peddie (10/27/2009)
I was working along today and ran into something interesting with SQL Server that I didn't know before. It's not obscure or only limited to specific environments or rarely used features (everyone does a LEFT JOIN, right?),
OUTER JOIN default to a mathematical null so some optimizations on the physical generally does not improve performance so if you find something that cannot be repeated by random user in any box then your question is not valid. So before you post any questions about OUTER JOIN I need to know what Microsoft have done on the physical that changed its default define behavoir because things like index column include and index covering are some tricks on the physical. But these are vendor dependent features so I can say are not really valid per ANSI SQL standard.
But we aren't talking about ANSI Standard. We are talking about MS SQL Server and most of us will use the features and capabilities of MS SQL Server even if they aren't ANSI Standard. As I hear from Jeff Moden all the time, "Portable code is a myth" (Paraphrased, I'm sure).
October 27, 2009 at 1:22 pm
But we aren't talking about ANSI Standard. We are talking about MS SQL Server and most of us will use the features and capabilities of MS SQL Server even if they aren't ANSI Standard. As I hear from Jeff Moden all the time, "Portable code is a myth" (Paraphrased, I'm sure).
That is correct but Microsoft is not clear about their JOIN implementation which is one of the reasons people who are new to SQL Server don't understand what is in the BOL(books one line). Jeff Moden says that but uses both Oracle and SQL Server so I know he uses ANSI SQL as needed.
Kind regards,
Gift Peddie
October 27, 2009 at 1:33 pm
Gift Peddie (10/27/2009)
But we aren't talking about ANSI Standard. We are talking about MS SQL Server and most of us will use the features and capabilities of MS SQL Server even if they aren't ANSI Standard. As I hear from Jeff Moden all the time, "Portable code is a myth" (Paraphrased, I'm sure).
That is correct but Microsoft is not clear about their JOIN implementation which is one of the reasons people who are new to SQL Server don't understand what is in the BOL(books one line). Jeff Moden says that but uses both Oracle and SQL Server so I know he uses ANSI SQL as needed.
ANSI Standard Joins are fairly straight forward to me, they are written in the FROM clause. How and when you use them is the difficult part and requires an understanding of the application, the database, and the data.
As for Jeff using ANSI Standard code, not a bet I'd take even if he does write ANSI Standard code at times. I'm sure he uses the capabilities of either SQL Server or Oracle as needed to write the best code possible.
October 27, 2009 at 1:52 pm
As for Jeff using ANSI Standard code, not a bet I'd take even if he does write ANSI Standard code at times. I'm sure he uses the capabilities of either SQL Server or Oracle as needed to write the best code possible.
I did not say Jeff uses ANSI SQL code by using ANSI SQL as needed I mean applying RDBMS vendor extensions to base definitions.
Kind regards,
Gift Peddie
October 27, 2009 at 2:17 pm
I've submitted a couple QOD and they went well - no real big complainers, but I've seen others make pretty simple easy mistakes (the kind I could easily see myself making) and get ripped to shreds for it, and so I'm hesitant to step into the ring with all the carnage there. You're right, I should just go all out and if people don't like that, at least they don't know where I live right? (note to self... double check profile details :-P)
I don't mind sharing the principle - Gift, you're thinking too deep, it's more simple than that and Lynn, I said it was something I didn't know before. Although I don't post much, I lurk a lot, have seen your posts and have great amount of respect for all three of you. I'm 110% certain you already know and have seen this before (I have a degree in Statistics, so the value referenced is valid and properly researched :-D). I had a multi-table query with a left join against some of the tables, one table in particular was not referenced (yet) in the select nor in the where clause, only in the left join. STATISTICS IO and the query plan showed that the table was not read at all. So SQL Server was smart enough to know that since the table was on the right side of the left join and not used anywhere, it didn't matter what the data was or even if any existed and it could be skipped. Of course it doesn't seem to do that all the time, so I need to figure out what enables it to make that decision before I can post a QOD.
Chad
October 27, 2009 at 2:28 pm
I had a multi-table query with a left join against some of the tables, one table in particular was not referenced (yet) in the select nor in the where clause, only in the left join. STATISTICS IO and the query plan showed that the table was not read at all.
That means Microsoft query processor is ANSI SQL 1999 compliant because in ANSI SQL 1999 the WHERE clause is a filter like the AND operator but most T-SQL developers still write old JOIN which for compatibility reasons SQL Server executes without issues.
Kind regards,
Gift Peddie
October 27, 2009 at 2:58 pm
Gift Peddie (10/27/2009)
I had a multi-table query with a left join against some of the tables, one table in particular was not referenced (yet) in the select nor in the where clause, only in the left join. STATISTICS IO and the query plan showed that the table was not read at all.
That means Microsoft query processor is ANSI SQL 1999 compliant because in ANSI SQL 1999 the WHERE clause is a filter like the AND operator but most T-SQL developers still write old JOIN which for compatibility reasons SQL Server executes without issues.
Only with regard to INNER JOINs. The old style OUTER JOINs are not supported in SQL Server 2005 and later (compatibility mode 90 and >).
October 27, 2009 at 3:11 pm
Only with regard to INNER JOINs. The old style OUTER JOINs are not supported in SQL Server 2005 and later (compatibility mode 90 and >).
I am not talking about the old style JOIN I am talking about JOINs using the WHERE clause not as a filter, about 80 percent of SQL Server developers in SQL Server 2000 write those JOINs which are technically obsolete in early 2000. That is the reason some 2000 code with a lot of JOINs moved to 2005 either does not execute or comes with performance problems.
Kind regards,
Gift Peddie
October 27, 2009 at 3:19 pm
Gift Peddie (10/27/2009)
Only with regard to INNER JOINs. The old style OUTER JOINs are not supported in SQL Server 2005 and later (compatibility mode 90 and >).
I am not talking about the old style JOIN I am talking about JOINs using the WHERE clause not as a filter, about 80 percent of SQL Server developers in SQL Server 2000 write those JOINs which are technically obsolete in early 2000. That is the reason some 2000 code with a lot of JOINs moved to 2005 either does not execute or comes with performance problems.
Yes, JOINs done in the WHERE clause are the old style joins.
This
SELECT
*
FROM
dbo.Table1 t1, dbo.Table2 t2
WHERE
t1.PKCol = t.FKCol
is an old style join.
This
SELECT
*
FROM
dbo.Table1 t1
INNER JOIN dbo.Table2 t2
ON (t1.PKCol = t.FKCol)
is ANSI STANDARD join.
I can say since I started using SQL Server (and InterBase for a while) I have always written ANSI STANDARD joins. They just seem cleaner and easier to understand.
October 27, 2009 at 3:52 pm
Lynn,
That is very good example but you will find most T-SQL developers write the old one even in SQL Server 2005 and 2008 and SQL Server in most cases executes it and returns correct results.
Kind regards,
Gift Peddie
October 27, 2009 at 4:31 pm
Fortunately, they can't do the *= outer joins in the where clause in 2K5 or later unless they set the compatibility level back to 80. Those always seemed to be messy and updating them when there were several was a hair-raising task.
Chad
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply