SQL Server Cheatsheet
#
SQL Server is a relational database management system developed by Microsoft. It is widely used for its robust performance and integration with other Microsoft products.
General Commands
#
| Command/Option | Example | Description |
|---|
sqlcmd -S <server_name> -U <username> -P <password> | sqlcmd -S localhost -U sa -P password123 | Connect to a SQL Server instance using sqlcmd |
exit | exit | Exit sqlcmd |
Database Management
#
| Command/Option | Example | Description |
|---|
CREATE DATABASE <db_name>; | CREATE DATABASE mydb; | Create a new database |
DROP DATABASE <db_name>; | DROP DATABASE mydb; | Delete a database |
USE <db_name>; | USE mydb; | Switch to a specific database |
Table Management
#
| Command/Option | Example | Description |
|---|
CREATE TABLE <table_name> (column_definitions); | CREATE TABLE employees (id INT PRIMARY KEY, name NVARCHAR(100)); | Create a new table |
DROP TABLE <table_name>; | DROP TABLE employees; | Delete a table |
ALTER TABLE <table_name> ADD (column_name datatype); | ALTER TABLE employees ADD hire_date DATE; | Add a column to a table |
ALTER TABLE <table_name> DROP COLUMN <column_name>; | ALTER TABLE employees DROP COLUMN hire_date; | Drop a column from a table |
EXEC sp_help <table_name>; | EXEC sp_help employees; | Describe the structure of a table |
Data Manipulation
#
| Command/Option | Example | Description |
|---|
INSERT INTO <table> (columns) VALUES (values); | INSERT INTO employees (name) VALUES ('John Doe'); | Insert new data into a table |
SELECT * FROM <table>; | SELECT * FROM employees; | Retrieve all data from a table |
UPDATE <table> SET column = value WHERE condition; | UPDATE employees SET name = 'Jane Doe' WHERE id = 1; | Update existing data in a table |
DELETE FROM <table> WHERE condition; | DELETE FROM employees WHERE id = 1; | Delete data from a table |
Query Filtering and Sorting
#
| Command/Option | Example | Description |
|---|
SELECT column1, column2 FROM <table> WHERE condition; | SELECT name FROM employees WHERE id = 1; | Retrieve specific data based on a condition |
| `SELECT * FROM ORDER BY column [ASC | DESC];` | SELECT * FROM employees ORDER BY name ASC; |
Indexing
#
| Command/Option | Example | Description |
|---|
CREATE INDEX <index_name> ON <table> (column); | CREATE INDEX idx_name ON employees (name); | Create an index on a column |
DROP INDEX <index_name>; | DROP INDEX idx_name; | Delete an index |
Backup and Restore
#
| Command/Option | Example | Description |
|---|
BACKUP DATABASE <db_name> TO DISK = '<file_path>'; | BACKUP DATABASE mydb TO DISK = 'C:\backup\mydb_backup.bak'; | Backup a database |
RESTORE DATABASE <db_name> FROM DISK = '<file_path>'; | RESTORE DATABASE mydb FROM DISK = 'C:\backup\mydb_backup.bak'; | Restore a database |
Server Status and Configuration
#
| Command/Option | Example | Description |
|---|
SELECT @@VERSION; | SELECT @@VERSION; | Show the SQL Server version |
EXEC sp_configure; | EXEC sp_configure; | Show the server configuration settings |
EXEC sp_helpdb; | EXEC sp_helpdb; | Show information about databases |
This cheatsheet covers the most commonly used SQL Server commands and options, assisting you with managing databases, tables, data, indexing, backups, and server configurations effectively.