Indexes in sql server?

Question

When clustered and non-clustered index ( in SQL SERVER ) should be used ?


And what is correlated subquery ? Example please.


Thanks in advance.

Answer 1

As a rule of thumb, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases--such as an identity column, or some other column where the value is increasing--and is unique. In many cases, the primary key is the ideal column for a clustered index.

Non-clustered indexes are best for queries:

That return few rows (including just one row) and where the index has good selectivity (generally above 95%).
That retrieve small ranges of data (not large ranges). Clustered indexes perform better for large range queries.
Where both the WHERE clause and the ORDER BY clause are both specified for the same column in a query. This way, the non-clustered index pulls double duty. It helps to speed up accessing the records, and it also speeds up the sorting of the records (because the returned data is already sorted).
That use JOINs (although clustered indexes are better).
When the column or columns to be indexed are very wide. While wide indexes are never a good thing, if you have no choice, a non-clustered index will have overall less overhead than a clustered index on a wide index.

A correlated subquery is a SELECT statement nested inside another SQL statement, which contains a reference to one or more columns in the outer query

Example of correlated subquery:
select CompanyName, ContactName, Address,
City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in (
select top 2 InnerC.CustomerId
from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
on OD.OrderId = O.OrderID
join Northwind.dbo.Customers InnerC
on O.CustomerID = InnerC.CustomerId
Where Region = OuterC.Region
group by Region, InnerC.CustomerId
order by sum(UnitPrice * Quantity * (1-Discount)) desc
)
order by Region

Answer 2

Clustered indexes are good when you are doing range scans, e.g searching for people with ages between 35 and 59 or other kind of ranges. This is because clustered indexes are sorted and getting a specified range from sorted data is very inexpensive. However, you can only have one clustered index per table. Simply because you can't sort one table in two different dimensions at the same time.

Non-clustered indexes are good for searches that return less than 25% of the entire table. You can also have several non-clustered indexes per table. However, the performance of the non-clustered index depends strongly on how you arrange your predicates in the where clause and how if its also a composite non-clustered index.