Where this runs: in the AWPractice database built on Day 9, continuing from the state Day 9 left. Steps run once, in order. Three of the plan's statements fail as written (drill 4, drill 5 and CREATE OR ALTER); each is shown with its error and then fixed.
USE AWPractice;
GO
CREATE TABLE dbo.SalesSummary (
SummaryID INT IDENTITY(1,1) PRIMARY KEY,
TerritoryID INT NOT NULL,
YearMonth CHAR(7) NOT NULL,
OrderCount INT NOT NULL DEFAULT 0,
Revenue DECIMAL(18,2) NOT NULL CHECK (Revenue >= 0),
LoadedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
CONSTRAINT UQ_SalesSummary UNIQUE (TerritoryID, YearMonth)
);
What this query does
CREATE TABLE defines each column's name, data type and rules. IDENTITY(1,1) numbers rows automatically from 1; PRIMARY KEY makes SummaryID unique and never NULL. NOT NULL forbids missing values. DEFAULT fills a value when an INSERT leaves the column out (0 orders, the current time). CHECK rejects any row where the condition is false (no negative revenue). UNIQUE on two columns means each territory can have only one row per month. Together these make the table refuse bad data instead of storing it.
Result in SSMS: Messages tab: Commands completed successfully
#2
2. Break each constraint on purpose. Read every error.
INSERT INTO dbo.SalesSummary (TerritoryID, YearMonth, Revenue) VALUES (1, '2013-01', -5); -- CHECK
INSERT INTO dbo.SalesSummary (TerritoryID, YearMonth, Revenue) VALUES (1, '2013-01', 100);
INSERT INTO dbo.SalesSummary (TerritoryID, YearMonth, Revenue) VALUES (1, '2013-01', 200); -- UNIQUE
INSERT INTO dbo.SalesSummary (TerritoryID, Revenue) VALUES (1, 100); -- NOT NULL
INSERT INTO dbo.SalesSummary (SummaryID, TerritoryID, YearMonth, Revenue) VALUES (99, 2, '2013-02', 1); -- IDENTITY
SELECT * FROM dbo.SalesSummary;
What this query does
Each INSERT breaks one rule, and each rule has its own error: 547 for the CHECK (negative revenue), 2627 for the UNIQUE key (a second row for territory 1 in 2013-01), 515 for NOT NULL (no YearMonth), and 544 for typing a value into an identity column (Day 9). Constraint errors fail only that statement, so the batch carries on and the one valid row is stored. It gets SummaryID 2, not 1: identity values are used up even by inserts that fail, so gaps in identity numbers are normal.
📍 Four errors, four different numbers. The one good row got SummaryID 2: the failed first INSERT still used up ID 1.
Result in SSMS: Messages tab: errors 547 (CHECK), 2627 (UNIQUE), 515 (NOT NULL) and 544 (IDENTITY); only the second INSERT succeeds
#3
3. Add a foreign key after the fact
ALTER TABLE Territory ADD CONSTRAINT PK_Territory PRIMARY KEY (TerritoryID);
ALTER TABLE dbo.SalesSummary ADD CONSTRAINT FK_SalesSummary_Territory
FOREIGN KEY (TerritoryID) REFERENCES Territory(TerritoryID);
INSERT INTO dbo.SalesSummary (TerritoryID, YearMonth, Revenue) VALUES (999, '2013-03', 1); -- FK fails
What this query does
A foreign key says 'every TerritoryID here must exist in Territory'. It can only reference a column that is a primary key or unique, so Territory gets its primary key first; this works because Day 9 removed the duplicate Pakistan row (it would fail with duplicates). The INSERT for territory 999 is then rejected with error 547, the same number as a CHECK failure but naming the FOREIGN KEY.
📍 A foreign key can only point at a primary key (or unique) column, so Territory needed its key first.
Result in SSMS: Messages tab: error 547, FOREIGN KEY conflict on TerritoryID 999
#4
4. ALTER TABLE: add, change, drop a column
ALTER TABLE dbo.SalesSummary ADD AvgOrderValue DECIMAL(18,2) NULL;
ALTER TABLE dbo.SalesSummary ALTER COLUMN YearMonth VARCHAR(10) NOT NULL;
ALTER TABLE dbo.SalesSummary DROP COLUMN AvgOrderValue;
What this query does
ADD works. ALTER COLUMN fails with error 5074 ('UQ_SalesSummary is dependent on column YearMonth') and 4922: SQL Server will not change a column's type while a constraint or index uses it. Error 4922 also aborts the rest of the batch, so the DROP COLUMN never runs and AvgOrderValue is left behind. That matters later: the computed column in the stretch has the same name.
📍 The plan's version fails: a column used by a constraint cannot be changed. Error 4922 also stops the batch, so DROP COLUMN never runs.
Result in SSMS: Messages tab: errors 5074 and 4922, YearMonth is used by UQ_SalesSummary
#5
4. ALTER TABLE: add, change, drop a column (continued)
-- Fix: drop the constraint, change the column, put the constraint back
ALTER TABLE dbo.SalesSummary DROP CONSTRAINT UQ_SalesSummary;
ALTER TABLE dbo.SalesSummary ALTER COLUMN YearMonth VARCHAR(10) NOT NULL;
ALTER TABLE dbo.SalesSummary ADD CONSTRAINT UQ_SalesSummary UNIQUE (TerritoryID, YearMonth);
ALTER TABLE dbo.SalesSummary DROP COLUMN AvgOrderValue;
What this query does
The standard order for changing a constrained column: drop the constraint, alter the column, add the constraint back. Then the leftover AvgOrderValue column is dropped, finishing what the plan's batch meant to do. On a big table, re-adding a UNIQUE constraint rebuilds its index, so this is done in a quiet period.
Result in SSMS: Messages tab: Commands completed successfully
#6
5. Fill it from the source. This is the query SSIS will run on Day 13.
INSERT INTO dbo.SalesSummary (TerritoryID, YearMonth, OrderCount, Revenue)
SELECT TerritoryID, FORMAT(OrderDate, 'yyyy-MM'), COUNT(*), SUM(TotalDue)
FROM SalesOrderHeader GROUP BY TerritoryID, FORMAT(OrderDate, 'yyyy-MM');
SELECT COUNT(*) AS RowsInSummary FROM dbo.SalesSummary;
What this query does
The plan's version fails. The test row from drill 2, territory 1 in 2013-01, is still in the table, and the load also produces a row for territory 1 in 2013-01, so the UNIQUE key rejects it. A single INSERT is all-or-nothing: one bad row cancels all 364. This is exactly what the UNIQUE constraint is for, because it stops a load from double-counting a month.
📍 One leftover test row made the whole 364-row load fail. A statement is all-or-nothing.
Result in SSMS: Messages tab: error 2627, duplicate key (1, 2013-01); RowsInSummary is still 1
#7
5. Fill it from the source. This is the query SSIS will run on Day 13. (continued)
-- Fix: clear the table first, so the load can be re-run safely
DELETE FROM dbo.SalesSummary;
INSERT INTO dbo.SalesSummary (TerritoryID, YearMonth, OrderCount, Revenue)
SELECT TerritoryID, FORMAT(OrderDate, 'yyyy-MM'), COUNT(*), SUM(TotalDue)
FROM SalesOrderHeader GROUP BY TerritoryID, FORMAT(OrderDate, 'yyyy-MM');
SELECT COUNT(*) AS RowsInSummary FROM dbo.SalesSummary;
What this query does
Clearing the table first makes the load re-runnable: running it twice gives the same result instead of an error. This 'delete then insert' (or truncate then insert) is the simplest full-reload pattern for summary tables. 364 rows: one per territory per month, matching the 364 territory-months from Day 8. The CHECK, NOT NULL and FOREIGN KEY constraints all checked every one of them.
📍 Delete-then-insert makes the load re-runnable. Day 13's SSIS package does the same.
Result in SSMS: 364 rows loaded, one per territory per month
#8
6. A view. Saved query, no data of its own.
CREATE VIEW dbo.vw_ProductCatalog AS
SELECT p.ProductID, p.Name, p.Color, p.ListPrice, s.Name AS Subcategory, c.Name AS Category
FROM Product p
JOIN AdventureWorks2022.Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID
JOIN AdventureWorks2022.Production.ProductCategory c ON s.ProductCategoryID = c.ProductCategoryID
WHERE p.ListPrice > 0;
GO
SELECT * FROM dbo.vw_ProductCatalog WHERE Category = 'Bikes';
What this query does
A view stores a query, not data. Every SELECT from the view runs the saved query against the current tables, so users can query 'products with category names' without writing the joins. CREATE VIEW must be the only statement in its batch, hence the GO. The prices are the AWPractice prices raised on Day 9, which proves the view reads live data.
Result in SSMS: 97 rows: the bikes, with their raised Day 9 prices
#9
7. Change the view, drop the view
ALTER VIEW dbo.vw_ProductCatalog AS SELECT ProductID, Name, ListPrice FROM Product WHERE ListPrice > 0;
GO
DROP VIEW dbo.vw_ProductCatalog;
What this query does
ALTER VIEW replaces the saved query but keeps the view's name and permissions. DROP VIEW deletes the view only; the tables it read are untouched, because a view holds no data of its own.
Result in SSMS: Messages tab: Commands completed successfully
#10
8. Temp table. Lives until the session ends.
SELECT TerritoryID, SUM(TotalDue) AS Revenue INTO #TerritoryRevenue
FROM SalesOrderHeader GROUP BY TerritoryID;
SELECT t.Name, r.Revenue FROM #TerritoryRevenue r JOIN Territory t ON r.TerritoryID = t.TerritoryID;
DROP TABLE #TerritoryRevenue;
What this query does
A table whose name starts with # is a local temporary table. It is stored in tempdb, only this session (this query window) can see it, and it is dropped automatically when the session ends. Temp tables are useful for breaking a big report into steps, and unlike a CTE they can be indexed and reused by several later queries. Dropping it yourself when done is good practice.
Result in SSMS: 10 rows
#11
9. Table variable. Same idea, smaller scope, no statistics.
DECLARE @Top AS TABLE (ProductID INT, Name NVARCHAR(50), ListPrice MONEY);
INSERT INTO @Top SELECT TOP 5 ProductID, Name, ListPrice FROM Product ORDER BY ListPrice DESC;
SELECT * FROM @Top;
What this query does
A table variable (@name) lives only for the batch it is declared in; after the batch ends it is gone, so the three statements must run together. SQL Server keeps no statistics on it, so it suits small sets like this one; for thousands of rows a #temp table usually gives a better query plan.
Result in SSMS: 5 rows
#12
10. See what you built
SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES;
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'SalesSummary';
What this query does
INFORMATION_SCHEMA views describe the database (Day 1). The first lists every table; the second shows SalesSummary's constraints: PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY. NOT NULL and DEFAULT are not listed here because they are column properties. Names like PK__SalesSum__... were made up by SQL Server because the CREATE TABLE did not name them, which is why teams name every constraint (UQ_SalesSummary).
Result in SSMS: two result sets: the tables in AWPractice, and SalesSummary's 4 constraints
Stretch
#13
Computed column
ALTER TABLE dbo.SalesSummary ADD AvgOrderValue AS
(CASE WHEN OrderCount = 0 THEN 0 ELSE Revenue / OrderCount END);
GO
SELECT TOP 3 TerritoryID, YearMonth, OrderCount, Revenue, AvgOrderValue FROM dbo.SalesSummary;
What this query does
A computed column stores a formula, not a value; it is calculated whenever it is read, so it can never be out of date. The CASE avoids dividing by zero. The GO matters: SQL Server compiles a whole batch before running it, and without it the SELECT fails with error 207 because AvgOrderValue does not exist yet at compile time. This only works because drill 4's fix dropped the old AvgOrderValue column; with the plan's version it fails with error 2705 (column name used twice).
Result in SSMS: 3 rows with AvgOrderValue calculated
#14
Schema. Group your objects like AdventureWorks does.
CREATE SCHEMA reporting;
GO
ALTER SCHEMA reporting TRANSFER dbo.SalesSummary;
SELECT TOP 3 SummaryID, TerritoryID, YearMonth, Revenue FROM reporting.SalesSummary;
What this query does
A schema is a named folder for objects, like Sales and Production in AdventureWorks. CREATE SCHEMA must be alone in its batch. ALTER SCHEMA ... TRANSFER moves the table, with its data and constraints, into the new schema, and from then on it must be called reporting.SalesSummary. Schemas also make permissions easy: grant a reporting team access to the whole reporting schema.
Result in SSMS: 3 rows, now read as reporting.SalesSummary
#15
DROP IF EXISTS and CREATE OR ALTER: the deploy-script pattern
DROP TABLE IF EXISTS dbo.Scratch;
CREATE OR ALTER VIEW dbo.vw_Territories AS SELECT TerritoryID, Name FROM Territory;
GO
What this query does
The plan's version fails with error 111. CREATE OR ALTER VIEW follows the same rule as CREATE VIEW: it must be the first statement in its batch, and here DROP TABLE comes first.
Result in SSMS: Messages tab: error 111, CREATE VIEW must be the first statement in a query batch
#16
DROP IF EXISTS and CREATE OR ALTER: the deploy-script pattern (continued)
DROP TABLE IF EXISTS dbo.Scratch;
GO
CREATE OR ALTER VIEW dbo.vw_Territories AS SELECT TerritoryID, Name FROM Territory;
GO
SELECT * FROM dbo.vw_Territories;
What this query does
With a GO between them both statements work. DROP TABLE IF EXISTS does nothing when the table is missing instead of raising an error, and CREATE OR ALTER creates the view the first time and replaces it after that. Together they make a deploy script safe to run again and again. The view shows 12 territories, including those added on Day 9.
📍 Both statements are safe to re-run, which is what a deploy script needs.
Result in SSMS: 12 territories, including Pakistan and Bangladesh from Day 9
#17
Global temp table, visible to other sessions
SELECT TOP 10 * INTO ##Shared FROM Product;
SELECT @@SPID AS ThisSession, COUNT(*) AS RowsInShared FROM ##Shared;
What this query does
Two hashes make a global temporary table: any session can read it until the session that created it closes. @@SPID is this connection's session ID, shown so the next screenshot can prove a different session reads the same table.
Result in SSMS: 1 row: session 69 created ##Shared with 10 rows
#18
Global temp table, visible to other sessions (continued)
-- Run in a NEW query window (a different session)
SELECT @@SPID AS OtherSession, COUNT(*) AS RowsSeen FROM ##Shared;
What this query does
This runs in a second query window, which is a separate session with a different @@SPID, and it still sees the 10 rows. A #local temp table would be invisible here. Global temp tables are rarely needed, and their names can clash between users.
Result in SSMS: 1 row: session 56, a different session, still sees the 10 rows
Self-test
#19
Q1. Create reporting.ProductSales and fill it
USE AWPractice;
CREATE TABLE reporting.ProductSales (
ProductID INT NOT NULL,
YearMonth CHAR(7) NOT NULL,
Qty INT NOT NULL CONSTRAINT CK_ProductSales_Qty CHECK (Qty > 0),
Revenue DECIMAL(18,2) NOT NULL,
CONSTRAINT PK_ProductSales PRIMARY KEY (ProductID, YearMonth)
);
INSERT INTO reporting.ProductSales (ProductID, YearMonth, Qty, Revenue)
SELECT d.ProductID, CONVERT(char(7), h.OrderDate, 120), SUM(d.OrderQty), SUM(d.LineTotal)
FROM SalesOrderDetail d JOIN SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
GROUP BY d.ProductID, CONVERT(char(7), h.OrderDate, 120);
SELECT COUNT(*) AS RowsLoaded FROM reporting.ProductSales;
What this query does
A composite primary key on (ProductID, YearMonth) means one row per product per month. The named CHECK keeps Qty positive. The load joins order lines to their orders to get the month (CONVERT(char(7), ..., 120) gives 'yyyy-mm', from Day 8), then groups by product and month. Grouping by exactly the primary-key columns guarantees no duplicate keys: 3,862 rows. USE AWPractice is needed because this query window was opened fresh.
Result in SSMS: 3,862 product-months loaded
#20
Q2. A view on it that adds the product name
CREATE VIEW reporting.vw_ProductSales AS
SELECT ps.ProductID, p.Name AS ProductName, ps.YearMonth, ps.Qty, ps.Revenue
FROM reporting.ProductSales ps JOIN Product p ON ps.ProductID = p.ProductID;
GO
SELECT TOP 5 * FROM reporting.vw_ProductSales ORDER BY Revenue DESC;
What this query does
The view joins in the product name so report users never have to. The best single product-month is the Mountain-200 Black, 38 in March 2014, with 336,401.
Result in SSMS: top 5 product-months by revenue
#21
Q3. Add a foreign key to Product and prove it rejects a bad ProductID
Product gets its primary key first, as Territory did in drill 3. Adding the foreign key also checks the 3,862 existing rows, and they all pass. The INSERT for product 99999 is rejected with error 547.
Result in SSMS: Messages tab: error 547, FOREIGN KEY conflict on ProductID 99999
#22
Q4. Drop everything in the right order: view, FK, table
DROP VIEW reporting.vw_ProductSales;
ALTER TABLE reporting.ProductSales DROP CONSTRAINT FK_ProductSales_Product;
DROP TABLE reporting.ProductSales;
SELECT OBJECT_ID('reporting.ProductSales') AS TableLeft, OBJECT_ID('reporting.vw_ProductSales') AS ViewLeft;
What this query does
Things that depend on a table go first: the view that reads it, then the foreign key on it, then the table itself. (Dropping a table also drops its own foreign keys; dropping one explicitly is the habit for when other tables reference it.) OBJECT_ID returns NULL for an object that does not exist, which proves both are gone.
📍 Drop from the top of the dependency chain down: view, then foreign key, then table.
Result in SSMS: no errors; both OBJECT_IDs are NULL, so both objects are gone
Not shown: the plan's manual step, right-click Product in Object Explorer > Script Table as > CREATE To, is a click-through in SSMS rather than a query. Doing it on dbo.Product now shows the PRIMARY KEY added in self-test Q3.
What batches taught today: CREATE VIEW, ALTER VIEW, CREATE OR ALTER and CREATE SCHEMA must be alone in their batch, and a new column cannot be used later in the same batch that adds it. When in doubt, put GO after any statement that creates or changes an object.