Mastering SQL Server: Essential Commands, Tips, and Advanced Techniques

1. Basics

Explanation and commands for creating and managing databases and tables.

Create Database CREATE DATABASE database_name Drop Database DROP DATABASE dbname Create Table

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

Drop Table DROP TABLE tabname Add Column ALTER TABLE tabname ADD COLUMN col type Add Primary Key ALTER TABLE tabname ADD PRIMARY KEY(col) Drop Primary Key ALTER TABLE tabname DROP PRIMARY KEY(col) Create Index CREATE [UNIQUE] INDEX idxname ON tabname(col...) Drop Index DROP INDEX idxname Create View CREATE VIEW viewname AS SELECT ... Drop View

DROP VIEW viewname

2. Common SQL Statements

Basic DML operations.

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 * FROM table1 WHERE field1 LIKE '%value1%'

SELECT * FROM table1 ORDER BY field1, field2 [DESC]

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

3. Advanced Query Operators

Set operators and joins.

UNION SELECT ... FROM TABLE1 UNION SELECT ... FROM TABLE2 EXCEPT SELECT ... FROM TABLE1 EXCEPT SELECT ... FROM TABLE2 INTERSECT SELECT ... FROM TABLE1 INTERSECT SELECT ... FROM TABLE2 Outer Joins

SELECT a.*, b.* FROM a LEFT OUTER JOIN b ON a.id = b.id

SELECT a.*, b.* FROM a RIGHT OUTER JOIN b ON a.id = b.id

SELECT a.*, b.* FROM a FULL OUTER JOIN b ON a.id = b.id

4. Grouping and Aggregation

SELECT type, SUM(CASE WHEN vender='A' THEN pcs ELSE 0 END) AS A_total, ... FROM tablename GROUP BY type

5. Database Maintenance

Backup, restore, shrink, and repair.

BACKUP DATABASE pubs TO testBack

RESTORE VERIFYONLY FROM DISK='E:\dvbbs.bak'

DBCC REINDEX

DBCC INDEXDEFRAG

DBCC SHRINKDB

DBCC SHRINKFILE

DBCC SHRINKDATABASE(dbname)

ALTER DATABASE [dvbbs] SET SINGLE_USER

DBCC CHECKDB('dvbbs', REPAIR_ALLOW_DATA_LOSS) WITH TABLOCK

ALTER DATABASE [dvbbs] SET MULTI_USER

6. Linked Servers and Distributed Queries

Creating linked servers and using OPENROWSET, OPENQUERY, OPENDATASOURCE.

EXEC sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', 'remote_server_ip'

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

SELECT * FROM ITSV.database_name.dbo.table_name

SELECT * FROM OPENROWSET('SQLOLEDB', 'server;uid=username;pwd=password', 'SELECT * FROM database.dbo.table')

INSERT INTO OPENROWSET('SQLOLEDB', 'server;uid=username;pwd=password', 'database.dbo.table') SELECT * FROM local_table

7. Scripting and Automation

Examples of loops, dynamic SQL, and scheduled jobs.

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

DECLARE @sql NVARCHAR(1000); SET @sql = 'SELECT * FROM ' + @table; EXEC(@sql)

8. Miscellaneous Utilities

Retrieving metadata, checking objects, and other handy queries.

SELECT name FROM sysobjects WHERE type='U'

SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName')

SELECT name FROM sysobjects WHERE xtype='P'

SELECT * FROM master..sysdatabases WHERE sid NOT IN (SELECT sid FROM master..syslogins WHERE name='sa')

SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'TableName'

Note: All promotional sections, author bios, and unrelated ads have been removed.