Category Archives: SQL Server 2012

Studying for MCSA: SQL Server 2012 – A 90 Day Journey


 With only a 9 weeks left until the end of the year, I figured I should start getting serious about meeting some of my goals for 2013. I did successfully complete my first goal for 2013 – speaking at the PASS Summit last week. My second goal for the year is to achieve the MCSA: SQL Server 2012 certification.  Since I am Microsoft Certified (MCTS) on SQL Server 2008, I will be taking the two exams to transition to MCSA SQL Server 2012 – the 70-457 & the 70-458, instead of the usual 3 exams. However, in the absence of any training materials specifically geared towards these transition exams, I am using the standard training kit from Microsoft Press, to augment my real work experience on SQL Server 2012. The Microsoft Press training kit consists of 3 books, 1 each for exam 70-461, 70-462 & 70-463. These training kits are available on Amazon .  I also happen to have a subscription to PluralSight’s training library, which has over 8 hours of training videos for exam 70-461 by christopher Harrison and over 16 hours of training videos for exam 70-462 by Sean McCown .

I am hoping that the combination of these two learning resources will help me prepare for the MCSA: SQL Server 2012 exam in about 90 days ( just over 12 weeks). My training schedule is inspired by Microsoft’s “90 Days to MCSA: SQL Server 2012 Edition” program. I intend to continue writing a weekly blog post that documents my learning and progress towards achieving the MCSA: SQL Server 2012 Certification. Hope to hear a lot of feedback and support from #sqlfamily on this journey !


SQL Server 2012 – What’s new in Service Broker?

Service Broker has come a long way since its’  introduction in Microsoft SQL Server 2005. SQL Server 2008 brought a host of Service Broker enhancements like conversation priorities, the ssbdiagnose utility, alternate poison message handling and new performance objects & counters. SQL Server 2012 is a major release that comes with an astonishing  array of enhancements and new features, including  new bells & whistles for Service Broker.

A significant new feature for Service Broker is the ability to Multicast messages. This enables a single initiator service to send messages to multiple target services, akin to SQL Server Replication, where multiple subscribers can subscribe to the same publication. The syntax of the send command has been extended to enable multicasting , by allowing multiple conversation handles.

 @TestMsgBody XML ;

SET @TestMsgBody = '<test>Test Message</test>' ;

FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService1'
ON CONTRACT [//TestProcessing] ;

FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService2'
ON CONTRACT [//TestProcessing] ;

FROM SERVICE [//InitiatorService]
TO SERVICE '//TargetService3'
ON CONTRACT [//TestProcessing] ;

SEND ON CONVERSATION (@cvh1, @cvh2, @cvh3)
 MESSAGE TYPE [//TestMgsType]
 (@TestMsgBody) ;

Another new feature is the addition of a new column – message_enqueue_time – to the Queues. This column helps us determine the time spent by a message on the queue.  The message_enqueue_time column is of datetimeoffset data type and stores the date and time (with time zone awareness) when the message arrives on the queue. Its’ exposed to the application via a direct query on the Queue itself, as well as a column in the Receive statement. Let’s look at an example;

-- Begin dialog & send message

 FROM SERVICE [manual_activation_initiator_service]
 TO SERVICE 'manual_activation_target_service'
 ON CONTRACT [manual_activation_contract]

 MESSAGE TYPE [manual_activation_demo_request]

-- A Direct Select statament from the Queue
 ,CAST ( message_body AS XML) AS [msg_body]
 ,DATEDIFF(second,message_enqueue_time,getutcdate()) AS [time_in_queue(Seconds)]
FROM [manual_activation_target_queue];

-- Results
conversation_handle                  msg_body                        time_in_queue(Seconds)
------------------------------------ ------------------------------- ----------------------
C0CB3200-809F-E111-8B29-005056BE0088 <test>test_message_body</test>  21

(1 row(s) affected)

-- Retrieve the message off of the Queue with a Receive
 ,CAST(message_body AS XML) AS [msg_body]
 ,DATEDIFF(second,message_enqueue_time,getutcdate()) AS [time_in_queue(Seconds)]
FROM manual_activation_target_queue),

-- Results
conversation_handle                  msg_body                        time_in_queue(Seconds)
------------------------------------ ------------------------------- ----------------------
C0CB3200-809F-E111-8B29-005056BE0088 <test>test_message_body</test>  21

(1 row(s) affected)

Please note that this column is not well documented yet and to derive the time spent by a message on the queue  in seconds, one must use a few date & time functions.   

 Service Broker Remote Messaging needs some additional configuration in order to get along with the Always On Availability Groups, due to the complexity added by the Listener for an Availability group. The Always On Availability Groups feature  introduced in SQL Server 2012,  is a high availability and disaster recovery solution that provides an enterprise level alternative to database mirroring.

For a Service in an Availability Group to be able to Receive remote messages;

  • The Availability Group must have a listener configured
  • Service Broker Endpoint must be created and configured with the Listener, for every instance of SQL Server in the Availability Group
  • Connect permissions must be granted on the endpoints to the the appropriate login(s)
  • msdb must contain a route

For a Service in an Availability Group to be able to Send messages to a remote service;

  • Configure a route to the target service using the listener
  • msdb must contain a route

And last but not the least, its always worth mentioning that since SQL Server 2008 R2, Poison message handling can be disabled at the time of queue creation or at a later time by using the alter queue statement. You can also check the status of poison message handling for each queue, by querying the sys.service_queues catalog view, which has the column, is_poison_message_handling_enabled to indicate whether poison message is enabled or disabled.

Advanced T-SQL with Itzik Ben-Gan

I recently had the privilege of learning Advanced T-SQL  with Itzik Ben-Gan (Blog|Twitter), a mentor and one of the founders of SolidQ. This 2 day Advanced T-SQL course spanned across a range of interesting topics that a Database Developer would find extremely valuable on a day to day basis ! Not only does Itzik deliver a remarkable lecture, but also encourages questions and discussions, which adds a great deal of value to the learning experience. Some of the topics we covered in detail are;

  • SQL Server Internals & Index Tuning – This was my very first deep dive into SQL Server Internals pertinent to Indexes. The multitude of Index access methods that the Optimizer can choose from, and their impact on Query performance has opened a whole new world of indexing strategies for me. Filtered Indexes and Columnstore Indexes some of the newer features I am looking forward to try out.
  • Temporary Tables – Never before has the distinction between Temporary tables, Table variables and Common Table Expressions (CTEs) been presented with so much clarity  and lucidity. I now have a solid understanding of which to use in any given scenario.
  • Sets vs Cursors – Again, a classic topic. Never hurts to reinforce the advantages of a Set based solution versus cursors.
  • Query Tuning has never been so interesting with a real life example of dramatic performance gains between various revisions of the same query.
  • Apply – I cannot believe I had used the apply operator only for XML Queries so for. I have now learned its uses for unpivoting, aggregating and reusing column aliases.
  • Grouping Sets – I had learned this one while studying for my MCTS certification, but had never got around to making much use of it. This gave me the chance to understand its full potential and applicability.
  • Sequences – I had done some research on this topic before to my blog post about the same, but I found out soon that  there is still so much more to it, especially when comparing it with Identity.
  • The enhancements to Window Functions and the world of Partitioning, Ordering and Framing offers a whole new paradigm for OLAP Queries used in Reporting.

Over the next few weeks, I intend to take the time to absorb all the new concepts and ideas I learned in this 2 day course, and find areas to implement these ideas.

Sequence Object in SQL Server 2012

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

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;
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS next_seq_no;

And the results look like:


(1 row(s) affected)



(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'),

-- Restart the sequence from 1
ALTER SEQUENCE dbo.demo_2012_sequence

-- 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;

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.

FROM sys.sequences
WHERE name = 'demo_2012_sequence';

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.

%d bloggers like this: