Welcome to EMC Consulting Blogs

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.