Table Basics

Tables are database objects that contain all the data in a database. A table definition is a collection of columns. In tables, data is organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field within the record. For example, a table that contains employee data for a company can contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

Tables in SQL Server have the following main components:

  • Columns

    Each column represents some attribute of the object modeled by the table, such as a parts table having columns for ID, color, and weight.

  • Rows

    Each row represents an individual occurrence of the object modeled by the table. For example, the parts table would have one row for each part carried by the company.

The following illustration shows the HumanResources.Department table in the AdventureWorks2008R2 sample database.

AdventureWorks.HumanResources.Department table

Users work with the data in tables using data manipulation language (DML) Transact-SQL statements, as shown in the following examples.

USE AdventureWorks2008R2;
-- Get a list of all employees named Smith.
SELECT p.FirstName, p.LastName
FROM HumanResources.Employee e JOIN Person.Person p ON
e.BusinessEntityID = p.BusinessEntityID
WHERE p.LastName = 'Smith';
-- Delete a purchase order detail record.
DELETE Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID = 732;

-- Add a new work shift:
INSERT INTO HumanResources.Shift ([Name], StartTime, EndTime)
VALUES ('Flex', '1900-01-01', '1900-01-01');
-- Change an employee name.
UPDATE Person.Person
SET LastName = 'Smith'
FROM Person.Person p, HumanResources.Employee e
WHERE p.BusinessEntityID = e.BusinessEntityID
AND e.BusinessEntityID = 116;