Updating serial number without using row number or identity

Updating serial number without using row number or identity

 

BEGIN TRAN

CREATE TABLE #EMP_TEMP(SNO INT,NAME VARCHAR(10))

INSERT INTO #EMP_TEMP SELECT NULL,'A'

INSERT INTO #EMP_TEMP SELECT NULL,'B'

INSERT INTO #EMP_TEMP SELECT NULL,'C'

INSERT INTO #EMP_TEMP SELECT NULL,'D'

INSERT INTO #EMP_TEMP SELECT NULL,'D'

INSERT INTO #EMP_TEMP SELECT NULL,'D'

INSERT INTO #EMP_TEMP SELECT NULL,'D'

DECLARE @no INT

SET @NO=0

 

update      #EMP_TEMP

set         SNO = @no,@no=@no+1

 

SELECT * FROM  #EMP_TEMP

 

ROLLBACK TRAN

 

Output

 

SNO	NAME
1	A
2	B
3	C
4	D
5	D
6	D
7	D