Sequence_Number Function

The Sequence_Number() function generates a sequence number with each row like row number. For just row numbers use RowId builtin column. The sequence number takes two (2) to three (3) agruments and generates a number with each row. When you pass two arguments the First argument is the number to start with and second is increment by. e.g. (1 , 1 ) will start with 1 and increment by 1 i.e 1, 2, 3, 4... . The three argument can be writte in english as In this number keep adding this number... e.g. (1 , '+' , 1)

The valid values for [Optional Operation Type ] are : "+" , "-" , "/" , "*" , "+%" , "-%", "/%", "*%" , "+%r" ,"-%r" ,"/%r" , "*%r". The last +% is sign and % sign , it means that instead of using the third argument as number treat it as percentage. e.g. Sequence_Number(100 , '+%' , 2).. the SQLEngine will calculate 2 % of 100 and then start adding it. When you use ..%r it stands for repeat the percentage calculation on each row. e.g. Sequence_Number(100 , '+%r' , 2).. first calculate percentage on 100 then on 98 and so on... keep repeating as long as there are rows.

Syntax Sequence_Number(Start Number , Increment By Number).

Syntax Sequence_Number(Start Number , [Optional Operation Type ] , Increment By Number).

SELECT Sequence_Number(1 ,1) as RowNumber /* generates 1 2 3 4 ... */ ;

SELECT Sequence_Number(10 ,15) /* generates 10, 25, 40.. */ ;

SELECT Sequence_Number(10 , -1) as RowNumber /* generates 10 9 8 7 ... */ ;
    

SELECT Sequence_Number(100, '-%', 2) AS Percent1 /* generates 100 98 96 94... */ ;

SELECT Sequence_Number(100, '-%r', 2) AS Percent1 /* generates 100 96.04 92.236816 88.584... */ ;