sqlserver

SQL Server Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: sqlserver for comprehensive documentation.

Data Types

Numeric Types

Type	Description	Range
BIT	Boolean (0/1)	0, 1, NULL
TINYINT	1 byte integer	0 to 255
SMALLINT	2 byte integer	-32,768 to 32,767
INT	4 byte integer	-2^31 to 2^31-1
BIGINT	8 byte integer	-2^63 to 2^63-1
DECIMAL(p,s)	Fixed precision	p: 1-38 digits
NUMERIC(p,s)	Same as DECIMAL	p: 1-38 digits
FLOAT	8 byte float	~15 digits precision
REAL	4 byte float	~7 digits precision
MONEY	8 byte currency	±922 trillion
SMALLMONEY	4 byte currency	±214,748

String Types

Type	Description	Max Size
CHAR(n)	Fixed-length	8,000 bytes
VARCHAR(n)	Variable-length	8,000 bytes
VARCHAR(MAX)	Large variable	2 GB
NCHAR(n)	Unicode fixed	4,000 chars
NVARCHAR(n)	Unicode variable	4,000 chars
NVARCHAR(MAX)	Unicode large	1 GB
TEXT	Legacy large (deprecated)	2 GB
NTEXT	Legacy Unicode (deprecated)	1 GB

Date/Time Types

Type	Description	Range
DATE	Date only	0001-01-01 to 9999-12-31
TIME(n)	Time only (n=0-7 precision)	00:00:00 to 23:59:59
DATETIME	Date + time	1753-01-01 to 9999-12-31
DATETIME2(n)	Extended datetime	0001-01-01 to 9999-12-31
DATETIMEOFFSET(n)	With timezone	Same + timezone
SMALLDATETIME	Less precision	1900-01-01 to 2079-06-06

Other Types

Type	Description
UNIQUEIDENTIFIER	16-byte GUID
BINARY(n)	Fixed binary
VARBINARY(n)	Variable binary
VARBINARY(MAX)	Large binary (2 GB)
XML	XML data
JSON	JSON (stored as NVARCHAR)
GEOGRAPHY	Spatial data
GEOMETRY	Geometric data
HIERARCHYID	Hierarchy position

CREATE TABLE example (
    id INT IDENTITY(1,1) PRIMARY KEY,
    uuid UNIQUEIDENTIFIER DEFAULT NEWID(),
    name NVARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    quantity INT DEFAULT 0,
    is_active BIT DEFAULT 1,
    created_at DATETIME2 DEFAULT SYSDATETIME(),
    metadata NVARCHAR(MAX),  -- For JSON
    document VARBINARY(MAX)  -- For files
);

Identity Columns

-- Auto-increment
CREATE TABLE employees (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100)
);

-- Get last identity
SELECT SCOPE_IDENTITY();      -- Current scope
SELECT @@IDENTITY;            -- Any scope (avoid)
SELECT IDENT_CURRENT('employees');  -- Specific table

-- Insert with identity off
SET IDENTITY_INSERT employees ON;
INSERT INTO employees (id, name) VALUES (100, 'Admin');
SET IDENTITY_INSERT employees OFF;

-- Reseed identity
DBCC CHECKIDENT ('employees', RESEED, 0);

Sequences (2012+)

-- Create sequence
CREATE SEQUENCE dbo.OrderSeq
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    NO MAXVALUE
    NO CYCLE
    CACHE 20;

-- Use sequence
INSERT INTO orders (id, customer_id)
VALUES (NEXT VALUE FOR dbo.OrderSeq, 1);

-- In DEFAULT
ALTER TABLE orders
ADD CONSTRAINT df_order_id DEFAULT (NEXT VALUE FOR dbo.OrderSeq) FOR id;

-- Get current without incrementing
SELECT current_value FROM sys.sequences WHERE name = 'OrderSeq';

-- Reset sequence
ALTER SEQUENCE dbo.OrderSeq RESTART WITH 1;

Date/Time Functions

-- Current date/time
SELECT GETDATE();           -- DATETIME
SELECT SYSDATETIME();       -- DATETIME2 (more precise)
SELECT GETUTCDATE();        -- UTC datetime
SELECT SYSUTCDATETIME();    -- UTC datetime2
SELECT SYSDATETIMEOFFSET(); -- With timezone

