# Sql Server

> SQL Server and Azure SQL database design with proper types, naming conventions, indexing strategies, temporal tables, dynamic data masking, and T-SQL patterns. Use when designing schemas, writing DDL, or working with SQL Server or Azure SQL databases.

- **Type:** Skill
- **Install:** `agentstack add skill-stonegiantstudio-skills-sql-server`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [stonegiantstudio](https://agentstack.voostack.com/s/stonegiantstudio)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** Apache-2.0
- **Upstream author:** [stonegiantstudio](https://github.com/stonegiantstudio)
- **Source:** https://github.com/stonegiantstudio/skills/tree/main/plugins/stone-giant/skills/sql-server

## Install

```sh
agentstack add skill-stonegiantstudio-skills-sql-server
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# SQL Server & Azure SQL Database Design

Database design guidance specific to Microsoft SQL Server and Azure SQL Database. This skill covers SQL Server-specific patterns—for universal relational theory (normalization, keys, constraints), see the `relational-db-theory` skill.

## Azure SQL vs On-Premises: Critical Differences

**Read this first.** Azure SQL Database has significant limitations compared to on-premises SQL Server.

### Commands NOT Supported in Azure SQL Database

```sql
-- ❌ NEVER USE THESE IN AZURE SQL DATABASE

USE master;                    -- Cannot switch databases; use separate connections
USE [OtherDatabase];           -- Same limitation

BACKUP DATABASE ...            -- Managed by Azure (automatic backups)
RESTORE DATABASE ...           -- Use Azure portal or PITR

sp_configure ...               -- Use ALTER DATABASE SCOPED CONFIGURATION instead
RECONFIGURE;

SHUTDOWN;                      -- Not applicable

-- Cross-database queries (limited)
SELECT * FROM OtherDb.dbo.Table;  -- Use elastic query for read-only access
```

### Feature Comparison

| Feature | On-Premises | Azure SQL DB | Azure SQL MI |
|---------|-------------|--------------|--------------|
| USE statement | ✅ | ❌ | ✅ |
| Cross-database queries | ✅ | ❌ (elastic query only) | ✅ |
| Windows Authentication | ✅ | ❌ | ✅ |
| SQL Server Agent | ✅ | ❌ (use Azure Automation) | ✅ |
| Linked Servers | ✅ | ❌ | ✅ |
| CLR Integration | ✅ | ❌ | ✅ |
| BACKUP/RESTORE | ✅ | ❌ (managed) | ✅ (to URL) |
| Filestream/Filetable | ✅ | ❌ | ❌ |
| Replication | ✅ | Subscriber only | ✅ |
| Always On AG | ✅ | ❌ (built-in HA) | ❌ (built-in HA) |

### Azure SQL Authentication

```sql
-- ❌ Windows Authentication NOT supported in Azure SQL Database
-- ✅ Use Microsoft Entra ID (formerly Azure AD) or SQL Authentication

-- Create contained database user (recommended for Azure SQL)
CREATE USER [app_user] WITH PASSWORD = 'SecurePassword123!';
ALTER ROLE db_datareader ADD MEMBER [app_user];
ALTER ROLE db_datawriter ADD MEMBER [app_user];

-- Entra ID user
CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;
```

---

## CRITICAL: Verify Before Writing SQL

**NEVER guess object names.** Before writing any DDL or DML:

```sql
-- List all tables
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- Search for tables by pattern
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_NAME LIKE '%user%';

-- Get columns for a table
SELECT
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Users'
ORDER BY ORDINAL_POSITION;

-- Get foreign keys
SELECT
    fk.name AS ConstraintName,
    tp.name AS ParentTable,
    cp.name AS ParentColumn,
    tr.name AS ReferencedTable,
    cr.name AS ReferencedColumn
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tp ON fkc.parent_object_id = tp.object_id
INNER JOIN sys.columns cp
    ON fkc.parent_object_id = cp.object_id
    AND fkc.parent_column_id = cp.column_id
INNER JOIN sys.tables tr ON fkc.referenced_object_id = tr.object_id
INNER JOIN sys.columns cr
    ON fkc.referenced_object_id = cr.object_id
    AND fkc.referenced_column_id = cr.column_id
WHERE tp.name = 'Posts';

-- Get indexes
SELECT
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique,
    i.is_primary_key,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS Columns
FROM sys.indexes i
INNER JOIN sys.index_columns ic
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c
    ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE OBJECT_NAME(i.object_id) = 'Users'
  AND i.name IS NOT NULL
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key;
```

---

## Data Types

### Preferred Types

| Use Case | Type | Notes |
|----------|------|-------|
| Text (Unicode) | `NVARCHAR(n)` or `NVARCHAR(MAX)` | **Always** for international text |
| Text (ASCII only) | `VARCHAR(n)` or `VARCHAR(MAX)` | Only when certain ASCII-only |
| Timestamps | `DATETIME2(7)` | Higher precision than DATETIME |
| Timestamps + TZ | `DATETIMEOFFSET(7)` | Stores timezone offset |
| Boolean | `BIT` | 0/1 (no native BOOLEAN) |
| Integer | `INT` | -2B to +2B |
| Large integer | `BIGINT` | IDs, counts exceeding 2B |
| Money/currency | `DECIMAL(p,s)` | **Never** use MONEY type |
| JSON data | `NVARCHAR(MAX)` | With JSON functions |
| Unique identifier | `UNIQUEIDENTIFIER` | 16-byte GUID |

### Primary Keys

```sql
-- Sequential GUID (best for clustered index performance)
Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID()

-- Random GUID (causes fragmentation but globally unique)
Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()

-- Auto-increment BIGINT (simple, performant)
Id BIGINT IDENTITY(1,1) NOT NULL

-- Auto-increment INT (for smaller tables)
Id INT IDENTITY(1,1) NOT NULL
```

**When to use which:**

- `NEWSEQUENTIALID()` - Best default for GUIDs (sequential = less fragmentation)
- `NEWID()` - When global uniqueness matters more than performance
- `IDENTITY` - Simple internal tables, better join performance

### Timestamps

```sql
-- High precision, no timezone (most common)
CreatedAt DATETIME2(7) NOT NULL DEFAULT GETUTCDATE()
UpdatedAt DATETIME2(7) NOT NULL DEFAULT GETUTCDATE()

-- With timezone offset (when you need to preserve original TZ)
CreatedAt DATETIMEOFFSET(7) NOT NULL DEFAULT SYSDATETIMEOFFSET()

-- ❌ NEVER use DATETIME (precision issues, limited range)
-- CreatedAt DATETIME  -- 3.33ms precision, ends 2079
```

---

## Naming Conventions

### Tables

- **PascalCase**, plural
- Prefix with schema when not dbo

```sql
dbo.Users
dbo.BlogPosts
dbo.OrderLineItems

-- Junction tables
dbo.UserRoles
dbo.PostTags

-- Schema-organized
Sales.Orders
Sales.OrderItems
HR.Employees
```

### Columns

- **PascalCase**
- No table prefix

```sql
-- Primary key
Id

-- Foreign keys: singular entity + Id
UserId
OrganizationId

-- Booleans: Is/Has/Can/Should prefix
IsActive
HasVerifiedEmail
CanPublish
ShouldNotify

-- Timestamps: At suffix
CreatedAt
UpdatedAt
DeletedAt
PublishedAt

-- Counts
ViewCount
CommentCount
```

### Indexes and Constraints

```sql
-- Primary key: PK_TableName
CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (Id)

-- Foreign key: FK_ChildTable_ParentTable
CONSTRAINT FK_Posts_Users FOREIGN KEY (UserId) REFERENCES Users(Id)

-- Unique: UQ_TableName_Columns
CONSTRAINT UQ_Users_Email UNIQUE (Email)

-- Check: CK_TableName_Description
CONSTRAINT CK_Orders_QuantityPositive CHECK (Quantity > 0)

-- Default: DF_TableName_Column
CONSTRAINT DF_Users_CreatedAt DEFAULT GETUTCDATE() FOR CreatedAt

-- Index: IX_TableName_Columns
CREATE NONCLUSTERED INDEX IX_Posts_UserId ON Posts(UserId);
```

---

## Standard Table Template

```sql
CREATE TABLE dbo.Posts (
    -- Primary key
    Id UNIQUEIDENTIFIER NOT NULL
        CONSTRAINT DF_Posts_Id DEFAULT NEWSEQUENTIALID(),

    -- Foreign keys
    UserId UNIQUEIDENTIFIER NOT NULL,
    CategoryId UNIQUEIDENTIFIER NULL,

    -- Business columns
    Title NVARCHAR(200) NOT NULL,
    Slug NVARCHAR(200) NOT NULL,
    Content NVARCHAR(MAX) NULL,

    -- Status (SQL Server has no ENUM)
    Status NVARCHAR(20) NOT NULL
        CONSTRAINT DF_Posts_Status DEFAULT 'draft',

    -- Boolean
    IsFeatured BIT NOT NULL
        CONSTRAINT DF_Posts_IsFeatured DEFAULT 0,

    -- JSON data
    Metadata NVARCHAR(MAX) NULL,

    -- Timestamps
    CreatedAt DATETIME2(7) NOT NULL
        CONSTRAINT DF_Posts_CreatedAt DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2(7) NOT NULL
        CONSTRAINT DF_Posts_UpdatedAt DEFAULT GETUTCDATE(),
    PublishedAt DATETIME2(7) NULL,

    -- Constraints
    CONSTRAINT PK_Posts PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT FK_Posts_Users FOREIGN KEY (UserId)
        REFERENCES Users(Id) ON DELETE CASCADE,
    CONSTRAINT FK_Posts_Categories FOREIGN KEY (CategoryId)
        REFERENCES Categories(Id) ON DELETE SET NULL,
    CONSTRAINT UQ_Posts_Slug UNIQUE (Slug),
    CONSTRAINT CK_Posts_Status CHECK (Status IN ('draft', 'published', 'archived')),
    CONSTRAINT CK_Posts_TitleLength CHECK (LEN(Title) >= 1),
    CONSTRAINT CK_Posts_Metadata CHECK (Metadata IS NULL OR ISJSON(Metadata) = 1)
);

-- Always index foreign keys (SQL Server doesn't auto-index these!)
CREATE NONCLUSTERED INDEX IX_Posts_UserId ON Posts(UserId);
CREATE NONCLUSTERED INDEX IX_Posts_CategoryId ON Posts(CategoryId);

-- Index commonly filtered columns
CREATE NONCLUSTERED INDEX IX_Posts_Status ON Posts(Status);
CREATE NONCLUSTERED INDEX IX_Posts_CreatedAt ON Posts(CreatedAt DESC);

-- Filtered index for published posts only
CREATE NONCLUSTERED INDEX IX_Posts_PublishedAt_Active
ON Posts(PublishedAt)
WHERE Status = 'published';
```

---

## Temporal Tables (System-Versioned)

**Automatic history tracking** - SQL Server maintains a complete history of all changes.

### Creating a Temporal Table

```sql
CREATE TABLE dbo.Products (
    Id UNIQUEIDENTIFIER NOT NULL
        CONSTRAINT DF_Products_Id DEFAULT NEWSEQUENTIALID(),
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) NOT NULL,
    CategoryId UNIQUEIDENTIFIER NULL,

    -- Required: period columns (SQL Server manages these)
    ValidFrom DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo),

    CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (Id)
)
WITH (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.ProductsHistory,
    DATA_CONSISTENCY_CHECK = ON
));
```

### Querying Temporal Data

```sql
-- Current data only (default)
SELECT * FROM Products WHERE Id = @ProductId;

