Comprehensive SQL Server Database Operations and Techniques Guide

This guide presents a wide range of SQL Server commands and best practices for managing relational databases.

Basic Operations

Creating and dropping databases and tables:

CREATE DATABASE database_name;
DROP DATABASE dbname;
CREATE TABLE tabname(col1 type1 NOT NULL PRIMARY KEY, col2 type2 NOT NULL, ...);
DROP TABLE tabname;

Adding columns, primary keys, and indexes:

ALTER TABLE tabname ADD column_b INT IDENTITY(1,1);
ALTER TABLE tabname ADD PRIMARY KEY(col);
CREATE UNIQUE INDEX idxname ON tabname(col...);
DROP INDEX idxname ON tabname;

Data Manipulation

Insert, update, delete, and select statements with examples of batch inserts, subqueries, and conditional updates:

INSERT INTO tabname(a,b) VALUES (1,2);
UPDATE tabname SET col = col*1.01 WHERE col < 60;
DELETE FROM tabname WHERE id NOT IN (SELECT MAX(id) FROM tabname GROUP BY col1, col2);
SELECT * FROM table1 WHERE field1 LIKE '%value%';

Advanced Queries

Set operations (UNION, EXCEPT, INTERSECT), joins (LEFT, RIGHT, FULL), grouping, and pagination techniques:

SELECT * FROM a UNION ALL SELECT * FROM b;
SELECT * FROM a EXCEPT SELECT * FROM b;
SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.id;
SELECT col, COUNT(*) FROM tab GROUP BY col;
SELECT TOP 5 * FROM (SELECT TOP 15 * FROM table ORDER BY id ASC) t ORDER BY id DESC;

Dynamic SQL and Stored Procedures

Building dynamic queries with sp_executesql and handling pagination:

DECLARE @sql NVARCHAR(600);
SET @sql = 'SELECT TOP ' + CAST(@end-@start+1 AS VARCHAR) + ' * FROM T WHERE rid NOT IN (SELECT TOP ' + CAST(@start-1 AS VARCHAR) + ' rid FROM T)';
EXEC sp_executesql @sql;

Performance and Maintenance

Rebuilding indexes, shrinking databases, and checking integrity:

DBCC REINDEX('tablename');
DBCC SHRINKDB;
DBCC CHECKDB('dbname') WITH TABLOCK;

Security and Encryption

SELECT ENCRYPT('original_password');
SELECT PWDENCRYPT('original_password');
SELECT PWDCOMPARE('original','encrypted') = 1;

Linked Servers and Distributed Queries

Creating linked servers and using OPENROWSET , OPENQUERY , and OPENDATASOURCE for cross‑server data access:

EXEC sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', 'remote_server_ip';
SELECT * FROM ITSV.database.dbo.table;
SELECT * FROM OPENROWSET('SQLOLEDB','server;uid=user;pwd=pass', 'SELECT * FROM db.dbo.table');
SELECT * FROM OPENQUERY(ITSV, 'SELECT * FROM db.dbo.table');

Replication Setup

Steps to configure publishing, subscription, and distribution for SQL Server replication, including snapshot configuration and agent scheduling.

Overall, the article serves as a practical reference for SQL Server developers and DBAs, covering essential commands, advanced query patterns, performance tuning, security, and multi‑server integration.