-- Date parts
SELECT YEAR(GETDATE());
SELECT MONTH(GETDATE());
SELECT DAY(GETDATE());
SELECT DATEPART(QUARTER, GETDATE());
SELECT DATENAME(WEEKDAY, GETDATE());  -- 'Monday'

-- Date arithmetic
SELECT DATEADD(DAY, 7, GETDATE());     -- Add 7 days
SELECT DATEADD(MONTH, -1, GETDATE());  -- Subtract 1 month
SELECT DATEDIFF(DAY, '2024-01-01', GETDATE());  -- Days between

-- Format (2012+)
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss');

-- Parse
SELECT CAST('2024-01-15' AS DATE);
SELECT CONVERT(DATETIME, '01/15/2024', 101);  -- US format
SELECT PARSE('15 January 2024' AS DATE);

-- First/last of month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);  -- First of month
SELECT EOMONTH(GETDATE());  -- Last of month (2012+)

String Functions

-- Concatenation
SELECT 'Hello' + ' ' + 'World';
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS(' ', 'Hello', 'World', NULL);  -- With separator, ignores NULL

-- Substring
SELECT SUBSTRING('Hello World', 1, 5);  -- 'Hello'
SELECT LEFT('Hello World', 5);          -- 'Hello'
SELECT RIGHT('Hello World', 5);         -- 'World'

-- Length
SELECT LEN('Hello');     -- 5 (chars, trims trailing spaces)
SELECT DATALENGTH('Hello');  -- 5 (bytes)

-- Case
SELECT UPPER('hello');
SELECT LOWER('HELLO');

-- Trim
SELECT TRIM('  hello  ');
SELECT LTRIM('  hello');
SELECT RTRIM('hello  ');
SELECT TRIM('x' FROM 'xxxhelloxxx');  -- 2017+

-- Replace
SELECT REPLACE('hello', 'l', 'L');
SELECT STUFF('Hello World', 7, 5, 'SQL');  -- 'Hello SQL'

-- Position
SELECT CHARINDEX('o', 'Hello World');      -- 5
SELECT CHARINDEX('o', 'Hello World', 6);   -- 8 (start from 6)

-- Padding
SELECT RIGHT('0000000000' + '123', 10);    -- '0000000123'
SELECT FORMAT(123, '0000000000');          -- 2012+

-- Split (2016+)
SELECT value FROM STRING_SPLIT('a,b,c', ',');

-- Aggregate strings (2017+)
SELECT STRING_AGG(name, ', ') FROM employees;
SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) FROM employees;

JSON Support

-- JSON functions (2016+)
DECLARE @json NVARCHAR(MAX) = '{"name":"John","age":30,"address":{"city":"NYC"}}';

-- Extract values
SELECT JSON_VALUE(@json, '$.name');           -- 'John'
SELECT JSON_VALUE(@json, '$.address.city');   -- 'NYC'
SELECT JSON_QUERY(@json, '$.address');        -- '{"city":"NYC"}'

-- Check if valid JSON
SELECT ISJSON(@json);  -- 1

-- Modify JSON
SELECT JSON_MODIFY(@json, '$.age', 31);
SELECT JSON_MODIFY(@json, '$.email', '[email protected]');

-- Parse JSON array
DECLARE @arr NVARCHAR(MAX) = '[{"id":1,"name":"A"},{"id":2,"name":"B"}]';
SELECT * FROM OPENJSON(@arr) WITH (id INT, name NVARCHAR(50));

-- Generate JSON
SELECT id, name, email
FROM employees
FOR JSON AUTO;  -- Auto-structure

SELECT id, name, email
FROM employees
FOR JSON PATH, ROOT('employees');  -- Custom structure

Index Types

-- Clustered index (one per table, defines physical order)
CREATE CLUSTERED INDEX IX_emp_id ON employees(id);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_emp_email ON employees(email);

-- Unique index
CREATE UNIQUE INDEX IX_emp_email ON employees(email);

-- Composite index
CREATE INDEX IX_emp_dept_name ON employees(department_id, last_name);

-- Included columns (covering index)
CREATE INDEX IX_emp_email ON employees(email)
INCLUDE (first_name, last_name, phone);

-- Filtered index
CREATE INDEX IX_active_emp ON employees(email)
WHERE is_active = 1;

-- Columnstore index (analytics)
CREATE COLUMNSTORE INDEX IX_sales_cs ON sales(product_id, amount, sale_date);
-- Or clustered columnstore
CREATE CLUSTERED COLUMNSTORE INDEX IX_sales_ccs ON sales;

