Mastering SQL Server: Essential Commands, Queries, and Administration Tips

1. Basic Operations

Login to the database (e.g., mysql -uroot -p123123), create and drop databases, create tables, add columns, define primary keys, create indexes and views, and perform common SELECT, INSERT, UPDATE, DELETE statements.

2. Advanced Queries

Use set operators (UNION, EXCEPT, INTERSECT), outer joins (LEFT, RIGHT, FULL), GROUP BY, TOP and pagination techniques, and conditional clauses such as WHERE 1=1, BETWEEN, IN, and NOT IN.

3. Database Maintenance

Backup and restore databases, shrink and compress data files, rebuild indexes, change object owners, and manage linked servers for cross‑server data transfer using sp_addlinkedserver, openrowset, openquery, and opendatasource.

4. SQL Server Functions and Settings

String functions (DATALENGTH, SUBSTRING, LEFT, RIGHT), NULL handling with ISNULL, custom data types via sp_addtype, and session settings such as SET NOCOUNT ON. System procedures for integrity checks and metadata retrieval are also covered.

5. Replication and Synchronization

Configure publication and subscription servers, create snapshots, set up distribution, and write stored procedures to synchronize tables between servers, handling inserts, updates, and deletions in a scheduled job.

6. Useful Scripts

Examples include dynamic SQL construction, looped inserts, scripts to list tables, columns, indexes, and user‑defined objects, as well as scripts for checking checksum equality between tables.

Illustrative images are included in the original article.

-- Example: create a database
CREATE DATABASE MyDatabase;

-- Example: create a table
CREATE TABLE dbo.Employee(
    Id INT PRIMARY KEY,
    Name NVARCHAR(50),
    Salary MONEY
);

-- Example: add a column
ALTER TABLE dbo.Employee ADD HireDate DATE;

-- Example: create an index
CREATE UNIQUE INDEX IX_Employee_Name ON dbo.Employee(Name);

-- Example: backup a database
BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak';

-- Example: linked server query
SELECT * FROM RemoteServer.DatabaseName.dbo.TableName;