Archive

Posts Tagged ‘COMPUTED COLUMNS’

ISNULL vs COALESCE – expressions/functions in SQL Server

December 23, 2010 20 comments

ISNULL & COALESCE with some common features makes them equivalent, but some features makes them work and behave differently, shown below.

– Similarity
Both can be use to build/create a CSV list as shown below:

USE [AdventureWorks]
GO

DECLARE @csv VARCHAR(2000)

SELECT @csv = ISNULL(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv

set @csv=NULL
SELECT @csv = COALESCE(@csv + ', ', '') + FirstName
FROM Person.Contact
WHERE ContactID <= 10
ORDER BY FirstName

select @csv
Both will give the same output:
Carla, Catherine, Frances, Gustavo, Humberto, Jay, Kim, Margaret, Pilar, Ronald

– Difference #1
ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.
COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter, example below.

DECLARE @str1 VARCHAR(10), @str2 VARCHAR(10)

-- ISNULL() takes only 2 arguments
SELECT ISNULL(@str1, 'manoj') AS 'IS_NULL' -- manoj

-- COALESCE takes multiple arguments and returns first non-NULL argument
SELECT COALESCE(@str1, @str2, 'manoj') AS 'COALESCE' -- manoj

-- ISNULL() equivalent of COALESCE, by nesting of ISNULL()
SELECT ISNULL(@str1, ISNULL(@str2, 'manoj')) AS 'IS_NULL eqv' -- manoj

– Difference #2
ISNULL does not implicitly converts the datatype if both parameters datatype are different.
On the other side COALESCE implicitly converts the parameters datatype in order of higher precedence.

-- ISNULL Does not do Implicit conversion
select ISNULL(10, getdate()) as 'IS_NULL' -- Errors out
Error Message:
Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed.
Use the CONVERT function to run this query.
-- COALESCE Does Implicit conversion and gets converted to higher precedence datatype.
select COALESCE(10, getdate()) as 'COALESCE' -- 1900-01-11 00:00:00.000, outputs 10 but convert it to datetime [datetime > int]
select COALESCE(getdate(),10) as 'COALESCE' -- {Current date} 2010-12-23 23:36:31.110
select COALESCE(10, 'Manoj') as 'COALESCE' -- 10 [int > varchar]
select COALESCE('Manoj',10) as 'COALESCE' -- Errors out, it does an implicit conversion, but cannot change 'Manoj' to Integer.
Error Message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Manoj' to data type int.

– Difference #3
Similar to above point ISNULL always returns the value with datatype of first parameter.
Contrary to this, COALESCE returns the datatype value according to the precedence and datatype compatibility.

DECLARE @str VARCHAR(5)

SET @str = NULL

-- ISNULL returns truncated value after its fixed size, here 5
SELECT ISNULL(@str, 'Half Full') AS 'IS_NULL' -- Half

-- COALESCE returns full length value, returns full 12 char string
SELECT COALESCE(@str, 'Half Full') AS 'COALESCE' -- Half Full

– Difference #4
According to MS BOL, ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. Thus to index expressions involving COALESCE with non-null parameters, the computed column can be persisted using the PERSISTED column attribute.

-- ISNULL() is allowed in computed columns with Primary Key
CREATE TABLE T1 (
	col1 INT,
	col2 AS ISNULL(col1, 1) PRIMARY KEY)

-- COALESCE() is not allowed in non-persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PRIMARY KEY)
Error Message:
Msg 1711, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on column 'col2' in table 'T2'. 
The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
-- COALESCE() is only allowed as persisted computed columns with Primary Key
CREATE TABLE T2 (
	col1 INT,
	col2 AS COALESCE(col1, 1) PERSISTED PRIMARY KEY)

-- Clean up
DROP TABLE T1
DROP TABLE T2

MSDN BOL links:
ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx
COALESCE: http://msdn.microsoft.com/en-us/library/ms190349.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ISNULL_COALESCE

SQL Basics – Persisted and Non-Persisted Computed columns in SQL Server

October 2, 2009 Leave a comment

In SQL Server you have an option to create Calculated Columns in a table other than normal columns. By saying Normal columns I meant that their values are stored physically with the table. But with Calculated Columns their values are not stored physically, instead they are re-calculated every time whenever they are referenced.

A Computed Column is nothing but an expression, which can be a:

1. Non Computed column

2. Constant

3. Function

4. or any combination of above of these by using operators.

These type of Computed Columns are Non-Persisted in nature.
 

Persisted Computed Columns: You can also make Computed columns values stored physically by using the PERSISTED keyword. This will calculate the column values initially while inserting a row, or later if there is any change made to the column that is part of its expression.
 

Finally a Computed Column:

– is computed from an expression that can use other columns in the same table.

– can be a noncomputed column name, constant, function with one or more operators.

– cannot be a subquery.

– cannot be used as a DEFAULT or FOREIGN KEY constraint definition or with a NOT NULL constraint definition.

– used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED.

– can be used in SELECT list, WHERE clause, ORDER BY clause, etc.

– cannot be the target of an INSERT or UPDATE statement.
 

Check video on how to create Calculated Columns in a Table:

Computed Columns
 

–> SQL Script used in the above demo:

CREATE TABLE [dbo].[Employee] (
	[EmployeeID]	int,
	[EmployeeName]	nvarchar(100),
	[Gender]		nchar(1),
	[DOB]			datetime,
	[DOJ]			datetime,
	[DeptID]		int
)
GO

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('JHON K', 'M', '1970-03-03', NULL, NULL)

INSERT INTO [dbo].[Employee] (EmployeeName, DOB)
VALUES ('Brock H', '1980-02-02')
GO

SELECT * FROM [dbo].[Employee]
GO

ALTER TABLE [dbo].[Employee]
ADD AGE AS (DATEDIFF(YY, DOB, GETDATE()))
GO

ALTER TABLE [dbo].[Employee]
ADD XYZ AS 'nothing'
GO

SELECT * FROM [dbo].[Employee]
GO


CREATE TABLE dbo.Products (
    ProductID	int IDENTITY (1,1),
	Qty			smallint,
	Price		money,
	TotalValue AS (Qty * Price)
)

INSERT INTO dbo.Products (Qty, Price)
VALUES (5, 200)

INSERT INTO dbo.Products (Qty, Price)
VALUES (3, 150)

select * from dbo.Products
GO


ALTER TABLE dbo.Products
DROP COLUMN TotalValue

ALTER TABLE dbo.Products
ADD TotalValue AS (Qty * Price) PERSISTED
GO

-- Final Cleanup
DROP TABLE [dbo].[Employee]
DROP TABLE dbo.Products
GO