T-SQL Enhancements in SQL Server 2008 phase-1

Hello,

we know that microsoft has released new version of sql server i.e. 2012 but developers are using old features in development.

I will show you development features of mssql2008 and mssql2012.

Today I am going to represent phase-1 of mssql 2008 development features.

T-SQL Miscellaneous

  • Declare and Initialize Variables
  • Compound Assignment Operators
  • Row Constructors
  • Composable DML

1. Declare and Initialize Variables

  • Can now declare and initialize variables in the same statement
  • Value is a self-contained expression of the target type (or at least implicitly convertible)

Example :-

DECLARE
   @i AS INT = 0,
   @s AS NVARCHAR(50) = (SELECT firstName FROM TableName WHERE employeeid = 1),
   @d AS DATETIME = CURRENT_TIMESTAMP;

2. Compound Assignment Operators

  • New assignment operators:
    +=, -=, *=, /=, %=

Example :-

UPDATE dbo.Products SET UnitPrice *= 2.05
-- Also try += 10, -= 10, /= 2, UnitsInStock %= 10

3. Row Constructors

  • VALUES clause returns relational table with multiple rows
  • Use with INSERT statement to insert multiple rows as an atomic operation:

Example :-

INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

4. Composable DML

  • Combine multiple DML actions in one statement
  • Have one statement operate on rows affected by another
  • SQL Server 2008 introduces INSERT FROM DML:

INSERT INTO SELECT … FROM () AS D WHERE …;

Example :-

  • Update all products supplied by supplier 1;
  • increase the unit price by 15%
  • Audit only product updates that changed from below 20.0 to 20.0 or more
INSERT INTO dbo.ProductsAudit(ProductID, ColName, OldVal, NewVal)
SELECT ProductID, N'UnitPrice', OldVal, NewVal
FROM (UPDATE dbo.Products
SET UnitPrice *= 1.15
OUTPUT
inserted.ProductID,
deleted.UnitPrice AS OldVal,
inserted.UnitPrice AS NewVal
WHERE SupplierID = 1) AS D
WHERE OldVal < 20.0 AND NewVal >= 20.0;

That’s it. Now I will explain phase-2 in coming days.