NULLIF

NULLIF is a function that compares two values.  If the two values are the same, then the result of the operation is a Null matching the datatype of the first expression.  If the two expressions are different, then the result is the value of the first expression.


Syntax NULLIF(expression , expression)


Example : 

WITH RandomNumber AS (
SELECT TOP 100 RowNumber = ROW_NUMBER()  OVER (ORDER BY (SELECT 1)), 
FirstValue = ABS(CHECKSUM(NEWID()))%10+1 ,SecondValue = ABS(CHECKSUM(NEWID()))%10+1FROM Master.dbo.SysColumns t1 CROSS JOIN Master.dbo.SysColumns t2)
 
SELECT RowNumbe, FirstValue, SecondValue, NULLIF(FirstValue,SecondVal) AS 'Null if Equal' FROM RandomNumber ORDER BY RowNumber

This us giving us a nice random sampling of values to compare. I simply compare the FirstValue column to the SecondValue column.  Both columns are populated with random numbers.  As a part of the result set, I am labeling the comparison field to something descriptive of the field.  I am returning all of the columns so I can see what the values are, and the result of the comparison.  This visualization can help to understand what is happening with the code.  Now I know that if I see a null value, then the two columns are equal.


We can combine NULLIF with COALESCE if you want to test for NULLS and Blanks for example


DECLARE @v VARCHAR(20)
    SELECT @v = ' '
    SELECT COALESCE(NULLIF(@v,' '),'N/A')
Here is another NULLIF example:
CREATE TABLE Blah (SomeCol VARCHAR(33))
    INSERT Blah VALUES(NULL)
    INSERT Blah VALUES('')
    INSERT Blah VALUES(' ')
    INSERT Blah VALUES('A')
    INSERT Blah VALUES('B B')
    --Using COALESCE and NULLIF
    SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
    FROM Blah
    --Using CASE
    SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
    WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
    ELSE SomeCol END SomeCol
    FROM Blah

Query and Stored Procedure Optimization

In a stored procedure each query has to be optimized. There is one performance feature with stored procedures which goes beyond queries: slow down due to parameter sniffing.

The principles of query optimization are quite simple:

1. Each FOREIGN KEY and WHERE clause predicate column should be indexed (PRIMARY KEY Columns are indexed automatically).

2. Don’t User functions with Column in Where Clause. If you use any function than Database Engine could not perform Index Seek on that Column.

3. Example for other indexing candidates: Group By Column in frequent / business critical query.

4. Use FILLFACTOR for dynamic tables; example FILLFACTOR 80 if table will grow 10% during the week (requires experimentation); FILLFACTOR 80 leaves 20% empty space for growth.

5. Assign clustered index (PK default is clustered index, but not a requirement.) to a column which is used in business critical range searches.

6.Index should be REBUILD on every weekend.

7. Examine the query/sproc to streamline it; eliminate potential overhead.

8. Examine the execution plan for ways to improve the query.

SQL Script to generate INSERT Statement

DECLARE @TableName sysName
DECLARE @WhereClause VarChar(1024)
DECLARE @ColumnList NVarChar(4000)
DECLARE @ColumnName SysName
DECLARE @ColumnType TinyInt
DECLARE @ColumnStatus TinyInt
DECLARE @DebugMode Bit
DECLARE @IdentityInsert Int
DECLARE @ValueList NVarChar(4000)
DECLARE @String1 NVarChar(1000)
DECLARE @String2 NChar(10)
DECLARE @String3 NChar(1000)
SET @TableName = '' -- Add table name within single quote 
SET @WhereClause = '' -- limit scope of inserts
SET @DebugMode = 0 -- SET to 1 if you only want a script
SET @IdentityInsert = 0 -- SET to 1 if you want to force IDENTITY_INSERT statements


