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 Adds Modular Datacenter to Azure Space Efforts

    Microsoft this week introduced the Microsoft Azure Modular Datacenter as part of its overall Azure Space effort.

  • Microsoft and Partners Continue To Block Trickbot To Protect Elections

    Microsoft on Tuesday provided an update about its efforts, along with partners, to take down the Trickbot criminal network, which uses servers and devices to spread ransomware.

  • Microsoft Releases Windows 10 and Windows Server Versions 20H2

    Microsoft on Tuesday announced the "semiannual channel" release of Windows 10 version 20H2, otherwise known as the "October 2020 Update," and it also released Windows Server version 20H2.

  • How To Debug a PowerShell Script

    Here are three pointers for finding and fixing any bugs in your PowerShell script, no matter how long it is.

comments powered by Disqus