Table Indexing In Sql Server 2008
TABLE INDEXING IN SQL SERVER 2008
A person has to work with huge amount of information every day. In order to work with the enormous information, we must have a system where we can store, manipulate and share the information. It is one of the core reasons for introducing DBMS as well as RDBMS. So, one thing is that we store and manipulate data / information into a database, where the database contains various types of tables for storing various data.
What is an Index:
A SQL table is not good enough for getting the desired data very quickly or sorting the data in a specific order. We consider a huge amount of data in a table; we need some sort of cross reference to get the data very quickly. This is where an Index within sql server comes in.
· Indexes allow the database application to find data fast, without reading the whole data.
· An Index is an on-disk structure associated with a table or views that speed retrieval of rows from the table. An index contains keys built from one or more columns in the table. These keys are stored in a structure (B-Tree) that enables SQL server to find the rows associated with the key values quickly and efficiently.
Why use an Index:
SQL Server indexes provide many facilities such as:
· Rapid access of information.
· Efficient access of information.
· Enforcement of uniqueness constraints.
Types of Indexes:
SQL Server has two major types of indexes:
Clustered
2 Non-Clustered
Clustered:
· An index defined as being clustered, defines the physical order that the data in a table is sorted. Only one cluster can be defined per table, because the data rows themselves can be sorted in only one order.
· The only time the data rows in a table stored in a sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called clustered table. If a table has no clustered index, its rows are stored in un ordered structure called a heap.
Non-Clustered:
· Non-clustered indexes have a structure separate from the rows. A non-clustered index contains a non-clustered index key values and each key value has a pointer to the data row that contains the key value.
· The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table.
· For a heap, a row locator is a pointer to the row. For a clustered, the row locator is the clustered index key.
A table can only have one Clustered index and up to 249 Non-Clustered indexes.
Creating an Index:
There are two ways of creating an index as
· Creating an Index by using SQL Server Table Designer.
· Creating an Index by using Transact-SQL.
Creating an Index using SQL Server Table Designer:
->SQL Server table designer is a feature to manage various tables designing creation, modification, indexing and much more. Follow the steps to create an index using SQL Table Designer
1 ->Open your SQL Server Enterprise management Studio and choose your database for creating a table index. Now select your desired table and open the table design window.
1 ->A window will appear where you will find all the available inputs for creating a table. You just need to input columns name, data type length and also may set the columns to NULL value.
-> Now you know how to create a table. Let us see how to set an index in a table. Select the column by clicking mouse button, a new pop-up menu will appear. Select indexes/keys for opening a new window where you can configure the columns for indexing.
->Now just select the column from the general properties as above and add the specify column short order of the index. You may add multiple columns for indexing by clicking Add button.
You can configure the index as clustered or non-clustered from the Create As Clustered Properties as above.
Creating an index using Transact-SQL:
If we want to create an index by using Transact-SQL must know the column details for index creation.
Create index syntax:
Duplicate values are allowed.
CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)
EXAMPLE:
CREATE INDEX Emp_Index ON Employee (Emp_Id)
Create Unique Index Syntax:
Duplicate values are not allowed.
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)
EXAMPLE:
CREATE UNIQUE INDEX Emp_Index ON Employee (Emp_Id)
DROP INDEX:
The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. If we want to delete an index in a table then the constraint must be dropped.
Syntax:
DROP INDEX TABLE_NAME.INDEX_NAME
EXAMPLE:
DROP INDEX Employee.Emp_Index
Creation of Clustered and Non-Clustered Indexes:
To explain these constraints we need a table. For instance, create a student table with three fields as Student Id, Student Name, and Class.
CREATE TABLE Student (Student_Id smallint, Student_Name varchar (30), Class tinyint)
Here there are no constraints at present in this student table. We will add constraints as follows.
Primary Key Constraint:
A table column with this constraint is called as the key column for the table. If we add the primary key constraint to any column then that column value is not repeated and also no NULL entries.
Now we will mark Student Id as primary key as follows
1 Right click the student table and click on the Modify button.
2 From the displayed Layout, select Student Id row and click on the small square button on the left side of the row.
3 Click on the Set Primary Key button to set the Student Id column as primary key.
Now the column Student Id does not allow NULL values and duplicate values
Clustered Index:
The primary key created for Student Id column will create a clustered index for the Student Id column. A table can have only one clustered index. When creating the clustered index, SQL Server reads the Student Id column and forms a Binary tree on it. This binary tree information is then stored separately in the disc.Expand the table Student and then expand the Indexes then you will see the index when the primary key is created.
With the use of the binary tree, the search for the student based on Student Id decreases the number of comparisons to a large amount of data.
Non-Clustered Index:
A non-clustered index is useful for columns that have some repeated values. For instance, Class column of a student database may have 5 thousand records but the distinct classes may be 1-5. A clustered index is automatically created when we create the primary key for the table. We need to take care of creation of non-clustered index.
Follow the steps to create Non-Clustered Index by using Table Designer:
1. In Object Explorer, expand the database that contains the table.
2. Expand the Tables folder.
3. Right click the table on which you want to create a non-clustered index and select Design.
4. On the Table Designer menu, click Indexes/keys.
5. In the Indexes/keys dialog box, click Add.
6. Select the new index in the Selected Primary/Unique Key or Index text box.
7. In the grid, select Create As Clustered, and choose No from the drop-down list to the right of the property.
8. Click Close.
Create a Non-Clustered Index by using Object Explorer:
1. In Object Explorer, expand the database that contains the table.
2. Expand the Tables folder.
3. Expand the table on which you want to create a non-clustered index.
4. Right click the Indexes folder and select New Index.
5. In New Index dialog box, on Generalpage enter the name of the new index in the Index Name box.
6. Under Index Key Columns click Add.
7. Make sure that Index Type is Non clustered.
8. Click OK.
Creating Non-Clustered Index by using Transact-SQL:SYNTAX:
CREATE NONCLUSTERED INDEX INDEX_NAME
ON TABLE_NAME (COLUMN_NAME)
EXAMPLE:
CREATE NONCLUSTERED INDEX Stud_Nonclus_Index
ON Student (Class)
DROP INDEX:
If we want to delete an index in a table then the constraint must be dropped.
Syntax:
DROP INDEX TABLE_NAME.INDEX_NAME
EXAMPLE:
DROP INDEX Student.Class
Disabling Indexes:
Database Administrators can use the disabling indexes feature to prevent the index usage by user queries. This feature is very useful to figure out whether the indexes which are available on a table are really useful or not. When you are disabling an index, the index definition remains in metadata and index statistics are also kept on non-clustered indexes.If you are disabling a clustered index on a table then the table won’t be available for user access. But the data will still remain in the table, but it will be unavailable for DML operation until the index is rebuilt or dropped.
Disabling index by using Transact-SQL:
SYNTAX:
ALTER INDEX INDEX_NAME ON TABLE_NAME .COLUMN_NAME DISABLE
EXAMPLE:
ALTER INDEX Stud_Nonclus_Index ON Student .Class DISABLE
Enabling Indexes:
Enable index by using alter index statement.
SYNTAX:
ALTER INDEX INDEX_NAME ON TABLE_NAME.COLUMN_NAME REBUILD
EXAMPLE:
ALTER INDEX Stud_Nonclus_Index ON Student.Class REBUILD
Article by chnagachandrika@gmail.com
Category: .NET, Programming
0 comments