Deleting duplicate rows when there is no primary key

set nocount on
create table #tbFruit (Fruit varchar(20))
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘orange’)
insert into #tbFruit (Fruit) values (‘orange’)
insert into #tbFruit (Fruit) values (‘orange’)
create table #tbFruitsWithDupes (DupeID int identity, Fruit varchar(20), DupeCount int)
insert into #tbFruitsWithDupes (Fruit, DupeCount)
 select [...]

Current identity value is reset after delete the record

Create procedure DeleteTest(@ID int)
as
begin
 declare @cnt int 
 
 – delete current record
 delete from newTest where id=@ID
 – reset identity value to the total records in the table
 select @cnt = isnull(max(ID),0) from newTest
 DBCC CHECKIDENT ( ‘dbo.newTest’, RESEED, @cnt) 
————-
For your reference  http://msdn2.microsoft.com/en-us/library/ms176057.aspx
 

what’s the maximum size varbinary in Sql Server ?

In Microsoft SQL Server 2000 and earlier versions, the varbinary data type had a maximum limit of 8,000 bytes. To store up to 2 GB of binary data the image data type needs to be used instead. With the addition of MAX in SQL Server 2005, however, the image data type has been deprecated. It’s [...]

To find out the Nth highest salary in a table

select top 1 * from emp where empid not in (select top N-1 empid from emp order by empsalary desc) order by empsalary desc