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

  • Sync Issues Can Arise for PCs with Poor VPN Connections

    Microsoft this week reminded IT pros that PC connections through virtual private networks can sometimes lead to time synchronization issues, possibly causing reduced functionality for end users.

  • HoloLens 2 Borrows Its Killer Feature from Windows

    Turns out the secret to the HoloLens 2's success has nothing to do with holograms.

  • Microsoft Simplifying VPN Configurations for Its Video Streaming Services

    Microsoft this week announced that it is working on a more simplified way for an organization to leverage local end user Internet connections when accessing Microsoft Stream and Microsoft 365 Live Events video feeds.

  • Microsoft Previews MSIX App Attach for Windows Virtual Desktop

    Microsoft this week indicated in an announcement that the MSIX App Attach capability in the Windows Virtual Desktop service can now be tried via a preview of the Windows 10 Enterprise Multisession operating system.

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.