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 2005 have the following main components:
Each column represents some attribute of the object modeled by the table, such as a parts table having columns for ID, color, and weight.
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;