SQL Server Database Objects

Personal Interview - Answers to frequently asked questions Tips to earn promotions and perks 8 things you should NOT do in a Group Discussion How to write a good CV? How to write a good Cover Letter? Tips to become boss's favorite Soft skills every professional must have

By Nishant Kumar

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.

NEXT>>

Interview Questions on SQL Server Replication includes following questions with answers

What is Replication? | What are the types of Replication? | Define the terms used in Replication. | Describe the replication agents that SQL Server supports. | Describe in brief working of Replication.

Question: Define data, entity, domain and referential integrity.

Data Integrity validates the data before getting stored in the columns of the table. SQL Server supports four type of data integrity.....

Question: Describe in brief exporting and importing utility?

The Bulk Copy is a command utility that transfer SQL data to or from a data file.
This utility mostly used to transfer huge data to SQL server from other database....

Question: What is index? Define its types.

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.........