SQL 2012 Features



SQL Server 2012: Top New Features for .NET Developers
An overview of tooling, programmability improvements, and engine enhancements in SQL Server 2012 for developers
Jun. 24, 2012
Michael K. Campbell takes .NET developers on a tour of some of the many new features in SQL Server 2012 that are geared toward improving developer productivity. Highlights include a significant upgrade to SQL Server Management Studio, the ability to directly work with business intelligence projects within Visual Studio, the release of SQL Server Data Tools 2012, and T-SQL improvements such as a THROW statement, support for sequences, and many new T-SQL functions.
Like its predecessors SQL Server 2008 and SQL Server 2008 R2, SQL Server 2012 boasts many features and improvements targeted at business intelligence (BI) and operations personnel (i.e., DBAs). But that doesn't mean that SQL Server 2012 ignores developers. In fact, SQL Server 2012 includes a number of great improvements and features in the form of new tooling, coding and programmability enhancements, and even new improvements to the underlying SQL Server engine that developers can take advantage of. Let's run down the list of new features that will benefit .NET developers who use SQL Server. For more information, see "Free SQL Server Resources for Developers" and "SQL Server 2012 Brings DBAs and Developers Together."

New SQL Server 2012 Tooling
SQL Server 2012 boasts some exceptional tooling improvements in the form of a significant upgrade to SQL Server Management Studio (SSMS) and a replacement of Business Intelligence Development Studio (BIDS), resulting in developer-related additions to Visual Studio 2010 and the release of SQL Server Data Tools (SSDT). At the heart of all of these tooling changes, however, is the fact that SQL Server 2012 has been "upgraded" such that SSMS and all associated developer tooling now runs atop the Visual Studio 2010 engine.
SQL Server Management Studio 2012. SSMS 2012 packs a number of powerful productivity benefits that developers who use SSMS for development tasks will end up loving. The most obvious change to SSMS is that it's built on Visual Studio 2010, meaning that it picks up a new, darker theme that more closely matches the Visual Studio theme, as shown in Figure 1.
Being built atop Visual Studio 2010 also means that SSMS 2012 picks up Visual Studio 2010's vastly improved multi-monitor support -- something that I've long awaited (because my desktop sports three monitors). Consequently, my favorite new feature of SSMS is the ability to "tear out" tabs from the main work area and drag them into different monitors or outside of the main IDE. Doing so lets me easily compare scripts or context in one file with the contents of another file without having to toggle back and forth between tabs (and without having to spin up a different instance of SSMS, as I was wont to do previously). Likewise, the ability to "detach" tool windows and position them in other monitors is another huge productivity benefit.
Another subtle productivity benefit that SSMS 2012 picks up is that it finally inherits Visual Studio's Clipboard buffer. This means that you can cycle through previously copied text by holding down the Shift key while pressing Ctrl+V.
An even more powerful productivity benefit that SSMS 2012 sports is the ability to leverage code snippets from within SSMS. This capability is something that has been sorely lacking for those of us addicted to snippets from developer experience within Visual Studio over the plast half-decade. Strangely, though, rather than being keyed off of "mnemonics" as is the case in Visual Studio, SSMS snippets are (by default) injected by means of pulling up a snippets "menu" that can be accessed via the Edit, IntelliSense, Insert Snippet menu option -- or via a similar Surround With menu option. Both of these menu options, of course, can be accessed via hotkeys (Ctrl+K, X and Ctrl+K, S), and the existing library of snippets available is quite large and helpful out of the box.
Initially I was a bit shocked that these snippets don't use SQL Server's age-old template functionality, but it didn't take me long to realize that the parameter-replacement semantics provided by these snippets (in the form of tabbing from one parameter to the next) provide a much better user experience than "old-school" templates. Moreover, although the out-of-the-box snippets that come with SSMS 2012 are great, it doesn't take much energy or effort to customize or create your own snippets (especially if you're using an awesome tool like Snippet Designer).
Furthermore, when it comes to customizing and managing snippets, the Tools, Code Snippets Manager option provides paths to existing snippets along with the option to add or remove snippets as needed. All that Code Snippets Manager is missing is the option to manage shortcuts (or mnemonics); shortcuts are supported, but only if you crack files open manually. Still, SSMS 2012's snippets feature is a significant productivity boon for developers.
As expected, SSMS 2012 continues to deliver the native support for working with SQL Azure databases that debuted with SSMS 2008 R2, although (maybe this is just my imagination) working with SQL Azure databases in SSMS 2012 feels just a tiny bit more responsive than before. Either way, SSMS 2012 sports a few new tasks for managing data-tier applications when you right-click on an Azure database -- so that's a minor improvement as well.

SQL Server 2012 improvements for Visual Studio 2010. As mentioned previously, another significant tooling change for developers is that with SQL Server 2012, BIDS ceases to exist. Of course, BIDS was really never anything more than a lightly "skinned" version of Visual Studio that provided specialized project templates and underlying tooling support for SQL Server Reporting Services (SSRS), SQL Server Integration Services (SSIS), and SQL Server Analysis Services (SSAS). In my experience, BIDS was always a source of unending frustration for developers who wanted to do .NET development and manage any form of BI (or SS*S) projects as well -- simply because of all of the versioning problems that continued to plague BIDS throughout its history.
Consequently, one of the things I was most excited about with SQL Server 2012 was that it retired BIDS. This retirement means that it's now possible with SQL Server 2012 to directly create and manage all BI-related projects directly within Visual Studio 2010. It's also worth mentioning that these project types pick up some new UI benefits and tweaks. For example, one of my favorite changes with SQL Server 2012 is that SSIS projects now have a full-blown tab in the .dtsx tab for parameters, as shown in Figure 2. This tab replaces the formerly employed approach, where naturally horizontal editing of parameters was crammed into a Tool Window that was vertical by default.
Although it's nice to finally be able to painlessly open and work with BI projects in Visual Studio 2010, the bad news is that age-old compatibility problems still exist in the sense that if you want support for managing BI-related projects in Visual Studio 2010, you have to uninstall Visual Studio (if it's already installed) and then reinstall it after you install SQL Server 2012 -- which, of course, is beyond lame. That said, an easier way to get BI-related project templates is to just install SSDT, but that currently comes with some drawbacks as well.
SQL Server Data Tools 2012. According to Microsoft, SSDT was effectively designed to replace the SQL Server "Data Dude" SKU (aka Visual Studio Team System 2008 Database Edition) of yore. As such, SSDT provides a number of designers and editing capabilities for working with so-called database projects, where SSDT can help track changes made via designers, and so on. Correspondingly, SSDT also includes schema-comparison tooling and capabilities. A thorough overview of all the features and benefits provided by SSDT is outside the scope of this article. If you're interested in learning more about SSDT, you can find great information and insights about what it does and how it works by visiting the SSDT home page or by visiting the SSDT Team Blog.
Although I've spent a bit of time working with SSDT to "put it through the paces," I must confess that I've never been very excited about Data Dude (not just because of the name), and that lack of enthusiasm now extends to SSDT as well. Stated simply, as a longtime database developer and former production DBA, I'm just not afraid of the complexity associated with database change management, and I can't help but find that SSDT is inadequate for handling changes in the mission-critical environments of my SQL Server consulting clients. Therefore, given that SSDT (somehow) breaks the ability to close documents in SSMS 2012 by middle-clicking, I'd personally take the ability to middle-click my documents closed over all the features and benefits that SSDT provides. Of course, that bug is hopefully only a temporary setback, and I'm sure other developers and DBAs would feel much differently about SSDT. Consequently, SSDT is well worth a further look, because it's a major new feature of SQL Server 2012.
Programmability Enhancements: T-SQL and Engine Improvements
Even as SQL Server 2012's improved tooling and enhanced IDEs provide big productivity wins for developers, SQL Server 2012 also sports a number of programmability enhancements and functions that SQL Server developers will enjoy. SQL Server 2012 packs in a number of great features and capabilities that developers have been requesting for a while now, along with some great surprises that provide some useful new capabilities. Let's take a look at the new programmability features.
T-SQL gets a THROW statement. For SQL Server developers who are familiar with the .NET Framework, the lack of a T-SQL THROW statement has always been a bit strange. Happily, SQL Server 2012 has addressed that lack -- though you'll want to pay attention to the Remarks section of SQL Server Books Online ( BOL) for this new feature because there are a number of limitations as to how this statement can be used. Likewise, as cool as this new feature is, just remember that RAISERROR (despite its archaic spelling) still provides a number of powerful features that THROW (and PRINT) simply don't offer.
T-SQL finally supports built-in pagination. I still remember how disappointed I was more than a decade ago to learn that SQL Server didn't provide built-in support for pagination as MySQL did. Happily though, SQL Server 2012 finally introduces full-blown, first-class support within T-SQL for pagination, as shown in Figure 3. Note, too, that pagination is technically a feature or argument of the ORDER BY clause -- and that Microsoft actually recommends using OFFSET and FETCH as a replacement for TOP.
Figure 3: Built-in pagination with support for OFFSET and FETCH via the ORDER BY clause
-- Create and populate a simple table
-- full of 'contact' information:
CREATE TABLE dbo.Contacts (
        ContactId int IDENTITY(1,1) NOT NULL,
        FirstName varchar(60),
        LastName varchar(60),
        Phone varchar(60)
);

