SQL SERVER - Types of Database Objects

Today, we will see various Database Objects that are available with in SQL Server 2005. In general, Database Object is a piece of information or record that is stored in a database.

Different Types of Database Objects:

1. Table : Table is nothing but a collections rows and columns. The rows can also be called Records while Columns can also be called as fields.

2. View : View is a logical way of looking at the physical data which is located in the table.Here Only the definition of a view is stored in the database but not the actual data, like a table. Further,View stores the Select statement in a complied format to simplify the retrieval and modification of data.

3. Procedure : Procedure is An executable database object which can be used to move code,that enforces business rules , from Application to Database.

4. User Defined Function : User Defined Functions in SQL Server are nothing but the extension to the Microsoft Defined System Functions, Where user can include his own Business logic to get the required results.

5. Trigger : Trigger is one type of stored procedure which will be executed automatically whenever a data manipulation Command (Insert\Update\Delete) is executed.

6. Check Constraint : Check Constraint is a Domain Or Pool of values from which the column of a table can draw its values. That means, this Constraint limits the Number of values that we can store for a column of a table.

7. Rule : Rule is same that of the Check Constraint which enforces the Domain integrity. One additional feature of Rule is, it will enforce the Domain integrity not only on the columns of a table but also on User defined data type.

8. Default Constraint : Default Constraint is that which can assign a Default value to a column either with the CREATE TABLE or ALTER TABLE statements.

9. PRIMARY KEY constraint : In general, A table consists of one column or a combination of columns whose values can be uniquely identified each row in the table. which is called the Primary Key of the table. This Primary key enforces the entity integrity of the table. We can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table which enforces data uniqueness.

10. FOREIGN KEY constraint : A FOREIGN KEY constraint is that which is linked to a PRIMARY KEY constraint in another table

11. UNIQUE constraint : UNIQUE constraints ensure that no duplicate values are entered in specific columns that do not participate in a primary key

12. Synonym : synonyms allows us to give an alias to an already existing object in the database.

In addition to the above Database objects, we have some other Database objects, which can be used less frequently.

Aggregate function (CLR)
Assembly (CLR) scalar-function
Assembly (CLR) table-valued function
Assembly (CLR) stored-procedure
System base table
Internal table
Plan guide
Replication-filter-procedure
Extended stored procedure
Service queue
Assembly (CLR) DML trigger
Table-valued-function Log