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 Defender ATP Gets macOS Investigation Support

    The endpoint and detection response (EDR) feature in Microsoft Defender Advanced Threat Protection (ATP) has reached the "general availability" stage for macOS devices.

  • How To Block Self-Service Purchasing in Microsoft's Power Platform

    Microsoft threw Office 365 admins a bone when it gave them the ability to block users from purchasing Power Platform tools without IT approval. Here's how to prevent total anarchy.

  • Azure DevOps Services Losing Support for Alternate Credentials

    Microsoft gave notice last week that it's going to drop Alternate Credentials support for authenticating users of its Azure DevOps Services.

  • Microsoft Endpoint Configuration Manager Update 1910 Released

    Microsoft announced last week that it is starting to deliver Update 1910 for Microsoft Endpoint Configuration Manager users.

comments powered by Disqus

Office 365 Watch

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.