MSDN Documentation

Tables and Columns in SQL Server

Understanding tables and columns is fundamental to relational database design. In SQL Server, tables are the primary structures for storing data, organized into rows and columns.

What is a Table?

A table is a database object that contains data organized in a tabular format. Each table consists of rows (records) and columns (fields). Think of it like a spreadsheet where each row is an entry and each column represents a specific piece of information about that entry.

What is a Column?

A column, also known as a field, represents a single attribute or characteristic of the data being stored. For example, in a Customers table, columns might include CustomerID, FirstName, LastName, Email, and PhoneNumber.

Defining Tables

You define tables using the CREATE TABLE statement. This statement specifies the table name, the names of its columns, and the data type for each column.

Syntax for CREATE TABLE

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);
                

Example: Creating a 'Products' Table

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    StockQuantity INT DEFAULT 0
);
                

Column Properties

• Data Type: Specifies the type of data that can be stored in the column (e.g., INT for integers, VARCHAR for strings, DECIMAL for numbers with decimal points, DATETIME for dates and times).
• Constraints: Rules that enforce data integrity. Common constraints include:
  • PRIMARY KEY: Uniquely identifies each row in a table.
  • NOT NULL: Ensures that a column cannot have a NULL value.
  • UNIQUE: Ensures that all values in a column are distinct.
  • FOREIGN KEY: Links data in one table to data in another table.
  • DEFAULT: Sets a default value for a column if no value is specified during insertion.
  • CHECK: Ensures that all values in a column satisfy a specific condition.

Altering Tables

You can modify existing tables using the ALTER TABLE statement. This allows you to add, delete, or modify columns, as well as add or drop constraints.

Adding a Column

ALTER TABLE Products
ADD Description NVARCHAR(MAX);
                

Dropping a Column

ALTER TABLE Products
DROP COLUMN Category;
                

Data Types for Columns

Choosing the correct data type is crucial for efficient storage and data integrity. Here are some common SQL Server data types:

For more detailed information on specific data types and their usage, refer to the SQL Server Data Types documentation.

Best Practices

• Use descriptive names for tables and columns.
• Choose the most appropriate data type for each column.
• Define primary keys for all tables.
• Use constraints to enforce data integrity.
• Normalize your database schema to reduce redundancy and improve data consistency.