Tuesday, May 8, 2012

How to delete duplicate rows in single statement

Suppose we have a table named DuplicateData in which same row with Id and name exixts more than one.
then we have to delete those rows using this:

WITH T1 AS (Select Id,FirstName,LastName, ROW_NUMBER() OVER (PARTITION BY Id,FirstName,LastName Order By Id) AS NUMBER From DuplicateData )Delete From T1 Where Number >1 Go

Tuesday, December 20, 2011

IS NULL V/S =NULL In SQL Server

When a variable is created in SQL Server with the declare statement it is created with no data and stored in the variable table inside memory space. The variable table contains the name and memory address of the variable. However, when the variable is created no memory address is allocated to the variable and thus the variable is not defined in terms of memory.

When you SET the variable it is allotted a memory address and the initial data is stored in that address. When you SET the value again the data in the memory address pointed to by the variable is then changed to the new value.

“= NULL”

“= NULL” is an expression of value. Meaning, if the variable has been set and memory created for the storage of data it has a value. A variable can in fact be set to NULL which means the data value of the objects is unknown. If the value has been set like so:

DECLARE @dmyval CHAR(4)

SET @ dmyval = NULL

You have explicitly set the value of the data to unknown and so when you do:

If @ dmyval = NULL

It will evaluate as a true expression.

But if I do:

DECLARE @ dmyval CHAR(4)

If @ dmyval =  NULL

It will evaluate to false.

The reason for this is the fact that I am checking for NULL as the value of @ dmyval. Since I have not SET the value of @ dmyval no memory address has been assigned and therefore no value exists for @ dmyval.

 “IS NULL”


Now “IS NULL” is a little trickier and is the preferred method for evaluating the condition of a variable being NULL. When you use the “IS NULL” clause, it checks both the address of the variable and the data within the variable as being unknown. So if I for example do:

DECLARE @ dmyval CHAR(4)

If @ dmyval IS NULL
            PRINT ‘TRUE’
ELSE
            PRINT ‘FALSE’

SET @ dmyval = NULL

If @ dmyval IS NULL
            PRINT ‘TRUE’
ELSE
            PRINT ‘FALSE’


Both outputs will be TRUE. The reason is in the first @ dmyval IS NULL I have only declared the variable and no address space for data has been set which “IS NULL” check for. And in the second the value has been explicitly set to NULL which “IS NULL” checks also.