Everything about database,how to,example,guide,

8.2.1 founds index with command of CREATE INDEX
The bunch that CREATE INDEX can found the content of a changeable watch to straighten foreword already indexes,Also can found the index is not bunch that improves inquiry performance.Its grammar is as follows:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX Index_name ON {table | View} Column [ASC | DESC] [, . . .n] )
[WITH
[PAD_INDEX]
[[, ] FILLFACTOR = Fillfactor]
[[, ] IGNORE_DUP_KEY]
[[, ] DROP_EXISTING]
[[, ] STATISTICS_NORECOMPUTE]
[[, ] SORT_IN_TEMPDB]
] SORT_IN_TEMPDB]
[ON Filegroup]
Each parameter explains as follows:

UNIQUE
Found an only to index,The key value that indexes namely does not repeat.Include in the row when repeating a value,Cannot build only to index.If want to use this option,The row that should index place is included certainly all does not allow NULL to be worth,Often can make mistake when use otherwise.CLUSTERED
The index that demonstrate founds indexes to form a cluster.If this option default,The index that founds indexes to be not bunch.NONCLUSTERED
The index that demonstrate founds indexes to be not bunch its index.The index that the actual watch data page in pointing to a database included in data page.Index_name
The name that designates the index that founds.Only should be in indexing the name is expressed in,But can repeat in same database or different database.Table
The name that designates the watch that founds index.Still answer when necessary demonstrate database name and possessory name.View
The name that designates the view that founds index.View must be to use what SCHEMABINDING option has defined,Its are specific information is referred to please " view is founded " paragraphic.ASC | DESC
The sort means that appoints specific index line.Acquiescent value is ascend (ASC) .Column
Assign the rank that is indexed.If use two or the row composition of two above indexes,Call composite index.16 can be appointed at most in an index row,But the length of the data type of the row and cannot exceed 900 byte.PAD_INDEX
The linage of the in-house node that appoints fill index,Should be more than at least be equal to two.PAD_INDEX option just acts well after FILLFACTOR option is appointed only.Because PAD_INDEX uses as same as FILLFACTOR percentage.When default,SQL Server ensures every index page has the free space that can accommodate linage of a the oldest index to occupy at least.If FILLFACTOR is appointed per cent accommodates S of a data not quite,QL Server will be automatic interior changes percentage.FILLFACTOR = Fillfactor
FILLFACTOR calls fill factor,It is appointed when founding index,Every per cent that indexes the data of the page constitutes index page bulk,The value of Fillfactor arrives for 1 100.The per cent that it pointed out to the free space that index page withholds constitutes index page bulk at the same time actually.Namely 100 - Fillfactor.The watch that undertake mass data is inserted often to those or deletes should be the space with the index data larger obligate that generates in the future when constructing index,Be about to Fillfactor is set lesserly,Otherwise,Index meeting factor occupies the page insert and very fast cram,Produce minute of page,And the expense that divides a page to be able to add a system greatly.But if be set too smally,Can waste much disk space again,Reduce inquiry performance.Because of this,The Fillfactor that sets to be 10 about normally to this kind of watch.And do not change to data, tall concurrent, read-only watch,Fillfactor can set 95 above and even 100.

If did not assign this option,SQL Server is acquiescent its value is 0.0 is a special value,With other small FILLFACTOR
Value (be like:1,2) the meaning is different,Page of its leaf node by complete cram,And there still are a few spaces in index page.Can
The FILLFACTOR that will change acquiesce with memory process Sp_configure is worth.IGNORE_DUP_KEY
This option was controlled should go to the reaction that place of the SQL Server when repeating data inserts to make in including the line in an only tie.When choosing this option,SQL Server returns a wrong message,Jump over what this linage occupies to insert,Continue to carry out the insertion below the operation of data:When choosing this option,SQL Server can return a wrong message not only,Still can answer boil (Rolls Back) statement of whole INSERT (boil about answering,Refer to please " the database is updated " paragraphic medium " general affairs " ) .DROP_EXISTING
Appoint should be deleted and found bunch to index afresh.Delete bunch index to be able to bring about all index that be not bunch to be rebuilt,Because need to replace bunch with travel guide,index bolts.If rebuild again,bunch indexes,So the index that be not bunch can rebuild again again,So that use bunch to index key will replace an index.Option of use DROP_EXISTING can make the index that be not bunch rebuilds only.STATISTICS_NORECOMPUTE
Appoint distributinging statistic to be updated not automatically.Need hand is moved carry out the UPDATESTATISTICS that does not carry NORECOMPUTE clause to command.SORT_IN_TEMPDB
Appoint the classified sort result that is used at founding index to will be gone to by memory in Tempdb database.If Tempdb database and user database are located in different disk device,go up,So use this one option to be able to reduce the time that founds index,But it can add the disk space that founds index place to need.ON Filegroup
Appoint the file team that deposits index.Notice:The row that the row that data type is TEXT, NTEXT, IMAGE or BIT cannot regard index as.
Cannot exceed 900 byte as a result of the width of index,The line of the row that is CHAR, VARCHAR, BINARY and VARBINARY because of this data type is broad degree exceeded 900 byte,Or the row that index also cannot serve as when data type was the row width of the row of NCHAR, NVARCHAR to exceed 450 byte.

