Examples of AUTO_INCREMENT in SQL Server
Example 1: Employees table with EmployeeID is auto increment
CREATE TABLE Employees (
EmployeeID INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
Now we will insert the data into this table.
INSERT INTO Employees (FirstName, LastName)
VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Michael', 'Johnson'),
('Emily', 'Davis');
When we insert this values they will get inserted into the table with the employee id.
Output:
Explanation: The provided SQL script is not valid for SQL Server. However, assuming it’s meant for a database system like SQLite, the AUTOINCREMENT keyword automatically generates unique values for the EmployeeID column, ensuring each record has a distinct identifier.
Example 2: Products table with ProductID as Auto Increment
CREATE TABLE Products (
ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
ProductName VARCHAR(50),
Price DECIMAL(10, 2),
StockQuantity INT
);
Now we will insert the data into this table.
INSERT INTO Products (ProductName, Price, StockQuantity)
VALUES
('Smartphone', 699.99, 100),
('Laptop', 1299.99, 50),
('Headphones', 99.99, 200),
('Smart Watch', 249.99, 150);
When we insert this values they will get inserted into the table with the product id.
Output:
Explanation: The provided SQL script creates a Products table with a primary key ProductID set to AUTOINCREMENT. The subsequent INSERT statements add records with unique ProductID values automatically generated by the system, ensuring each product entry has a distinct identifier.
In the resulting table ProductName, Price, and StockQuantity are added with Sequential ProductID.
Example 3: Customer table with CustomerID as Auto Increment
CREATE TABLE Customers (
CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
Phone VARCHAR(20)
);
Now we will insert the data into this table.
INSERT INTO Customers (FirstName, LastName, Email, Phone)
VALUES
('Alice', 'Smith', 'alice@example.com', '123-456-7890'),
('Bob', 'Johnson', 'bob@example.com', '234-567-8901'),
('Charlie', 'Davis', 'charlie@example.com', '345-678-9012'),
('David', 'Brown', 'david@example.com', '456-789-0123');
When we insert this values they will get inserted into the table with the customer id.
Output:
Explanation: The provided SQL script creates a Customers table with a primary key CustomerID set to AUTOINCREMENT. The subsequent INSERT statements add records with unique CustomerID values automatically generated by the system, ensuring each customer entry has a distinct identifier.
In the resulting table FistName, LastName, Email, and Phone are added with sequential CustomerID.
How to Create Id with AUTO_INCREMENT in SQL Server?
Structured Query Language also known as SQL is a tool for storing, managing, and manipulating relational databases. SQL Server is a popular relational database management system (RDBMS) developed by Microsoft, providing a variety of operators to perform different operations on given datasets.
In this we will explore SQL’s AUTO_INCREMENT, exemplifying its usage in tables like Employees, Products, and Customers, ensuring unique identifiers for each record.