Inline variable initialization
declare @test int=0;
Powershell
Partition-Aligned Indexed Views
todo
Data Types
- DATE - no time - will make date related queries a lot easier to write
- DATETIME2 - goes back to year zero and is accurate to the nanosecond!
MERGE statement
MERGE SalesArchive AS SA
USING (
SELECT
CustomerID,
LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)),
TotalSalesAmount = SUM(SaleAmount),
TotalSalesCount = COUNT(*)
FROM SalesFeed
GROUP BY CustomerID
) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount)
ON
(
SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate
)
WHEN NOT MATCHED THEN
INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate)
VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE())
WHEN MATCHED THEN
UPDATE
SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount,
SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount,
SA.UpdatedDate = GETDATE();
Multiple rows in the insert statement
INSERT INTO SalesFeed
(CustomerID, Product, SaleAmount)
VALUES
(1,'PoolTable', 1000),
(2,'BigScreen', 955),
(3,'Computer', 590),
(4,'BigScreen', 880),
(5,'Computer', 700)
Grouping Sets
SELECT Product, SUM(SalePrice) AS TotalSales, COUNT(*) AS SaleCount
FROM SalesHistory
GROUP BY GROUPING SETS((Product),())
ORDER BY Product DESC, TotalSales DESC, SaleCount DESC
Change Data Capture
Can eneable CDC on a table for a role which creates a mirror table in the cdc scema to capture any changes to the table. Sprocs exist to control behaviour and even delete contents of the mirror table from a point in time.
Not done by trigger but asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row's data which change.
Minimal Logging
Minimal logging means that only extent allocations will be logged, rather than the actual data copied to the destination. This is a huge plus when moving enough data to fill many new pages.
Existed in 2005 but only for SELECT * INTO -which is always a bit limited.
2008 has it for INSERT INTO X SELECT * FROM y - constraints: has to be a heap and have to provide a tablock hint
insert into t_heap with (TABLOCK) select * from t_source
Star Join Optimization
Under the hood, the query optimiser tries to tell if the query is running on a star schema (If PK/PF are enforced then its obvious, if not then it tries to suss it by largest table vs other table sizes and if all joins are inner). If so it then alters the plan based on selectivity for more performance. Case studies show 15-20% perf increase.
technet
Resource Governor
In server explorer under management.
Works on the concept of resource pools having priority over each other - these can be
any sql object as it is a sql function that decides what is assigned to which pool. this script can be changed to point at anything.
DBCC FREEPROCCACHE
Now has plan handle param so a single plan can be cleared.
Sparse Columns¶
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.