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 AdventureWorks sample database.

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

USE AdventureWorks;
GO
-- Get a list of all employees named Smith.
SELECT c.FirstName, c.LastName
FROM HumanResources.Employee e JOIN Person.Contact c ON
e.ContactID = c.ContactID
WHERE c.LastName = 'Smith';
GO
-- 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');
GO
-- Change an employee name.
UPDATE Person.Contact
SET LastName = 'Smith'
FROM Person.Contact c, HumanResources.Employee e
WHERE c.ContactID = e.ContactID
AND e.EmployeeID = 116;