SET @ColumnList = ''
SET @ValueList = ''
SET @String1 = 'SELECT REPLACE(''INSERT INTO ' + @TableName + ' ('
SET @String2 = ') VALUES ('
SET @String3 = ')'', ''''''null'''''', ''null'') FROM ' + @TableName
IF @DebugMode = 1 PRINT '-- StmtShell: ' + @String1 + @String2 + @String3
DECLARE curColumns CURSOR LOCAL FAST_FORWARD FOR
SELECT C.Name, C.XType, C.Status
FROM SysColumns C
INNER JOIN SysObjects O
ON O.ID = C.ID
WHERE O.Name = @TableName
AND O.XType IN ('U', 'S')
ORDER BY ColID


OPEN curColumns
FETCH NEXT FROM curColumns INTO @ColumnName, @ColumnType, @ColumnStatus
WHILE @@fetch_status = 0
BEGIN
SET @ColumnList = @ColumnList + ' ' + @ColumnName
IF @ColumnType IN (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165) -- numeric types (nulls not supported yet)
SET @ValueList = @ValueList + ' ''+CONVERT(VarChar(200),' + @ColumnName + ')+'''
ELSE IF @ColumnType IN (175, 239, 231, 231, 167) -- uid and string types


SET @ValueList = @ValueList + ' ''''''+ISNULL(' + @ColumnName + ',''null'')+'''''''


ELSE IF @ColumnType IN (58, 61) -- dates (nulls not supported yet)


SET @ValueList = @ValueList + ' ''''''+CONVERT(VarChar(200),' + @ColumnName + ')+'''''''


ELSE IF @ColumnType = 36 -- uniqueidentfiers (nulls not supported yet)


SET @ValueList = @ValueList + ' ''''{''+CONVERT(VarChar(200),' + @ColumnName + ')+''}'''''


IF @DebugMode = 1 BEGIN PRINT '-- @ValueList: ' + RTRIM(@ValueList) END 


IF (@ColumnStatus & 0x80) = 0x80 BEGIN SET @IdentityInsert = 1 END -- Check if column has Identity attribute


FETCH NEXT FROM curColumns INTO @ColumnName, @ColumnType, @ColumnStatus


END
CLOSE curColumns
DEALLOCATE curColumns


SET @ColumnList = REPLACE(LTRIM(@ColumnList), ' ', ', ')
SET @ValueList = REPLACE(LTRIM(@ValueList), ' ', ', ')
IF @IdentityInsert = 1
PRINT 'SET IDENTITY_INSERT ' + @TableName + ' ON'


IF @DebugMode = 1
PRINT @String1 + @ColumnList + @String2 + @ValueList + @String3 + ' ' + @WhereClause
ELSE
EXEC (@String1 + @ColumnList + @String2 + @ValueList + @String3 + ' ' + @WhereClause)


IF @IdentityInsert = 1
PRINT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'

Fill Factor

The fill-factor option is provided for fine-tuning index data storage and performance. When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.
The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Fill-factor values 0 and 100 are the same in all respects.

You can use the CREATE INDEX or ALTER INDEX statements to set the fill-factor value for individual indexes. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill-factor value of one or more indexes, use sys.indexes.
The fill-factor setting applies only when the index is created, or rebuilt. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages. Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.


Page Splits

A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table.When a new row is added to a full index page, the Database Engine moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. A page split makes room for new records, but can take time to perform and is a resource intensive operation. Also, it can cause fragmentation that causes increased I/O operations. When frequent page splits occur, the index can be rebuilt by using a new or existing fill-factor value to redistribute the data. 


Although a low, nonzero fill-factor value may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can decrease database read performance by an amount inversely proportional to the fill-factor setting. For example, a fill-factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.

Adding Data to the End of the Table

A nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table. However, if all the data is added to the end of the table, the empty space in the index pages will not be filled. For example, if the index key column is an IDENTITY column, the key for new rows is always increasing and the index rows are logically added to the end of the index. If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length

Function to Get age

Here is a function to compute the age of a individual, by passing Birth Date and As On Date.

CREATE FUNCTION [dbo].[fn_GetAge]

(

@DOB DATETIME ,

@AsOfDate DATETIME

)

RETURNS INT

AS

BEGIN

DECLARE @Age INT

IF @DOB >= @AsOfDate

RETURN 0

SET @Age = DATEDIFF(yy, @DOB, @AsOfDate)

IF MONTH(@DOB) > MONTH(@AsOfDate)

OR ( MONTH(@DOB) = MONTH(@AsOfDate)

AND DAY(@DOB) > DAY(@AsOfDate)

)

SET @Age = @Age – 1

RETURN @Age

END

How to Execute this Function:-

SELECT [dbo].[fn_GetAge] (’02/04/1983′,’03/09/2011′)