CREATE TABLE and choosing column types

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Define a small disposable table with types chosen for its values. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #PracticeLabels (LabelID int NOT NULL PRIMARY KEY,
    LabelName nvarchar(40) NOT NULL, CreatedOn date NOT NULL);
INSERT INTO #PracticeLabels VALUES (1, N'Example', CONVERT(date, '2023-01-02'));
SELECT LabelID, LabelName, CreatedOn FROM #PracticeLabels ORDER BY LabelID;
DROP TABLE #PracticeLabels;

Read it step by step

1. int represents the key; nvarchar holds Unicode text; date holds a calendar date without a time.
2. NOT NULL requires a value and PRIMARY KEY enforces unique non-NULL IDs.
3. The # prefix makes the table session-local; DROP removes it after the check.

Check the result

The canonical training schema remains unchanged.

Practice

Create a temporary product note table with int ID and nvarchar(40) Note, insert one row, show it, and drop the table.

CREATE TABLE #ProductNotes (ProductID int NOT NULL PRIMARY KEY, Note nvarchar(40) NOT NULL);
INSERT INTO #ProductNotes (ProductID, Note) VALUES (1, N'Practice');
SELECT ProductID, Note FROM #ProductNotes ORDER BY ProductID;
DROP TABLE #ProductNotes;