-

Indexing in SQL

Let us understand how normal (without indexing) query runs. It basically traverse each rows one by one and when it finds the data it returns.
Without Index:
Without indexingSo suppose query is to find 50 , it will have to read 49 records as a linear search.
With Index:
enter image description here
When we apply indexing, the query will quickly find out the data without reading each one of them just by eliminating half of the data in each traversal like a binary search. 

Comments

Post a Comment

Popular posts from this blog

-
Image
General purpose queries: Query 1: Retrieve List of All Database   EXEC  sp_helpdb     Query 2: Display Text of Stored Procedure, Trigger, View   exec  sp_helptext @objname =  'Object_Name'      Query 3: Get All Stored Procedure Relate To Database   SELECT   DISTINCT  o. name , o.xtype      FROM  syscomments c      INNER   JOIN  sysobjects o  ON  c.id=o.id      WHERE  o.xtype= 'P'      To retrieve the View use “V” instead of “P” and for functions use “FN.   Query 4: Get All Stored Procedure Relate To Table   SELECT   DISTINCT  o. name , o.xtype      FROM  syscomments c      INNER   JOIN  sysobjects o  ON  c.id=o.id ...
Read more
-
Image
What is DMBS? A database management system (DBMS) is system software for creating and managing databases in the form of tables . The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data .
Read more
-
DDL OPERATIONS: The various DDL commands are CREATE, ALTER and DROP.   Let us learn about each DDL Command one by one with examples. CREATE Statement:   The CREATE statement in SQL is used to create the database and the database objects like Tables, Views, Sequences, etc. Let us see how a database and table are created in SQL. Creating a database in SQL:   Syntax: CREATE DATABASE DatabaseName; Example: CREATE   DATABASE  Demos;     On executing this query, a database named "Demos" is created.   Creating Table in SQL:   Database is a collection of related tables and other objects. This implies that a table is a part of the database. Now, we have created the "Demos" database. And, we need to create a "Students" table in this database. By default, the master database is selected. Select Demos database from the dropdown list or alternatively you can use the following command: Use Demos;   This ...
Read more