Chapter 12. Creating and Managing Tables in SQL Server


by Paul Jensen

IN THIS CHAPTER

  • Datatypes

  • Numeric Datatypes

  • Datatype Synonyms

  • User-Defined Datatypes

  • Creating Tables

  • Creating Tables Using T-SQL

  • Creating Tables Using Table Designer

  • Viewing Table Properties and Data in Enterprise Manager

  • Limitations on Table Size and Dimensions

  • Dropping Tables

  • Temporary Tables

The table is the basic structure upon which the relational database is built. A table is a set of columns , having defined properties, used to store data. The stored data is represented as rows in the table. These rows are used to represent an entity (an employee, for example), that has a number of attributes (last name , salary) associated with it. These attributes are stored in the table's columns. Table 12.1 illustrates a typical table structure.

Table 12.1. An Example of a Table, Showing Columns and Rows
Emp_no Lname Fname Phone Dept Salary
1 Smith John 555-1111 20 10000
2 Jones Jill 555-1211 20 10000
3 Johnston Bob 555-3214 30 20000
4 Jensen Carl 555-4321 40 12000
5 Wright Alex 555-2156 40 14000
6 Ivings Kris 555-3215 20 21000

The CREATE TABLE and UPDATE TABLE commands are used to create and modify tables. When you create a table, you must provide a table name, a name for each of the columns, and a datatype for each column. Optionally, but highly recommended, you can specify whether a column should allow null values. The script for creating the table in Table 12.1 is shown in Listing 12.1.

Listing 12.1 A Simple Create Table Script
 CREATE TABLE employee (   Emp_no int NOT NULL,   Lname char(20) NULL,   Fname char(20) NULL,   Phone char(13) NULL,   Dept smallint NULL,   Salary int NULL ) 

The example in Listing 12.1 uses three different datatypes int , char , and smallint ”to define how data will be stored. Before you learn more about the business of creating tables, it's important that you have an understanding of datatypes.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net