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