Sql 2008 features - Powered by Kayako SupportSuite Help Desk Software

08 Sep 2010

Support Center » Knowledgebase » Sql 2008 features

	Sql 2008 features

Article SQL 2008 Features
---------------------------
1) Compound Operators
Compound Operator Old Syntax New Syntax
+= (Add EQUALS) SET @Index = @Index + 1 SET @Index += 1
-= (Subtract EQUALS) SET @Step = @Step - 1 SET @Step -= 1
*= (Multiply EQUALS) SET @TotalPriceWithTax = @TotalPriceWithTax * 1.06 SET @TotalPriceWithTax *= 1.06
/= (Divide EQUALS) SET @Level1 = @Level1 / @Total SET @Level1 /= @Total
%= (Modulo EQUALS) SET @Remainder = @Remainder % 3 SET @Remainder /= 3
&= (Bitwise AND EQUALS) SET @BitwiseAND = @BitwiseAND & 8 SET @BitwiseAND &= 8
^= (Bitwise Exclusive OR EQUALS) SET @BitwiseXOR = @BitwiseXOR ^ 128 SET @BitwiseXOR ^= 128
|= (Bitwise OR EQUALS) SET @BitwiseOR = @BitwiseOR | 16 SET @BitwiseOR |= 16
2) Multiple Value Inserts Within a Single INSERT Statement

CREATE TABLE [dbo].[USState] (
[StateCode] CHAR(2),
[StateName] VARCHAR(20)
)
GO

INSERT INTO [dbo].[USState]
VALUES ('AK', 'Alaska'),
('AL', 'Alabama'),
('AR', 'Arkansas'),
('AZ', 'Arizona'),
('CA', 'California')
GO

3)Row Constructor (or Table-Valued Constructor) as Derived Table

SELECT *
FROM (VALUES ('USD', 'U.S. Dollar'),
('EUR', 'Euro'),
('CAD', 'Canadian Dollar'),
('JPY', 'Japanese Yen')) AS [Currency] ( [CurrencyCode], [CurrencyName] )

4)MERGE Statement

CREATE PROCEDURE [dbo].[usp_MergeEmployee]
@EmployeeNumber VARCHAR(10),
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Position VARCHAR(50)
AS

MERGE [dbo].[Employee] AS [Target]
USING (SELECT @EmployeeNumber, @FirstName, @LastName, @Position)
AS [Source] ( [EmployeeNumber], [FirstName], [LastName], [Position] )
ON [Target].[EmployeeNumber] = [Source].[EmployeeNumber]
WHEN MATCHED THEN
UPDATE SET [FirstName] = [Source][FirstName],
[LastName] = [Source].[LastName],
[Position] = [Source].[Position]
WHEN NOT MATCHED THEN
INSERT ( [EmployeeNumber], [FirstName], [LastName], [Position] )
VALUES ( [Source].[EmployeeNumber], [Source].[FirstName],
[Source].[LastName], [Source].[Position] );
GO

5)Table-Valued Parameters

CREATE PROCEDURE [dbo].[usp_ProcessContact]
@Contact ContactTemplate READONLY
AS

-- Update First Name and Last Name for Existing Emails
UPDATE A
SET [FirstName] = B.[FirstName],
[LastName] = B.[LastName]
FROM [dbo].[Contact] A INNER JOIN @Contact B
ON A.[Email] = B.[Email]

-- Add New Email Addresses
INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
SELECT [Email], [FirstName], [LastName]
FROM @Contact A
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Contact] B
WHERE A.[Email] = B.[Email])
GO

Article Details

Article ID:

Created On:

05 Feb 2010 3:40 PM