1. What are indexes?
To speed up data retrieval indexes are used. Indexes also enforce the uniqueness of rows.
Advantages
1. Improves the speed of execution.
2. Enforces uniqueness of data.
3. Speeds up joins between tables.
Disadvantages
1.Takes disk space to store.
2.Data modification takes longer.
3.Takes time to create index.
Types of indexes
Clustered index
1.The data is physically sorted
2.One clustered index can be created per table, so you should build it on attributes that have a high percentage of unique values and that are not modified often.
Nonclustered index
1.The physical order of the rows is not the same as the index order.
2.There can be as many as 249 nonclustered indexes per column.
2.What are views?
A view is a virtual table, which gives access to a subset of columns from one or more tables. It is a query stored as an object in the database. Hence a view is an object that derives it data from one or more tables.
Advantages
1.A view serves as a security mechanism.
2.A view simplifies the usage of complex queries.
3. What are store procedures and its advantages?
A stored procedure is collection or batch of Transact-SQL statements and control flow language that is stored under one name, and executed as single unit. It helps in improving the performance of a query. It is a precompiled object. As it is ready to execute no time is needed for parsing and compiling the procedure.
Advantages
Improved performance – Applications do not have to compile the procedure over and over again.
Reduction in network congestion – applications need not submit multiple SQL statements to server for the purpose of processing.
Enhanced accuracy – SQL statements included in a procedure are designed by experienced programmers and are therefore more efficient, error free, and tested.
Better security mechanism – users can be granted permission to execute a stored procedure even if they do not own it.
Types of stored procedures
1.User-defined
2.System defined
These are prefixed with sp_. These are for administrative purpose and are stored in the database and are accessible to all users.
3.Temporary
These are prefixed with #, stored in tempdb and are automatically dropped when connection terminates.
4.Remote
These are created and stored in remote servers and can accessed by users with appropriate permissions.
5.Extended
These are dlls that are executed outside SQL Server. They are prefixed by xp_.
Posts