When use index founds guide to found index,Cannot include computational line in index,But when be founded directly or using CREATE INDEX command to found index,Can found index to computer line,This is unallowed in the version before SQL Server2000,Calculate going up is improve greatly.
Exemple 8-1:Index to express Products to found a bunch.Create Unique Clustered Index Pk_p_idOn Products(p_id)WithPad_index, Fillfactor = 10, Ignore_dup_key, Drop_existing, Statistics_norecomputeOn [primary]
Exemple 8-2 founds a composite index to express ProductsCreate Index Pk_p_mainOn Products(p_id, p_name, sumvalue)- - among them Sumvalue is a computation lists expression to be Price*quantityWithPad_index, Fillfactor = 50On [primary]
Exemple 8-3 founds a view and construct an index for itCreate View Dbo.work_yearsWithSchemabindingAsSelect Top 100 Percent Emp_id, e_name, birthday, hire_date, year(getdate() )
- Year(hire_date) As Work_yearsFrom Dbo.employeeOrder By Work_years DescCreate Unique ClusteredIndex Emp_id_view On Dbo.work_years (emp_id)

8.2.2 founds index with Enterprise Manager.
Index is founded to have two kinds of methods in Enterprise Manager
1 founds guide to found index with index
Found a method to be as follows:
(1) the expresses a place database that chooses to want to found index in catalog tree,Click the guide page in task board
" Wizards " ,The choice guide interface that if place of graph 8-2 is shown,appears.

Graph 8-2 chooses index to found guide

(2) in clicking task board " Create An Index " link,The index that if place of graph 8-3 is shown,appears founds guide interface.OK also from " Tools " choose in menu " Izards " dish is individual,The dialog box of guide of choice SQL Server that if place of graph 5-17 is shown,appears,Choose from inside tree catalog again " Database " below " Create Index Wizard " option,The index that if place of graph 8-3 is shown,also can appear founds guide interface.

(3) click " next " pushbutton,The interface that if place of graph 8-4 is shown,appears,The watch that chooses to want to found index from which,Reach the database that its belong to.

(4) click " next " pushbutton,The interface that if place of graph 8-5 is shown,appears,The information of the index that has existed in the list that it showed to place chooses.If the watch has not founded index,Won't appear this interface,And the interface that if place of graph 8-6 is shown,appears directly.

(5) click " next " pushbutton,The interface that if place of graph 8-6 is shown,appears,It listed the information that expresses all medium kinds,The line that chooses to found index from which.

(6) click " next " pushbutton,The interface that if place of graph 8-7 is shown,appears,It listed the index that can offer an alternative installs option (about the setting of index options,The CREATE INDEX that tells about before reference please commands) .

(7) click " next " pushbutton,The finishing index that if place of graph 8-8 is shown,appears founds an interface,In the name that this can designate place found index,The order that still can adjust the line that forms index.Click " finish " pushbutton,End index establishs a procedure.The system can be played give to found dialog box of index success information.

2 founds index directly
The list that the choice wants to found index,Click right key,Choose from inside quick menu " all tasks (All Tasks) " child in menu " Manage Indexes " option,The index management dialog box that if place of graph 8-9 is shown,will appear,The index that listed had existed in the watch among them.Choose " New " pushbutton,Enter what show like place of graph 8-10 to found index dialog box.In graph 8-10,The name of the index that the input should found,The line that chooses to be used at founding index again,All sorts of option that set index (about the setting of index options,The CREATE INDEX that tells about before reference please commands) .Click " OK " pushbutton,Of the index that finish found.