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.
Category: T-SQL
Using NULLIF() to avoid division by zero
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.
A function to calculate recurring dates
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.
Weeks and weekday parts in dates
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 indexes
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.
Using DDL triggers to set up version control
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.
Working with intervals
At one point or another, you’re going to come across intervals when working in SQL Server. You could say that an interval is where you don’t have a single value, but actually a range of values, commonly delimited within a start and an end value. This range could be a group of accounts, versions of dimension members (in an SCD) or date/time intervals.
A function to parse ranges
Here’s a quick function to parse a list of ranges (in a varchar variable) into a table of ranges. Might come in practical, for instance when parsing user arguments for a report.
An introduction to table types
As of SQL Server 2008, you can create user-defined datatypes as tables, which comes in pretty handy when you want to pass an entire table as an argument to a stored procedure or a function. And as a bonus, you get get really pretty, highly readable code!
Know your collation orders
We’ve discussed earlier on the effect of using SQL wildcards, such as ‘%[a-z]%’ with different collation orders, particularly case sensitive ones. Here’s another take on collations, and how different collation orders can sort text differently.