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