CountryID Countries.
CREATE INDEX IX_ Countries _ CountryID
ON Countries (CountryID);
SalesQuota SalesYTD Sales.SalesPerson.
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
ON Sales.SalesPerson (SalesQuota, SalesYTD);
Name Production.UnitMeasure. , Name.
CREATE UNIQUE INDEX AK_UnitMeasure_Name
ON Production.UnitMeasure(Name);
, .
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
VALUES ('OC', 'Ounces', GetDate());
:
: 2601, 14, 1, 1
UnitMeasure AK_UnitMeasure_Name. .
()
(PostalCode) (AddressLine1, AddressLine2, City, StateProvinceID).
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
ALTER INDEX .
, . CREATE INDEX DROP_EXISTING.
ALTER INDEX { index_name | ALL }
ON <object>
{ REBUILD
[ [PARTITION = ALL]
[ WITH (<rebuild_index_option> [,...n ]) ]
| [ PARTITION = partition_number
[ WITH (<single_partition_rebuild_index_option>
[,...n ])
]
]
]
| DISABLE
| REORGANIZE
[ PARTITION = partition_number ]
| SET (<set_index_option> [,...n ])
}
<object>::=
{
[ database_name. [ schema_name ]. | schema_name. ]
table_or_view_name
}
<rebuild_index_option >::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { 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 ({ <partition_number_expression> | <range> }
[,...n ]) ]
}
<range>::=
<partition_number_expression> TO <partition_number_expression>
}
<single_partition_rebuild_index_option>::=
{
SORT_IN_TEMPDB = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE } }
}
<set_index_option>::=
{
ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
}
Index_name
, .
ALL
, , .
Database_name
.
Schema_name
, .
Table_or_view_name
, .
REBUILD [ WITH ( <rebuild_index_option> [,... n ] ) ]
, , , . REBUILD . , ALL. , , sys.indexes. , sys.indexes, , .
XML- ONLINE = ON IGNORE_DUP_KEY = ON .
ALL, , . , .
, .
PARTITION
, . PARTITION , index_name .
PARTITION = ALL, .
Partition_number
, partition_number. partition_number , .
WITH (<single_partition_rebuild_index_option>)
SORT_IN_TEMPDB, MAXDOP DATA_COMPRESSION - , (PARTITION = n).
DISABLE
Database Engine. , . . , ALTER INDEX REBUILD CREATE INDEX WITH DROP_EXISTING.
REORGANIZE
, . ALTER INDEX REORGANIZE . , ALTER INDEX REORGANIZE. REORGANIZE ALLOW_PAGE_LOCKS OFF.
|
|
|
SET ( <set_index option> [,... n ] )
. SET .
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 - .
DATA_COMPRESSION
, . .
NONE - .
ROW - .
PAGE - .
ON PARTITIONS ({ <partition_number_expression> | <range> } [,...n])
, DATA_COMPRESSION. , ON PARTITIONS . ON PARTITIONS, DATA_COMPRESSION .
< partition_number_expression > :
, ON PARTITIONS (2).
, ON PARTITIONS (1, 5).
: ON PARTITIONS (2, 4, 6 TO 8).
PRIMARY KEY PRIMARY KEY. FOREIGN KEY , .
ALTER INDEX PK_Department_DepartmentID
ON Department
DISABLE;
AK_SalesOrderHeader_SalesOrderNumber.
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
Sales.SalesOrderHeader
SET (STATISTICS_NORECOMPUTE = ON,
IGNORE_DUP_KEY = ON,
ALLOW_PAGE_LOCKS = ON);
SQL Server Database Engine , . , ( ). :
, , , -.
|
|
|
- , , , , .
.
. , . , .
, , .
, , . sys.dm_db_index_physical_stats , , . sys.dm_db_index_physical_stats .
, sys.dm_db_index_physical_stats, :
| avg_fragmentation_in_percent | ( ). |
| fragment_count | ( ) . |
| avg_fragment_size_in_pages | . |
, .
| avg_fragmentation_in_percent | |
| > 5 % <= 30 % | ALTER INDEX REORGANIZE |
| > 30% | ALTER INDEX REBUILD |
. . , .
:
sys.dm_db_index_physical_stats Cources. , IX_Id_Unique_Clustered IX_Unique_Code.
SELECT a.index_id, name, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Cources'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;GO index_id name avg_fragmentation_in_percent------------------------------------------------------------------------------------------1 IX_Id_Unique_Clustered 15.0769230769230773 IX_Unique_Code 99.666666666666657 (2 row(s) affected)
ALTER INDEX REORGANIZE. DBCC INDEXDEFRAG.
, ( ) . . , . , . .
, . , , , . .
. , . .
ALTER INDEX REBUILD. DBCC DBREINDEX.
|
|
|
, . , , , . ALL, .
, . , , .
, , , . SQL Server Enterprise Developer.
( 128 ) Database Engine , , , : .
, , , . .
. , , . , .
, , .
1:
Employee.
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
2:
ALL. , .
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
, . , , , - . , , . .
DROP INDEX .
DROP INDEX index_name ON table_ name
[ WITH (<drop_clustered_index_option> [,...n ]) ]
<drop_clustered_index_option>::=
{
MAXDOP = max_degree_of_parallelism
| ONLINE = { ON | OFF }
| MOVE TO { partition_scheme_name (column_name)
| filegroup_name
| "default" }
}
index_name
, .
table_name
, .
<drop_clustered_index_option>
. .
MAXDOP = max _ degree _ of _ parallelism
MAXDOP , . Developer, Enterprise DataCenter. (Enterprise 64, DataCenter 256). 0 - .
ONLINE = { ON | OFF }
, ( SQL Server Enterprise Developer). - OFF.
MOVE TO { partition_scheme_name ( column_name ) | filegroup_name | " default " }
, , . . , . MOVE TO . , , , .
MOVE TO, , .
partition_scheme_name ( column_name )
, . . , , .
. .
filegroup_name
, . , , . .
"default "
.
IX_ProductVendor_VendorID ProductVendor.
DROP INDEX IX_ProductVendor_BusinessEntityID
ON Purchasing.ProductVendor;
15
