SQL Server 2008 definitely has loads of improvements over previous versions, however there are some cool new features that are overlooked which could improve productivity and ease usability, so they are definitely worth having a look at.
1. Compound Operators
This new feature do make some interactions patently easier, especially if you're comfortable with the greater range of assignment operators available in C# (and VB.NET ).
For example, prior to SQL Server 2008, if you wanted to append text or increment a variable, you needed to do it in a rather verbose manner, as follows:
-- declare:
DECLARE @myInt int
DECLARE @myString varchar(10)
-- initialize:
SET @myInt = 5
SET @myString = '123'
-- increment:
SET @myInt = @myInt + 5
SET @myString = @myString + '456'
SELECT @myInt, @myString
But with SQL Server 2008, you can do it a bit more logically and more tersely like so:
-- declare and init:
DECLARE @myInt int = 5
DECLARE @myString varchar(10) = '123'
-- increment:
SET @myInt += 5
SET @myString += '456'
SELECT @myInt, @myString
It also works with all of the standard mathematical assignment operators including bitwise operations which can help make interacting with bitmaps a bit easier as well.
2. Inline Variable Assignments
For a database developer who spends a lot of time writing stored procedures and playing around with variables, this feature gives the ability to new-up a variable (similar to how it’s done in C# or VB.NET) to a specified value.
An example, prior to SQL Server 2008, if you wanted to create a new datetime variable and set it equal to right now, you’d to do that in two steps, like so:
DECLARE @myNow datetime
SET @myNow = GETDATE()
SELECT @myNow AS TimeNow
But with SQL Server 2008, you can do that in a single line of code, as follows:
DECLARE @myNow datetime = GETDATE()
SELECT @myNow AS TimeNow
This minor change helps improve code readability by letting you instantiate variables and assign their initial value inline while simultaneously providing a minor productivity boost as well.
3. Row Constructors
This is about Bulk Inserts or the ability to INSERT multiple, static, rows with a single INSERT statement. For example, if you are creating a simple table defining user types and want to populate it with some initial values, each value will require its own INSERT statement like so:
CREATE TABLE OfflimtRules (
OfflimtRuleID int IDENTITY(1,1) NOT NULL,
OfflimtRule varchar(20) NOT NULL
)
INSERT INTO OfflimtRules (OfflimtRule)
VALUES (‘ClientA’)
INSERT INTO OfflimtRules (OfflimtRule)
VALUES (‘SC’)
INSERT INTO OfflimtRules (OfflimtRule)
VALUES (‘AC’)
The above can also be achieved by SELECTing rows into a table using a UNION, for those that hate typing VALUES and open/close parenthesis:
INSERT INTO OfflimtRules (OfflimtRule)
SELECT ‘ClientA’
UNION SELECT ‘SC’
UNION SELECT ‘AC’
In SQL Server 2008 and you can now insert multiple rows within a single INSERT statement with T-SQL 2008's new Row Constructors:
CREATE TABLE OfflimtRules (
OfflimtRuleID tinyint IDENTITY(1,1) NOT NULL,
OfflimtRule varchar(20) NOT NULL
)
INSERT INTO OfflimtRules (OfflimtRule)
VALUES (‘ClientA’),(‘SC’),(‘AC’)
Again, this isn’t a huge change, but a very nice improvement that can really help boost usability and increase productivity. (Too bad there's no sensible way to ditch those parenthesis though.)
Conclusion:
I agree that all three of these new additions are rather minor when compared to things like Full Text Integration, sparse columns, FILESTREAM storage, Table-Valued Parameters, and a host of other improvements and additions, but they all represent subtle improvements and the benefits cannot be overlooked.