Tables in SQL Server
Tables are the primary storage structure for relational data in SQL Server. They consist of rows and columns where each column has a defined data type and constraints.
Key Concepts
| Concept | Description |
|---|---|
Primary Key | Uniquely identifies each row in a table. |
Foreign Key | Defines a relationship to a primary key in another table. |
Identity | Auto-incrementing numeric column. |
Computed Column | Column whose value is derived from an expression. |
Partitioning | Splits a large table into smaller, manageable pieces. |
Creating a Table
CREATE TABLE dbo.Employee (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
HireDate DATE NOT NULL,
Salary MONEY CHECK (Salary >= 0),
DepartmentID INT FOREIGN KEY REFERENCES dbo.Department(DepartmentID)
);
Altering a Table
You can add, modify, or drop columns and constraints using ALTER TABLE statements.
-- Add a column
ALTER TABLE dbo.Employee
ADD Email NVARCHAR(255);
-- Modify a column
ALTER TABLE dbo.Employee
ALTER COLUMN Salary MONEY NULL;
-- Drop a constraint
ALTER TABLE dbo.Employee
DROP CONSTRAINT CK_Employee_Salary;
Best Practices
- Choose appropriate data types to minimize storage.
- Define primary keys as narrow, non-nullable, and immutable.
- Use appropriate constraints to enforce data integrity.
- Consider partitioning for very large tables.
- Regularly review and maintain indexes associated with tables.