INDEX
SCAN VS SEEK:
--------------------------------
Index
Scan: Since a scan touches every row in the table
whether or not it qualifies, the cost is proportional to the total
number of rows in the table. Thus, a scan is an efficient strategy if
the table is small or if most of the rows qualify for the predicate.
Index Seek:
Since a seek only touches 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.
A table with no index is forced to do
table scan. Note that for 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
.
Index seeks are generally preferred for
the highly selective queries. What that means is that the query is
just requesting a fewer number of rows or just retrieving the other
10 (some documents says 15 percent) of the rows of the table.
Table scan means iterate over all table
rows.
Index scan means iterate over all index
items, when item index meets search condition, table row is retrieved
through index.
Usually index scan is less expensive
than a table scan because index is more flat than a table.
Clustered Indexes: With a clustered
index the table data is stored in the leaf nodes of that index
instead of being in a separate heap structure. This means that there
will never need to be any extra row lookups after finding rows using
that index no matter what columns are needed [unless you have
off-page data like TEXT columns or VARCHAR(MAX) columns containing
long data]. You can only have one clustered index for this reason, so
if you use one chose where you put it carefully in
order to get maximum gain.
table scan/index
scan/index seek possibilities
-----------------------------------------------------------
· A table with no index will
do table scan.
· A table with only clustered
index will do index scan when no search criteria used.
· A table with only clustered
index will do index seek when clustered index column used in search
criteria.
· A table with only clustered
index will do index scan when nonindexed column used in search
criteria.
· A table with only
nonclustered index will do table scan when nonindexed column used in
search criteria.
· A table with only
nonclusterd index will do index scan/index seek depending on volume
of table when indexed column used in search criteria.
· A table with clustered and
non clustered index will do index scan or seek depending on the
search column and volume of table in where clause.
INDEX TYPES
================
CREATE [ UNIQUE ] [ CLUSTERED |
NONCLUSTERED ] INDEX index_name
ON
[ INCLUDE ( column_name [ ,...n ] )
]
[ WHERE ]
[ WITH (
[ ,...n ] ) ]
[ ON { partition_scheme_name (
column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON {
filestream_filegroup_name | partition_scheme_name | "NULL"
} ]
[ ; ]
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF
}
| DROP_EXISTING = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW |
PAGE}
[ ON PARTITIONS ( {
| }
[ , ...n ] ) ]
}
By default a nonclustered index is
created if not specified.
UNIQUE :
-----------
Creates a unique index on a table or
view. A unique index is one in which no two rows are permitted to
have the same index key value. A clustered index on a view must be
unique.
The Database Engine does not allow
creating a unique index on columns that already include duplicate
values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried,
the Database Engine displays an error message. Duplicate values must
be removed before a unique index can be created on the column or
columns. Columns that are used in a unique index should be set to NOT
NULL, because multiple null values are considered duplicates when a
unique index is created.
CLUSTERED :
-------------
Creates an index in which the logical
order of the key values determines the physical order of the
corresponding rows in a table. The bottom, or leaf, level of the
clustered index contains the actual data rows of the table. A table
or view is allowed one clustered index at a time.
A view with a unique clustered index is
called an indexed view. Creating a unique clustered index on a view
physically materializes the view. A unique clustered index must be
created on a view before any other indexes can be defined on the same
view.
Create the clustered index before
creating any nonclustered indexes. Existing nonclustered indexes on
tables are rebuilt when a clustered index is created.
NONCLUSTERED :
------------------
Creates an index that specifies the
logical ordering of a table. With a nonclustered index, the physical
order of the data rows is independent of their indexed order.
The bottom,or leaf, level of the
Nonclustered index contains the pointer address of actual data rows
of the table
WHERE :
-----------------------------
Creates a filtered index by specifying
which rows to include in the index. The filtered index must be a
nonclustered index on a table. Creates filtered statistics for the
data rows in the filtered index.
PAD_INDEX = { ON | OFF } :
------------------------
ON : The percentage of free space that
is specified by fillfactor is applied to the intermediate-level pages
of the index.
OFF or fillfactor is not specified :
The intermediate-level pages are filled to near capacity, leaving
sufficient space for at least one row of the maximum size the index
can have.
FILLFACTOR =fillfactor :
------------------------
Specifies a percentage that indicates
how full the Database Engine should make the leaf level of each index
page during index creation or rebuild. fillfactor must be an integer
value from 1 to 100. If fillfactor is 100, the Database Engine
creates indexes with leaf pages filled to capacity.
DROP_EXISTING = { ON | OFF } :
------------------------------
Specifies that the named, preexisting
clustered, or nonclustered is dropped and rebuilt. The default is
OFF.
ONLINE = { ON | OFF } :
-------------------------
Specifies whether underlying tables and
associated indexes are available for queries and data modification
during the index operation. The default is OFF.
KEYS:
=====
Super Key :
------------
Super key is a set of one or more than
one keys that can be used to identify a record uniquely in a
table.Example : Primary key, Unique key, Alternate key are subset of
Super Keys.
Candidate Key :
---------------
A Candidate Key is a set of one or more
fields/columns that can identify a record uniquely in a table. There
can be multiple Candidate Keys in one table. Each Candidate Key can
work as Primary Key.
Primary Key :
---------------
Primary key is a set of one or more
fields/columns of a table that uniquely identify a record in database
table. It can not accept null, duplicate values. Only one Candidate
Key can be Primary Key.
Alternate key :
----------------
A Alternate key is a key that can be
work as a primary key. Basically it is a candidate key that currently
is not primary key.
Composite/Compound Key :
-----------------------
Composite Key is a combination of more
than one fields/columns of a table. It can be a Candidate key,
Primary key.
Unique Key :
---------------
Uniquekey is a set of one or more
fields/columns of a table that uniquely identify a record in database
table. It is like Primary key but it can accept only one null value
and it can not have duplicate values. For more help refer the article
Foreign Key :
-------------
Foreign Key is a field in database
table that is Primary key in another table. It can accept multiple
null, duplicate values. For more help refer the article Difference
between primary key and foreign key.
Natural key :
--------------
A natural key is a single column or set
of columns that uniquely identifies a single record in a table, where
the key columns are made up of real data. “real data” means data
that has meaning and occurs naturally in the world of data. A
natural key is a column value that has a relationship with the rest
of the column values in a given data record. Here are some examples
of natural keys values: Social Security Number, ISBN, and TaxId.
surrogate key :
----------------
A surrogate key like a natural key is a
column that uniquely identifies a single record in a table. But this
is where the similarity stops. Surrogate keys are similar to
surrogate mothers. They are keys that don’t have a natural
relationship with the rest of the columns in a table. The surrogate
key is just a value that is generated and then stored with the rest
of the columns in a record. The key value is typically generated at
run time right before the record is inserted into a table. It is
sometimes also referred to as a dumb key, because there is no meaning
associated with the value. Surrogate keys are commonly a numeric
number.
Identity Key :
---------------
The IDENTITY columns are auto
incrementing columns provided by SQL Server.
There can only be one IDENTITY column
per table.
SQL Server will take care of
incrementing this column automatically.
No comments:
Post a Comment