SQL Server Management Studio
| sys.indexes | , , , . |
| sys.index_columns | , , ( ) (ASC DESC). |
| sys.stats | , , , , . |
| sys.stats_columns | , . |
| sys.xml_indexes | XML- ( ), . |
| sys.dm_db_index_physical_stats | . |
| sys.dm_db_index_operational_stats | - . |
| sys.dm_db_index_usage_stats | . |
| sys.dm_db_missing_index_columns | , . |
| sys.dm_db_missing_index_details | . |
| sys.dm_db_missing_index_group_stats | . |
| sys.dm_db_missing_index_groups | , . |
| INDEXKEY_PROPERTY | (ASC DESC). |
| INDEXPROPERTY | , , . |
| INDEX_COL | . |
CREATE INDEX.
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> (column [ ASC | DESC ] [,...n ])
[ INCLUDE (column_name [,...n ]) ]
[ WHERE <filter_predicate> ]
[ WITH (<relational_index_option> [,...n ]) ]
[ ON { filegroup_name | default } ]
<object>::=
{
[ database_name. [ schema_name ]. | schema_name. ]
table_or_view_name
}
<relational_index_option>::=
{
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
}
UNIQUE
. , . .
Database Engine , , IGNORE_DUP_KEY ON. Database Engine . , . , , NOT NULL, . . NULL .
|
|
|
CLUSTERED
, . CLUSTERED , .
NONCLUSTERED
, . .
, .
, NONCLUSTERED.
index_name
. , .
column
, . . , , table_or_view_name .
ntext, text, varchar(max), nvarchar(max), varbinary(max), xml image . , ntext, text image, CREATE INDEX.
[ ASC | DESC ]
: . - ASC.
INCLUDE ( column [, ... n ] )
, . .
WHERE <filter_predicate>
, , . , . , .
, , hierarchyID. NULL . IS NULL IS NOT NULL.
.
| SET | |
| ANSI_NULLS | ON |
| ANSI_PADDING | ON |
| ANSI_WARNINGS | ON |
| ARITHABORT | ON |
| CONCAT_NULL_YIELDS_NULL | ON |
| NUMERIC_ROUNDABORT | OFF |
| QUOTED_IDENTIFIER | ON |
ON filegroup_name
. , , . .
|
|
|
ON " default "
, .
<object>::=
.
<relational_index_option>::=
, .
PAD_INDEX = { ON | OFF }
. OFF.
ON
, fillfactor, .
OFF fillfactor
, , .
PAD_INDEX , FILLFACTOR, , . , FILLFACTOR, , Database Engine , . fillfactor.
WITH PAD_INDEX WITH PAD_INDEX = ON.
FILLFACTOR = fillfactor
, , Database Engine . fillfactor 1 100. 0. fillfactor 100 0, Database Engine .
FILLFACTOR . Database Engine . sys.indexes.
SORT_IN_TEMPDB = { ON | OFF }
, tempdb. - OFF.
ON
, , tempdb. , , tempdb . , .
OFF
, .
IGNORE_DUP_KEY = { ON | OFF }
, , . IGNORE_DUP_KEY , . - OFF.
ON
, . , .
OFF
, . INSERT.
STATISTICS_NORECOMPUTE = { ON | OFF }
, . - OFF.
ON
.
OFF
.
DROP_EXISTING = { ON | OFF }
, . - OFF.
ON
. , . , , , .
|
|
|
OFF
, .
DROP_EXISTING.
ONLINE = { ON | OFF }
, ( SQL Server Enterprise Developer). - OFF.
ALLOW_ROW_LOCKS = { ON | OFF }
, . ON.
ON
. Database Engine.
OFF
.
ALLOW_PAGE_LOCKS = { ON | OFF }
, . - ON.
ON - . Database Engine.
OFF - .
MAXDOP = max_degree_of_parallelism
MAXDOP , . Developer, Enterprise DataCenter. (Enterprise 64, DataCenter 256). 0 - .
. PERSISTED - , Database Engine , . Database Engine , .
. , . , . ( ), , , 3.
, SET , SET .
, image, ntext, text, varchar(max), nvarchar(max), varbinary(max) xml, , . 900 , .
, . , . , "c" , INSERT .
CREATE TABLE T (a int, b int, c AS a/b);INSERT INTO T VALUES (1, 0);"c", INSERT .
|
|
|
CREATE TABLE T (a int, b int, c AS a/b);CREATE UNIQUE CLUSTERED INDEX IdxT ON T(c);INSERT INTO T VALUES (1, 0);
:
