Docstoc

index

Document Sample
index Powered By Docstoc
					//Indexes

//Indexes are B-tree structure
Clustered -->leaf node contains actuale data (Frequently accessed ,used
for Between ,used in aggregate)
NON-Clustered--> leaf contains pointer to acual data(frequently
referenced in WHERE, JOIN, and ORDER BY clauses,Highly selective
coloumns)

Frequently updated columns and non-unique columns are usually
not a good choice for a clustered index key

can also add non key coloumns to Indexes   can add upto 16 columns



//dealring indexes
only 1 clustered key pr table but 249 non clustered on a table
when a primary key is created on a column->it creates and index

default is asecending in
you can order index according the most frequent order by going to be used

//alter indexes index_name on tablename DISABLE-->can be use to disable
the index
Disabling index will drop non clustered index but keep clustered index
but will not be available for use

recreating ->CREATE INDEX.. with (DROP_EXISTING= ON) or ALTER INDEX
REBUILD to enable the index back
Rebuilding an index can help defragment the data
It can be used to drop and create index again on same column

//Drop index
We can’t use DROP INDEX to remove indexes that result fromthe creation of
a PRIMARY KEY or UNIQUE
CONSTRAINT


//SORT_IN_TEMPDB--> can be used to index on tempDb and then reflect on
orginal this is when a table is heavily crowded with transaction

//if(enterprise edition) can use MAXDOP=#number -> this will allocate
particular limit on potential processor
create index .. with(MAXDOP=#)

create index .. WITH (ONLINE = ON)-> allowes to access index even while
computing

create nonclustered index on....INCLUDE(nonkey column)-->> can index non
key with key using include helps index in nonclustered way non key
                                            columns
//PAD_INDEX FILLFACTOR
create index/alter index....with (PAD_INDEX=on|FILLFACTOR=#)--> used so
that some space is left in the leaf node ,0 is default means fill
                                  as much as possible,leaving space help
in inserting since the tree will no be splti if not found



//Disabling index Locking using indexs

should be left to SQl server only in special cases should it be specified
by user
WITH ( ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF })


//LARGE INDEXES
should apply indexes sepretely on filegroup for better performance


//index on file group

using "create index name on table (columns) ON FILEGRUP NAME" -->index on
filegroup

if the index and patition are on the same filegroup they are said to be
alligned

unalligned can be used when two columns(same data type) of seperate table
joined together frequently then it is adv

//Indexing a Subset of Rows
Make index on some condition like between,IS, IS NOT, =,<>, >, <,
this can be used when you know only certain values are accessed
frequently and a full index willl not be useful
create index...
WHERE UnitPrice >= 150.00 AND UnitPrice <= 175.00


//Reducing Index Sizwe---FILTERED INDEX
create index...
WITH (DATA_COMPRESSION = PAGE)

Alter Index...on table..
REBUILD WITH (DATA_COMPRESSION = PAGE)

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:3/29/2013
language:English
pages:2