SQL Server - Database Objects

Define database objects.

Table
SQL Server database stores information in a two dimensional objects of rows and columns called table.

Data types
Data types specify the type of data that can be stored in a column.
Data types are used to apply data integrity to the column.
SQL Server supports many data type like character, varchar, integer, binary, decimal, money etc.
You can also create you own data type (User defined datatype) using system data type.

Function
Microsoft SQL server allows you to create functions.
These functions are known as User Defined Functions.
It represents business logic using one or more transact SQL statements.
It can accept parameter(s) and can return scalar data value or a table data type.
It can be used in the SQL statement which is added advantage over stored procedure.

Index
Index can be thought as index of the book that is used for fast retrieval of information.
Index uses one or more column index keys and pointers to the record, to locate record.
Index is used to speed up query performance.
Kind of the indexes are clustered and non-clustered. Both exist as B-tree structure.

Clustered index exists as sorted row on disk.
Clustered index re-orders the table record.
Clustered index contains record in the leaf level of the B-tree.
There can be only one Clustered index possible in a table.

Non-clustered is the index in which logical order doesn’t match with physical order of stored data on disk.
Non-clustered index contains index key to the table records in the leaf level.
There can be one or more Non-clustered indexes in a table.

Unique index is the index that is applied to any column of unique value.
A unique index can also be applied to a group of columns.

Constraint
Using Constraint, SQL Server enforces the integrity to the database.
It defines the rules that restrict unwanted data in the column.
Constraints can be table constraints or column constraints.

Primary Key Constraint
It is defined for one column or more columns that ensure data within these columns uniquely identify each row of the table. Primary key column doesn’t allow null value. It doesn’t allow duplicate data in the table.

Foreign Key Constraint
Foreign Key is to create link between two tables. It creates parent-child relationships. It ensures that data from parent can be deleted only when there is no corresponding data in the child.

Unique Key Constraint
Unique key constraint is also like primary key except it allows null value to the column with unique key constraints.

Check Key Constraint
It enforces domain integrity by restricting unwanted data to the column.

Rule
Rule is older version of check function. You can apply only one rule to the column. You should first create rule using ‘Create Rule’ statement and then bind the rule to the column using sp_bindrule system stored procedure.

Default
It ensures default value to the column if you do not specify a value to the column while inserting a row.

Stored Procedures
A stored procedure is a compiled set of Transact-SQL statements.
The business logic can be encapsulated using stored procedure.
It improves network traffic by running set of Transact-SQL statements at one go.

Trigger
A trigger is a special type of event driven stored procedure.
It gets initiated when Insert, Delete or Update event occurs.
It can be used to maintain referential integrity.
A trigger can call stored procedure.

View
View can be created to retrieve data from one or more tables.
Query used to create view can include other views of the database.
We can also access remote data using distributed query in a view.

Define database object.

Database object depicts the properties of a single SQL Server instance.