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', 'john@example.com');

-- 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