As we traverse through different versions of SQL Server, there are new enhancements that always make us learn for newer techniques. One such addition to SQL Server 2012 is called as Sequences. Sequences are just like a running number like Identity but are not attached to any table. In a nutshell, sequences generate a number which can be used anywhere agnostic of the table under question. You can learn more about the fine prints about SEQUENCE from MSDN. In this blog post, let us look at some interesting trivia’s around using SEQUENCES.
Knowing your Limits
The first thing to know is the limits that a sequence can get to when used inside a server. The maximum number is completely based on the data type used for the sequence. Some of the most common datatypes used are:
-
Int (Integer) – Max value achieved is 2,147,483,647
-
BigInt – Max value achieved is 9,223,372,036,854,775,807
That is one big number to count by any means. I am sure you are still not done counting the number of digits using BigInt.
MINVALUE can be different
The value of MINVALUE can be user defined or left to defaults. For most cases, we have seen administrators and developers use the default value while working with sequences. There are some interesting numbers to keep a note of.
-
TinyInt – minimum value will start from 0
-
Int – values start from -2,147,483,648
And so on. Hence if the defaults are taken and the sequence is to cycle. Then as developers or DBAs we must know the negative values that will be shown using sequences.
Since minimum values can be negative, the increments can also be negative and this is a great way to have values that are reducing in value when compared to the start values.
Knowing the Cache Value
Sequences can be used to cache values as part of their definition. This is an efficient way to preallocate the values in-memory as the sequence values are requested. Though this is a performance improvement mechanism, incase the SQL Server instance is stopped abruptly, there can be a break in the sequence of numbers generated or loss of numbers. So use this option with care. We can check the cache usage using the below DMVs.
SELECTcache_size,current_value
FROMsys.sequences
WHEREname=‘<<Seq-Name>>’
DMVs always have a wealth of information inside SQL Server and this DMV is no different. If you want to know more details of the sequence created, we can get them from other columns from cache, max values, min values, current values, cycling, system used, is_exhausted and many more.
Conclusion
As we sign off this blog post, we would like to bring to notice yet another important SP that can be used to know the range of values used for sequence. This stored procedure is sp_sequence_get_range. We would love to hear from you on your usage scenarios for sequences in your production environments. You may find this Debugging Techniques with SQL Server Whitepaper useful to help increase your productivity.
Learn more about Embarcadero DBArtisan , the premiere database administration toolset helping DBAs maximize availability, performance and security across multiple DBMSs. And try DBArtisan for free.