- In SQL Server 2005, it is recommended to use the table valued dynamic management function sys.dm_db_index_physical_stats instead of the DBCC SHOWCONTIG command.
RANKING
4 new *functions* and a keyword:
ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE()
SELECT name,
ROW_NUMBER() OVER (ORDER BY name) as row_number,
RANK() OVER (ORDER BY name) as rank,
DENSE_RANK() OVER (ORDER BY name) as dense_rank,
NTILE(2) OVER (ORDER BY name) as tile
FROM people
ORDER BY nameIf there are 2 identical rows at the top of the list the rank will leave a space before 3rd but dense_rank will not.
NTILE takes a number which is the amount tiles over the whole result set to apply the same position number to.
EXCEPT AND INTERSECT
Versions of UNION - obvious what they do
SYNONYM
CREATE SYNONYM barry FOR EMPLOYEES
ERROR HANDLING
Trans thron in for extra goodness:
BEGIN TRY
BEGIN TRANSACTION
SELECT SYNTAX ERROR
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISEERROR('SYNTAX ERROR',16,1)
END CATCHEXECUTE
Now has an AT param so it the sql can be exed on a linked server.
CATALOG VIEWS
Contain the static meta-data of the db - think sysobjects, syscolumns, sysindexes.
Anything beginning sys.x is a catalog view:
sys.columns
sys.tables
sys.sql_modules
sys.sql_dependencies
sys.procedures
etc...
The system views folder list them all as well as:
DYNAMIC MANAGEMENT VIEWS
Also found in the system views folder.
dm prefix - contain dynamic info like locks and exe plans.
SNAPSHOT ISOLATION LEVEL
www.sql-server-performance.com article
- READ_COMMITTED_SNAPSHOT
- ALLOW_SNAPSHOT_ISOLATION
DDL TRIGGERS
Will fire upon meta data changes on either a server or database wide level.
Good for auditting changes - possibly on the quite.
CHECKSUM - builds a checsum value for specified columns. Can be used to simplify pesky a.a = b.a AND a.b = b.b AND a.c = b.c etc as you just compare the already calculated and stored in a col checksum value of 1 col against the checksum of the other.
PIVOT & UNPIVOT