I finally found some time to have a look at SQL Server 2012. There is still a lot to discover but I already want to highlight 2 great new features:
Sequence Objects
It’s a feature I already know from Oracle, but now SQL Server finally supports Sequence Objects too. A sequence object generates sequence of unique numeric values. It’s a nice alternative if you don’t want to use Identity fields or want to get a unique number up front.
Here is a sample that creates a sequence that starts with 1 and gets incremented by 1. The minimum value is 0 and maximum value is 100. If you go higher that 100 an error is thrown(if you don’t want this you can change this by specifying ‘cycle’ instead of ‘no cycle’.
| create sequence SampleSequence as int | |
| start with 1 -- Start with value 1 | |
| increment by 1-- Increment with value 1 | |
| minvalue 0 -- Minimum value to start is zero | |
| maxvalue 100 -- Maximum it can go to 100 | |
| no cycle -- Do not go above 100 | |
| cache 50 -- Increment 50 values in memory rather than incrementing from IO |
To increment the value we need to call the statement below:
| SELECT NEXT VALUE FOR dbo.SampleSequence AS seq_no; |
Pagination
Another nice features that got added to SQL Server 2012 is the built-in support for pagination. Before I had to combine “ROW_NUMBER and “TOP” to get a subset of results. Now I can use “OFFSET” and “FETCH’ commands to do pagination.
To skip the first 10 results and take the next 10 results from a Products table, I can write the following query:
| SELECT * FROM PRODUCTS | |
| ORDER BY PRODUCTNAME | |
| OFFSET 10 ROWS | |
| FETCH NEXT 10 ROWS ONLY |