-- Data as it existed at a specific point in time
SELECT * FROM Products
FOR SYSTEM_TIME AS OF '2024-06-15 14:30:00'
WHERE Id = @ProductId;

-- All versions of a record
SELECT * FROM Products
FOR SYSTEM_TIME ALL
WHERE Id = @ProductId
ORDER BY ValidFrom;

-- Data within a time range
SELECT * FROM Products
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-30'
WHERE Id = @ProductId;

-- Data that was valid during any part of a range
SELECT * FROM Products
FOR SYSTEM_TIME FROM '2024-01-01' TO '2024-06-30'
WHERE Id = @ProductId;

-- Data fully contained within a range
SELECT * FROM Products
FOR SYSTEM_TIME CONTAINED IN ('2024-01-01', '2024-06-30')
WHERE Id = @ProductId;
```

### Temporal Table Management

```sql
-- Disable versioning (required before schema changes)
ALTER TABLE Products SET (SYSTEM_VERSIONING = OFF);

-- Make schema changes...
ALTER TABLE Products ADD NewColumn NVARCHAR(50) NULL;
ALTER TABLE ProductsHistory ADD NewColumn NVARCHAR(50) NULL;

-- Re-enable versioning
ALTER TABLE Products SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.ProductsHistory
));

-- Query history table directly (when needed)
SELECT * FROM ProductsHistory WHERE Id = @ProductId;
```

### Converting Existing Table to Temporal

```sql
-- Add period columns
ALTER TABLE Products ADD
    ValidFrom DATETIME2(7) GENERATED ALWAYS AS ROW START
        CONSTRAINT DF_Products_ValidFrom DEFAULT SYSUTCDATETIME() NOT NULL,
    ValidTo DATETIME2(7) GENERATED ALWAYS AS ROW END
        CONSTRAINT DF_Products_ValidTo DEFAULT CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999') NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