INSERT INTO dbo.Contacts
SELECT name, name, name
FROM master.dbo.spt_values;

-- Skip 300 rows 'into' the results and
-- take the next 10 records:
SELECT ContactId, FirstName, LastName, Phone
FROM dbo.Contacts
ORDER BY ContactId
        OFFSET 300 ROWS
        FETCH NEXT 10 ROWS ONLY;
Support for sequences. Another great new feature that SQL Server 2012 provides (which Oracle has had for nearly forever) is support for sequences. A sequence can best be described as being a bit like an IDENTITY "object" because it behaves just like an IDENTITY column without actually being a column. Developers can create sequences, query them for one or more IDs (which are returned in sequence), and then do whatever they want with those supplied IDs prior to INSERTing them into a table that needs a unique, sequentially incrementing, identity value, as shown in Figure 4.
Figure 4: Example of a simple sequence in operation
-- Create a simple sequence:
CREATE SEQUENCE dbo.ExampleSequence AS int
        START WITH 1 INCREMENT BY 1;

-- Create a simple/test table, too:
CREATE TABLE dbo.SequentialTable (
        SampleId int NOT NULL,
        SampleValue nvarchar(40) NOT NULL
);

-- Sample/example of easiest way to grab value:
SELECT NEXT VALUE FOR dbo.ExampleSequence;

