Scan
Ø
When search definition can’t point close on
single row/ Range of rows to satisfy search predicates. It called as scan
Ø
SQL Server has to scan multiple pages to find
range of rows which satisfy search predicates.
Ø
Scan is good if we have to retrieves all the
rows from the table.
Ø
It reads every row in the table whether or not
it qualifies
Ø
Scan cost is proportional to the total number of
rows in the table
Ø
If Scan is table scan means table doesn’t have
index
Ø
If table is having very small no. of rows and
it’s not very frequently used, scan and seek will be almost same
Ø
When SQL Server does a scan it loads the
complete object which it wants to read from disk into memory, then reads
through that object from top to bottom looking for the records that it needs.
Ø
We never want to convert seek to scan but
if we just want it, then drop index
Ø
A scan means whole data irrespective of data we
want
Ø
Scan can be possible with index or table
Ø
Scan is of 3 types
o
clustered index scan
o
non
clustered index scan
o
table
scan
Ø
An Non Clustered index scan is a complete scan
of all the leaf pages in B tree to to find index key of cluster index
Ø
A clustered index scan is a complete scan of all
data pages at leaf in a clustered index
Ø
No clustered index scan means lot fewer pages
then in clustered index scan
Ø
Order of data doesn’t impact much
Ø
Fragmented data affects scan most as whole data
need to be read from disk
Ø
Scan is generally not good, some queries it will
use a table scan even when a useful index is present – this is usually because
the data in the table is so small that it is more hassle to traverse the
indexes (if this is the case you would expect the plan to change as the data
grows, assuming the selectivity measure of the index is good).
Ø
Table scan only appears for a heap like table
without a clustered index. The first page in the heap is located based on info
in the system tables, and then the pages are read one by one, using the next
and, if necessary, previous pointers in the page headers. This is generally an
expensive operation and should be avoided where ever possible
Ø
Clustered index scan is similar to table scan,
just on a table that has clustered index. This operation reads the leaf pages
of the clustered index, using the next and previous page pointers. Like with
the table scan, this can be an expensive operation and should, wherever
possible be avoided
Ø
Index scan means reading all the leaf pages of a
non-clustered index using the next and previous page pointers to navigate.
Because non-clustered indexes generally have fewer pages in the leaf than a
clustered index, this operation is usually cheaper than a clustered index scan
Ø
This sometimes comes with lookups (Row \
Bookmark) , a part of scanning , Another index is used to over result of scan
Ø
We can see scan is horizontal arrow
Seek
Ø
When index definition can point close on single
row/ Range of rows to satisfy search predicates. It called as Seek
Ø
SQL Server knows which page / Range of rows to
read
Ø
Seek is used if we need selected rows from the
table
Ø
It reads rows that qualify and pages that
contain these qualifying rows
Ø
The cost is proportional to the number of
qualifying rows and pages rather than to the total number of rows in the table.
Ø
Seek is always on index only
Ø
If table is having huge no. of records then seek
will give huge performance impact.
Ø
It knows where in the index data is going to be,
so goes directly to the part of the index that it needs and load to memory
Ø
A seek means that we are looking for specific
value(s) and the index provides us with the best way to do it
Ø
Seek is always with index , DB engine search
data in B tree
Ø
Seek is of two types
o
clustered index seek
o
non clustered index seek
Ø
An Non clustered index seek is a seek through
the B-tree structure of a non-clustered index to find index key of cluster
index leaf page, from the root down to the leaf
Ø
A clustered index seek is a seek through the
b-tree structure of a clustered index, from the root down to the leaf
Ø
Non clustered index seek means only pages required
for data address, where as in clustered index seek it only reads data pages.
Ø
Search is fast because data is stored in order
based on the clustered index key
Ø
Fragmented data affects but not as compare to
scan, as sql engine reed minimal required data.
Ø
seek is generally much better, but a great many
seeks (caused by bad query design with nasty correlated sub-queries for
instance, or because you are making many queries in a cursor operation or other
loop) can be worse than a scan, especially if your query may end up returning
data from most of the rows in the affected table.
Ø
Seek is not possible without index
Ø
Clustered index seek uses the clustered index’s
b-tree structure. The seek starts at the root of the tree and navigates down
the levels of the index until it reached the leaf page(s) with the desired
data. This operation also appears when a partial scan of the table is done,
when the index’s tree is used to locate a page, and the index is scanned from
that point until another point in the table (possibly the end).
Ø
Non-clustered index seek is the same as the
clustered index seek, just using a non-clustered index
Ø
In a seek not all of the index is considered.
Only one used index is enough
Ø
Seek is vertical arrow explain its nature of
work