-- Enable system versioning
ALTER TABLE Products SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.ProductsHistory
));
```

---

## Dynamic Data Masking

**Protect sensitive data** at the database level without changing application code.

### Masking Functions

```sql
-- Default mask: full masking
-- Numbers → 0, Strings → 'XXXX', Dates → 01-01-1900
Email NVARCHAR(255) MASKED WITH (FUNCTION = 'default()') NOT NULL

-- Email mask: shows first letter and domain
-- 'john.doe@example.com' → 'jXXX@XXXX.com'
Email NVARCHAR(255) MASKED WITH (FUNCTION = 'email()') NOT NULL

-- Partial mask: expose prefix and suffix
-- '1234567890' → '123XXXX890'
Phone NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(3, "XXXX", 3)') NOT NULL

-- Random mask: random value within range (for numbers)
Salary DECIMAL(10,2) MASKED WITH (FUNCTION = 'random(10000, 50000)') NOT NULL
```

### Creating Masked Columns

```sql
CREATE TABLE dbo.Customers (
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),

    -- Masked columns
    FirstName NVARCHAR(50) MASKED WITH (FUNCTION = 'partial(1, "***", 0)') NOT NULL,
    LastName NVARCHAR(50) MASKED WITH (FUNCTION = 'default()') NOT NULL,
    Email NVARCHAR(255) MASKED WITH (FUNCTION = 'email()') NOT NULL,
    Phone NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0, "XXX-XXX-", 4)') NULL,
    SSN CHAR(11) MASKED WITH (FUNCTION = 'partial(0, "XXX-XX-", 4)') NULL,
    CreditCardNumber NVARCHAR(20) MASKED WITH (FUNCTION = 'partial(0, "XXXX-XXXX-XXXX-", 4)') NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (Id)
);