-- Now copy 'next' value (2) into a parameter:
DECLARE @NextSequence int
SELECT @NextSequence = NEXT VALUE
        FOR dbo.ExampleSequence;

-- And use it for an INSERT.
-- But in non-trivial examples - you could use it for FK inserts
-- or other operations as well BEFORE trying the following INSERT.
INSERT INTO dbo.SequentialTable (SampleId, SampleValue)
VALUES (@NextSequence, '@NextSequence will have a value of 2.');

SELECT * FROM dbo.SequentialTable;
GO
One of the big benefits of sequences is that developers can use them in much the same way as they use GUIDs today, but without the type of performance issues associated with the ugly fragmentation that ensues when using unique identifiers as a clustered index key. In addition to that benefit, sequences open up other very cool possibilities by virtue of some of their extended capabilities, such as the ability to CYCLE or "repeat" sequence values after they hit a specified maximum identity value. SQL Server 2012 also provides a number of options for intelligently working with and querying sequence metadata.
Metadata discovery improvements. Speaking of metadata, SQL Server 2012 also provides drastically improved support for metadata discovery, which makes it much easier for developers to determine the shape of projected output from queries, sprocs, views, and other objects that can be queried. Although many developers will never use this functionality, I can't help but think of how useful it will be for developers creating micro ORMs (which I suspect will become much more popular as developers tire of the bloat and overhead associated with Entity Framework).
Projection redirection and the WITH RESULT SETS argument. Another great new feature of SQL Server 2012 that developers will find useful is the ability to "re-project" or change the output of stored procedures by specifying new column names, casts, and other sorts of operations when executing stored procedures by means of the WITH RESULT SETS clause (see Figure 5). This feature is something that will be very valuable for helping to assist with versioning concerns and considerations in larger and more complex applications and deployments.
Figure 5: The WITH RESULT SETS clause, which enables re-projection of stored procedure output
-- An INSANELY simple sample sproc:
CREATE PROC dbo.TestProc
        @input1 int,
        @input2 varchar(20)
AS
        SET NOCOUNT ON

        SELECT @input1 AS [Output1], @input2 [Output2]

        RETURN 0
GO

-- Now transform the output/projection/results:
EXEC dbo.TestProc 292, 'This is Some Text'
WITH RESULT SETS ( ([Column 1] int, [Column 2] varchar(20)) );
Programmability Enhancements: New T-SQL Functions
As outlined in BOL, SQL Server 2012 introduces a bevy of new functions that developers can use. Here are my thoughts about a few of them.
FORMAT(). In my mind, this single function is one of the hands-down best new features of SQL Server 2012, simply because the functionality that it provides has been so sorely needed for so long. And for .NET developers, the immediate and obvious benefits of this new function should be readily apparent just by looking at Figure 6. Another thing that I like about the new FORMAT() function is that it represents an additional influx of CLR functionality directly into T-SQL -- something that I hope to see more of in the future.
Figure 6: Examples of how T-SQL's new (CLR-based) FORMAT() function works
SELECT
        FORMAT(GETDATE(), 'yyyy-MM-dd') AS [ISO Formatted Date],
        FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss') AS [Full ISO],
        FORMAT(GETDATE(), 'MMMM dd, yyyy') AS [Long-hand Date (EN)],
        FORMAT(GETDATE(), 'MMMM dd, yyyy', 'fr-FR') AS [French Date],
        FORMAT(22.7, 'C', 'en-US') AS [US Currency],
        FORMAT(22.7, 'C', 'en-GB') AS [UK Currency],
        FORMAT(99 * 2.226, '000.000') AS [Padded Decimal],
        FORMAT(12345678, '0,0') AS [Finally: Commas in Large Numbers]
;
CHOOSE(). With the CHOOSE() function, you can quickly and easily translate an integer value into an array of corresponding string values, as shown in Figure 7.
Figure 7: The CHOOSE() function: a simple tool with potential for plenty of problems
-- CHOOSE() makes these easy to 'format' in ad hoc
-- reports. The following returns 'Male' or position
-- number 2 in the 1-based (i.e., non-0 based) array.
SELECT CHOOSE(2, 'Female','Male', 'Unknown')
AS [Gender];

