Monthly Archives: April 2012
SQL Saturday #118 | Madison, WI on Apr 21st 2012, definitely ranks amongst the most rocking SQL Saturdays I have attended ! Jes Borland [blog|twitter] and team produced a top-tier event for both speakers and attendees alike. This being their first SQL Saturday, I feel very fortunate to have been chosen as a speaker and present my session on Service Broker. Taking a 6 hour road trip on Friday evening, I rolled into Madison with the St.Louis contingent (Kim Tessereau [blog|twitter], Mike Lynn [twitter] & Jay Carter). It was too late to join the speakers’ dinner, but we did have an awesome dinner at a Hibachi grill !
On the morning of Apr 21st, we found our way into the Madison Area Technical College and were pleasantly welcomed at the registration desk – with no lines ! The SpeedPASS worked out really well for the event, and they also had a printer at the registration desk to help attendees print one. There were plenty of volunteers helping attendees find their way around the venue, and the breakfast was great. All of this made a great first impression – very well done !
Bill Fellows [blog|twitter] kicked off the event with an awesome session on TSQL sweets in SQL Server 2012, followed by an equally good session on SSIS 2012 new features by Norman Kelm [blog|twitter]. Next up, was my interview with Karla Landrum [blog|twitter], at the PASS booth, for the upcoming SQL Saturday event in St.Louis. It worked out for the best, to have a face to face discussion with Karla, because we ended up talking for over an hour and I learned so much more about the makings of a successful event. We have moved one step closer to hosting the very first SQL Saturday in St.Louis, in the Fall of 2012!
I hosted the Service Broker “Cows of a Spot” lunch table and it turned out to be an exciting opportunity to network with fellow Service Broker enthusiasts. The delicious baked beans, burgers, as well as the the efficient crowd management skills of the volunteers deserve a special mention. The next session on my list was about Filegroups. Jes made an impressive presentation (LEGOs were involved !) about how Filegroups help with Performance and Management of your database. Thanks to her presentation, I have learned the skill of Piecemeal Restore of selected Filegroups .
I took a short break from SQL Learning in the Speakers’ Lounge to brush up on my presentation . I then picked up my shiny new speaker’s shirt and settled down to review my presentation material. After dotting the i’s and crossing the t’s I headed up to my session (last session for the day). I was pleasantly surprised to see a few familiar faces in the audience. This was only the second time I presented my Service Broker session at a SQL Saturday, and I really appreciate all the support and positive feedback I got from the audience ! One of the very useful pieces of information I learned from the Q&A with the audience was the idea of using Extended events to troubleshoot service broker applications, since Microsoft announced deprecation of SQL Server profiler in a later version.
The closing ceremonies followed and I am really happy for all the winners of the raffle prizes from the event’s generous sponsors. After a big round of applause for the speakers, volunteers/organizer and the sponsors, the St.Louis contingent hit the road for the long ride back home.
Having worked with Oracle databases during some portion of the last decade, I was introduced to the Sequence Object. Since then, I had been especially missing this feature in SQL Server. But my wait is over with its introduction in the release of SQL Server 2012!
A sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table.
Before we explore the sequence object in more detail, here is a brief summary of the difference between identity and sequence.
Sequence is Table independent while Identity is Table Specific
Unlike Identity, you can obtain the new value from a Sequence before using it in a SQL Statement
New value for a Sequence can be generated for both UPDATE as well as INSERT statements
You can define the minimum & maximum values as well as cycling & cache size options for a Sequence
Unlike Identity, a sequence object will not auto increment without any reference to it
You can create a sequence by using the T-SQL Create Sequence statement or in SQL Server Management Studio by navigating through the Object Explorer to Your Database > Programmability > Sequences, Right click and Create New Sequence.
Let’s look at a simple T-SQL statement for creating a basic sequence;
CREATE SEQUENCE dbo.demo_2012_sequence AS INT START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 CACHE 20 CYCLE;
Some of the interesting points to note here are;
- The MINVALUE and MAXVALUE should fall within the range of the data type of the sequence object
- The INCREMENT BY can be positive (makes an ascending sequence) or negative (makes a descending sequence), but not Zero (0)
- CYCLE – Specifies if the sequence should restart when the maximum value ( minimum value for a descending sequence) has been reached
- CACHE – Can improve performance by pre-allocating the number of sequence numbers specified, thus minimizing disk IOs. Note that an unexpected shutdown may result in the loss of values remaining in cache.
Once this sequence object has been created, you can use the Next Value For function, to generate a sequence number from it. It is a non-deterministic function and can be used in stored procedures and triggers. When using it in a query, please be aware of the limitations and restrictions on its usage; the most notable one being the restriction on using it in queries that contain SET Operators like UNION, UNION ALL, etc. It can also be used in a Default Constraint as well as an OVER ORDER BY Clause. A complete list of the restrictions and limitations on its usage is available here.
You can get one or more values out of the sequence object. You can also ALTER the sequence object to RESTART it, or change many of its properties. Let’s look at some examples.
- Example 1 – Get one value out of the Sequence Object at a time
-- get one value out of the sequence object at one time SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no; GO SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS next_seq_no; GO
And the results look like:
seq_no ----------- 1 (1 row(s) affected) next_seq_no ----------- 2 (1 row(s) affected)
- Example 2- Restart the sequence and get multiple values out of the sequence object at one time
-- creating a table and populate it with 5 rows CREATE TABLE #demo_sequence (demo_name VARCHAR(12)); INSERT INTO #demo_sequence VALUES ('row_1'), ('row_2'), ('row_3'), ('row_4'), ('row_5'); GO -- Restart the sequence from 1 ALTER SEQUENCE dbo.demo_2012_sequence RESTART WITH 1 INCREMENT BY 1; GO -- get 5 values out of the sequence object at 1 time SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no, demo_name FROM #demo_sequence; GO
And the results look like:
seq_no demo_name ----------- ------------ 1 row_1 2 row_2 3 row_3 4 row_4 5 row_5 (5 row(s) affected)
You can also use the system object sys.sp_sequence_get_range to generate and return a number of values, along with some of the metadata related to the range.
The sys.sequences view can also be used to query metadata for a sequence object. It contains one row for each sequence object in the database.
SELECT current_value, cache_size, is_cached, create_date, is_exhausted FROM sys.sequences WHERE name = 'demo_2012_sequence'; GO
And the results look like:
current_value cache_size is_cached is_exhausted --------------- ----------- ----------- ------------ 5 20 1 0 (1 row(s) affected)
In conclusion, the new Sequence Object can provide a viable alternative to Identity, and is definitely worth exploring for your next project.