This presentation is designed to cover some of the basic mistakes that I find people making quite often when working with SQL Server. It is a mix of development and administrative items, designed to help beginners get a grounding in those skills that often cause the most problems in SQL Server.
The talk is 75 minutes.
Slide Decks:
Despite my joking with Aaron Bertrand on Twitter that his suggestion on Connect got voted down because he’s Canadian, I think he has a great point with a hole in Books Online. He wrote a nice blog on the item, and it’s one you ought to consider voting up if you agree.
I voted it up, and I think this is the type of half-*ssed documentation that is strewn throughout Books Online. While it’s a great document, and has a lot of information, it’s not always clear what is meant, and there are often holes. Most items have permissions listed, but not all, and even those that do are not clearly written or even correct.
In my mind, the ability to change the identity values on a table ought to be either explicitly a permission that is given to users (GRANT IDENTITY_INSERT on xx to yy) or it ought to be included with the INSERT permission. There isn’t really anything here that’s altering a schema. It’s an insert permission for a user, that explicitly needs to put in a value, perhaps to close a sequence, or fix a failed insert. There’s no reason to require any elevated permission for this action.
In any case, having the documentation clearer, even if it doesn’t behave as I’d expect it, is something I think is worth voting for.
In the old days of T-SQL, back when we wrote “CREATE TRIGGER …. FOR INSERT” we could only have one insert/update/delete trigger for each table. Eventually SQL Server allowed us to have multiple triggers, and even have some control over in what order the triggers fired.
Triggers are often hidden objects that confound DBAs who aren’t aware they exist. It’s not easy to tell when a table has a trigger on it, and since we don’t often use triggers, it’s not the first place people look when something strange happens.
However triggers are useful, and it seems that there are many people using them. For this Friday, I wanted to ask how people implement triggers in their applications.
Do you prefer one trigger for each table action or multiple triggers?
I’m curious what’s the 80 in your 0/20 rule for triggers. Should all update actions be handled in one trigger? Or should there be one trigger for business logic and a separate one for auditing? I’m not sure it matters a lot for performance, but I can see that it might be easier to manage and track fewer triggers. The flip side is that something like auditing can be handled with one trigger, and business logic with another: a clean separation.
Triggers aren’t usually my first solution to a problem, but I do think there is value in using them. However I don’t see a lot of guidance about how to best implement them, so I’m hoping your answers today will help.
Steve Jones
This continues my series on Common SQL Server mistakes, looking at more T-SQL mistakes.
If you saw a query like this, would you see a problem?
If there are 1,000 orders in this table, there probably isn’t an issue. But if there are 1,000,000, then this is an issue.
Why? Let’s examine the execution plan:
This table has 1000 rows in it, but it doesn’t use indexing to find those orders that were placed in 2010. Instead it scans all rows. The reason is that the function being used in the WHERE clause means that the index cannot be used.
Instead, what you would want to do is write the query like this:
In this way, we eliminate the function from the WHERE clause and allow the query optimizer to take advantage of the indexes on the column OrderDate.
You see similar issues with queries like:
This can be fixed as:
Basically you want to move the function away from the column and put it on the other side of the comparison so that indexes can be used.
Too often we have developers writing queries like this, assuming that the functions are efficient. They are, but when they are executed against every row in a table, an index can’t be used for seek operations, which are always quicker than scans for any significant data set.
When you are writing queries, do your best to avoid functions against columns in your tables. Instead try to rework the query to move the function. An alternative that I’ll blog about another time is computed columns.
Voice of the DBA 