-- Just be aware that hard-coding values into
-- 'permanent' code can/will cause problems long term.
-- The following returns NULL - as 4 exceeds array or is not found.
SELECT CHOOSE(4,'Female','Male','Unknown') AS [x];
Personally, I think that the new CHOOSE() function is a mixed blessing. On the one hand, this function can and will make minor "formatting" and reporting needs much easier to handle. On the other hand, it doesn't take much thought at all to see how the use of CHOOSE() within code can and will lead to "magic numbers" programming within code and how it will lead to versioning problems and issues that will ultimately cause this function to generate all sorts of problems over the long term.
Conversion functions and date and time functions. For developers familiar with the .NET Framework, T-SQL's new TRY_CONVERT() and TRY_PARSE() functions will be a welcome new addition, along with a host of new Date and Time functions (of varying benefit and usefulness). All told, SQL Server 2012 does a great job of introducing an assortment of new functions that will make programing T-SQL that much easier.
Engine Enhancements That Benefit Developers
Although it's a common or even logical assumption that enhancements to the core SQL Server engine are things that typically excite DBAs, that doesn't mean that many of these benefits can't translate into tangible improvements that will aid developers. As such, SQL Server 2012 actually ships with a number of programmability enhancements that it lists in the "What's New" section of BOL with descriptions about new developments for the SQL Server Engine. These enhancements include some great new improvements to full-text indexing, enhanced spatial features, and the addition of FILETABLE functionality that extends existing FILESTREAM storage capabilities.
But, above and beyond these obvious engine enhancement benefits, there are also a couple of other big benefits that aren't as obvious to spot: partially contained databases and a new version of SQL Server Express.
Contained databases. With SQL Server 2012, contained databases provide a means for effectively decoupling SQL Server databases (and their users, collations, and other assets) from the underlying SQL Server instance itself. Stated differently, contained databases are much more "portable" in the sense that they can be easily moved from one server to another -- without worries about orphaned users and other issues that have typically made moving databases problematic. Thus, not only are contained databases a big win in corporate environments where DBAs need to shunt databases around for load-balancing purposes, they're also a huge win for ISVs (and developers) who want to more easily copy, move, or deploy databases with their software. Similarly, for web developers who rely on SQL Server as a back-end database, contained databases help make developers less dependent on hosting platforms as their databases become more portable -- another huge win.
Better yet, since there remains a fine line between what kind of code and operations are server-dependent and what kinds of programming and operations can be isolated into contained databases, Microsoft has done a fantastic job of making it easy to query specialized dynamic management views (DMVs) that will let developers (and DBAs) know how "contained" or portable their databases are. As such, my anticipation is that contained databases are not only one of the biggest wins for developers in SQL Server 2012 but also a key component in eventually helping Microsoft push SQL Azure as a database-hosting platform.
SQL Server 2012 Express LocalDB. Another great feature that SQL Server 2012 offers for developers is a new lightweight installation of SQL Server Express. This new version of SQL Express, SQL Server 2012 Express LocalDB, makes ISV and other forms of "embedded" SQL Server deployments much easier to tackle by means of allowing a much more streamlined installation process along with the ability to let SQL Server run in-process with applications. Best of all, documentation for this new developer-friendly version of SQL Server is included in BOL -- and the price remains, of course, free.
Discontinued and Deprecated Features
Of course, with every new release of SQL Server, there are also a few features and bits of syntax that are either removed outright or are slated for later removal. Happily, with SQL Server 2012, the list of deprecated and removed features is relatively small. This, in turn, means that SQL Server 2012 does a great job of providing developers with new features -- with very little worry or concern for negative problems of backward compatibility.

SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012

By: Editor
April 12, 2011

Introduction

Microsoft SQL Server 2012 introduces an interesting clause namely OFFSET and FETCH to the existing ORDER BY clause. Using OFFSET and FETCH clause developers can write TSQL code to fetch only a set of rows from the complete result set window. This will help developers going forward achieve SQL Server Pagination there by improving performance when retrieving and displaying large number of records from SQL Server Database.

Example SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012

Let us go through an example which illustrates achieving SQL Server Pagination Using OFFSET and FETCH feature in SQL Server 2012.
Use AdventureWorks2008R2
GO
SELECT          
 PP.FirstName + ' ' + PP.LastName AS 'Name'    
,PA.City          
,PA.PostalCode
FROM  Person.Address PA 
INNER JOIN              
Person.BusinessEntityAddress PBEA                                 
ON PA.AddressID = PBEA.AddressID        
INNER JOIN
                        Person.Person PP                             
ON PBEA.BusinessEntityID = PP.BusinessEntityID
ORDER BY PP.FirstName
            OFFSET 0 ROWS      
                FETCH NEXT 5 ROWS ONLY

If you carefully look at the ORDER BY clause in the above TSQL code you would see that I have used “OFFSET 0 ROWS” and “FETCH NEXT 5 ROWS ONLY” extension to ORDER BY clause. Using OFFSET and FETCH NEXT ORDER BY clause developer going forward in SQL Server 2012 can write stored procedures which can retrieve only specific set of rows rather than retrieving the entire query result. This feature will improve the query performance and get only the required result set.
Result Using Offset and Fetch Feature of SQL Server 2012

