Creating a table in Microsoft SQL Server involves using the CREATE TABLE statement. Here’s a basic syntax for creating a table:
CREATE TABLE TableName
(
Column1 DataType Constraints, Column2 DataType Constraints );
(
Column1 DataType Constraints, Column2 DataType Constraints );
Example
Suppose you want to create a table called Employees with the following columns:
EmployeeID(an integer that will be the primary key)FirstName(a string of up to 50 characters)LastName(a string of up to 50 characters)HireDate(a date)
Here’s how you would write the CREATE TABLE statement:
CREATE TABLE Employees
( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, HireDate DATE );
( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, HireDate DATE );
Key Points
- Data Types: Define the type of data each column will hold (
INT,NVARCHAR,DATE, etc.). - Constraints: Enforce rules on the data (
PRIMARY KEY,NOT NULL,UNIQUE,FOREIGN KEY, etc.).- Primary Key: Uniquely identifies each row in the table.
- Not Null: Ensures a column cannot have a NULL value.
- Identity: Automatically generates a unique value for the column (useful for primary keys).
Additional Constraints
You can also add constraints like FOREIGN KEY to enforce relationships between tables or UNIQUE to ensure all values in a column are unique.
For example, if you want to add a foreign key constraint to reference a DepartmentID in a Departments table, you might do it like this:
CREATE TABLE Employees
( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, HireDate DATE, DepartmentID INT, CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) );
( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(50) NOT NULL, LastName NVARCHAR(50) NOT NULL, HireDate DATE, DepartmentID INT, CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) );
Feel free to adjust the column names, data types, and constraints according to your specific requirements.