SQL Server 2012 - Binding Sequence to a Column‏

Continuing my series on SQL Server 2012, today we will learn about Sequence which is an object in SQL Server 2012 and can be used to generate customized sequence numbers. Although it is independent of objects, however an object can bind it. In this post, we will see how to use that as default value for a column

Create a sequence named my_seq

create sequence my_seq
    as int
    start with 1
    increment by 1


Create a table in which one of the columns has a default value of my_seq

create table testing (col1 int, col2 int default next value for my_seq)

Now add some data to the table

insert into testing (col1)
select 34 union all
select 6

Select data from the table and see what col2 returns

select * from testing 


Col2 returns unique numbers. It should be noted that if the sequence is used by many objects, the value may not be sequential i.e. some values may be used somewhere else.

This way we can use sequence object to generate unique numbers like an identity column

OFFSET FETCH in SQL Server 2012

I have been exploring the new OFFSET-FETCH filter introduced in SQL Server 2012 and find it quite useful. In simple words, the OFFSET and FETCH clauses give you the capability to implement a paging solution by specifying the starting and ending set of rows to return.

Madhivanan has written a nice article on OFFSET-FETCH introducing this new feature over here: Pagination with OFFSET and FETCH NEXT in SQL Server 2012

In this article, I will list down some important points to consider while working with the OFFSET-FETCH clause in SQL Server 2012
  • OFFSET indicates the number of rows to skip, FETCH indicates the rows to return after the skipped rows
  • The TOP clause in SQL Server is similar to the FETCH clause except that it does not have skipping capability.
  • However TOP supports PERCENT and WITH TIES, but OFFSET-FETCH does not.
  • Any query that uses OFFSET-FETCH must also have an ORDER BY clause
  • You can use OFFSET without FETCH, but not the opposite. With every FETCH clause, you need OFFSET clause
  • The Filter has a singular and plural support. For example to fetch one row, you can use FETCH 1 ROW. Similarly to FETCH more than 1, you can specify the plural ROWS
Check out an OFFSET-FETCH example

Parsename to Extract Precision and Scale values‏ From Decimal Numbers

The numeric datatype stores numbers with precision and scale. Suppose you want to extract only a precision or a scale, you can do it via many ways. One of the ways is to make use of the PARSENAME function.

Consider the following example

declare @amount decimal(12,2)
set @amount=87234.50
select parsename(@amount,2) as precision, parsename(@amount,1) as scale

The result is

precision            scale
---------            --------
87234                  50

Parsename is used to extract specified part of a name. In general, it is used to extract names from four part object names separated by a dot. Argument number 1 extracts the last part of a string, and the 2nd argument extracts the next last part.

This way we can effectively make use of parsename function to extract precision and scale values from the decimal numbers.