Example – Achieve SQL Server Pagination Using OFFSET and FETCH Feature in SQL Server 2012

In the below example you would see that I have created a stored procedure namely PersonNameAndLocationUsingSQLServerPagingFeature which will help me achieve SQL Server Pagination by leveraging OFFSET and FETCH feature in SQL Server 2012.
Use AdventureWorks2008R2
GO 
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
    OBJECT_ID(N'[dbo].[PersonNameAndLocationUsingSQLServerPagingFeature]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PersonNameAndLocationUsingSQLServerPagingFeature]
GO 
CREATE PROCEDURE PersonNameAndLocationUsingSQLServerPagingFeature
(          
@StartingRowNumber        INT,    
@RowCountPerPage            INT
 )
AS
BEGIN
    SELECT      
 PP.FirstName + ' ' + PP.LastName AS 'Name'    
,PA.City          
,PA.PostalCode
    FROM  Person.Address PA         
INNER JOIN              
Person.BusinessEntityAddress PBEA                                 
ON PA.AddressID = PBEA.AddressID        
INNER JOIN
                        Person.Person PP                             
ON PBEA.BusinessEntityID = PP.BusinessEntityID
    ORDER BY PP.FirstName
              OFFSET (@StartingRowNumber - 1) * @RowCountPerPage ROWS  
              FETCH NEXT @RowCountPerPage ROWS ONLY
END
GO
/* Leveraging SQL Server Pagination Using OFFSET and FETCH NEXT Feature in SQL Server 2012 */
Use AdventureWorks2008R2
GO
EXEC PersonNameAndLocationUsingSQLServerPagingFeature 1,10
GO

Conclusion

The OFFSET and FETCH NEXT clause which is an extension to existing ORDER BY clause in SQL Server 2012 will help developers achieve SQL Server Paginiation. This feature will be widely used by SQL Server Developers to pull only required number of records which they would like to display in ASP.NET Data Grid rather than pull the entire set of records which are matching the requirement. Implementation of this feature within a stored procedure will help the databas query run faster and only reterive required number of rows.


Using WITH RESULT SETS Feature of SQL Server 2012

By: Editor
April 2, 2011

Introduction

Microsoft SQL Server 2012 extends the EXECUTE statement to introduce WITH RESULT SETS option which can be used to change the Column Name and Data Types of the result set returned by the execution of stored procedure.

Example Using WITH RESULT SETS Feature of SQL Server 2012

Let us go through an example which illustrates WITH RESULT SETS Feature of SQL Server 2012.
Use AdventureWorks2008R2
GO

IF EXISTS (       
SELECT * FROM sys.objects   
WHERE object_id = OBJECT_ID(N'[dbo].[WithResultSets_SQLServer2012]')           
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WithResultSets_SQLServer2012]
GO
CREATE PROCEDURE WithResultSets_SQLServer2012
AS       
BEGIN             
SELECT                                   
 TOP 5                        
                                                 PP.FirstName + ' ' + PP.LastName AS Name                        
,PA.City                                   
,PA.PostalCode                       
FROM  Person.Address PA                             
INNER JOIN                                        
Person.BusinessEntityAddress PBEA                                                  
ON PA.AddressID = PBEA.AddressID                          
INNER JOIN                                                    
Person.Person PP                                                       
ON PBEA.BusinessEntityID = PP.BusinessEntityID                 
ORDER BY PP.FirstName
            END
GO

Once the stored procedure is created successfully. The next step will be to execute the above stored procedure using WITH RESULT SET Feature of SQL Server 2012.
/* Execute Stored Procedure which uses WITH RESULT SETS  Feature of SQL Server 2012*/
EXEC WithResultSets_SQLServer2012GO
/*
 Example - Using WITH RESULT SETS Feature of SQL Server 2012
*/
EXEC WithResultSets_SQLServer2012
WITH RESULT SETS
(
 (
  [Employe Name]       NVARCHAR(100),
  [Employee City]                    NVARCHAR(20),
  [Employee Postal Code]        NVARCHAR(30)
 )
)
GO

In the above image you could see that once you execute WithResultSets_SQLServer2012 stored procedure using WITH RESULT SET feature of SQL Server 2012 you can change the Column Name and Data Type as per your need without actually altering the exisiting stored procedure. In the second result set (above image) you could see that the Column Names are changed from Name to Employee Name, City to Employee City and PostalCode to Employee Postal Code. Similary, the data type was changes from VARCHAR to NVARCHAR.

Conclusion

The WITH RESULT SET Feature of SQL Server 2012 is a great enhancement to the EXECUTE Statement. This feature will be widely used by Business Intelligence Developers to execute a stored procedure with in an SQL Server Integration Services (SSIS) Package to return the result set with required Columns and modified data types.
Sequence Numbers
SQL Server 2012
13 out of 15 rated this helpful - Rate this topic
A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Sequences, unlike identity columns, are not associated with tables. An application refers to a sequence object to receive its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.
A sequence is created independently of the tables by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance. For information about the options, see CREATE SEQUENCE.
Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table. The NEXT VALUE FOR function can be used as the default value for a column in a table definition. Use sp_sequence_get_range to get a range of multiple sequence numbers at once.
A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint.
Using Sequences
Use sequences instead of identity columns in the following scenarios:
  • The application requires a number before the insert into the table is made.
  • The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.
  • The application must restart the number series when a specified number is reached. For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.
  • The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call. The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.
  • An application requires multiple numbers to be assigned at the same time. For example, an application needs to reserve five sequential numbers. Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.
  • You need to change the specification of the sequence, such as the increment value.
Limitations
Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.
Uniqueness is not automatically enforced for sequence values. The ability to reuse sequence values is by design. If sequence values in a table are required to be unique, create a unique index on the column. If sequence values in a table are required to be unique throughout a group of tables, create triggers to prevent duplicates caused by update statements or sequence number cycling.
The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables. When created with the CACHE option, an unexpected shutdown, such as a power failure, can lose the sequence numbers in the cache.
If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQL statement. This behavior is consistent with the ANSI standard.
Typical Use
To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.
CREATE SEQUENCE Schema.SequenceName
    AS int
    INCREMENT BY 1 ;
To create an integer sequence number similar to an identity column that increments by 1 from 1 to 2,147,483,647, use the following statement.
CREATE SEQUENCE Schema.SequenceName
    AS int
    START WITH 1
    INCREMENT BY 1 ;

Managing Sequences
For information about sequences, query sys.sequences.
Examples
There are additional examples in the topics CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL), and sp_sequence_get_range.
A. Using a sequence number in a single table
The following example creates a schema named Test, a table named Orders, and a sequence named CountBy1, and then inserts rows into the table using the NEXT VALUE FOR function.
--Create the Test schema
CREATE SCHEMA Test ;
GO

