Sql Server: Understanding on indexes

Understanding on indexes

Indexes are created to provide faster data access during specific data fetch and retrieval form huge data tables.

There are various types of indexes in sql server

a.    Clustered Index

b.    Non clustered index

c.    Unique

d.    Filtered

e.    Spatial

f.     Xml

g.    Full text

h.    Index with computed column

i.     Index with non computed column

Mainly we will discuss here about Clustered index and non clustered index

Before going through understanding about indexes, let’s focus on the questions that one faces.

a.    What is index? Why do we needindexes?

b.    What are the various types ofindexes in sql server?

c.    How will you check if indexesalready exist on a table?

d.    What will happen in case of noindexes?

e.    What is a clustered index?

f.     How many clustered indexes canbe there for one table?

g.    Can you tell some example onclustered index that relates to real life experience?

h.    When you declare primary keyconstraint, then which index is created on that column?

i.     What is non clustered index?

j.     How many non clustered indexescan be created on one table?

k.    Can you tell me some example onnon clustered index that relates to real life experience?

l.     How non-clustered is differentfrom clustered indexes?

m.  What is unique index?

n.    What are the advantages anddisadvantages of index?

o.    How does indexing work?

p.    What does a table with no index called?

q.    Which index are faster clustered or non-clustered?

r.    When should we use clustered index?

s.    When should we use non clustered index?

Lookup vs Join

Lookup transformation in ssis vs. join in T-sql

Lookup transformation in SSIS and joins in sql server, both are used to reference some table or referece set. Still both have adavantages over each other.

Lookup	T-sql join
If the reference dataset is small, then use lookup file. As the whole file is loaded into main memory at the time of processing. So it can easily search and get back the matched record quickly than getting from the local disks.	If the reference datasets are big enough to cause trouble, use a join. This can involve I/O if the data is big enough, but the I/O is all highly optimized and sequential. Join processing is very fast and never involves paging or other I/O.
SSIS can well handle the source dataset coming from a heterogeneous database like text file, DB2 etc. and can reference the tables via lookup.	Its complex to implement through join in sql server
	TSQL can cope with more complicated business logic especially in the case of dependency between two datasets.
SSIS buffer cannot hold all dimension data (>1M rows), and can the whole package can stuck for some time at lookup.	It can handle as many data as required.
Blocking(or say “asynchronous”) components used like “Sort”, “aggregation
Error out No-Match rows – we can use this facility of lookup transformation, and use the rows which have no matching key in the reference table, we can redirect the no match rows to different destination for further analysis	N/A
via lookup in SSIS solution, we can easily configure the DB connections to solve this problem, which makes the design more loose-coupled and flexible to the changes like DB name changing or even DB migrate to the other server.	If using JOIN, what you have to do is to hard code the DB name or say to use corss-DB join.
Use if you have got SSIS developerJ	Use join when customer requests to wrap the business logic outside of SSIS and put more logics close to Database layer as much as possible.