-- Add mask to existing column
ALTER TABLE Customers
ALTER COLUMN BirthDate ADD MASKED WITH (FUNCTION = 'default()');

-- Remove mask
ALTER TABLE Customers
ALTER COLUMN BirthDate DROP MASKED;
```

### Granting Unmask Permission

```sql
-- Users see masked data by default
-- Grant permission to see unmasked data
GRANT UNMASK TO [analytics_user];

-- Revoke unmask permission
REVOKE UNMASK FROM [analytics_user];

-- Column-level unmask (SQL Server 2022+)
GRANT UNMASK ON dbo.Customers(Email) TO [support_user];
```

### Querying Masked Data

```sql
-- Regular user sees:
-- FirstName: J***, Email: jXXX@XXXX.com, Phone: XXX-XXX-1234

-- User with UNMASK permission sees:
-- FirstName: John, Email: john@example.com, Phone: 555-123-1234

-- Check current user's mask visibility
SELECT
    c.name AS ColumnName,
    mc.masking_function
FROM sys.masked_columns mc
JOIN sys.columns c ON mc.object_id = c.object_id AND mc.column_id = c.column_id
WHERE mc.object_id = OBJECT_ID('Customers');
```

---

## JSON Support

### Storing JSON

```sql
-- Store as NVARCHAR(MAX) with validation constraint
Metadata NVARCHAR(MAX) NULL
    CONSTRAINT CK_Posts_Metadata CHECK (Metadata IS NULL OR ISJSON(Metadata) = 1)