-- Create a table
CREATE TABLE Test.Orders
    (OrderID int PRIMARY KEY,
    Name varchar(20) NOT NULL,
    Qty int NOT NULL);
GO

-- Create a sequence
CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1 ;
GO

-- Insert three records
INSERT Test.Orders (OrderID, Name, Qty)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;
INSERT test.Orders (OrderID, Name, Qty)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;
INSERT test.Orders (OrderID, Name, Qty)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;
GO

-- View the table
SELECT * FROM Test.Orders ;
GO
Here is the result set.
OrderID  Name    Qty
1        Tire    2
2        Seat    1
3        Brake   1
B. Calling NEXT VALUE FOR before inserting a row
Using the Orders table created in example A, the following example declares a variable named @nextID, and then uses the NEXT VALUE FOR function to set the variable to the next available sequence number. The application is presumed to do some processing of the order, such as providing the customer with the OrderID number of their potential order, and then validates the order. No matter how long this processing might take, or how many other orders are added during the process, the original number is preserved for use by this connection. Finally, the INSERT statement adds the order to the Orders table.
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Test.CountBy1;
-- Some work happens
INSERT Test.Orders (OrderID, Name, Qty)
    VALUES (@NextID, 'Rim', 2) ;
GO

C. Using a sequence number in multiple tables
This example assumes that a production-line monitoring process receives notification of events that occur throughout the workshop. Each event receives a unique and monotonically increasing EventID number. All events use the same EventID sequence number so that reports that combine all events can uniquely identify each event. However the event data is stored in three different tables, depending on the type of event. The code example creates a schema named Audit, a sequence named EventCounter, and three tables which each use the EventCounter sequence as a default value. Then the example adds rows to the three tables and queries the results.
CREATE SCHEMA Audit ;
GO
CREATE SEQUENCE Audit.EventCounter
    AS int
    START WITH 1
    INCREMENT BY 1 ;
GO

CREATE TABLE Audit.ProcessEvents
(
    EventID int PRIMARY KEY CLUSTERED
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),
    EventTime datetime NOT NULL DEFAULT (getdate()),
    EventCode nvarchar(5) NOT NULL,
    Description nvarchar(300) NULL
) ;
GO

CREATE TABLE Audit.ErrorEvents
(
    EventID int PRIMARY KEY CLUSTERED
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),
    EventTime datetime NOT NULL DEFAULT (getdate()),
    EquipmentID int NULL,
    ErrorNumber int NOT NULL,
    EventDesc nvarchar(256) NULL
) ;
GO

CREATE TABLE Audit.StartStopEvents
(
    EventID int PRIMARY KEY CLUSTERED
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),
    EventTime datetime NOT NULL DEFAULT (getdate()),
    EquipmentID int NOT NULL,
    StartOrStop bit NOT NULL
) ;
GO

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
    VALUES (248, 0) ;
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
    VALUES (72, 0) ;
INSERT Audit.ProcessEvents (EventCode, Description)
    VALUES (2735,
    'Clean room temperature 18 degrees C.') ;
INSERT Audit.ProcessEvents (EventCode, Description)
    VALUES (18, 'Spin rate threashold exceeded.') ;
INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)
    VALUES (248, 82, 'Feeder jam') ;
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
    VALUES (248, 1) ;
