-

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
CLUSTERED INDEX VS NON CLUSTERED INDEX Clustered Index Only one clustered index can be there in a table because of the sorted order Sort the records and store them physically according to the order Data retrieval is faster than non-clustered indexes Do not need extra space to store logical structure A primary key by default creates a clustered index on it. Non Clustered Index There can be any number of non-clustered indexes in a table because of the number of unique keys we can create. Do not affect the physical order. Create a logical order for data rows and use pointers to physical data files Data insertion/update is faster than clustered index Use extra space to store logical structure A unique key/keys by dafault created an unclustered index on it. In Depth Explanation: Clustered Technically speaking, a clustered index is a B-Tree data structure where all the rows in a table are stored at the leaf level of the index. In other words, a clustered ind...
Read more
-
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
ANSI_NULL and QUOTED_IDENTIFIER: ANSI_NULL: When ever we are creat ing   or altering th e  SQL objects like the Stored Procedure s   and the User Defined Functions at t h at time   most of us use this.  Let us  understand this better with an example let us create a table and insert some records, CREATE   TABLE  #Demo ( FName  VARCHAR ( 200 ), LName  Varchar ( 200 ) ) Insert  #Demo  values ( 'Ravi' , 'Shekhar' ) Insert  #Demo  values ( 'Isha' ,NULL ) Insert  #Demo  values ( 'Santosh' , 'Thakur' )   SQL Server tend s to  behave differently when ever we use either  the  SET ANSI_NULL ON or the OFF . SET ANSI_NULL ON; If the ANSI_NULL is on then the comparison with NULL value with = or <> returns false. SET   ANSI_NULLS   ON SELECT   *   FROM  #Demo  WHERE  LName  =   NULL SELECT   *   FROM  #De...
Read more