Indexing Strategies
Clustered Index
Defines the order in which data is physically stored in a table.
1
| CREATE CLUSTERED INDEX idx_clustered ON table (column);
|
Non-Clustered Index
Creates a separate structure for indexing, allowing multiple indexes per table.
1
| CREATE NONCLUSTERED INDEX idx_nonclustered ON table (column);
|
Covering Index
Includes all the columns required to satisfy a query, eliminating the need to access the actual table.
1
| CREATE INDEX idx_covering ON table (column1, column2) INCLUDE (column3);
|
Query Optimization
Execution Plan
Review the execution plan to analyze how SQL Server processes a query.
1
| EXPLAIN SELECT column1, column2 FROM table WHERE condition;
|
Query Hints
Optimize queries by providing hints to the query optimizer.
1
2
3
| SELECT column1, column2
FROM table WITH (INDEX(idx_nonclustered), FORCESEEK)
WHERE condition;
|
Backup and Restore
Full Backup
1
| BACKUP DATABASE database_name TO DISK = 'path\to\backup\file.bak';
|
Restore Database
1
| RESTORE DATABASE database_name FROM DISK = 'path\to\backup\file.bak';
|
Security
Creating a User
1
2
| CREATE LOGIN login_name WITH PASSWORD = 'password';
CREATE USER user_name FOR LOGIN login_name;
|
Granting Permissions
1
| GRANT SELECT, INSERT ON table TO user_name;
|