INSERT Audit.ProcessEvents (EventCode, Description)
    VALUES (1841, 'Central feed in bypass mode.') ;
-- The following statement combines all events, though not all fields.
SELECT EventID, EventTime, Description FROM Audit.ProcessEvents
UNION SELECT EventID, EventTime, EventDesc FROM Audit.ErrorEvents
UNION SELECT EventID, EventTime,
CASE StartOrStop
    WHEN 0 THEN 'Start'
    ELSE 'Stop'
END
FROM Audit.StartStopEvents
ORDER BY EventID ;
GO

Here is the result set.
EventID  EventTime                Description
1        2009-11-02 15:00:51.157  Start
2        2009-11-02 15:00:51.160  Start
3        2009-11-02 15:00:51.167  Clean room temperature 18 degrees C.
4        2009-11-02 15:00:51.167  Spin rate threshold exceeded.
5        2009-11-02 15:00:51.173  Feeder jam
6        2009-11-02 15:00:51.177  Stop
7        2009-11-02 15:00:51.180  Central feed in bypass mode.
D. Generating repeating sequence numbers in a result set
The following example demonstrates two features of sequence numbers: cycling, and using NEXT VALUE FOR in a select statement.
CREATE SEQUENCE CountBy5
   AS tinyint
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE ;
GO

SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;
GO
E. Generating sequence numbers for a result set by using the OVER clause
The following example uses the OVER clause to sort the result set by Name before it adds the sequence number column.
USE AdventureWorks2012 ;
GO

CREATE SCHEMA Samples ;
GO

CREATE SEQUENCE Samples.IDLabel
    AS tinyint
    START WITH 1
    INCREMENT BY 1 ;
GO

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
F. Resetting the sequence number
Example E consumed the first 79 of the Samples.IDLabel sequence numbers. (Your version of AdventureWorks2012 may return a different number of results.) Execute the following to consume the next 79 sequence numbers (80 though 158).
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
Execute the following statement to restart the Samples.IDLabel sequence.
ALTER SEQUENCE Samples.IDLabel
RESTART WITH 1 ;
Execute the select statement again to verify that the Samples.IDLabel sequence restarted with number 1.
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
G. Changing a table from identity to sequence
The following example creates a schema and table containing three rows for the example. Then the example adds a new column and drops the old column.
-- Create a schema
CREATE SCHEMA Test ;
GO

-- Create a table
CREATE TABLE Test.Department
    (
        DepartmentID smallint IDENTITY(1,1) NOT NULL,
        Name nvarchar(100) NOT NULL,
        GroupName nvarchar(100) NOT NULL
    CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED
         (DepartmentID ASC)
    ) ;
GO

-- Insert three rows into the table
INSERT Test.Department(Name, GroupName)
    VALUES ('Engineering', 'Research and Development');
GO

INSERT Test.Department(Name, GroupName)
    VALUES ('Tool Design', 'Research and Development');
GO

INSERT Test.Department(Name, GroupName)
    VALUES ('Sales', 'Sales and Marketing');
GO

-- View the table that will be changed
SELECT * FROM Test.Department ;
GO

-- End of portion creating a sample table
--------------------------------------------------------
-- Add the new column that does not have the IDENTITY property
ALTER TABLE Test.Department
    ADD DepartmentIDNew smallint NULL
GO

-- Copy values from the old column to the new column
UPDATE Test.Department
    SET DepartmentIDNew = DepartmentID ;
GO

-- Drop the primary key constraint on the old column
ALTER TABLE Test.Department
    DROP CONSTRAINT [PK_Department_DepartmentID];
-- Drop the old column
ALTER TABLE Test.Department
    DROP COLUMN DepartmentID ;
GO

-- Rename the new column to the old columns name
EXEC sp_rename 'Test.Department.DepartmentIDNew',
    'DepartmentID', 'COLUMN';
GO

-- Change the new column to NOT NULL
ALTER TABLE Test.Department
    ALTER COLUMN DepartmentID smallint NOT NULL ;
-- Add the unique primary key constraint
ALTER TABLE Test.Department
    ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED
         (DepartmentID ASC) ;
-- Get the highest current value from the DepartmentID column
-- and create a sequence to use with the column. (Returns 3.)
SELECT MAX(DepartmentID) FROM Test.Department ;
-- Use the next desired value (4) as the START WITH VALUE;
CREATE SEQUENCE Test.DeptSeq
    AS smallint
    START WITH 4
    INCREMENT BY 1 ;
GO

-- Add a default value for the DepartmentID column
ALTER TABLE Test.Department
    ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)
        FOR DepartmentID;
GO

-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
-- Test insert
INSERT Test.Department (Name, GroupName)
    VALUES ('Audit', 'Quality Assurance') ;
GO

-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
GO

Transact-SQL statements that use SELECT * will receive the new column as the last column instead of the first column. If this is not acceptable, then you must create an entirely new table, move the data to it, and then recreate the permissions on the new table.
THROW statement in SQL Server 2012
In SQL Server 2012 Microsoft has introduced THROW Statement which will help developers to handle error easily. A developer will be able to use THROW statement to raise an exception and then to transfer the execution to a CATCH block of the TRY...CATCH.


