Database Tables

TABLES

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.

After you have designed the database , the tables that will store the data in the database can be created. The data is usually stored in permanent tables. Tables are stored in the database files until they are deleted and are available to any user who has the appropriate permissions.

Temporary Tables

You can also create temporary tables. Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.

For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect.

Table Properties

You can define up to 1,024 columns per table. Table and column names must follow the rules for identifiers; they must be unique within a given table, but you can use the same column name in different tables in the same database. You must also define a data type for each column.

Although table names must be unique for each owner within a database, you can create multiple tables with the same name if you specify different owners for each.

Creating a Table using Enterprise Manager:

1. Open the Enterprise Manger Tool from the Start menu.
2. Expand the server Group and the corresponding server.
3. Expand the corresponding database
4. Right click on Tables and select New Table… item

5. In the window displayed enter a name to the column, data type and lengh

1. Click on the save icon of the toolbar to save the table.

Creating a Table using Query Analyzer:

We can make use of the following syntax to create a table:

	CREATE TABLE tablename (colname datatype (size) [NOT NULL | DEFAULT <constant value> | IDENTITY(seed,increment)], colname2 ……..)

NULL | NOT NULL

Are keywords that determine if null values are allowed in the column. NULL is not strictly a constraint but can be specified in the same manner as NOT NULL.

DEFAULT

Specifies the value provided for the column when a value is not explicitly supplied during an insert.

constant_expression

Is a constant, NULL, or a system function used as the default value for the column.

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

seed

Is the value used for the very first row loaded into the table.

increment

Is the incremental value added to the identity value of the previous row loaded.

Example:

1. Select the corresponding database in which we want to create the table from the available drop down button.
2. Type the following command in the window.

1. click on Execute button to run the command.

Example 2: Crating a table with identity column

	CREATE TABLE dept ( dept_no int identity (10,10), dept_name varchar(20), location varchar(20) )

When an IDENTITY property to define an identifier column, consider that:

• A table can have only one column defined with the IDENTITY property
• The seed and increment can be specified. The default for both is 1
• The identifier column must not allow NULL values and must not contain DEFAULT definition
• In order to insert the values explicitly into the identity column , SET IDENTITY_INSERT option is used.

Syntax:

	SET IDENTITY_INSERT tablename ON | OFF

DATATYPES:

The choice of the datatype determines the kind of data that can be stored in the column and the maximum length of data that can be stored in the column. SQL Server provides different type of data. They are:

Numeric Datatypes:

SQL Server provides many ways to store numeric values, which provide flexibility in precision, range of values, and data storage size. Numeric type falls into various categories like int, smallint, tinyint, money, smallmoney, and bit. To store decimal values datatypes like float,real, decimal and numeric are use.

“Bigint” is the new datatype in SQL Server 2000 under this category.

Character Datatype:

There are three valid datatypes for storing strings. They are:

Char: this is used for storing fixed-length strings. Columns defined as char will store blanks to fill out a fixed number of characters. The maximum length of a character column is 8000 bytes.

Varchar: this is used for storing variable length strings. Columns defined as varchar will truncate blanks to save space.

Text: this is used for storing of virtually unlimited size(upto 2 gigabytes of text per row.

Binary Datatypes:

These datatypes store  strings consisting of binary values.i.e, haxadecima numbers instead of characters. They are:

Binary: Stores binary data of fixed lengh with a maximum lengh of 8,000 bytes

Varbinary: Stores variable lengh binary data with a maximum length of 8,000 bytes

Image: SQL Server provides a mechanism for storing binary data more than 8000 bytes using image datatypes. For Example, the photograph of the employees can be stored.

DateTime Datatype:

SQL Server enables to store date and time values. Columns using datetime or smalldatetime will store both date and time.

Unicode Datatype:

Unicode standard includes all the characters that are defined in the various character sets. Using Unicode data types, a column can store any character that is defined by the Unicode standard. Unicode data is stored using nchar, nvarchar, ntext datatypes.

Special DataTypes:

Cursor,TimeStamp, Unique Identifier and Bit.

New Datatypes:

SQL Server 2000 introduces three new data types. bigint is an 8-byte integer type. sql_variant is a type that allows the storage of data values of different data types. table is a type that allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user-defined functions

Modifying Tables

After a table is created, you can change many of the options that were defined for the table when it was originally created, including:

• Columns can be added, modified, or deleted. For example, the column name, length, data type, precision, scale, and nullability can all be changed, although some restrictions exist. For more information.
• PRIMARY KEY and FOREIGN KEY constraints can be added or deleted.
• UNIQUE and CHECK constraints and DEFAULT definitions (and objects) can be added or deleted.
• An identifier column can be added or deleted using the IDENTITY or ROWGUIDCOL property.

Renaming a  Table

	Syntax: SP_RENAME <old_tableName>,<new_tableName> Example: SP_RENAME employee.emp

Renaming a Column:

	Syntax: SP_RENAME <’ tableName.columnname’>,<’newcolName’> Example: SP_RENAME ‘emp.basic_salary’,’bsal’

Syntax for ALTER TABLE command:

	ALTER TABLE tablename ADD columnName datatype [not null |identity|default…] |ALTER COLUMN <column specifications> |DROP COLUMN columnName |ADD CONSTRAINT constraintName {CHECK(..) | UNIQUE | PRIMARY KEY..} |DROP CONSTRAINT constraintName

Examples:

Changing datatype

	ALTER TABLE emp ALTER COLUMN emp_no varchar(5)

Changing NULLs

	ALTER TABLE emp ALTER COLUMN emp_no varchar(5) not null

Adding Columns

	ALTER TABLE emp ADD doj datetime

Sp_help:

This System Stored Procedure is used to provide description about the table structure.

Example:

	SP_HELP emp

Inserting Values in a Table

The Insert command is used to add rows to a table. The data entered in the table should match the order of the columns as they appear in the table.

Syntax:

	INSERT <table_name> (column_list) VALUES (data_list)

Example:

	INSERT emp VALUES(‘E001’,’sam’,10,’B’,4500,’11/3/96’)

• When only the date is specified in the datetime datatype, the time is included as 00:00 AM by default.
• If we want to insert only certain columns data into the table, the column names have to be specified.

Example:

	INSERT emp ( emp_no,emp_name) VALUES(‘E004’,’Sammy’)

Data Manipulation

To modify the values stored in a table, UPDATE statement is used.

Example: the employee name Samuel with the emp_no “E001” was wrongly entered as Sam in the table. To correct it, the following statement is used.

	UPDATE emp SET emp_name=’Samuel’ WHERE emp_no=’E001’

Deleting data from a Table:

To remove rows from a table, DELETE or TRUNCATE statements can be used.

To delete a particular row from the table we have to use DELETE command.

	Syntax: DELETE FROM tablename WHERE condition Example: DELETE FROM emp WHERE emp_no=’E002’

To delete all rows from the table, TRUNCATE command can also be used.

	Syntax: TRUNCATE TABLE tablename Example: TRUNCATE TABLE emp

The TRUNCATE statement removes the whole table at a time. Whereas the delete table command removes each row from the table one by one. This is much faster than a DELETE statement, especially on large tables.

To remove the table along with the structure, DROP statement is used.

Syntax: DROP TABLE tablename Example: DROP TABLE dept1

Retrieval of Data:

To retrieve data from a database object, SELECT statement is used.

Syntax:

	SELECT <column_list> FROM <table_name> WHERE <condition>

Example:

	SELECT * FROM emp SELECT emp_no,emp_name FROM emp WHERE dept_no=10

Tags: Database Tables, SQL Recordes, Temporary Tables