SQL Server Tables

Tables in Database

In this tutorial we will learn about table which is one of the most important and fundamental database objects. A table is a business entity which stores information about the entity in different columns of the table. Each column represents property of the entity. Each row in the table represents a new entity member. For example an employee entity will have properties like employee ID, first name, last name, date of joining salary etc. In employee table, these properties will be represented by columns like employee ID, first name, last name, date of joining, salary etc. Each row in the employee table will represent a new employee of the company.

In database terminology an entity property is called field or column and entity entry is called record or row of table.

The data entered into a column of table has data type and other properties.

Table Definition

When a table is defined in database then each column of the table is given its data type and other properties e.g. checks, default, unique, primary key and other constraints.

Schema

Meta information of all the columns of a table constitutes schema of table. Schema is not the data of table rather it defines the structure of the table which implies the number of fields, their order of position in table, their data types and other properties.

Primary key is a piece of information of a record which uniquely distinguishes the record from other records in table. No two records in a table can have the same primary key value. The primary key value cannot be null. The value of more than one column together can constitute primary key value. It means that a single column or more than one column of a table can represent a primary key. Primary key based on two or more columns of table is called composite primary key.

Relationship between tables: Two tables in database can be related using primary key and foreign key. Foreign key is a value of primary key in another table to relate one table with another. Table with PK is called parent table and table with FK is called child table. These tables have Parent-Child relationship, also called Many-One relationship.

SEARCHING A TABLE

An index is created on a table to quickly search table. Index on a table can be created inside the table or in a separate table. A table can have many indexes but can have only one clustered index. When primary key is set on a table, index is automatically created on primary key field of the table.

Validation Rules: On a column of a table validation rules and defaults can be applied. Validation rule is a Boolean expression which returns true or false value. If the value is true then the operation is successful otherwise not. In T-SQL, validation can be defined using constraint. Check constraint is used to check a condition on column. Unique constraint is used to allow only unique value in column. Default constraint is a single value that is applied on a column of a table. To insert a default value we use default keyword in insert query as a value. Primary key constraint is used to allow unique and not null value on column. Foreign key constraint is applied to reference primary key of related table.

In the next post we will learn about System Tables and Views in SQL Server.