My apologies for taking so long to post this. I've finally uploaded the PowerPoint slides from "What I Wish Developers Knew About SQL Server". I presented this at PASS 2005 in Dallas, the Kansas City SQL Server Users Group, the Kansas City .NET SIG, a couple of clients and any other group I could find that would listen to it. All the deliveries really helped make the PASS presentation smooth and polished.

Thanks for sharing the slides (and wisdom), Bill! I can see why it won the PASS 2005 Best Presentation award and suggest you consider doing a podcast (or otherwise capturing a "live" presentation as an audio file) next time you present. You're so modest that you didn't mention that folks can also download your excellent PASS 2004 slides on Profiler and SQLTrace in SQL 2005 from http://www.sqlteam.com/downloads/pass2004_306.zip.PS I blogged about this at http://blog.pinpub.com/sqlblog/.Karen

Great info, Graz. One item in the presentation caught my attention that I hope you can expand upon. You mention that the following syntax isn't preferrable:

WHERE Col=COALESCE(@Var, Col)

and, instead, you like to see:

WHERE (@Var is NULL or Col = @Var)

I often use the first syntax when creating queries that will be called from different areas of an application where one request needs to restrict based on @Var and the other does not. The alternative, creating two separate queries or creating two very similar logic blocks in the same proc tends to become a maintenance nightmare since you have to change both as your schema changes or the data required by the app changes. Furthermore, I'll even use the following syntax on occasion when Col accepts NULLs and I want to include those in the resultset:

WHERE IsNull(Col, '') = COALESCE(@Var, Col, '')

I'm interested to hear your experience on this approach and why you recommend against it (performance, readability, other...).

The biggest problem is see is in performance. I don't know that either really does what I want though. If you use the first example if won't use the index in the query even if you do pass it a parameter. For example, if you're wrapping the COALESCE around the CustomerID and you pass in a CustomerID it won't use any index on CustomerID.

I'd encourage you to test it yourself and verify those results. QA will allow you to display the actual query plan that's executed.

When using (@Var is NULL or Col = @Var), the optimizer can do two things the other versions can't support:

1. Short-circuit evaluation of the OR condition (if @var is null, then the rest of the expression does not need to be evaluated)2. Allow the optimizer to use indexes on Col

The last syntax you posted cannot use an index because the searchable expression is wrapped in the IsNull() function. While the COALESCE() syntax is logically equivalent to the preferred syntax, it can't do a logical short-circuit, and may end up choosing a less optimal plan.

To check for existance, which It certainly does, Coming from a Sybase System 10/ SQL Server 4.3 background I have always used

IF EXISTS (SELECT 1 …)

As that is what I was told produced more optimised code. So I had a look at a database table I know isn't particularly blessed with indexs and has a reasonable number of rows 100K (Not huge either Need to look for a bigger table, I've got some somewhere with 10's of Millions), then just simply did a If Exists ( Select ... ) Print 'Found'

Subtree Cost was 0.613 in both instances, Using the Primary Key dropped it to an even smaller amount. I would hazard a guess that the optimiser internally does a Select 1 Or a Select * (Rowsize is a constant in both at 1022) regardless of what you put in the Select Statement. Interestingly I just did the same thing putting a couple of fields in instead of * or 1 and got exactly the same result.

So what does it mean in the real world? Not a lot I guess, Unless someone can come up with a reason why not to, I will continue to use Select 1 as I would be crucified by my developers if they ever saw Select * in a piece of my code

quote:Originally posted by TonyTheDBA...I will continue to use Select 1 as I would be crucified by my developers if they ever saw Select * in a piece of my code

YOU are the DBA! YOU decide what code is executed against the database. YOU are (or should be) the authority on indexing, SQL, execution plans, etc.Can't be a DBA without a spine. Now walk out to their cubicles and let 'em know who's da boss.

quote:Originally posted by blindmanYOU are the DBA! YOU decide what code is executed against the database. YOU are (or should be) the authority on indexing, SQL, execution plans, etc.Can't be a DBA without a spine. Now walk out to their cubicles and let 'em know who's da boss.

Funny you should say that, I keep telling them its in my job description to say No, and be Obstructive and unhelpful but do they listen . . . .

quote:So what does it mean in the real world? Not a lot I guess, Unless someone can come up with a reason why not to, I will continue to use Select 1 as I would be crucified by my developers if they ever saw Select * in a piece of my code

I mentioned that exact thing in my presentation. In regards to your entire post I think the optimizer ignores everything between the SELECT and FROM when the query is inside an EXISTS function. At least I've never been able to put anything in there that affected the query plan.

quote:I'm interested to hear your experience on this approach and why you recommend against it (performance, readability, other...).

Rob already addressed this but I wanted to add a little. I did quite a bit of research on query plans and such for this presentation. I tried to test everyting I said if possible. It was a very educational experience :) One of the things I always encourage people to do is test it yourself. Write a few sample queries and see which works faster.

Think oracle used to optime select 1 better, sql at least used to work better with select * as it allowed it to select it's own index.Now it doesn't matter with sql server just that select * seems more readable i.e. are there any rows here.

And more importantly - is Graz giving out sweets.

I just remembered that I was going to post something about the sp recompile thing - i.e. how much of an sp recompiles.

==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.

Last year I did a review of a data warehouse design for a client that had built their schema based on seminars given by Ralph Kimbal. In each of their tables I found a column with nothing but the value "1" in it for every record.Why? Because Kimbal told them it was faster to sum([1column]) than count(*).Interestingly, they had this column even in a denormalized star schema (Kimbal's one-size-fits-all datawarehouse design...).

That is really good advise. I see a lot of posts on SQLTeam where people ask questions about query performance, or if something will work. My first thought is "Why don't you just test it and find our yourself?"

There seem to be a lot of people who would rather ask an "expert" than just try it and see real numbers. Maybe you should include the Scientific Method in "What I Wish Developers Knew About SQL Server"

quote:Originally posted by graz...One of the things I always encourage people to do is test it yourself. Write a few sample queries and see which works faster...

This PPT would be more helpful to me if it included the dialog that would have accompanied it's live presentation. Doesn't PPT have a way of "commenting" or "dialoging" a presentation? Is it perhaps there and I'm just not hearing it?

I'm not exactly a PPT guru, so it could be the simplest of things that I'm missing.