Difference between COALESCE and ISNULL

I have often been asked about the difference between ISNULL and COALESCE. In most cases they work the same, but there are some fundamental differences. Let's review some of them...

ISNULL is a T-SQL function while Coalesce is an ANSI Standard.

ISNULL only takes two arguments and basically evaluates the first argument for null value, if true, it returns the second value back. For example, if you execute the following command

SELECT ISNULL(NULL,NULL) you will get NULL as your result set. Now try executing the same statement with COALESCE -- SELECT COALESCE(NULL,NULL), you will receive the following error

At least one of the arguments to COALESCE must be a typed NULL. 

What does this error mean? Does it mean you can't pass two null arguments to Coalesce? What it really means is that at least one of the argument must be of a particular data type even if it is NULL. For Example, I can do the following..

DECLARE @First Varchar(10)
DECLARE @Second varchar(10) and execute the following statements, they will all work fine.
SELECT COALESCE(@First,@Second)
SELECT COALESCE(NULL, @Second)
SELECT COALESCE(@First,NULL)

Let's run another test...

DECLARE @First char(2) and run the following statements
SELECT ISNULL(@First,'This is a Test') -- returns Th
SELECT COALESCE(@First,'This is a Test') -- returns This is a Test

The data type in ISNULL is determined by the first argument. In this case, the data type of the first argument is Char(2) so it only returns 2 characters of the non-null argument which is the second argument. Coalesce on the other hand determines the data type based on the precedence. Check out the following article about data type precedence ( http://msdn.microsoft.com/en-us/library/ms190309.aspx).

Depending on how you use one or the other, you could actually get different results or an error. For example, if I run the following code...

DECLARE @First varchar(4)='Test'
DECLARE @Second INT = 1
SELECT ISNULL(@First,@Second)
SELECT COALESCE(@First,@Second)

ISNULL will work just fine but COALESCE will generate an error. This is because an INT has higher precedence than varchar and COALESCE will try to convert @First to INT, generating an error. If @First is NULL, then it will work fine and return you the value of @Second.

COALESCE can take more than 2 arguments and will work like Switch/Case (Select/Case) working down the cases until it finds the non-null value or until it has evaluated all the arguments.

Performance wise they both are same in most situations, except there are cases where SQL Server generates different execution plan based on whether you use ISNULL or COALESCE. This generally happens when you are using scalar sub-queries as part of your arguments to ISNULL or COALESCE.

Thank you.

T-SQL Best Practices

When writing a T-SQL either as an ad-hoc query or a stored procedure, besides ensuring the queries are fully optimized, you should keep few things in mind. Although none of these are required, but trust me someone reading your queries in the future will thank you for it, and it may even save you a headache in the future. I don't have many, but there are few guidelines / best practices that I try to adhere to.

Indentation
 Properly Indenting your query makes it so much easier to read and / or troubleshoot. 
 For example, consider the following query..

SELECT FirstName,LastName,EmailAddress,Address1,Address2,Phone,Fax FROM Customers

Can be rewritten as...

SELECT
    FirstName,
    LastName,
    EmailAddress,
    Address1,
    Address2,
    Phone,
    Fax
FROM Customers

It is certainly easier to read and you can quickly figure out what columns are being selected. You can make it even more useful by making the following change...

SELECT
    FirstName
    ,LastName
    ,EmailAddress
    ,Address1
    ,Address2
    ,Phone
    ,Fax
FROM Customers

By using "," as a prefix in subsequent columns, you can easily comment out a line containing the column that you don't want your query to return instead of having to rearrange it.


Using Square Brackets
Although square brackets are not necessary unless you have a space in column name, using it ensures that if your column name ever becomes a reserved word in SQL, you query won't break. For example, you can write the above query as follows...

SELECT
    [FirstName]
    ,[LastName]
    ,[EmailAddress]
    ,[Address1]
    ,[Address2]
    ,[Phone]
    ,[Fax]
FROM [Customers]

Using Statement Terminators
Using semicolon (;) at the end of your SQL Statement. If you use C# or C++ or Java for application development, this may be second nature, but not so much for VB Developers. Although SQL Server currently doesn't require semicolon in most cases, it may change in the future as it is part of the ANSI SQL-92 standard. So to avoid future headaches, always use semicolon. Thus the proper way to write the above query would be...

SELECT
    [FirstName]
    ,[LastName]
    ,[EmailAddress]
    ,[Address1]
    ,[Address2]
    ,[Phone]
    ,[Fax]
FROM [Customers];

If you have multiple statements in your query, use semicolon after each one. For example, I can add SET NOCOUNT ON in my query above.

SET NOCOUNT ON;
SELECT
    [FirstName]
    ,[LastName]
    ,[EmailAddress]
    ,[Address1]
    ,[Address2]
    ,[Phone]
    ,[Fax]
FROM [Customers];

Capitalize all T-SQL Keywords
Capitalizing T-SQL Keywords such as SELECT, FROM, WHERE, JOIN etc. makes it so much easier to read your query and to differentiate between the T-SQL keywords and your own columns etc.

Happy Programming!

SQL SERVER - Little things can save you a big headache

Recently I came across a situation where someone was running a query, basically copying data from one table to another.

The query was simple enough. Basically it was using a fast forward cursor, copying one record from one table into another table. Using SCOPE_IDENTITY, the query retrieved the identity value of the newly inserted record and then inserted another record in a child table, using newly generated identity key. There were several hundred millions of records in the table from which the data was being moved to a new table. Whether this could have been done another way is not the topic for today.

In this particular instance, the query was running in Query Analyzer and it will run for a while, but then will throw an out of memory exception. Since the query didn't track what records were inserted already in the new table before out of memory exception was thrown, the developer truncated the new table and started all over again, only to hit the same issue again.

The reason query was throwing out of memory exception was because the query didn't have SET NOCOUNT ON clause at the beginning. As a result, the query was outputting messages for each statement in this query, not only resulting in memory consumption but also degrading the performance and increasing the network traffic. SET NOCOUNT ON basically prevents the sending of DONE_IN_PROC messages to the client.

The query need not run in a loop like above or in query analyzer to make use of SET NOCOUNT ON. If you don't need messages being returned from the SQL Server or aren't capturing them, be sure to add this clause to your stored procedures.

Thank you.

SQL Server - Degree of Parallelism

In SQL Server 2008, Microsoft introduced a concept called max degree of parallelism. Max degree of parallelism allows the engine to take advantage of all the available processors on the server up to 64 processors to execute a single SQL statement. By default, SQL Server is configured to use all available processors, however, there may be instances where you want to limit the number of processors a query should use. For example, you may have a low priority SQL agent job running in the background and you want to limit it to only use one processor. You can set max degree of parallelism to 1.

The query below will only use up to 2 processors for parallel operation regardless of the number of processors available.

USE AdventureWorks
GO
SELECT ProductID, OrderQty, SUM(LineTotal) As Total
FROM Sales.SalesOrderDetail 
WHERE UnitPrice < $10.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2)
GO