-- Full-text index
CREATE FULLTEXT CATALOG ft_catalog;
CREATE FULLTEXT INDEX ON documents(content) KEY INDEX PK_documents;

Table Partitioning

-- 1. Create partition function
CREATE PARTITION FUNCTION pf_sales_date (DATE)
AS RANGE RIGHT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01');

-- 2. Create partition scheme
CREATE PARTITION SCHEME ps_sales_date
AS PARTITION pf_sales_date
TO (fg_2021, fg_2022, fg_2023, fg_2024);

-- 3. Create partitioned table
CREATE TABLE sales (
    id INT IDENTITY(1,1),
    sale_date DATE,
    amount DECIMAL(10,2),
    CONSTRAINT PK_sales PRIMARY KEY (id, sale_date)
) ON ps_sales_date(sale_date);

-- View partitions
SELECT
    $PARTITION.pf_sales_date(sale_date) AS partition_number,
    COUNT(*) AS row_count
FROM sales
GROUP BY $PARTITION.pf_sales_date(sale_date);

-- Split partition (add new)
ALTER PARTITION SCHEME ps_sales_date NEXT USED fg_2025;
ALTER PARTITION FUNCTION pf_sales_date() SPLIT RANGE ('2025-01-01');

-- Merge partitions
ALTER PARTITION FUNCTION pf_sales_date() MERGE RANGE ('2022-01-01');

-- Switch partition (instant move)
ALTER TABLE sales SWITCH PARTITION 1 TO sales_archive;

Temporal Tables (2016+)

-- Create temporal table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name NVARCHAR(100),
    salary DECIMAL(10,2),
    -- System time columns
    valid_from DATETIME2 GENERATED ALWAYS AS ROW START,
    valid_to DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employees_history));

-- Query historical data
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2024-01-01';
SELECT * FROM employees FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-01';
SELECT * FROM employees FOR SYSTEM_TIME ALL;

-- Disable versioning for maintenance
ALTER TABLE employees SET (SYSTEM_VERSIONING = OFF);
-- ... maintenance ...
ALTER TABLE employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.employees_history));

Common Table Expressions

-- Basic CTE
;WITH emp_stats AS (
    SELECT department_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.name, es.emp_count, es.avg_salary
FROM departments d
JOIN emp_stats es ON d.id = es.department_id;

-- Recursive CTE
;WITH hierarchy AS (
    -- Anchor
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    -- Recursive
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy OPTION (MAXRECURSION 100);

Pagination

-- OFFSET-FETCH (2012+)
SELECT id, name, email
FROM employees
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

-- With variable
DECLARE @PageNum INT = 3, @PageSize INT = 10;
SELECT id, name, email
FROM employees
ORDER BY id
OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

-- ROW_NUMBER (older method)
;WITH numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM employees
)
SELECT * FROM numbered WHERE rn BETWEEN 21 AND 30;

When NOT to Use This Skill

• T-SQL programming - Use tsql skill for stored procedures, functions, triggers
• PostgreSQL - Use postgresql skill for PostgreSQL-specific features
• Oracle - Use oracle skill for Oracle features
• Basic SQL - Use sql-fundamentals for ANSI SQL basics

Anti-Patterns

Anti-Pattern	Problem	Solution
Not using SET NOCOUNT ON	Network overhead	Add to all stored procedures
Using cursors	Slow performance	Use set-based operations
Missing clustered index	Heap scans	Add clustered index
Not using INCLUDE in indexes	Key lookups	Use covering indexes
Using deprecated TEXT/NTEXT	Compatibility issues	Use VARCHAR(MAX)/NVARCHAR(MAX)
@@IDENTITY instead of SCOPE_IDENTITY	Wrong identity value	Use SCOPE_IDENTITY()

Quick Troubleshooting

Problem	Diagnostic	Fix
Slow queries	SET STATISTICS IO ON	Add indexes, check execution plan
Blocking	sp_who2 or Activity Monitor	Kill blocking process, optimize
Deadlocks	Extended Events, trace flag 1222	Consistent access order, shorter transactions
Identity issues	DBCC CHECKIDENT	Reseed or use SCOPE_IDENTITY
TempDB contention	Check wait stats	Add TempDB files, optimize queries

Reference Documentation

• Data Types
• Indexes
• Partitioning