COALESCE Function in TSQL

If you are new to TSQL or SQL in general, it’s always good idea to read about functions which we rarely use. Someday it might come handy (Who know it’s a tricky question and people want to trip you down memory lane.)

So let’s try to understand COALESCE function.

If you are not a big fan of msdn site, I don't blame you!!!! However it will give you a great starting point to do your research.

According to MSDN (http://msdn.microsoft.com/en-us/library/ms190349.aspx)

COALESCE says that it will "Returns the first nonnull expression among its arguments." 

Let’s go through some example:

Suppose we have a table called Address with three columns (Home Number, Work Number and Cell Number) along with all other columns. Also suppose that none of these column (phone number columns) are NOT NULL (meaning it’s up to user to enter information if he or she likes it). So we can have some users who entered phone number under Home and some under Work and still few under cell number and same never bothered to enter any number.

Let’s assume that we want to see this information in our select output along with other information.  Our preference choice is Home number first, Work number second and cell number third or it can be cell number first, home number second and work number third. Also we just want to show only 1 number at any given time. So how do we do this in our TSQL.

Here’s come-à COALESCE

Let’s go with our first choice (home then work then cell)

Select COALESCE (Home Number, Work Number, Cell Number) AS Phone Number from Address

This statement will look at home number first, if there is home number, it will return that number. If home number is missing, it will jump to work number. If work number is present it will return as output otherwise it will jump to cell number. So our result will have one number from these three columns.

COALESCE (444-444-4444, 555-555-5555, 666-666-6666) will give you-à 444-444-4444

COALESCE (NULL, 555-555-5555, 666-666-6666) will give you à 555-555-5555

COALESCE (NULL, NULL, 666-666-6666) will give you à 666-666-6666

More generic example:

COALESCE(1, 2, 3, 4, 5, NULL)à 1

COALESCE(NULL, NULL, NULL, 1, 2, 3) à 1

COALESCE(9, 8, 7, 6, 5, NULL)à 9

COALESCE(NULL, NULL, NULL, 4, 5, NULL)  à 4

What happen if none of the three columns have any number? In that case it will return a NULL value.

COALESCE(NULL, NULL, NULL, NULL) à NULL

Creating Constraint on Table to check for data integrity

Creating Constraint on Table to check for duplicate values

Sometime we are asked to make sure that there is no duplicate data in our table based on certain columns combination. There are many ways to do this.

The first thing that will come to our mind is TRIGGER.

Well Trigger are good but its not always  best solution, particularly in our case here.

So let see how we can do it.

Create a table and insert some data.



CREATE TABLE [dbo].[Product](
[ProductID] [int],
[ProductName] varchar(100) NOT NULL,
[IsActive] Bit NOT NULL,
[RetiredDate] Datetime2,
[MinPrice] Float,
[MaxPrice] Float
)

Just to show how constraint works, I am not adding any Primary Key or Foreign Key relation with other tables.

Let's insert some data in this table.



  Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 100.00, 500.00), (102, 'Mountain-rugged',1,299.99,999.99), (103, 'Mountain', 0, 199.99,399.99)

Check to make sure you have data in table you just inserted.


SELECT * FROM Product

Now let's say that our ProductID + ProductName+ IsActive has to be unique row in our table. So in this case based on this, there can be only two combination (0 or 1 for active and inactive values)

Suppose we do not want user to enter a value of above combination because we are considering this to be a unique values.



"101--Mountain-Bike--1"

Right now with no constraint, we will be able to enter this value.


 Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 59.99, 199.00)

Here, I have just changed MinPrice and MaxPrice but productID, productName and IsActive are same, which should not be allowed if we want to maintain data consistency.


To prevent these type of data, we can create trigger based on unique combination of three columns where we want to maintain data integrity.


To do so, we have to remove last inserted row from our table. Let's go and remove that row.

Method 1:


   
ALTER TABLE dbo.Product
ADD CONSTRAINT unique_PID_PNAME_Active UNIQUE([ProductID],[ProductName] ,[IsActive])

Method 2:


CREATE UNIQUE INDEX uq_product
  ON dbo.Product([ProductID],[ProductName] ,[IsActive]);

Method 3: which is trigger (not recommended but you can still do it)

CREATE TRIGGER dbo.BlockDuplicatesproduct ON dbo.product

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON;

IF NOT EXISTS (

SELECT 1

FROM inserted AS i

INNER JOIN dbo.product AS t ON i.productID = t.productID

AND i.[ProductName] = t.[ProductName]

)

BEGIN

INSERT dbo.product (

[ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

)

SELECT [ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

FROM inserted;

END

ELSE

BEGIN

PRINT N'';

END

END

GO;

Now test and see it works for you or not.

Inserting Data in temp table from Another table

Sometime when we update certain table, its always a good idea to save the entire data into another temporary table which will hold unless we delete that temp table.

Let's say we are working on a Employees table and we need to manually update certain rows or columns for this table.

To do so, let's back them up somewhere for our reference (incase we mess up whole table..I have done few times in beginning!!)


Select * INTO tmpEmployees
FROM Employees

This statement will create a table "tmpEmployees" which will have same structure as  Employees table.


Even we when we close current session, this table will remain there in our database.


However, if you do not want to retain your temp table after you are done with you update, you can create #tmp table


Select * INTO #tmpEmployees
FROM EMPLOYEES


If table already exist, than we have to use this


INSERT INTO #tmpEmployees 
Select * FROM Employees


:Kumar