Mastering SQL Server: Essential Commands, Advanced Queries, and Maintenance Techniques

Basic Operations

Common DDL statements include creating a database with CREATE DATABASE database_name, dropping a database with DROP DATABASE dbname, and creating a new table using

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

. To copy a table structure only, use CREATE TABLE tab_new LIKE tab_old or CREATE TABLE tab_new AS SELECT col1, col2 FROM tab_old. Deleting a table is done via DROP TABLE tabname. Adding a column: ALTER TABLE tabname ADD COLUMN col type (note that once added, columns cannot be removed in DB2). Primary keys can be added with ALTER TABLE tabname ADD PRIMARY KEY(col) and removed with ALTER TABLE tabname DROP PRIMARY KEY(col). Index creation uses CREATE [UNIQUE] INDEX idxname ON tabname(col...) and removal with DROP INDEX idxname (indexes cannot be altered directly). Views are created with CREATE VIEW viewname AS SELECT ... and dropped with DROP VIEW viewname.

Basic SQL Statements

Select: SELECT * FROM table1 WHERE condition Insert: INSERT INTO table1(field1, field2) VALUES(value1, value2) Delete: DELETE FROM table1 WHERE condition Update: UPDATE table1 SET field1 = value1 WHERE condition Search with LIKE: SELECT * FROM table1 WHERE field1 LIKE '%value1%' Order: SELECT * FROM table1 ORDER BY field1, field2 [DESC] Count: SELECT COUNT(*) AS totalcount FROM table1 Sum: SELECT SUM(field1) AS sumvalue FROM table1 Average: SELECT AVG(field1) AS avgvalue FROM table1 Max/Min: SELECT MAX(field1) AS maxvalue FROM table1 /

SELECT MIN(field1) AS minvalue FROM table1

Advanced Query Operators

UNION: Combines result sets and removes duplicates; UNION ALL retains duplicates.

EXCEPT: Returns rows from the first query that are not present in the second; EXCEPT ALL retains duplicates.

INTERSECT: Returns rows common to both queries; INTERSECT ALL retains duplicates.

Join Techniques

Left (outer) join returns all rows from the left table and matching rows from the right: SELECT ... FROM a LEFT OUTER JOIN b ON a.key = b.key. Right join returns all rows from the right table: SELECT ... FROM a RIGHT OUTER JOIN b ON a.key = b.key. Full (cross) join returns all rows from both tables: SELECT ... FROM a FULL OUTER JOIN b ON a.key = b.key.

Grouping and Aggregation

Use GROUP BY to aggregate data. In SQL Server, text, ntext, and image columns cannot be used as grouping keys. Aggregate functions (COUNT, SUM, MAX, MIN, AVG) cannot be mixed with non‑aggregated columns in the same SELECT without proper grouping.

Database Administration

Detach/Attach: sp_detach_db and sp_attach_db 'path oile.mdf'.

Rename Database: sp_renamedb 'old_name', 'new_name'.

Backup & Restore: BACKUP DATABASE pubs TO testBack, RESTORE VERIFYONLY FROM DISK='E:\dvbbs.bak'.

Shrink & Rebuild Indexes: DBCC REINDEX, DBCC INDEXDEFRAG, DBCC SHRINKDB, DBCC SHRINKFILE.

Change Owner: exec sp_changeobjectowner 'tablename','dbo' or batch procedure dbo.User_ChangeObjectOwnerBatch.

Log Truncation: Use BACKUP LOG db_name WITH TRUNCATE_ONLY and DBCC CHECKDB('db_name', REPAIR_ALLOW_DATA_LOSS).

SQL Scripting Techniques

Dynamic WHERE clauses often use WHERE 1=1 to simplify concatenation. Example for counting rows:

IF @strWhere <> '' BEGIN SET @strSQL = 'SELECT COUNT(*) AS Total FROM ['+@tblName+'] WHERE '+@strWhere END ELSE BEGIN SET @strSQL = 'SELECT COUNT(*) AS Total FROM ['+@tblName+']' END

. Loop insertion:

DECLARE @i INT = 1; WHILE @i < 30 BEGIN INSERT INTO test(userid) VALUES(@i); SET @i = @i + 1; END

. Pagination example:

SELECT TOP 10 * FROM (SELECT TOP 15 * FROM table ORDER BY id ASC) t ORDER BY id DESC

.

Data Development (Linked Servers & Replication)

Create a linked server:

exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', 'remote_server_ip'

and login:

exec sp_addlinkedsrvlogin 'ITSV', 'false', NULL, 'username', 'password'

. Query remote data: SELECT * FROM ITSV.DatabaseName.dbo.TableName. Import data:

SELECT * INTO LocalTable FROM ITSV.DatabaseName.dbo.TableName

. Use OPENROWSET or OPENQUERY for ad‑hoc remote queries. For synchronization between two servers, a stored procedure can update, insert, and optionally delete mismatched rows, and be scheduled via a SQL Server Agent job.

SQL Server Built‑in Functions

DATALENGTH: Returns the number of bytes, excluding trailing spaces.

SUBSTRING: SUBSTRING(expression, start, length) extracts a substring (1‑based index).

RIGHT / LEFT: Returns characters from the right or left side of a string.

ISNULL: ISNULL(check_expression, replacement) substitutes a value when the expression is NULL.

sp_addtype: Defines a user‑defined data type, e.g., EXEC sp_addtype birthday, datetime, 'NULL'.

SET NOCOUNT: Controls whether the count of affected rows is returned; SET NOCOUNT ON improves performance for procedures that do not need row counts.

Common Knowledge

SQL Server can reference up to 256 tables or views in a single query. The ORDER BY clause is evaluated after the result set is generated. The maximum size of a column is 8000 bytes; NVARCHAR(4000) stores Unicode characters.

Images