Skip to main content

How to Create and Manage Indexes in SQL Server

Database developer is often required to improve the performance of queries. SQL Server allows implementing indexes to reduce the execution time of queries. In addition they can restrict the view of data to different users by implementing views.

The SQL Server also provides an in-built full-text search capability that allows fast searching of data. This article discusses how to create and manage indexes and views.

Creating and Managing Indexes

When a user queries data from a table based on conditions, the server scans all the data stored in the database table. With an increasing volume of data, the execution time for queries also increases. As a database developer, you need to ensure that the users are able to access data in the least possible time. SQL Server allows you to create indexes on tables to enable quick access to data. In addition, SQL Server allows you to create XML indexes for columns that store XML data.

At times, the table that you need to search contains voluminous data. In such cases, it is advisable to create partitioned indexes. A partitioned index makes the index more manageable and scale able as they store only data of a particular partition.

As a database developer, you need to create and manage indexes. Before creating an index, it is important to identify the different types of indexes.

Identifying the Types of Indexes

Before identifying the types of indexes, it is important to understand the need to implement an index.
The data in the database tables is stored in the form of data pages. Each data page is 8 KB in size. Therefore, data of the complete table is stored in multiple data pages. When a user queries a data value from the table, the query processor searches for the data value in all the data pages. When it finds the value, it returns the result set. With an increasing volume of data, this process of querying data takes time.

To reduce the data query time, the SQL Server allows you to important indexes on tables. An index is a data structure associated with a table that helps in fast search of data in the table. Indexes in the SQL Server are like the indexes at the back of a book that you can use to locate text in the book.

Benefits provided by using Indexes:


  • Accelerate queries that join tables, and perform sorting and grouping
  • Enforce uniqueness of rows, (if configured for that)

An index contains a collection of keys and pointers. Keys are values built from one or more columns in the table with which the key is associated. The column on which the key is built is the one on which the data is frequently searched. Pointers store the address of the storage location where a data page is stored in the memory, as depicted in the following figure.

How to Create and Manage Indexes in SQL Server

When the users query data with conditions based on the key columns, the query processor scans the indexes, retrieves the address of the data page where the required data is stored in the memory, and accesses the information. The query processor does not need to search for data in all the data pages. Therefore, the query execution time is reduced.

The keys in the indexes are stored in a B-Tree in the memory. A B-Tree is a data-indexing method that organizes the index into a multi-level set of nodes. Each page in an index B-Tree is called an index node. Each index contains a single root page at the top of the tree. This root page, or root node, branches out into n number of pages at each intermediate level until it reaches the bottom, or leaf level, of the index. The index tree is traversed by following pointers from the upper-level pages down through the lower-level pages.

The key values in the root page and the intermediate pages are sorted in the ascending order. Therefore, in the B-Tree structure, the set of nodes on which the server will search for data values is reduced. This enables the SQL Server to find the records associated with the key values quickly and efficiently. When you modify the data of an indexed column, the associated indexes are updated automatically.

Comments

Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video



Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:


      Suppose next node is allocated at an address 506, so the list becomes,



  Suppose next node is allocated with an address with an address 10,s the list become,


The other way to represent the linked list is as shown below:




 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …