Microsoft SQL Server is a relational database management system (RDBMS) with a modular database engine, a storage engine, a query processor, and integrated services for reporting, integration, analysis, machine learning, and graph queries.
| Edition | CPU Limit | Memory Limit | Key Features |
|---|---|---|---|
| Express | 1 socket / 4 cores | 1 GB RAM / 10 GB DB | Free starter edition |
| Standard | Up to 24 cores | 128 GB | Core engine + basic HA |
| Enterprise | OS max | OS max | All features, Always On, partitioning, compression |
| Developer | OS max | OS max | Enterprise feature‑set, non‑production |
-- Verify edition & version
SELECT @@VERSION AS 'Build',
SERVERPROPERTY('Edition') AS 'Edition',
SERVERPROPERTY('ProductLevel') AS 'Service Pack';
setup.exe → New SQL Server stand‑alone installation.
-- Set max memory (~80 % of total for dedicated box)
EXEC sys.sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sys.sp_configure 'max server memory (MB)', 32768; RECONFIGURE;
-- Enable instant file initialisation
EXEC sys.sp_configure 'xp_cmdshell', 1; RECONFIGURE;
-- Create a new database
CREATE DATABASE SalesDB
ON ( NAME = SalesDB_data,
FILENAME = 'D:\MSSQL\Data\SalesDB.mdf',
SIZE = 4GB, FILEGROWTH = 512MB )
LOG ON ( NAME = SalesDB_log,
FILENAME = 'L:\MSSQL\Logs\SalesDB.ldf',
SIZE = 1GB, FILEGROWTH = 256MB );
CREATE TABLE dbo.Customers(
CustomerID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50),
Email NVARCHAR(255) UNIQUE,
CreatedAt DATETIME2 DEFAULT sysdatetime()
);
Clustered indexes define the physical order; non‑clustered provide separate tree‑structures. Partitioning improves manageability for very large tables.
CREATE NONCLUSTERED INDEX IX_Customers_Email
ON dbo.Customers(Email);
-- Partition function by year
CREATE PARTITION FUNCTION pf_Sales (DATE)
AS RANGE RIGHT FOR VALUES ('2023‑12‑31', '2024‑12‑31');
INT, BIGINT, DECIMAL(p,s), MONEYCHAR, VARCHAR(max), NVARCHARDATETIME2, DATE, TIME, DATETIMEOFFSETUNIQUEIDENTIFIER, XML, JSON (via OPENJSON)VARBINARY(max) – files, images, binaries| Category | Purpose | Examples |
|---|---|---|
| DDL | Define schema | CREATE, ALTER, DROP |
| DML | Manipulate data | SELECT, INSERT, UPDATE, DELETE |
| DCL | Control permissions | GRANT, DENY, REVOKE |
| TCL | Transactional ops | BEGIN TRAN, COMMIT, ROLLBACK |
CREATE PROCEDURE dbo.GetCustomer
@CustomerID INT
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM dbo.Customers WHERE CustomerID = @CustomerID;
END;
CREATE FUNCTION dbo.fn_TotalOrders(@CustomerID INT)
RETURNS MONEY
AS
BEGIN
RETURN ( SELECT SUM(Amount) FROM dbo.Orders WHERE CustomerID = @CustomerID );
END;
SELECT c.FirstName, o.OrderID, o.Amount
FROM dbo.Customers AS c
JOIN dbo.Orders AS o ON o.CustomerID = c.CustomerID
UNION ALL
SELECT ... -- additional sets
SELECT CustomerID,
Amount,
SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM dbo.Orders;
;WITH Rec (Level, CategoryID) AS
(
SELECT 1, CategoryID FROM dbo.Categories WHERE ParentID IS NULL
UNION ALL
SELECT r.Level+1, c.CategoryID
FROM dbo.Categories c JOIN Rec r ON c.ParentID = r.CategoryID
)
SELECT * FROM Rec;
Choose balance between consistency & throughput:
READ UNCOMMITTED → dirty readsREAD COMMITTED (default) → blockingSNAPSHOT → row versioning, no locksSERIALIZABLE → highest isolation
-- Enable trace flag 1222
DBCC TRACEON (1222, -1);
-- View recent deadlocks (SQL Server 2019+)
SELECT * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_DEADLOCK';
CREATE LOGIN report_reader WITH PASSWORD = 'Str0ngPa$$word!';
CREATE USER report_reader FOR LOGIN report_reader;
EXEC sp_addrolemember 'db_datareader', 'report_reader';
Use clustered index on narrow, ever‑increasing key; cover frequent seek predicates with non‑clustered indexes; implement filtered indexes for sparse data.
ALTER DATABASE SalesDB SET QUERY_STORE = ON;
EXEC sys.sp_query_store_force_plan @query_id = 123, @plan_id = 456;
SELECT TOP 10
qs.execution_count,
qs.total_worker_time/qs.execution_count AS avg_cpu,
qs.total_logical_reads/qs.execution_count AS avg_reads,
qs.query_hash
FROM sys.dm_exec_query_stats qs
ORDER BY avg_cpu DESC;
BACKUP DATABASE SalesDB
TO DISK = '\\Backup\SalesDB_full.bak'
WITH COMPRESSION, CHECKSUM;
RESTORE DATABASE SalesDB
FROM DISK = 'D:\Restore\SalesDB_full.bak'
WITH MOVE 'SalesDB_data' TO 'D:\Data\SalesDB.mdf',
MOVE 'SalesDB_log' TO 'L:\Logs\SalesDB.ldf',
RECOVERY;
Log shipping = scheduled log backups + restore jobs; Database mirroring (SYNCHROUS or ASYNC) adds automatic failover with a witness.
-- Create lightweight XE session
CREATE EVENT SESSION TrackLongQueries
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(WHERE duration > 5000000)
ADD TARGET package0.event_file (SET filename = 'D:\XE\LongQueries.xel')
WITH (STARTUP_STATE = ON);
Graphical packages move/transform data from heterogeneous sources with connections, transformations, and destinations.
SSRS delivers pixel‑perfect paginated reports. Power BI builds dashboards by live‑querying Analysis Services or DirectQuery.
Multidimensional cubes for MOLAP; Tabular models (column‑store + xVelocity). DAX & MDX languages for analytics.
| Option | Admin Control | Patching | Network Isolation |
|---|---|---|---|
| Azure SQL DB | Limited | Automatic | Public endpoint / Private Link |
| Managed Instance | High | Automatic | VNet‑only |
| SQL on VM | Full (root) | User‑driven | VNet / hybrid |
#cores / 2 and Cost Threshold > 50.#sqlhelp on Twitter, SQLServerCentral forums.