Lots of SQL Server 2012 interview questions are making circles in interview now a days and this is one of them. Do also watch our SQL Server interview question video on UNION VS UNION ALL
A sequence object generates sequence of unique numeric values as per specifications. Many developers would have now got a thought we have something similar like this called “Identity” columns. But the big difference is sequence object is independent of a table while identity columns are attached to a table.
Below is a simple code to create a sequence object. You can see we have created a sequence object called as “MySeq” with the following specification:-
•Increments with value 1
•Minimum value it should start is with zero
•Maximum it will go to 100.
•No cycle defines that once it reaches 100 it will throw an error. If you want to restart it from 0 Starts with value 1.
•you should provide “cycle”. •“cache 50” specifies that till 50 the values are already incremented in to cache to reduce IO. If you specify “no cache” it will make input output on the disk.
To increment the value we need to call the below select statement. This is one more big difference as compared to identity.In identity the values increment when rows are added here we need to make an explicit call.
This is one more SQL Server interview question asked around referential integrity.
http://dotnetinterviewquestion.wordpress.com/2011/11/07/sql-server-interview-questions-explian-referential-integrity-in-sql-server/
The above question is taken from the book SQL Server interview question which is published by BPB publication