To set the option for SQL Server to always use up to X number of processors, you can use SP_Configure.

For example, the following query forces SQL engine to only use 4 processors.

USE Master
GO
SP_Configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
SP_Configure 'Max Degree of Parallelism', 4
GO
RECONFIGURE
GO

Default "Max Degree of Parallelism" option is 0, which means use all available processors up to 64.

Thank you.

SQL 2012 (Denali) - Paged Query Results

Anyone who has been developing web based or even windows based systems must have gone through the pain of fetching few records at a time from the database.

Suppose your grid view displays 20 records per page. Your option was to either fetch all records on every grid page and every time user changed the page, make another trip and fetch all records again (or save everything in view state / session) - not an ideal solution. Your other option was to get a list of all primary / identity keys and then pass a set of keys to the query to only fetch the records that pertain to those keys. For example, get keys 1 - 20 on page 1, 21 - 40 on page 2 and so on. But what if someone deleted, say record #22 from the database? Another implementation I have seen is where code passes the page number and number of records that each page displays and the stored procedure applies some complex logic to figure out what records to send back. Using ROW_NUMBER() and CTE was a better alternative, but it was still not very intuitive and easy to use.

SQL 2012 introduced Ad-Hoc paging, allowing you to only fetch the appropriate number of records. Two clauses "OFFSET" and "FETCH" make this happen. Fetch clause fetches only the next X number of records you want to retrieve and OFFSET tells the query to start from the record # Y.

For example, suppose I have 1000 users in my table and my grid only shows 10 users at a time. I can pass two parameters to my query and it will return me correct 10 users every time.

