## 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 ... */ ;
``````

The sequence number function should not be called twice with same arguments in single statement. SELECT Sequence_Number(1 ,1) as Col1, Sequence_Number(1 ,1) as Col2.... As each time a call is made to function it will add the value which will produce unexpected results. Correct method call require unique arguments, it does have collusion prevention after initial call. Proper method is SELECT Sequence_Number(1 ,1) as Col1, Sequence_Number(2 ,1) as Col2, notice different parameter values.
```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... */ ; ```
The function is extensively used in calculation and projection of value over the period of time. Such as discounts, gains as percentage or increase and decrease of value in numbers.