```

### Querying JSON

```sql
-- Extract scalar value (returns NVARCHAR)
SELECT
    Id,
    Title,
    JSON_VALUE(Metadata, '$.author') AS Author,
    JSON_VALUE(Metadata, '$.stats.viewCount') AS ViewCount
FROM Posts;

-- Extract object or array (returns NVARCHAR with JSON)
SELECT JSON_QUERY(Metadata, '$.tags') AS Tags
FROM Posts;

-- Filter by JSON value
SELECT * FROM Posts
WHERE JSON_VALUE(Metadata, '$.featured') = 'true';

-- Check if path exists
SELECT * FROM Posts
WHERE JSON_VALUE(Metadata, '$.author') IS NOT NULL;

-- Parse JSON array into rows
SELECT p.Id, p.Title, t.value AS Tag
FROM Posts p
CROSS APPLY OPENJSON(JSON_QUERY(p.Metadata, '$.tags')) t;

-- Parse JSON object into columns
SELECT p.Id, j.*
FROM Posts p
CROSS APPLY OPENJSON(p.Metadata)
WITH (
    Author NVARCHAR(100) '$.author',
    ViewCount INT '$.stats.viewCount',
    Tags NVARCHAR(MAX) '$.tags' AS JSON
) j;
```

### Modifying JSON

```sql
-- Set/update a value
UPDATE Posts
SET Metadata = JSON_MODIFY(Metadata, '$.viewCount', 100)
WHERE Id = @PostId;

-- Set nested value
UPDATE Posts
SET Metadata = JSON_MODIFY(Metadata, '$.stats.viewCount', 100)
WHERE Id = @PostId;

-- Add new property
UPDATE Posts
SET Metadata = JSON_MODIFY(Metadata, '$.featured', 'true')
WHERE Id = @PostId;

-- Remove property (set to NULL)
UPDATE Posts
SET Metadata = JSON_MODIFY(Metadata, '$.deprecated', NULL)
WHERE Id = @PostId;

-- Append to array
UPDATE Posts
SET Metadata = JSON_MODIFY(
    Metadata,
    'append $.tags',
    'new-tag'
)
WHERE Id = @PostId;
```

### Indexing JSON for Performance

```sql
-- Add computed column for frequently queried JSON path
ALTER TABLE Posts
ADD Author AS JSON_VALUE(Metadata, '$.author');

-- Index the computed column
CREATE NONCLUSTERED INDEX IX_Posts_Author ON Posts(Author);

--

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [stonegiantstudio](https://github.com/stonegiantstudio)
- **Source:** [stonegiantstudio/skills](https://github.com/stonegiantstudio/skills)
- **License:** Apache-2.0

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-stonegiantstudio-skills-sql-server
- Seller: https://agentstack.voostack.com/s/stonegiantstudio
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.