case sensitive searches....Script in T-SQL

When we are using where clause for conditional search sql using case insensitive and produce result.

But some time we need to search case sensitive then i show you some example for it...

First create one table.

CREATE TABLE [dbo].[checkName](

[Id] [int] IDENTITY(1,1) PRIMARY KEY,

[Cname] [varchar](100)

and execute above script.

then insert some records in above table.

insert into checkName (Cname) values('Anil')

insert into checkName (Cname) values('aNil')

insert into checkName (Cname) values('anIl')

insert into checkName (Cname) values('aniL')

insert into checkName (Cname) values('AnIl')

execute above script.

then execute below script for finding value of 'aniL'

select *

from checkName

where BINARY_CHECKSUM(Cname) = BINARY_CHECKSUM('aniL')

-------------------------------------------------------------------------------------------

select *

from checkName

where Cname = 'aniL' COLLATE SQL_Latin1_General_CP1_CS_AS

-------------------------------------------------------------------------------------------

select *

from checkName

where CAST(Cname AS varbinary(100)) = CAST('aniL' AS varbinary(100))

-------------------------------------------------------------------------------------------

Output will be....

Id Cname

4 aniL

Using ROW_NUMBER() to paginate your data ... Script in T-SQL

Here making paginating functionality by Sql Server side...

Here first of all set @totalPageSize, which set how many row should be display...

Then set @pageIndex , which set the pageindex,

declare @totalPageSize int

declare @pageIndex int

declare @startIndex int

declare @endIndex int

set @totalPageSize = 10

set @pageIndex = 1

set @startIndex = (@pageIndex * @totalPageSize) + 1

set @endIndex = @startIndex + @totalPageSize

select top (@totalPageSize) z.*

from

(

select

ROW_NUMBER() OVER(ORDER BY userId DESC) AS 'RowNumber',

userId, userFirstName, userLastName,

userAddress1, userAddress2, userPhone1,

userPhone2

from [userInfo]

) as z

where z.RowNumber between @startIndex and @endIndex

RowNumber userId userFirstName userLastName userAddress1 userAddress2 userPhone1 userPhone2

11 45 A B sdf sdf 34343

12 44 G W ds ds 1121

13 43 K T Ahd Ahd 121 12

14 42 T Y Ahd Ahd 12323 34234

17 38 ehilly Bons Ahmedabad

18 37 dhilly Bons Ahmedabad

19 36 chilly Bons Ahmedabad

20 35 bhilly Bons Ahmedabad

Finding nth highest values... Script in T-SQL

The following script will gives you the nth highest values.

It will find nth highest salary from employee table

which has empid,salary fields.

Set value of @nthhighest variable to whatever number you want

to assign. Result will be according to the specified value.

declare @nthhighest int

set @nthhighest = 5

select * from employee order by salary desc

select top 1 salary from 

(

select top (@nthhighest) salary from employee

order by salary desc

) as employee

order by salary 

SELECT max(salary) FROM employee WHERE salary NOT IN 

(

SELECT top (@nthhighest - 1)  salary FROM employee order by salary desc

)

Delete duplicates rows of table when there is no any unique column exist Script in T-SQL

Here when there are many duplicates row in table and

table do not have any unique columns

example.

suppose one table 'test' have three column.

1.tid 

2.tname 

3.tcity

and this table contain records like.

tid tname tcity

1 A G

1 A G

1 A G

1 A G

2 B B

2 B B

3 C V

3 C V

3 C V

3 C V

3 C V

Here when group by query fire like.

select tid,tname,tcity

from test

group by tid,tname,tcity

output of above query is.

tid tname tcity

1 A G

2 B B

3 C V

So here total three records exists

So when u want to delete duplicats records

from this type of table, i show i idea for it

like..

create table test

(tid int not null,

tname varchar(50) not null,

tcity varchar(50) not null

)

go

go

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(1,'A','G')

insert test values(2,'B','B')

insert test values(2,'B','B')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

insert test values(3,'C','V')

go

Here i show u one query which delete duplicats rows

where there is not any unique key..

 

 

delete   maindata

from 

(

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as maindata

where maindata.rowid not in

(

select

rowid

from (

select ROW_NUMBER() OVER (ORDER BY tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as datawithRow

where rowid in 

(

select

top 1 rowid

from (

select ROW_NUMBER() OVER (ORDER BY  tid,tname,tcity DESC) as rowid,

itt.tid,itt.tname,itt.tcity

from ttest itt

) as idatawithRow

where

idatawithRow.tid = datawithRow.tid and

idatawithRow.tname = datawithRow.tname and

idatawithRow.tcity = datawithRow.tcity

) and

datawithRow.tid = maindata.tid and

datawithRow.tname = maindata.tname and

datawithRow.tcity = maindata.tcity

)

----------------------------------------------------------------------------------------------

After executing delete query

select tid,tname,tcity

from test

group by tid,tname,tcity

Output will be...

tid tname tcity

1 A G

2 B B

3 C V

Split Function Script in T-SQL

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE FUNCTION [dbo].[SplitString]

(

@str varchar(100),

@delchar varchar(1)

)

RETURNS @mytbl table(val varchar(100)) 

AS

begin

declare @tstr varchar(100)

declare @i int

declare @j int

declare @strlen int

declare @charat varchar(1)

set @strlen = len(@str)

set @i = 1

set @tstr = ''

while(@i <= @strlen)

begin

set @charat = substring(@str,@i,1)

if(@charat <> @delchar)

set @tstr = @tstr + @charat

else

begin

insert into @mytbl values(@tstr)

set @tstr = ''

end

set @i = @i + 1

end

insert into @mytbl values(@tstr)

return

end

----------------------------------------------------------------------------------------------

To use above function write:

select * from SplitString('anil,tejas,nilesh,paresh,yashesh,manish',',')

Output will be...

anil

tejas

nilesh

paresh

yashesh

manish

Use of With...As clause Script in T-SQL

First create one table for employee..

USE [test]

GO

/****** Object:  Table [dbo].[employee]    Script Date: 07/12/2008 14:03:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[employee](

[empid] [int] NOT NULL,

[empname] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,

[parentid] [int] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

then enter some records in above table...

1 Tejas 0

2 Anil  3

3 Paresh 5

4 Manish 1

5 Lafdo 4

6 Yashesh 2

-----------------------------------------------------------------------------------------------------------------

then execute below query to find upper-level parents and low-level parents...

select * from dbo.employee;

with RecursionCTE (empid,empname,parentid,upperlevel)

 as

 (

   select empid,empname,parentid,

          convert(varchar(100),'') upperlevel

      from dbo.employee

      where parentid = 0

   union all

   select R1.empid,R1.empname,

          R1.parentid,

          case when DataLength(R2.upperlevel) > 0

                    then convert(varchar(100),(select empname from dbo.employee where empid = R1.empid)

       + ' -> '+ R2.upperlevel )

                    else (select empname from dbo.employee where empid = R1.empid)

                    end as upperlevel

      from dbo.employee as R1

     join RecursionCTE as R2 on R1.parentid = R2.empid

  )

select empid,empname,parentid,upperlevel from RecursionCTE order by empid asc ;

with RecursionCTE (empid,empname,parentid,lowerlevel)

 as

 (

   select parentid,empname,empid,

          convert(varchar(100),'') lowerlevel

      from dbo.employee

   union all

   select R1.empid,R1.empname,

          R1.parentid,

          case when DataLength(R2.lowerlevel) > 0

                    then convert(varchar(100),(select empname from dbo.employee where empid = R1.empid)

       + ' -> '+ R2.lowerlevel )

                    else (select empname from dbo.employee where empid = R1.empid)

                    end as lowerlevel

      from dbo.employee as R1

     join RecursionCTE as R2 on R1.empid = R2.parentid

  )

select empid,empname,parentid,max(lowerlevel) as lowerlevel

from RecursionCTE 

where datalength(lowerlevel) > 0

group by empid,empname,parentid order by empid asc 

-----------------------------------------------------------------------------------------------------------------

Output will be...

All records of above table..

1 Tejas 0

2 Anil 3

3 Paresh 5

4 Manish 1

5 Lafdo 4

6 Yashesh 2

All records with upper-level parents..

1 Tejas 0

2 Anil 3 Anil -> Paresh -> Lafdo -> Manish

3 Paresh 5 Paresh -> Lafdo -> Manish

4 Manish 1 Manish

5 Lafdo 4 Lafdo -> Manish

6 Yashesh 2 Yashesh -> Anil -> Paresh -> Lafdo -> Manish

All records with low-level parents..

1 Tejas 0 Tejas -> Manish -> Lafdo -> Paresh -> Anil -> Yashesh

2 Anil 3 Anil -> Yashesh

3 Paresh 5 Paresh -> Anil -> Yashesh

4 Manish 1 Manish -> Lafdo -> Paresh -> Anil -> Yashesh

5 Lafdo 4 Lafdo -> Paresh -> Anil -> Yashesh

6 Yashesh 2 Yashesh

How Many Sunday In Give Month & Year Script in T-SQL

Here this script calculate total Sunday in give month and give year @month = 6 and @year = 2008.


declare @year int
declare @month int
declare @totaldays int
declare @isLeapYear bit
declare @firstDayofmonth varchar(20)
declare @date varchar(20)
set @isLeapYear = 'false'
--------------------------------------------------
--- Here change year and month for diff result
--------------------------------------------------
set @year = 2008
set @month = 6
--------------------------------------------------
set @totaldays = 31
set @date = cast(@year as varchar) + '-' + cast(@month as varchar) + '-01'
if(@year % 4 = 0)
set @isLeapYear = 'true'
if(@year % 100 = 0)
if(@year % 400 = 0)
set @isLeapYear = 'false'
if(@month = 1) set @totaldays = 31
if(@month = 2 )
begin
if(@isLeapYear = 'true') set @totaldays = 29
else set @totaldays = 28
end
if(@month = 3) set @totaldays = 31
if(@month = 4) set @totaldays = 30
if(@month = 5) set @totaldays = 31
if(@month = 6) set @totaldays = 30
if(@month = 7) set @totaldays = 31
if(@month = 8) set @totaldays = 31
if(@month = 9) set @totaldays = 30
if(@month = 10) set @totaldays = 31
if(@month = 11) set @totaldays = 30
if(@month = 12) set @totaldays = 31
set @firstDayofmonth = DATENAME(dw,@date)
print 'Given Year : ' + cast(@year as varchar)
print 'Given Month : ' + cast(@month as varchar)
print 'Total days : ' + cast(@totaldays as varchar)
print 'First day is : ' + @firstDayofmonth

declare @i int
declare @cnt int
declare @currday varchar(20)
set @i = 1
set @cnt = 0
set @currday = DATENAME(dw,@date)
while(@i <= @totaldays)
begin
if(@currday = 'Sunday') set @cnt = @cnt + 1
set @currday = DATENAME(dw,dateadd(dd,1,@date))
set @date = dateadd(dd,1,@date)
set @i = @i + 1
end
print 'Total Sunday in give month are : ' + cast(@cnt as varchar)

Output will be...


Given Year : 2008
Given Month : 6
Total days : 30
First day is : Sunday
Total Sunday in give month are : 5