Comprehensive SQL Server Cheat Sheet: Commands, Queries, and Administration Tips

This article serves as a detailed reference for Microsoft SQL Server, presenting essential DDL and DML statements as well as advanced administrative techniques.

Basic database objects are created and modified with examples such as CREATE DATABASE database_name , CREATE TABLE tabname(col1 type1, col2 type2) , ALTER TABLE tabname ADD column_name int , and DROP TABLE tabname .

Common data‑retrieval queries demonstrate SELECT syntax, filtering, ordering, grouping, and aggregation: SELECT * FROM table1 WHERE column1 = 'value' , SELECT col, COUNT(*) FROM table GROUP BY col , and set operations like SELECT * FROM a UNION SELECT * FROM b , EXCEPT , INTERSECT . Join examples include inner, left, right, and full outer joins.

Advanced administration topics cover index maintenance ( DBCC REINDEX , DBCC INDEXDEFRAG ), database shrinking ( DBCC SHRINKDB , DBCC SHRINKFILE ), backup and restore ( BACKUP DATABASE db TO DISK='path' , RESTORE VERIFYONLY ), encryption functions ( SELECT ENCRYPT('password') ), and changing object ownership ( EXEC sp_changeobjectowner 'tablename','dbo' ).

The guide also includes scripts for pagination using TOP, batch updates with WHILE loops, and a stored procedure p_process that synchronizes tables between linked servers, illustrating how to insert, update, and delete rows based on differences.

Additional tips discuss using SET NOCOUNT ON to reduce network traffic, ISNULL for null handling, querying system catalogs ( SELECT name FROM sysobjects WHERE type='U' ), and configuring linked servers, openrowset, openquery, and opendatasource for cross‑server data access.