Overcome Divide by Zero Using NULLIF

Anytime we are dividing we need to think of the divide by zero scenario. Even if you think you will never encounter this with your result set, it’s advisable to guard against it because when divide by zero is encountered, an error is thrown. The best method I’ve found to overcome this is by using the NULLIF function. This function takes two parameters and if they are equal, a NULL value is returned. Lets take a look at an example that throws a divide by zero error. [cc lang=”sql”] DECLARE @iter float; DECLARE @num float SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / @iter SET @iter = @iter – 1 END [/cc] Running the following query, we see that once the variable @iter becomes zero, we receive an error. So the most elegant way to overcome this is to use NULLIF function and compare @iter to zero. When it does equal zero, it will instead change it to a null. And when dividing anything by NULL will equal a NULL. [cc lang=”sql”] DECLARE @iter float; DECLARE @num float; SET @num = 10; SET @iter = 5; WHILE @iter > -5 BEGIN SELECT @num / NULLIF(@iter,0); SET @iter = @iter – 1; END [/cc] This executes without error, however we still receive a null as a result. If you need otherwise, then you may want to wrap the equation in an ISNULL, to return a different value. [cc lang=”sql”] DECLARE @iter float; DECLARE […] Continue reading ...

A Query Method Faster Than BETWEEN

This is an invaluable trick that can be sometimes be used instead of the BETWEEN operator. A common place this can be used is in an historical table that contains EffectiveFrom & EffectiveTo dates. In these kinds of tables, many historical versions of a record can be stored. Each record in this scenario will contain a Start & End Date which signifies the time span when a record is active. Because two records cannot be active at the same time, we can be sure that the Start & End dates will never overlap. While this describes one particular scenario for this example, there are many other situations this method can be used. Let’s look at the typical query we would use to find the record which is active for an employee at a specific point in time. [cc lang=”sql”] SELECT * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND ‘2001-04-28’ BETWEEN StartDate AND EndDate [/cc] This query will return to us the single record that is active as of ‘2001-04-28’. However if you think about the constraints our table contains, we actually only need to query one column (the StartDate) in order to isolate this record. This is because the StartDate will not overlap any other StartDate & EndDate for this employee. Here’s an example: [cc lang=”sql”] SELECT TOP 1 * FROM [HumanResources].[EmployeeDepartmentHistory] WHERE EmployeeID = 274 AND StartDate >= ‘2001-04-28’ ORDER BY StartDate [/cc] Because only one record can be active for an employee at a single point in time, […]

SELECT INTO IDENTITY

Adding an identity column to a SELECT..INTO query is probably something I use nearly on a daily basis. I’m providing it here merely for reference purposes. It useful when you want to loop through a temporary table. [cc lang=”sql”] SELECT id = IDENTITY(int,1,1) ,FirstName = pc.FirstName ,LastName = pc.LastName INTO #person_contact FROM Person.Contact pc WHERE EmailPromotion = 2 ORDER BY pc.LastName [/cc] The first parameter for the IDENTITY function is that data type. It must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable. The second parameter is the seed (or starting) value. The third parameter is the increment. An alternative method is to use the ROW_NUMBER window function. This can actually be a bit more pliable in certain situations because of it’s optional partitioning function. The obvious difference here though is that any rows subsequently added to the table do not get an identity value. [cc lang=”sql”] SELECT id = ROW_NUMBER() OVER (ORDER BY pc.LastName) ,FirstName = pc.FirstName ,LastName = pc.LastName INTO #person_contact2 FROM Person.Contact pc WHERE EmailPromotion = 2 [/cc] Note that with the IDENTITY method, Microsoft does not guarantee that the identity will be in the correct order when performing an order by clause, especially when using TOP or ROWCOUNT. Continue reading ...

Making a Procedure Available in all Databases

If you’ve ever wondered how to make a procedure available in any database, it’s actually pretty simple. If you create a procedure in the master database with the prefix of “sp_”, it will be callable from any database. I personally think this feature is great for utilities, however I would not use this for dependent objects. In other words, I would not advise creating a procedure (or function that is referenced by other procedures) within the master database. The biggest reason is that when restoring databases to other servers, you typically do not restore the master database. So it can be easily left out or forgotten. If that happens you may have a mission critical troubleshooting situation on your hands. However sometimes there may not be a way around it. But you can look into utilizing synonyms as an alternative. Another interesting observation regarding placing procedures in the master database with the sp_ prefix is that the procedure is actually executed from the context of the master database. In other words, if you create a procedure to enumerate all the other procedures within your database and place it in master, once you execute the procedure from a database other than master, it will still enumerate the procedures in the master database. Here’s an example: After creating this procedure and calling it from AdventureWorks, it still enumerates all the procedures within the master database. To get around this, we can use the undocumented procedure which will mark the procedure as a […]