Design and develop database solutions

-- Applies to: all four platforms (vector and json need SQL Server 2025 or later)
CREATE TABLE dbo.Product
(
    ProductID     int IDENTITY(1, 1) NOT NULL,
    Sku           varchar(20)        NOT NULL,       -- ASCII codes: varchar, not nvarchar
    Name          nvarchar(200)      NOT NULL,       -- user-facing text: Unicode
    Description   nvarchar(max)      NULL,           -- LOB, stored off-row when large
    ListPrice     decimal(10, 2)     NOT NULL,       -- exact numeric; avoid float and money
    CategoryID    smallint           NOT NULL,       -- smallest type that fits
    Attributes    json               NULL,           -- native binary JSON
    Embedding     vector(1536)       NULL,           -- float32 elements; size the model's dimensions
    CreatedAt     datetime2(3)       NOT NULL
        CONSTRAINT DF_Product_CreatedAt DEFAULT (SYSUTCDATETIME()),
    RowVer        rowversion         NOT NULL,       -- optimistic concurrency token
    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID),
    CONSTRAINT UQ_Product_Sku UNIQUE (Sku),
    CONSTRAINT CK_Product_ListPrice CHECK (ListPrice >= 0),
    CONSTRAINT FK_Product_Category FOREIGN KEY (CategoryID)
        REFERENCES dbo.Category (CategoryID) ON DELETE NO ACTION
);

-- Covering nonclustered index for a frequent lookup
CREATE NONCLUSTERED INDEX IX_Product_Category
    ON dbo.Product (CategoryID) INCLUDE (Name, ListPrice)
    WHERE ListPrice > 0;                               -- filtered index

-- Real-time operational analytics: a nonclustered columnstore index on an OLTP table
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_SalesOrderLine
    ON dbo.SalesOrderLine (OrderDate, ProductID, Quantity, LineTotal);

-- Analytics table: clustered columnstore, ordered to improve segment elimination
-- (ordered CCI: SQL Server 2022+ and Azure SQL; ordered NCCI: SQL Server 2025)
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
    ON dbo.FactSales
    ORDER (OrderDateKey);

-- Applies to: all four platforms
CREATE TABLE dbo.Price
(
    ProductID  int            NOT NULL PRIMARY KEY CLUSTERED,
    ListPrice  decimal(10, 2) NOT NULL,
    ValidFrom  datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo    datetime2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = dbo.PriceHistory,
        HISTORY_RETENTION_PERIOD = 2 YEARS));

-- What was the price on 1 March?
SELECT ProductID, ListPrice
FROM dbo.Price FOR SYSTEM_TIME AS OF '2026-03-01T00:00:00'
WHERE ProductID = 42;

-- Applies to: SQL Server 2022+, Azure SQL Database, Azure SQL MI (not SQL database in Fabric)

-- Updatable ledger table: keeps a history table and a ledger view
CREATE TABLE dbo.AccountBalance
(
    AccountID int            NOT NULL PRIMARY KEY,
    Balance   decimal(19, 4) NOT NULL
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);

