Applying Index, View, As Well As Full – Text Search Part – 1

Introduction

As a database designer it is regularly needed to upgrade the query performance in the database. SQL Server permits an individual to apply Index to decrease the run time of the quires. Additionally, an individual can control the sight of information to various employers or end – users by means of employing View. The SQL Server offers a built in Full – Text searching ability which in turns permits quick information searching capability.

This article deliberates exactly how to form as well as handle the indexes plus views. Moreover, it discusses which method should be castoff to apply a full – text search in the direction of refining the information search. From this article, an individual will acquire knowledge about how to build as well as handle the index, view plus full – text search.

Building Plus Handling Index

Whenever, any employee or end – user enquiries information from a table or relation built on conditions, the SQL Server analyses every single records deposited in the particular database table or relation. Due to a growing size of information, the run time for the enquiries upsurges too. Begin a database designer; an individual must guarantee that the employees or end – users are capable of getting the information within the minimum time. SQL Server permits, an individual to form indexes on the tables or relations to allow fast and prompt access to the information. Moreover, SQL Server lets an individual to form Extensible Markup Language (XML) indexes for columns or attributes which has Extensible Markup Language (XML) information stored in it.

From time to time, the table or relation that an individual wants to search has huge information. For this type of circumstances, it is desirable to build segregated index (Partitioned Index). The partition index marks the index further controllable as well as robust as it stocks information of a specific partition only. As a database designer, an individual should build plus handle indexes. It is significant to recognize the diverse sorts of indexes, in advance of building an index.

Recognizing Different Sorts Of Index

Earlier to classifying the different sorts of indexes, it is vital to know why the indexes are required to be applied.

The records in the database tables or relations are kept in Data Pages format. The size of every single Data Page is 8 KB (Kilobytes). For that reason, records of the complete table or relation are kept in numerous Data Pages. Whenever, any employee or end – user enquiries a record value from the table or relation, the query processor hunts for that particular record value in every single Data Pages. As and when it discovers the record value, it yields back the result set. Due to a growing size of information, this procedure of enquiring information consumes time. In the direction of, decreasing the information query time, the SQL Server permits an individual to apply indexes on the tables or relations. The index is an information arrangement connected with a table or relation which assistances in quick search of information in the table or relation. The indexes in the SQL Server are as same as the indexes at the end of a book which an individual may practice to trace text within the book.

The index offers the subsequent assistances:

· It speed up the enquiries which link tables or relations, plus does categorization besides grouping

· It implements exclusivity of rows or tuples.

The index is comprised of Keys plus Pointers. The keys are the values constructed from one or several columns or attributes within the table or relation through which the key is related. The column or attribute on which the key is constructed is the one on which the information is often hunted. The pointers have the address of the storing position where a Data Page is kept in the memory, as showed in the subsequent image:-

Whenever, any employee or end – user enquiry information with situations grounded on the key column or attributes (Primary Key), the query processor analyses the index and recovers the address of the Data Page where the needed information is kept in the memory, as well as gets the data. Now, every single Data Page is not needed to be searched by the query processor. Consequently, the query run time is condensed.

The keys in the indexes are kept in a Binary Tree format (B – Tree) within the memory location. A Binary Tree (B- Tree) is the information – directory which systematizes the indexes into several – levels of nodes. Every single Data Page in an index Binary Tree (B – Tree) is termed as an Index Node. Respectively every single index has a sole Origin or Root Page at the topmost of the Binary Tree (B – Tree). This Origin or Root Page / Node, outlets into numerous (N) number of pages at every single in – between levels until and unless it touches the bottommost or last level of the index. The index’s Binary Tree (B – Tree) is navigated by means of tailing the pointers starting from the top – level pages trailing through the lowest – level pages.

The key values in the Origin or Root page as well as the in – between pages are arranged in the ascending format. Hence, in the Binary Tree (B – Tree) configuration, the group of nodes on which the database server will hunt for data values is condensed. This permits the SQL Server to discover the information related with the key values fast as well as proficiently. As soon as an individual alter the information of an indexed column or attribute, the accompanying indexes are brought up to date spontaneously.

The SQL Server permits an individual to form the subsequent kinds of indexes:

· Clustered Index, and

· Non – Clustered Index

In the upcoming part we will be discussing about What Is Clustered Index? & What Is Non – Clustered Index? in details.