Microsoft .NET Framework Tutorial Video for beginners - Part 1

Insert multiple null values in Unique key index column

hello friends,

 

Here i am sharing scripts which can be useful to us in our developement

 

CREATE TABLE [dbo].[testUnique](

      [col1] [varchar](50) NULL,

      [ID] [int] IDENTITY(1,1) NOT NULL,

) ON [PRIMARY]

 

CREATE UNIQUE INDEX IX_testUnique ON testUnique(col1)

WHERE col1 IS NOT NULL –-Here we can set any value to which we want to allow multiple value

 

Insert into testUnique(col1)

values ('a'),(null),(null)

Custom Paging in gridview in Sql server 2000

Declare @PageSize int,
@Page int,
@Segment varchar(10)

Set @PageSize=100
set @Page=3
Set @Segment='N'

---Logic to Find Total pages---
Declare @TPages int
Select @TPages=Case When Count(*)<=@PageSize Then 1 when (Count(*)%@PageSize)!=0 then (Count(*)/@PageSize)+1 Else (Count(*)/@PageSize) End
from tblname where Segment=@Segment
Print @TPages

-----Logic to Get Rows as per selected Page------
SET ROWCOUNT @PageSize
Declare @Sql varchar(8000)
set @sql='Select * from
(
      Select top '+Convert(varchar,(@Page*@PageSize))+' * from tblname where Segment='''+@Segment+'''
      order by ID
)a
order by ID desc'

Exec(@Sql)
SET ROWCOUNT 0