Ok, so even if you’re a seasoned veteran T-SQL coder, at some time you will write a query that runs away and supersizes the tempdb database. This, in turn, might fill up your disk and cause other server-related problems for you. At that point, you may find out the hard way that shrinking tempdb isn’t like shrinking any other database.
Here are some tricks that I’ve tried successfully – but bear in mind that your mileage may vary.
I took some time today to read up on Dale Burnett’s series on statistics in SQL Server. In seven detailed posts, Dale goes through different aspects of how statistics work in SQL Server, and how to use them to your advantage.
If you’re into query optimization, and you’ve got your glasses on, chances are this article is for you.
The MERGE statement is a really powerful way to create what’s called “upserts”. In this article, I’ll take a more detailed look at how you can make the best use of MERGE and I’ll also show you some cool tricks I picked up along the way.
One of the most basic concepts to master in T-SQL (or any dialect of SQL, for that matter) is understanding how the JOIN operator works. This post goes through the fundamentals of joining, and I highly recommend it if you’re just starting out with T-SQL.
When you perform divisions, you need to make sure that the divisor, the number to the right of the division operator, isn’t zero. If it is, you will end up with a division-by-zero error message:
Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Dividing by NULL, however, doesn’t. So to avoid division-by-zero errors, simply use NULLIF() to replace zeros in the divisor with a NULL value.
If you’re lucky enough to be working with an Enterprise Edition of SQL Server, you may be missing out on a really great feature that can boost your I/O performance considerably. And as an added bonus, it’ll save you disk space..
When you’re using Microsoft Outlook, or pretty much any other personal information manager, you can create calendar appointments that are “recurring”, i.e. you can have them repeat at a defined frequency. This, however may not only apply to your project meeting appointments, but also to some database solution. I decided to give it a go at building a table value function that returns a list of dates, based on a given set of parameters.
Here are two common pitfalls that you may encounter when it comes to working with dates, specifically with week numbers and weekdays. The first is to mistake week numbers in SQL Server for ISO week numbers (which are the weeknumbers used in Sweden, for instance). The other is to make assumptions about which day of the week is the first. The good news is that these are relatively easy fixes.
XML documents are, by design, at best like an entire relational database, stored in a single column. Worst-case, they don’t even come with a schema. Small wonder then, that XML queries take a good deal of CPU and I/O performance to complete. The solution to this problem is to index them, pretty much like you would index regular tables.
DDL triggers allow you to write SQL code that executes whenever a DDL (data definition language) event occurs. This means you can capture, and handle, any event that modifies for instance stored procedures, views, DML trigger, etc. In this post, I’m going to set up a simple version control process using DDL triggers.