Difference Betwixt Clustered Index Together With Not Clustered Index Inwards Sql Server - Database Interview Question

In SQL Server database at that topographic point are mainly ii types of  indexes, Clustered index, together with Non-Clustered index together with the departure betwixt Clustered together with Non Clustered index is real of import from SQL performance perspective. It is likewise ane of the most mutual SQL Interview question, similar to the departure betwixt truncate together with delete,  primary substitution or unique substitution or  correlated vs non-correlated subquery. For those, who are non aware of benefits of Index or why nosotros role an index inwards the database, they assist inwards making your SELECT query faster. H5N1 query alongside an index is sometimes 100 times faster than a query without an index, of class depending upon how large your tabular array is, but, yous must index on columns which are oftentimes used inwards WHERE clause of the SELECT query, or which forms a major standard for searching inwards the database. For instance inwards Employee database, EmployeeId or  EmployeeName are mutual atmospheric condition to honour an Employee inwards the database. 

As I said, at that topographic point tin forcefulness out live either clustered index or non clustered index inwards database, onetime is used to make upwards one's heed how information is physically stored inwards disk together with that's why at that topographic point tin forcefulness out live alone ane clustered index on whatsoever table. 

In this article, nosotros volition explore to a greater extent than well-nigh both of this indexes together with acquire some substitution difference betwixt clustered together with non clustered index from interview together with performance perspective. 

2 words on Indexes inwards SQL

SELECT query, or if role index thence which one. You tin forcefulness out fifty-fifty see, which index is used for executing your query yesteryear looking at query plan, a FULL TABLE SCAN agency no index is used together with every row of tabular array is scanned yesteryear database to honour data, on the other manus INDEX UNIQUE SCAN or INDEX RANGE SCAN propose role of Index  for finding data. By the Index likewise has at that topographic point ain disadvantage every bit they brand INSERT together with UPDATE query slower together with they likewise demand space. H5N1 careful role of index is the best way to go. 

Clustered vs Non Clustered Index inwards SQL

Now nosotros accept some thought well-nigh what is Index inwards database together with how they work, it's fourth dimension to expect some substitution differences betwixt clustered together with non clustered index inwards SQL Server, which is generally truthful for other database every bit good e.g. Oracle or MySQL.

1) One of the primary departure betwixt clustered together with non clustered index inwards SQL Server is that, one tabular array tin forcefulness out alone accept ane clustered Index but It tin forcefulness out accept many non clustered index, roughly 250. This limitation comes from the fact clustered index is used to determines how information is stored physically inwards table. You should live real careful spell choosing clustered index together with should role columns which tin forcefulness out live queried inwards make e.g. select * from Employee where EMP_ID > twenty together with EMP_ID < 50. Since clustered index stores information inwards cluster, related information are stored together together with it's like shooting fish in a barrel for database to recollect all information inwards ane shot. This farther reduces lots of disk IO which is real expensive operation. Clustered Index is likewise real skilful on finding unique values inwards a tabular array e.g. queries similar select * from Employee where EMP_ID=40; tin forcefulness out live real fast if EMP_ID has clustered index  on it.

2) Another substitution departure betwixt Clustered Index together with Non Clustered Index inwards database is that many relational database including SQL Server  yesteryear default creates clustered index on PRIMARY KEY constraint, if at that topographic point is no clustered index exists inwards database together with a nonclustered index is non specified spell declaring PRIMARY KEY constraint.

3) One to a greater extent than departure betwixt them is that, clustered index contains information i..e rows inwards at that topographic point leafage node, every bit Index is represented every bit BST, spell nonclustered index contains pointer to information (address or rows) inwards at that topographic point leafage node, which agency ane to a greater extent than extra measuring to acquire the data. 

4) By the way at that topographic point is a misconception that nosotros tin forcefulness out alone define clustered index alongside ane column, which is non true. You tin forcefulness out make clustered index alongside multiple columns, known every bit composite index. For instance inwards Employee table, a composite index on firstname and lastname can live a skilful clustered index, because most of the query uses this every bit criterion. Though yous should effort to minimize pose out of columns inwards clustered index for amend performance inwards SQL Server. On related not, spell declaring composite index, pay some attending to the companionship of columns inwards index, that tin forcefulness out make upwards one's heed which disceptation volition role index together with which volition not. In fact this is ane of the generally asked query as, does companionship of columns inwards composite index matters. 

Last but non the least, pay some attending spell creating clustered  together with non clustered index inwards database. Create clustered index for columns which contains unique values, are accessed sequentially, used inwards make queries together with render large final result set. Avoid creating clustered index on columns, which are update oftentimes because that would Pb rearrangement of rows on disk level, a potentially tiresome operation. 

That's all on difference betwixt clustered together with nonclustered index inwards SQL Server database. Remember that, it's possible to make clustered index on non PRIMARY KEY column together with PRIMARY KEY constraint only  creates a clustered index, if at that topographic point is non already inwards database together with a nonclustered index is non provided. Key departure is that, clustered index decides physical sorting or companionship of information inwards disk.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post