Essential SQL Server Commands: From Basic CRUD to Advanced Replication and Automation

Basic SQL Server Commands

CREATE DATABASE database_name

– create a new database. DROP DATABASE dbname – delete an existing database.

Backup example:

USE master;
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7\backup\MyNwind_1.dat';
BACKUP DATABASE pubs TO testBack;

CREATE TABLE tabname(col1 type1 [NOT NULL] [PRIMARY KEY], col2 type2 ...)

– define a new table.

Copy table structure:

CREATE TABLE tab_new LIKE tab_old;   -- SQL Server syntax
CREATE TABLE tab_new AS SELECT col1, col2 FROM tab_old;

DROP TABLE tabname

– remove a table. ALTER TABLE tabname ADD column col TYPE – add a column (cannot be dropped later in DB2). ALTER TABLE tabname ADD PRIMARY KEY(col) – add a primary key; ALTER TABLE tabname DROP PRIMARY KEY(col) – remove it. CREATE [UNIQUE] INDEX idxname ON tabname(col…) – create an index; DROP INDEX idxname – delete it. CREATE VIEW viewname AS SELECT … – create a view; DROP VIEW viewname – delete it.

Basic DML examples:

SELECT * FROM table1 WHERE condition;
INSERT INTO table1 (field1, field2) VALUES (value1, value2);
DELETE FROM table1 WHERE condition;
UPDATE table1 SET field1 = value1 WHERE condition;
SELECT COUNT(*) AS totalcount FROM table1;
SELECT SUM(field1) AS sumvalue FROM table1;
SELECT AVG(field1) AS avgvalue FROM table1;
SELECT MAX(field1) AS maxvalue FROM table1;
SELECT MIN(field1) AS minvalue FROM table1;

Set operators:

UNION   – combine results, remove duplicates.
UNION ALL – combine results, keep duplicates.
EXCEPT  – rows in first query not in second.
INTERSECT – rows common to both queries.

Join types:

LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN

GROUP BY – aggregate functions (COUNT, SUM, MAX, MIN, AVG) cannot use TEXT/NTEXT/IMAGE columns in SQL Server.

Advanced Operations

Copy table data only: SELECT * INTO b FROM a WHERE 1=1; Cross‑database copy:

INSERT INTO b (a, b, c) SELECT d, e, f FROM a IN 'remote_db_path' WHERE condition;

Subquery example:

SELECT a, b, c FROM a WHERE a IN (SELECT d FROM b);
SELECT a, b, c FROM a WHERE a IN (1,2,3);

Retrieve latest article, submitter, and reply time:

SELECT a.title, a.username, b.adddate
FROM table a,
     (SELECT MAX(adddate) AS adddate FROM table WHERE table.title = a.title) b;

Random row selection:

SELECT TOP 10 * FROM tablename ORDER BY NEWID();

Pagination example (SQL Server 2005+):

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

Techniques and Tips

Using WHERE 1=1 to simplify dynamic query building; WHERE 1=2 returns no rows.

When using TOP, a variable cannot follow directly; you must build the statement dynamically.

To avoid mismatched index order, always add an ORDER BY clause when paging.

SQL Server Built‑in Functions

DATALENGTH(col)

– returns number of bytes, excluding trailing spaces. SUBSTRING(expr, start, length) – extracts a substring (1‑based index). RIGHT(col, n) / LEFT(col, n) – get characters from the right or left. ISNULL(expr, replacement) – replace NULL with a value. EXEC sp_addtype birthday, datetime, 'NULL' – create a user‑defined data type. SET NOCOUNT ON|OFF – suppress or show the row‑count message for better performance.

Common Knowledge

SQL Server can reference up to 256 tables or views in a single FROM clause.

When ORDER BY is present, sorting occurs before row limiting.

Maximum size of a non‑Unicode column is 8000 bytes; NVARCHAR(4000) uses 2 bytes per character because it stores Unicode.

Replication and Synchronization (SQL Server 2000)

Step‑by‑step configuration of publishing, subscription, and distribution servers, including creating a shared snapshot folder, setting SQL Server Agent service accounts, enabling mixed authentication, registering servers, and configuring snapshot agents.

Linking Servers for Cross‑Database Sync

EXEC sp_addlinkedserver 'srv2', '', 'SQLOLEDB', 'srv2_instance_or_ip';
EXEC sp_addlinkedsrvlogin 'srv2', 'false', NULL, 'username', 'password';
GO

Start the Distributed Transaction Coordinator (MSDTC) on both servers and set it to automatic start.

Synchronization Stored Procedure

CREATE PROC p_process AS
-- Update changed rows
UPDATE b SET name = i.name, telphone = i.telphone
FROM srv2.DatabaseName.dbo.author b
JOIN author i ON b.id = i.id
WHERE b.name <> i.name OR b.telphone <> i.telphone;

-- Insert new rows
INSERT INTO srv2.DatabaseName.dbo.author (id, name, telphone)
SELECT id, name, telphone FROM author i
WHERE NOT EXISTS (SELECT * FROM srv2.DatabaseName.dbo.author WHERE id = i.id);

-- Delete removed rows (optional)
DELETE b FROM srv2.DatabaseName.dbo.author b
WHERE NOT EXISTS (SELECT * FROM author WHERE id = b.id);
GO

Scheduling with SQL Server Agent

Create a job, add a step that executes EXEC p_process against the appropriate database, and define a schedule (e.g., recurring every hour). Ensure the SQL Server Agent service is set to start automatically.

Removed Non‑Technical Content

All promotional social‑media references, QR‑code instructions, and decorative images have been omitted to keep the focus on technical guidance.