SQL Server - Database Objects

          
Interview questions

SQL Server Database Objects

NEXT>>

Table
Data types
Function
Index
Constraint
Rule
Default
Stored Procedures
Trigger
View

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.

Sql server - Define database object - Feb 11, 2010 at 11:55 AM by Shuchi Gauri

Define database object.

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

NEXT>>

Write your comment - Share Knowledge and Experience

Discussion Board
COmment

Careerride.com it is great achievement to upload such a material which help the student and as well as good information for interview

Fazli Bari 02-20-2014 11:15 PM

 

How to find tables without Indexes?

Tables without indexes can be found using the OBJECTPROPERTY method.......... 

SQL Profiler

What are the tasks performed by SQL Profiler?
How can you use the SQL Profiler to ensure database security?
How can you reduce the amount of data collected by a trace?
What is SQL Profiler?
When do you use SQL Profiler?............

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

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

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

 

Latest placement tests
Latest links
 
 
Home | Want a Job? Submit Key Skills | Employer login | My key skills | About us | Sitemap | Contact us