# ✅ **1. Create a table `Product` with id, price, and another column**
“`sql
CREATE TABLE Product (
ProductId INT IDENTITY(1,1) PRIMARY KEY,
Price DECIMAL(10,2) NOT NULL,
ProductName VARCHAR(100) NOT NULL
);
“`
—
# ✅ **2. Add a constraint to that table**
### Example: add a constraint that **Price must be greater than 0**
“`sql
ALTER TABLE Product
ADD CONSTRAINT CK_Product_Price_Positive
CHECK (Price > 0);
“`
### Example: add a **unique constraint** on ProductName
“`sql
ALTER TABLE Product
ADD CONSTRAINT UQ_Product_ProductName UNIQUE (ProductName);
“`
—
# ✅ **3. Create a hierarchy data structure**
Use a **self-referencing table** (commonly used for category trees, org charts, menus, etc.)
### Example: Category hierarchy
“`sql
CREATE TABLE Category (
CategoryId INT IDENTITY(1,1) PRIMARY KEY,
CategoryName VARCHAR(100) NOT NULL,
ParentCategoryId INT NULL,
FOREIGN KEY (ParentCategoryId) REFERENCES Category(CategoryId)
);
“`
This allows:
* Category → Subcategory → Sub-subcategory, etc.
—
NUMBER 4
In **SSMS**, step **4 (“Create 1–2 .NET structures in SQL”)** usually means:
➡️ **Create SQL Server types that mimic structures/classes**
➡️ These are called **User-Defined Table Types (UDTT)** or **User-Defined Types (UDT)**.
Here are the two ways you can do it in SSMS:
—
# ✅ **Option A — Create a SQL User-Defined Table Type (UDTT)**
This is the closest to a “.NET structure” but in SQL form.
### Example: Product structure
“`sql
CREATE TYPE ProductType AS TABLE
(
ProductId INT,
Price DECIMAL(10,2),
ProductName VARCHAR(100)
);
“`
### Example: Category structure
“`sql
CREATE TYPE CategoryType AS TABLE
(
CategoryId INT,
CategoryName VARCHAR(100),
ParentCategoryId INT NULL
);
“`
You can now pass these types to stored procedures:
“`sql
CREATE PROCEDURE InsertProducts
@Products ProductType READONLY
AS
BEGIN
INSERT INTO Product(ProductId, Price, ProductName)
SELECT ProductId, Price, ProductName
FROM @Products;
END
“`
—
# ✅ **Option B — Create a SQL User-Defined Scalar Type (UDT)**
This is when you want a custom **single value type**, similar to a C# `struct` with only one field.
### Example: custom SQL type:
“`sql
CREATE TYPE PriceType FROM DECIMAL(10,2) NOT NULL;
“`
But this is less common.
—
# 🧭 **Where to do this in SSMS**
1. Open SSMS
2. Expand your database
3. Go to **Programmability**
4. Expand **Types**
5. Right-click:
* **User-Defined Table Types** → *New User-Defined Table Type…*
* **User-Defined Data Types** → *New User-Defined Data Type…*
Or paste the CREATE TYPE scripts into a new query window and run them.
—
# If you want, tell me:
⚡ What structure you want (fields + types),
and I’ll generate the exact SSMS script for you.
—
# ✅ **5. Create a scalar function**
Example: return **discounted price**
“`sql
CREATE FUNCTION dbo.GetDiscountedPrice
(
@Price DECIMAL(10,2),
@DiscountPercent INT
)
RETURNS DECIMAL(10,2)
AS
BEGIN
RETURN @Price – (@Price * @DiscountPercent / 100.0);
END;
“`
Use it like:
“`sql
SELECT dbo.GetDiscountedPrice(100, 20) AS FinalPrice;
“`