Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

AUTO INCREMENT

The IDENTITY property in SQL Server is used to automatically generate unique values for a column. This is commonly used for primary key columns to ensure each row has a unique identifier without requiring manual input. The IDENTITY property is similar to the AUTO_INCREMENT feature in other database systems.

Key Concepts

1.Purpose of IDENTITY Property

Automatically generates unique values for a column.
Typically used for primary key columns to uniquely identify each row.

2.Syntax

IDENTITY(seed, increment): Defines the starting value (seed) and increment value for the column.
- seed: The starting value for the first row.
- increment: The value by which the column value is incremented for each new row.

3.Default Behavior

By default, if only IDENTITY is specified, it starts at 1 and increments by 1.

4.Usage

Applied when creating a new table or altering an existing table to add an identity column.

Code Samples

1. Creating Tables with IDENTITY

Define tables with the IDENTITY property to automatically generate unique values for primary key columns.

CREATE TABLE Store (
    StoreID INT PRIMARY KEY IDENTITY(1,1),-- Automatically incremented primary key
    StoreName VARCHAR(100) NOT NULL,
    Location VARCHAR(100),
    OpenDate DATETIME
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),-- Automatically incremented primary key
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50) DEFAULT 'General',
    Price DECIMAL(10,2) NOT NULL,
    StockQuantity INT DEFAULT 0
);

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),     -- Automatically incremented primary key
    mail VARCHAR(100),
    PhoneNumber VARCHAR(15)
);

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY IDENTITY(1,1),
    SaleDate DATETIME NOT NULL DEFAULT GETDATE(),
    StoreID INT NOT NULL,
    ProductID INT NOT NULL,
    CustomerID INT NULL,
    Quantity INT NOT NULL DEFAULT 1,
    TotalAmount DECIMAL(10,2) DEFAULT 0.00

);

These examples demonstrate how to use the IDENTITY property to create columns with auto-incrementing values in SQL Server. This ensures that each new row receives a unique value without manual intervention, simplifying data management and maintaining uniqueness.

Tansy SQL Course - AUTO INCREMENT - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found

Microsoft SQL Server

AUTO INCREMENT

Key Concepts

Code Samples

Most visited