CREATE PROCEDURE getPagedRecords

@Page INT = 1,
@RecordsPerPage INT=10

AS
BEGIN
    DECLARE @Offset INT
    SET @OffSet = Page * @RecordsPerPage

    SELECT 
        UserID,
        FirstName,
        LastName,
        EmailAddress 
    FROM Users 
    ORDER BY UserID
    OFFSET @Offset ROWS
    FETCH NEXT @RecordsPerPage ROWS ONLY;
END

You can even use expressions in OFFSET and FETCH Clause such as @Offset - 1 and @RecordsPerPage   - 1.

You don't have to have paging in grid view to make use of this. Often time you may need X number of records from the middle of the result set, where this could be very helpful.

For Fetch to return expected set of records every time, you obviously have to make sure other routines aren't deleting / inserting records in the middle which may cause an unexpected behavior. For example, let's say your OFFSET clause tells the query to start at record # 11, but what if someone deleted a record say at #5 after you retrieved first 10 records? Record number 11 is now what would have been record number 12, thus your query will not return you one record (original record at #11 but now at # 10).

Thank you.

SQL Server 2012 (Denali) Sequence Object

In addition to several enhancements in SQL 2012 - (some of which we will cover in subsequent posts), one of the key feature introduced is a sequence object.

Think of sequence object as an identity field on steroids. An identity field allows you to generate an auto number incremented by whatever increment value you desire (default is one) every time a record is inserted in a table. But, identity field is specific to a table, just like any other field defined in a given table. Additionally, if you delete the previous record, the identity value associated with the deleted record is lost forever until you do something to reseed it. Once the identity field hits the maximum limit (depends on the data type of this field), you've got a problem and you must reseed the table to start over or do something else.

Sequence object can help you mitigate this problem somewhat. First, let's briefly review this object. The Sequence object is created independently of a table and can be used across multiple tables.

For example - I can create a sequence object called OrderNumber, have it start with 1000 and increment by 1.

Create SEQUENCE [dbo].[OrderNumber]
as int
START WITH 1000
INCREMENT BY 1;

Now this sequence object is available to be used in multiple tables.

Let's imagine I have a table called OrderHeader and OrderDetails in my database.

CREATE TABLE [dbo].[OrderHeader](
    [OrderNumber] [int] NOT NULL,
    [OrderDate] [smalldatetime] NOT NULL,
    [StatusID] [int] NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[OrderDetails](
    [OrderNumber] [int] NOT NULL,
    [ItemID] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
    [Price] [money] NOT NULL
) ON [PRIMARY]

Instead of inserting record in OrderHeader and then using SCOPE_IDENTITY() or some other method to get the OrderNumber before inserting the record in OrderDetails table, I can use the sequence "OrderNumber" I created above and insert the same number in both tables.

DECLARE @OrderNumber INT
SELECT @OrderNumber = NEXT VALUE FOR dbo.OrderNumber
INSERT INTO Orders(OrderNumber,OrderDate,StatusID)
VALUE (@OrderNumber,GetDate(),1)

INSERT INTO OrderDetails(OrderNumber,ItemID,Quantity,Price)
VALUE (@OrderNumber,100,10,'2.25')

NEXT VALUE FOR gives you the next available sequence number. Every time you call it, the object will return the next available number, so if you want to use the same number in multiple tables, you have to be careful to call it only once.


Another nice feature of the sequence object is that you can reset it. As in my example above, if you are deleting previous records, then you know your previous order numbers are becoming available. After you have reached fairly large number, you can start over. You obviously have to ensure you don't reset sequence and then try to use the new sequence number which may already exist in your table.

There are two ways to reset the sequence (dropping and recreating is a third way). One, by defining a maximum value for the sequence when you created it and another by altering an already created sequence.

Create SEQUENCE [dbo].[OrderNumber]
as INT
START WITH 1000
INCREMENT BY 1
MINVALUE  1000
MAXVALUE  1000000
CYCLE

The OrderNumber sequence in above example will recycle once it hits 1000000.

ALTER SEQUENCE OrderNumber
 RESTART WITH 1000 ;

As you can see, Sequence object is quite flexible and if used judiciously can serve as a nice tool. Sys objects provide additional methods to get the range of values or to find out the current sequence value etc.

Thank you.