Amazing…see the results

—=======================
IF NULL = NULL
SELECT ‘EQUAL’;
ELSE
SELECT ‘NOT EQUAL’;
—=======================
GO
—=======================
Set Ansi_nulls off
IF NULL = NULL
SELECT ‘EQUAL’;
ELSE
SELECT ‘NOT EQUAL’;
Set Ansi_nulls on
—=======================

GO

—=======================
IF NULL is NULL
SELECT ‘EQUAL’;
ELSE
SELECT ‘NOT EQUAL’;
—=======================
GO
—=======================
IF ” = ”
SELECT ‘EQUAL’;
ELSE
SELECT ‘NOT EQUAL’;
—=======================

Delete the duplicate records using CTE (common table expression)

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’tmp_Student’) AND type in (N’U’))
DROP TABLE tmp_Student
GO
CREATE TABLE tmp_Student(stdID INT,stdName VARCHAR(50))
GO
INSERT INTO tmp_Student
SELECT 3,’AAA’ union all
SELECT 4,’BBB’ union all
SELECT 3,’QQQ’ union all
SELECT 5,’XXX’ union all
SELECT 3,’YYY’
GO
select * from tmp_Student –Result before the DELETE action
GO
WITH CTE (stdID, DupCount)
AS
(
SELECT stdID,
ROW_NUMBER() OVER(PARTITION BY stdID ORDER BY stdID) AS DupCount
FROM tmp_Student
)
delete
FROM CTE
WHERE DupCount > 1
go
select * from tmp_Student–Result after the DELETE action

Case sensitivity in SQL Server

create table tstTable(Header varchar(50))
go
insert into tstTable
select ‘Adventure Works’ union all
select ‘adventure works’ union all
select ‘ADVENTURE WORKS’ union all
select ‘adventure WORKS’

select Header
from tstTable
where  Header COLLATE SQL_Latin1_General_CP1_CS_AS  like ‘%adventure%’

drop table tstTable

Date series

create procedure DateSeries (@StartDate datetime,@EndDate datetime = ”,@dateDiff int = ”)
as
–===========================================================
–@StartDate is mandatory field, Either @EndDate or @dateDiff has to be null or ” always.
–but both can’t be null or ” as well as both can’t be assaigned simultaneously.
–Refer the following example
–exec DateSeries ‘01/18/2011′,’02/18/2011′,null
–exec DateSeries ‘01/18/2011′,null,7
–===========================================================
declare @varstartdate datetime
declare @varenddate datetime
declare @vardiff int

set @varstartdate = @StartDate
if (isnull(@EndDate,”) ” ) and (isnull(@dateDiff,”) = ”)
begin
set @varenddate = @EndDate
set @vardiff = DATEDIFF(d,@varstartdate,@varenddate)
end
–print @vardiff
if (isnull(@EndDate,”) = ” ) and (isnull(@dateDiff,”) ”)
begin
set @vardiff = @dateDiff
end

declare @s varchar(max)
set @s=’with numbers as
(
select 1 as Num, convert(datetime,”’ + convert(varchar,@varstartdate,101) + ”’) as vardate1
union all
select Num + 1, convert(datetime,”’ + convert(varchar,@varstartdate,101) + ”’) + Num from numbers where Num < ‘ + convert(varchar,@vardiff) + ‘
)
select convert(varchar,vardate1,101) from numbers option(Maxrecursion ‘ + convert(varchar,@vardiff) + ‘)’
execute (@s)

Follow

Get every new post delivered to your Inbox.