In this edition of T-SQL Tuesday, Jorge Segarra (Blog | Twitter) asks us what our favorite new feature of SQL 2008 or 2008 R2 is. I've decided to focus on the T-SQL and query writing enhancements of 2008. Before I do so though, let me preface this by noting that in no way do I believe these changes are the biggest improvements or best new things in SQL 2008, but things like Data compression are bound to be covered by several others. Also, while writing this post I noticed that most of this has already been covered better than I could hope to by Itzik Ben-Gan in his white paper (Link). Please refer to that for more information on the new features of 2008.
Your New home for One stop Variable Declaration
In the past, you always had to declare variables on one line and then assign them on the next, as so:
DECLARE @MyInt int
SET @MyInt = 44
Now, you can do this in one statement.
DECLARE @MyInt int = 44
That might seem small, but it's significant when you're dealing with large numbers of variables.
SQL++ ... almost
While you still can't do something like:
SET @MyInt++
You can do the slightly longer version of:
SET @MyInt+=@Myint
-- OR
SET @MyInt+=5
Instead of :
SET @MyInt = @MyInt + @MyInt
-- OR
SET @MyInt = @MyInt + 5
This applies to:
+= (plus equals)
-= (minus equals)
*= (multiplication equals)
/= (division equals)
%= (modulo equals)
This one isn't too huge a deal in my opinion, but it's a nice shortcut for those used to using it in other programming languages.
Values of all rows, Union of None
In 2008, you can create multiple lines of data using the VALUES clause. This one is really handy when I'm doing code examples in blog posts / on forums / in presentations etc. It serves as an excellent replacement for the UNION ALL or repeated insert values pairs you used to have to use when supplying sample data.
Say you have a simple table:
CREATE TABLE #Cake(
SomeInt int,
SomeChar char(5)
)
You want to provide some sample data for that table. The most common ways prior to now were either:
INSERT INTO #Cake(SomeInt,SomeChar)
VALUES(1,'AAA')
INSERT INTO #Cake(SomeInt,SomeChar)
VALUES(2,'BBB')
INSERT INTO #Cake(SomeInt,SomeChar)
VALUES(3,'CCC')
-- OR
INSERT INTO #Cake(SomeInt,SomeChar)
SELECT 1,'AAA' UNION ALL
SELECT 2,'BBB' UNION ALL
SELECT 3,'CCC'
Now, you can use the much cleaner:
INSERT INTO #Cake(SomeInt,SomeChar)
VALUES(1,'AAA'),
(2,'BBB'),
(3,'CCC')
-- Or, on 1 line:
INSERT INTO #Cake(SomeInt,SomeChar)
VALUES(1,'AAA'),(2,'BBB'),(3,'CCC')
Lack of Intellisense
No conversation on the topic of coding enhancements would be complete without mentioning Intellisense in some way. So... it's there. It works (kinda). It could use a whole lot more fine tuning and configuration options than are there right now, but if you don't have a copy of SQL Prompt, it's better than not having anything (sometimes). If you *do* have a copy of SQL Prompt and want to use a couple of the cool things SQL intellisense has that SQL Prompt does not, you can use the hybrid approach that I've gone with. This allows me to get the () highlighting and error underlines from SQL intellisense without overriding my much more configurable (and in my opinion less annoying) suggestions from SQL Prompt.
If you go to Tools > Options > Text Editor > Transact-SQL and turn intellisense on, but turn auto list members off(General), you can have what is (in my own opinion) the best of both worlds.
That about wraps up what I wanted to point out this go round, hopefully you found something new that you didn't know about before. Don't forget to check out all the other T-SQL Tuesday posts (Click the image at the top for a link to the others) that will no doubt point out many of the much bigger improvements in SQL Server 2008 and R2.
