Almost a month ago, Microsoft released the first CTP (Community Technology Preview) version of their upcoming SQL Server 2011, code-named Denali. I immediately downloaded and installed it on all my machines, and I’ve been using it ever since. It works perfectly with both 2005 and 2008, and of course with 2011.
Now, what’s new in this release? The biggest difference is that Management Studio is built into Visual Studio. Or, more correct, Management Studio has got a Visual Studio 2010-shining. I think this is a good call. For instance, I HATE the intellisense in 2008. It’s the first thing I remove when I’ve installed it, but in 2011, I haven’t turned it off yet, because it’s not in my way. It’s actually helping me write queries! The other big new UI related feature is snippets. Now you can, finally, have them inside Management Studio. I’m going to create a library of my scripts and make a snippet folder to share with all that’s interested. I’ll post it here on my blog when it’s ready.
When it comes to T-SQL enhancements, there’s actually a couple of new features that looks very interesting:
ORDER BY has some new features. With the following query, you get 10 rows. Not the first 10, but from row 10 (OFFSET 10) to 19 (FETCH NEXT 10). I think this will be easier to use for paging than using ROW_NUMBER() in 2008.
ORDER BY name
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
Notice that in books online they specify that FETCH FIRST and FETCH NEXT are synonyms and that they’re there to provide ANSI compatibility.
CREATE SEQUENCE is a completely new T-SQL feature. The following query makes a sequence that you can poll.
CREATE SEQUENCE test
START WITH 1
INCREMENT BY 1
As you see, the script is not refering to any tables, which means you can use this sequence anywhere by using NEXT VALUE FOR MySequence. For instance:
INSERT INTO MyTable
(NEXT VALUE FOR MySequence, 'Value2')
You can also use this feature as a default constraint. This could be helpful when identity-columns isn’t good enough.