Worthwhile T-SQL Additions

Compound operators offer coding shortcuts, while the INSERT statement lets you ask for rows of data without wearing out the keyboard.

Last time, we started looking at some of the new features offered in T-SQL in SQL Server 2008. This time, I want to take a closer look at some of the smaller additions, but additions that are likely to pack a big punch in terms of efficiency.

First let's talk a little about compound operators. This is a concept that has been around in programming languages for a long time, but has just now found its way into T-SQL. For example, the += operator will add the values to the current variable and then assign the new value to the variable. In other words, SET @ctr += 1 is functionally the same as SET @ctr = @ctr + 1. The shorthand version is a little quicker to type and offers a cleaner piece of finished code. Here's a list of the compound operators:

+= Add EQUALS
-= Subtract EQUALS
*= Multiply EQUALS
/= Divide EQUALS
%= Modulo EQUALS
&= Bitwise AND EQUALS
^= Bitwise Exclusive OR EQUALS
|= Bitwise OR EQUALS

Starting in SQL Server 2008, you can now set a variable's value at the same time you declare it. The code will declare a variable named @ctr of type int and set its value to 100:

DECLARE @ctr int = 100

This was previously only possible with parameters, but now it works with all variable declarations.

Last, but certainly not least, the INSERT statement will accept multiple row predicates on the VALUES clause. In other words, I can insert multiple rows with a single INSERT statement. The following example shows the old syntax and the new multi-row INSERT syntax:

Pre-SQL Server 2008
INSERT SALES (customer_id, year, sales_amt) VALUES (1,2007,25000)
INSERT SALES (customer_id, year, sales_amt) VALUES (1,2008,22000)
INSERT SALES (customer_id, year, sales_amt) VALUES (1,2009,15000)
INSERT SALES (customer_id, year, sales_amt) VALUES (2,2007,35500)
INSERT SALES (customer_id, year, sales_amt) VALUES (2,2008,56800)
INSERT SALES (customer_id, year, sales_amt) VALUES (2,2009,65600)

SQL Server 2008 Multi-Row INSERT
INSERT SALES (customer_id, year, sales_amt)
VALUES (1,2007,25000),
  (1,2008,22000),
  (1,2009,15000),
  (2,2007,35500),
  (2,2008,56800),
  (2,2009,65600)

We certainly haven't covered everything that has been added to T-SQL in SQL Server 2008, but these are few of the more useful little additions.

About the Author

Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology consulting. He is also the President of the Colorado Springs SQL Server User Group. He can be contacted at www.consortioservices.com.

Featured

  • Microsoft Warns IT Pros on Windows Netlogon Fix Coming Next Month

    Microsoft on Thursday issued a reminder to organizations to ensure that their systems are properly patched for a "Critical"-rated Windows Netlogon vulnerability before next month's "update Tuesday" patch distribution arrives.

  • Microsoft Nudging Skype for Business Users to Teams

    Microsoft on Thursday announced some perks and prods for Skype for Business unified communications users, with the aim of moving them to the Microsoft Teams collaboration service instead.

  • How To Improve Windows 10's Sound and Video Quality

    Windows 10 comes with built-in tools that can help users get the most out of their sound and video hardware.

  • Microsoft Offers More 'Solorigate' Advice Using Microsoft 365 Defender Tools

    Microsoft issued yet another article with advice on how to use its Microsoft 365 Defender suite of tools to protect against "Solorigate" advanced persistent threat types of attacks in a Thursday announcement.

comments powered by Disqus