-- Append-only ledger table: INSERT only, UPDATE and DELETE are blocked
CREATE TABLE dbo.AuditEvent
(
    EventID   bigint IDENTITY NOT NULL,
    EventTime datetime2      NOT NULL DEFAULT SYSUTCDATETIME(),
    Detail    nvarchar(4000) NOT NULL
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

-- Verify the database against its stored digests
EXECUTE sys.sp_verify_database_ledger_from_digest_storage;

-- Applies to: SQL Server (needs a MEMORY_OPTIMIZED_DATA filegroup),
-- Azure SQL Database Premium/Business Critical, Azure SQL MI Business Critical.
-- Not available in SQL database in Fabric.
CREATE TABLE dbo.ShoppingCart
(
    CartID     int       NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    CustomerID int       NOT NULL INDEX IX_Customer NONCLUSTERED,
    CreatedAt  datetime2 NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);   -- SCHEMA_ONLY: data lost on restart

-- Natively compiled procedure for the hot path
CREATE PROCEDURE dbo.usp_AddCart @CartID int, @CustomerID int
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT dbo.ShoppingCart (CartID, CustomerID, CreatedAt)
    VALUES (@CartID, @CustomerID, SYSUTCDATETIME());
END;

-- Data virtualization: Parquet files in ADLS Gen2
-- GA in SQL Server 2022+ and Azure SQL MI, preview in Azure SQL Database;
-- SQL database in Fabric reads Parquet and CSV from OneLake.
CREATE EXTERNAL DATA SOURCE SalesLake
WITH (LOCATION = 'adls://sales@contosolake.dfs.core.windows.net');   -- endpoint-specific prefix, not https://

CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (FORMAT_TYPE = PARQUET);

CREATE EXTERNAL TABLE dbo.ext_WebClicks
(
    ClickTime datetime2,
    Url       nvarchar(400),
    UserId    int
)
WITH (LOCATION = 'clicks/2026/*.parquet', DATA_SOURCE = SalesLake, FILE_FORMAT = ParquetFormat);

-- Ad hoc exploration without an external table
SELECT TOP (10) *
FROM OPENROWSET(BULK 'clicks/2026/*.parquet', DATA_SOURCE = 'SalesLake', FORMAT = 'parquet') AS f;

-- Applies to: SQL Server 2017+, Azure SQL Database, Azure SQL MI
CREATE TABLE dbo.Person   (PersonID int PRIMARY KEY, Name nvarchar(100)) AS NODE;
CREATE TABLE dbo.Skill    (SkillID  int PRIMARY KEY, Name nvarchar(100)) AS NODE;
CREATE TABLE dbo.HasSkill
(
    Level tinyint,
    CONSTRAINT EC_HasSkill CONNECTION (dbo.Person TO dbo.Skill) ON DELETE CASCADE
) AS EDGE;

-- Edges are inserted with the pseudo-columns $from_id and $to_id
INSERT dbo.HasSkill ($from_id, $to_id, Level)
SELECT p.$node_id, s.$node_id, 4
FROM dbo.Person AS p, dbo.Skill AS s
WHERE p.PersonID = 1 AND s.SkillID = 7;

-- Applies to: SQL Server 2025, Azure SQL Database, Azure SQL MI (2025 or AUTD policy),
-- SQL database in Fabric. A JSON index needs a clustered primary key.
CREATE TABLE dbo.Orders
(
    OrderID      int IDENTITY PRIMARY KEY CLUSTERED,
    OrderDetails json NOT NULL
);

CREATE JSON INDEX IX_Orders_Details
    ON dbo.Orders (OrderDetails)
    FOR ('$.customer.id', '$.status');          -- optional: index only these paths

-- Served by the JSON index
SELECT OrderID
FROM dbo.Orders
WHERE JSON_VALUE(OrderDetails, '$.status') = 'shipped';

-- JSON stored as nvarchar (any version): validate it and index one property
ALTER TABLE dbo.LegacyOrders
    ADD CONSTRAINT CK_LegacyOrders_IsJson CHECK (ISJSON(Payload) = 1),
        CustomerId AS CAST(JSON_VALUE(Payload, '$.customer.id') AS int);
CREATE INDEX IX_LegacyOrders_CustomerId ON dbo.LegacyOrders (CustomerId);

-- Applies to: all four platforms
CREATE SEQUENCE dbo.OrderNumber
    AS bigint
    START WITH 100000
    INCREMENT BY 1
    CACHE 50;                    -- faster, but cached values are lost on an unexpected shutdown (gaps)

CREATE TABLE dbo.OrderHeader
(
    OrderNo   bigint NOT NULL
        CONSTRAINT DF_OrderHeader_OrderNo DEFAULT (NEXT VALUE FOR dbo.OrderNumber),
    OrderDate date   NOT NULL,
    CONSTRAINT PK_OrderHeader PRIMARY KEY (OrderNo)
);

-- The same sequence can number rows in several tables, and you can take the
-- value before the INSERT — neither is possible with IDENTITY.
DECLARE @next bigint = NEXT VALUE FOR dbo.OrderNumber;

-- Applies to: all four platforms; Azure SQL Database and Fabric have only the PRIMARY
-- filegroup, and SQL database in Fabric does not allow SWITCH PARTITION.
CREATE PARTITION FUNCTION pfOrderMonth (date)
    AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01');   -- boundary starts a partition

CREATE PARTITION SCHEME psOrderMonth
    AS PARTITION pfOrderMonth ALL TO ([PRIMARY]);

CREATE TABLE dbo.OrderLine
(
    OrderDate date   NOT NULL,
    OrderNo   bigint NOT NULL,
    LineNo    int    NOT NULL,
    Amount    decimal(19, 4) NOT NULL,
    CONSTRAINT PK_OrderLine PRIMARY KEY CLUSTERED (OrderDate, OrderNo, LineNo)   -- partitioning column in the key
) ON psOrderMonth (OrderDate);

-- Which partition holds a row, and fast removal of old data
SELECT $PARTITION.pfOrderMonth('2026-02-14') AS PartitionNumber;
TRUNCATE TABLE dbo.OrderLine WITH (PARTITIONS (1));

-- Indexed view: SCHEMABINDING, two-part names, COUNT_BIG(*) with GROUP BY
CREATE OR ALTER VIEW dbo.vw_SalesByProduct
WITH SCHEMABINDING
AS
SELECT ProductID, SUM(LineTotal) AS Total, COUNT_BIG(*) AS LineCount
FROM dbo.SalesOrderLine
GROUP BY ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX UCX_vw_SalesByProduct ON dbo.vw_SalesByProduct (ProductID);
GO

-- Inline table-valued function: one SELECT, optimized like a parameterized view
CREATE OR ALTER FUNCTION dbo.tvf_OrdersForCustomer (@CustomerID int)
RETURNS TABLE
AS
RETURN
    SELECT OrderNo, OrderDate, Total
    FROM dbo.OrderHeader
    WHERE CustomerID = @CustomerID;
GO

-- Stored procedure skeleton the exam expects
CREATE OR ALTER PROCEDURE dbo.usp_PlaceOrder
    @CustomerID int,
    @OrderNo    bigint OUTPUT
AS
BEGIN
    SET NOCOUNT ON;      -- no "n rows affected" messages
    SET XACT_ABORT ON;   -- any runtime error dooms and rolls back the transaction
    BEGIN TRY
        BEGIN TRANSACTION;
        SET @OrderNo = NEXT VALUE FOR dbo.OrderNumber;
        INSERT dbo.OrderHeader (OrderNo, CustomerID, OrderDate) VALUES (@OrderNo, @CustomerID, GETUTCDATE());
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;           -- re-raise the original error to the caller
    END CATCH;
END;

-- AFTER trigger: set-based, works for multi-row statements
CREATE OR ALTER TRIGGER dbo.trg_Product_Audit
ON dbo.Product
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE(ListPrice) RETURN;
    INSERT dbo.PriceChange (ProductID, OldPrice, NewPrice, ChangedAt)
    SELECT d.ProductID, d.ListPrice, i.ListPrice, SYSUTCDATETIME()
    FROM inserted AS i
    JOIN deleted  AS d ON d.ProductID = i.ProductID
    WHERE i.ListPrice <> d.ListPrice;
END;

<!-- .github/copilot-instructions.md — applies to every chat request in the repository -->
# Database conventions
- Target platform: Azure SQL Database, compatibility level 170.
- Always schema-qualify objects (dbo.Customer) and list columns explicitly; never SELECT *.
- Stored procedures: SET NOCOUNT ON, SET XACT_ABORT ON, TRY...CATCH with THROW.
- Parameterize everything; build dynamic SQL only with sp_executesql and parameters.
- Never emit GRANT ... TO public, credentials, or connection strings.

---
applyTo: "src/**/*.sql"
---
<!-- .github/instructions/tsql.instructions.md — only for files that match applyTo -->
Use the json data type for semi-structured columns and add a JSON index for queried paths.
Prefer CREATE OR ALTER for programmability objects.