
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 NowNext up:
- Where did my Acrobat tab go in Microsoft Word?
- Enhancing the IF function with nested IF functions in Microsoft Excel
- What’s new in SharePoint 2013 for developers
- Think Leader. Think Legacy. Think Auntie Marg.
- SQL Server 2014: In-Memory Database Capabilities
- How to create a PivotTable using Excel VBA
- Database indexing issues in Exchange Server 2013
- Creating a drop-down list in Microsoft Excel
- And if you tell that to the young folks today…
- How to convert text to columns in Microsoft Excel
Previously
- “Hey Rocky, watch me pull a rabbit out of my hat!” said Bullwinkle the trainer
- Monetise your Windows Store Apps
- Using custom fields to calculate GST in Microsoft Project
- Make CSS3 animations stay put after they’ve performed
- Data Deduplication in Windows Server 2012: The Solution for Ennui!
- Leonardo, Steve and Basil
- Creating Building Blocks in Microsoft Outlook
- A Closer Look at Excel PowerPivot and Power View
- How to sort lists with multiple levels in Excel
- What Lies Beneath – Part 2