A new neat feature in SQL Server 2012 is THROW


In SQL Server 2005 we got the TRY…CATCH construct which was a big help for developers to effectively handle errors within their T-SQL code. Prior to that the developers had to use the @@ERROR in-built system function to check for errors and they had to check for error conditions after every operation! That sucked big and too often the developers forgot.



Developers also had to use the RAISERROR (notice the spelling mistake that we are forced to live with) statement to show an error message. But there where many issues:



  • It require an error number to exist within the sys.message.
  • The severity level controls the error actions, such as dtatement abort.
  • RAISERROR does not honors XACT_ABORT.
  • The error number, message, line number could get changed when using RAISERROR.
  • ...
And even if you could go around most of the issues, example by embed the original error details as a custom message passed to RAISERROR, it was always hard to know what happened just by reading the T-SQL since you had to know the parameters, like ErrorSeverity.

In SQL Server 2012, you can use new THROW statement, borrowed from throw in the .NET model) and it can be used in two ways:

  • As an alternative to RAISERROR.
  • As an away to re-throw the original error that occurred.


Note that THROW() need the preceding statement to end with a proper statement terminator. So start using those semi-colons!



SQL Server 2008

SET NOCOUNT ON
BEGIN TRY
     SELECT 1/0
END TRY
BEGIN CATCH
     DECLARE @ErrorNumber int
     DECLARE @ErrorState int
     DECLARE @ErrorSeverity int
     DECLARE @ErrorLine int
     DECLARE @ErrorProcedure NVARCHAR(MAX) 
     DECLARE @ErrorMessage NVARCHAR(MAX) 
     DECLARE @UserName NVARCHAR(256) 
     DECLARE @HostName NVARCHAR(128)
     SELECT  @ErrorNumber = ERROR_NUMBER() 
     ,@ErrorState = ERROR_STATE() 
     ,@ErrorSeverity = ERROR_SEVERITY() 
     ,@ErrorLine = ERROR_LINE() 
     ,@ErrorProcedure = ERROR_PROCEDURE() 
     ,@ErrorMessage = ERROR_MESSAGE() 
     ,@UserName = SUSER_SNAME() 
     ,@HostName = Host_NAME() 
     SELECT@ErrorNumber,@ErrorState,@ErrorSeverity,@ErrorLine,@ErrorProcedure,@ErrorMessage,@UserName,@HostName
  
     RAISERROR (@ErrorMessage, @ErrorSeverity, 1 )
END CATCH

Msg 50000, Level 16, State 1, Line 26
Divide by zero error encountered.
SQL Server 2012

SET NOCOUNT ON;

BEGIN TRY
   SELECT 1/0;
END TRY
BEGIN CATCH
    THROW;
END CATCH

Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.


Start lobbying your developer to use the new THROW statement and you will get better error handling and easier problem detection. This equals more time for you to do the fun SQL stuff!

Problem
SQL Server 2012, has introduced the THROW statement which can be used for enhanced error handling. In this tip we will discuss how the THROW statement in SQL Server 2012 can help developers to effectively handle errors.
Solution
Microsoft introduced the TRY…CATCH construct in SQL Server 2005 which helped database developers to effectively handle errors within their T-SQL code. Using the CATCH block a developer could easily write the error handling logic that needs to be executed whenever a problem occurs within the TRY block. Prior to the introduction of TRY…CATCH construct developers had to use the @@ERROR in-built system function to check for error conditions after every operation. In the previous versions of SQL Server you had to use RAISERROR statement to show an error message. However, a RAISERROR requires a user defined message to be added to sys.messages table before you use it to raise an error.
In SQL Server 2012, when you use THROW statement it doesn’t require an error number to exist within the sys.messages table however, the error number used should be greater than 50000. It is important to note that all exceptions raised using the THROW statement will have a severity of 16 and the statement before the THROW statement must be followed by the semicolon (;) statement terminator.

Using THROW Statement in SQL Server 2012

Let us go through a simple example to demonstrate the THROW statement in SQL Server 2012.
SYNTAX: - 
THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable }
] [ ; ]
Use TempDB
GO
CREATE TABLE ErrorHistory
(
 ErrorTime   DATETIME
,ErrorSeverityLevel  NVARCHAR(100)
,ErrorMessage   NVARCHAR(1000)
)
GO
BEGIN TRY
 DECLARE @TryDivision int = 10/0
END TRY
BEGIN CATCH
 /* Insert Error Information & Then Re-Throw the error message received */
 INSERT INTO ErrorHistory VALUES(GETDATE(), ERROR_SEVERITY(), ERROR_MESSAGE());
 THROW;
END CATCH
GO
SELECT * FROM ErrorHistory
GO



In the above snippet you could see that in the code CATCH block the error information is instered into a table and the error message is re-thrown to the client application.


No comments:

Post a Comment

Subscribe to: Posts (Atom)