New features in Sql server 2012
Sequences
Sequences are introduced in sql server 2012 which are similar to sequences in oracle.
We can associate sequences to a column in the table. Then a question came into my mind we have already identity in sq server. Why we need another object to have auto increment column?
Sequences have following advantages over Identity
There are not related to particular table.
The are not stored in disk. They will come from memory ,So they are faster than identity
We will have control over sequences like max value, min value starting value and restart value
which are not feasible in case of identity
With out inserting value value we can get next sequence value (Identity value will be associated once column is inserted into table other wise we cant get identity of that column)
We can set default value for a column using NEXT VALUE FOR function
We can insert multiple sequence values at once.
All in all we can say it gives more performance than identity based columns
Ad-Hoc Query Paging: Using a combination of OFFSET and FETCH along with ORDER BY we can have control of paging through a result set.
For suppose we have grid view with pagination we can use this ad-hoc query paging for better perfomance
Sequences are introduced in sql server 2012 which are similar to sequences in oracle.
We can associate sequences to a column in the table. Then a question came into my mind we have already identity in sq server. Why we need another object to have auto increment column?
Sequences have following advantages over Identity
There are not related to particular table.
The are not stored in disk. They will come from memory ,So they are faster than identity
We will have control over sequences like max value, min value starting value and restart value
which are not feasible in case of identity
With out inserting value value we can get next sequence value (Identity value will be associated once column is inserted into table other wise we cant get identity of that column)
We can set default value for a column using NEXT VALUE FOR function
We can insert multiple sequence values at once.
All in all we can say it gives more performance than identity based columns
Ad-Hoc Query Paging: Using a combination of OFFSET and FETCH along with ORDER BY we can have control of paging through a result set.
For suppose we have grid view with pagination we can use this ad-hoc query paging for better perfomance