What’s new in SQL Server 2014 Database Development

 Mar 13, 2014

Are you excited yet? SQL Server 2014 is just around the corner. The actual release date is vague, but it’s generally believed to be in the first half of this year, and given that we’re already in March, it’s close. In a previous blog post, I discussed Hekaton, which is now simply known as ‘In-Memory Online Transaction Processing’ (to be honest I liked saying “Hekaton” – ah well). That’s certainly coming, but I wanted to let you know about a few other bits and bobs. There are some nice enhancements to Transact-SQL, or TSQL to us nerds. Let’s have a look, shall we? First of all, there is the new ‘WITH RESULT SETS’ clause, which is part of the EXECUTE statement. Basically, in the past we’ve been stuck with the names and data types that were defined within a stored procedure when we used the EXECUTE statement, but not anymore. The ‘WITH RESULT SETS’ clause allows us to override the schema of the results. This means we can change the column names and compatible data types. Next, we have a new keyword: ‘THROW.’ ‘THROW’ allows us to move an exception along, so to speak. For example, we might have a nice little ‘CATCH’ block, but we couldn’t be bothered handling the error at that point, so we can simply ‘THROW’ the original exception up the line in the hope that someone else has written some code to handle it (I’m kidding! Most likely, you’ve written your exception handling code a little higher in the call stack.). The other thing about the ‘THROW’ keyword is that it allows you to define, or throw, custom exceptions. All you need to do is provide an error number (above 50,000, please, below that is reserved – not that there are that many exceptions), an error message and a tiny integer (0-255) for the state value. I have to say I very much prefer this to the old ‘RAISERROR’, mainly because my OCD kicks in with that atrocious spelling ERROR! What about ‘OFFSET’ and ‘FETCH’? Glad you asked. These keywords are all about paging. Most data-bound applications need to display data in some sort of pageable interface. Now, we can manage that ourselves through the ‘SELECT’ statement itself. The ‘OFFSET’ keyword is used to indicate the starting row of the results (that is, the number of rows to skip) and then the ‘FETCH’ keyword indicates the number of rows to FETCH, funnily enough. Ready for more? Sequence objects. You know how you can use an ‘IDENTITY’ column to generate a unique, increasing value as an identifier? Presumably, you also know that there are issues with this. For example, if you wanted to merge results from several tables, or even the same table from different regions, you could get yourself in a bit of trouble, particularly if you decide to use a ‘GUID’ (do you know how big those things are?). So, a sequence object allows us to grab a number for the next row we add in a table, in fact, we could use the same sequence object across multiple tables. To be honest, the way I visualise it is to imagine that the sequence object is just a single column table that increments when you grab a number from it. You need to create your sequence object before you can use it, of course, and that requires you to specify a starting value and an increment. Too easy! Once that’s done, you can simply define columns in any number of tables to use that sequence by using the ‘NEXT VALUE FOR’ clause when you define your column. Finally, I want to discuss some new syntax in the ‘CREATE TABLE’ statement. You can now keep all of the code that defines a table and its indexes in a single TSQL statement. That’s right, you don’t need a separate statement for the indexes, you can do it inline with your create table statement. Something like this:
CREATE TABLE dbo.SalesOrders ( SalesOrderID INTEGER PRIMARY KEY NONCLUSTERED, OrderDate DATETIME NOT NULL, CustomerID INTEGER NOT NULL, ProductID INTEGER NOT NULL, INDEX ix_SalesOrders_OrderDate CLUSTERED (OrderDate) );
So, there we go, just a few little things for you to chew on. SQL Server 2014 is really not far away now, so we need to make sure we know what it contains so that we’re in a good position to either utilise it or suggest an upgrade because of it’s cool new features.

How do your Excel skills stack up?   

Test Now  

About the Author:

Adam Keats  

With over 25 years of real-world IT experience Adam is one of New Horizons’ most senior Database and Software Development trainers. Starting out as a physics teacher Adam displayed exceptional mathematical and technical capabilities early on in his career. He went on to work in a variety of programming and technical management roles within several government agencies including the Department of Defence and Maritime Patrol. In 1998 Adam found his true calling, gaining MCT status and thus beginning his technical training career specialising in SQL Server administration, development, BI, and .NET development. Since then he has worked for several training organisations and found a home at New Horizons where he is now our resident Database and Development specialist. Throughout his tenure at New Horizons, Adam has assisted over 500 students in their endeavours to improve their skills, knowledge, and to achieve industry certifications.

top