What's New in SQL 2008 - Part 1 - Functionality

Function	Usage Example	Notes
Declare & Initialise	DECLARE @iValue as integer = 100;
Compound Assignments	SET @iValue -= 50 UPDATE dimCustomer SET Balance += 200	+= -= *= /= %=
Multi-Row Inserts Using VALUES	INSERT INTO dimProduct (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate) VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00'), ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00'), ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00')	The insert is executed as a single transaction. Interestingly, it can also be used like this: SELECT * FROM (VALUES ('Grey Slate Paver 10x10',1111,'dbo','20091001 13:12:00') ('Blue Slate Paver 10x10',1112,'dbo','20091001 13:12:00') ('Black Slate Paver 10x10',1113,'dbo','20091001 13:12:00') ) as p (Label, BusinessKey,LastUpdatedBy,LastUpdatedDate)
MERGE	MERGE INTO dbo.dimProduct as d USING dbo.dimProductStage as s ON d.ProductKey = s.ProductId WHEN MATCHED THEN UPDATE SET d.ProductName = s.ProductName, d.ProductCategory = s.ProductCategory WHEN NOT MATCHED THEN INSERT (ProductName,ProductCategory) VALUES (s.ProductName, s.ProductCategory) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, deleted.ProductId as DeletedId, inserted.ProductId as InsertedId;
Grouping Sets	SELECT Year(OrderDate) as OrderYear, SalespersonId, OrderType, count(OrderId) as OrderCount, sum(Amount) as OrderAmount FROM factOrders GROUP BY GROUPING SETS ( (OrderType, Year(OrderDate)), (SalespersonId,OrderType, Year(OrderDate)), (SalespersonId, Year(OrderDate)), (Year(OrderDate)), ());	() empty brackets represents an 'ALL' grouping
Filtered Indexes	CREATE NONCLUSTERED INDEX idxOrders_Product ON factOrders(OrderId,ProductId) WHERE CreditFlag IS NULL
Change Data Capture	** to be discussed in further detail in an upcoming post **	Tracks data changes into a table. Enabled using sys.sp_cdc_enable_db or sys.sp_cdc_enable_table Disabled using sys.sp_cdc_disable_db or sys.sp_cdc_disable_table Uses SQL Agent