EDITOR’S NOTE: This is the second part of a two-part series on new T-SQL commands in SQL Server 2012. It reviews...
tweaks to serial numbering and data type conversion. Part 1 describes changes to stored procedures and error handling.
I’d gladly accept a dime for every time I’ve heard an Oracle programmer say to a SQL Server programmer, “You’re just getting that feature now? Why, we Oracle users have had that for years!” Well, here’s another case where Oracle folks can finally stop thumbing their nose at SQL Server developers: the SEQUENCE object.
Sequences work a little like identity columns, except they’re not bound to a particular table but rather to a given schema. When created, the object follows certain rules to define the sequence. Sequences are also not inherently unique by design, unlike identity columns, which means they can be used in a whole variety of contexts at once -- for example, they can be applied to tables or used as part of loops in a procedure.
Let’s take a look:
CREATE SEQUENCE mySchema.Identity
START WITH 1
INCREMENT BY 1
This would create a new sequence that starts at 1, increments by 1, has no maximum value and does not restart (CYCLE) from MINVALUE. The lack of a maximum value implies that no cycling takes place, but I wanted to be explicit in this example.
For more on SQL Server statements and commands
Find out which SQL command is better for your needs
Get the answers to frequently asked questions about creating and altering SQL Server tables
Learn how to secure SQL Server from SQL injection attacks
On the other hand, there’s this:
CREATE SEQUENCE mySchema.Identity2
START WITH -10
INCREMENT BY 1
This starts a sequence at -10, which increments by one until it reaches 10 and then starts over again at one. In this way, the numbering and behavior sequences are a bit more flexible than ID columns. For instance, you could share a specific number sequence between multiple tables without having to manually draft code (such as a user-defined function) to do it.
To use sequence values, you use the NEXT VALUE FOR statement. Here’s one example using a SELECT:
SELECT NEXT VALUE FOR mySchema.Identity as IdentityFromSequence;
This retrieves whatever the current sequence value is for mySchema.Identity and then increments it according to the rules for the sequence. Another way to use it would be with an INSERT:
INSERT myTable (myColumn)
VALUES (NEXT VALUE FOR mySchema.Identity);
You could also use it in the context of a variable:
SET @myVariable=NEXT VALUE FOR mySchema.Identity;
TRY_CONVERT and TRY_PARSE
These are minor but useful new additions that take some of the pain out of converting data types. TRY_CONVERT takes a value passed to it and attempts to return a valid conversion value. If it can’t, it returns null instead of throwing an error. Here’s an example:
SELECT TRY_CONVERT(DATETIME, ‘This is not a date’)
It returns null. This is useful if you want to use the null value as part of the processing for a procedure, for instance, and don’t want it to choke if it’s passed as an invalid value.
TRY_PARSE does the same thing as a variant on the original PARSE command. Microsoft recommends using PARSE for converting strings to dates or numbers and CONVERT for more general type conversions.
This returns a date value based on a year, month, and day value.
SELECT DATEFROMPARTS (2012,05,1)
This stored procedure returns a date value of May 1st, 2012.
SELECT IIF (1>2,’Impossible’,’Possible’)
The first part is a condition to test. The second is what expression to return if the test returns true; the third is returned if the test proves false. Note that you can only nest up to 10 IIFs, in the same way you can only nest up to 10 CASE statements. (Frankly, if you’re nesting more than 10 of those, you need to rethink the way you’re writing your T-SQL statements.)
This is a quick way to create a string from one or more values, all of which are implicitly converted to strings.
SELECT CONCAT(‘Once upon’,’a time’,32+32)
This would return this:
Once upona time64
Note the missing spaces. CONCAT doesn’t add those between expressions.
Also note that the return type will depend largely on the arguments supplied, which can range from varchar to nvarchar(max). Generally, CONCAT tries to return a type that is best suited to the total length of the string and the expressions passed to it. For instance, if none of the input arguments works as a supported large object type, then the return type is never more than 8,000 characters.
There’s a little something for everyone in these new features. Those who want slightly better error-handling have the THROW function; those who are tired of building custom pagination solutions for data have OFFSET/FETCH; those who are looking for more elegant ways to rename result columns from stored procedures have WITH RESULT SETS; and those looking for a more flexible approach to serial numbering have sequences.
ABOUT THE AUTHOR
Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeekand Windows Magazine.