Microsoft Docs

Tables and Columns

This section provides comprehensive guidance on designing, creating, and managing tables and their columns within SQL Server. Understanding these fundamental building blocks is crucial for effective database development.

Understanding Tables

A table is the basic unit of data storage in a relational database. It consists of rows (records) and columns (fields), where each row represents an instance of an entity and each column represents an attribute of that entity. Tables are organized into schemas for better management.

Designing Your Tables

Effective table design involves:

• Identifying Entities: Determine the main subjects or concepts your database needs to store information about (e.g., Customers, Products, Orders).
• Defining Attributes: For each entity, identify the specific pieces of information you need to store (e.g., Customer Name, Product Price, Order Date).
• Choosing Data Types: Select appropriate data types for each attribute to ensure data integrity and efficient storage.
• Establishing Relationships: Define how tables relate to each other through primary and foreign keys.
• Normalization: Applying normalization principles to reduce data redundancy and improve data integrity.

Creating Tables

You can create tables using SQL Data Definition Language (DDL) statements, primarily the CREATE TABLE statement. Here's a basic example:

CREATE TABLE dbo.Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName NVARCHAR(100) NOT NULL,
    SupplierID INT,
    CategoryID INT,
    UnitPrice DECIMAL(10, 2),
    UnitsInStock INT
);
                

Explanation:

• dbo.Products: Specifies the schema (dbo) and table name (Products).
• ProductID INT PRIMARY KEY IDENTITY(1,1): Defines an integer column named ProductID, which is the primary key and automatically increments its value starting from 1.
• ProductName NVARCHAR(100) NOT NULL: Defines a Unicode string column that can hold up to 100 characters and cannot be null.
• SupplierID INT, CategoryID INT: Integer columns that may reference other tables.
• UnitPrice DECIMAL(10, 2): A decimal number column with a precision of 10 digits in total, with 2 digits after the decimal point.
• UnitsInStock INT: An integer column representing the current stock level.

Understanding Columns

Columns are the vertical components of a table, each representing a specific data attribute. Key aspects of columns include:

• Data Type: The type of data a column can store (e.g., INT, VARCHAR, DATE, DECIMAL).
• Nullability: Whether the column can contain NULL values (specified by NULL or NOT NULL).
• Default Values: A predefined value that is inserted if no value is explicitly provided.
• Constraints: Rules enforced on column data, such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints.

Common Column Data Types

Modifying Tables and Columns

You can alter existing tables and columns using the ALTER TABLE statement. This includes adding, dropping, or modifying columns, as well as adding or dropping constraints.

Example: Adding a column

ALTER TABLE dbo.Products
ADD DateAdded DATE DEFAULT GETDATE();
                

Example: Dropping a column

ALTER TABLE dbo.Products
DROP COLUMN UnitsInStock;
                

Best Practices for Table and Column Design

• Use clear and descriptive names for tables and columns.
• Employ a consistent naming convention.
• Define primary keys for every table.
• Use foreign keys to enforce relationships between tables.
• Select appropriate data types to match the data being stored.
• Use NOT NULL constraints where applicable to ensure data integrity.
• Normalize your database schema to reduce redundancy.
• Consider performance implications when choosing data types and designing indexes.