Amazing…see the results

Execute the following Query…!

– this will generate error

SELECT COUNT(NULL)

GO

–this will not generate error

DECLARE @A INT
SET @A=NULL
SELECT COUNT(@A)

why??

To find the hard coded value in procedures or functions

SELECT distinct object_name(o.id)
FROM syscomments c,sysobjects o
where c.id=o.id
and o.type in (‘P’ ,’FN’)–here ‘P’ for procedure and ‘FN’ for function
and o.category = 0
and c.text like ‘%Hard coded value goes here%’

 

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

To find the last executed statement in a session

Take a new session and run the following query. Here ‘where’ clause is optional.

‘=====================================================

SELECT session_id,text
FROM SYS.DM_EXEC_CONNECTIONS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(most_recent_sql_handle)
WHERE session_id = @@spid

‘=====================================================

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)