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
|
Posts
