SQL SERVER indexing and view learning

The advantages of the index. Creating a unique index ensures the uniqueness of row data, greatly improves data retrieval speed, accelerates the connection between tables, and reduces the time to group and sort in queries when using the order By,group by clause.

The disadvantage of the index. It takes time to create and maintain indexes, indexes occupy physical space, and indexes are maintained dynamically when data in a table is deleted, changed, and modified.

To create an index:

1 CREATE [UNIQUE] [CLUSTERED | Nonclustered]2 INDEX <IndexName>  on <Table or ViewName>(<columnName> [asc| DESC][,... N])3INCLUDE (<columnName> [,... N])4 [5  with6 [pad_index = {on | OFF}]7     [[,] FILLFACTOR = <FillFactor>]8     [[,]Ignore_dur_key={ on | OFF}]9     [[,]Drop_existing={ on | OFF}]Ten     [[,]Statistics_norecompute={ on | OFF}] One     [[,]Sort_in_tempdb={ on | OFF}] A     [[,]ONLINE={ on | OFF}] -     [[,]Allow_row_locks={ on | OFF}] -     [[,]Allow_page_locks={ on | OFF}] the     [[,]MAXDOP= <Maxinum degree ofParallelism> - ] - [On {<filegroup> | <partition Scheme name> | DEFAULT}]

eg.

Use Test1

GO

IF EXISTS (SELECT * from sysindexes WHERE name= ' Ceshi_index ')

DROP INDEX Ceshi_index

Create unique nonclustered index Ceshi_index

On orders (CustomerID)

With FILLFACTOR = 30

GO

modifying indexes

1 Alter Index  on  2 with (FILLFACTOR=+, ignore_dup_key=on )

SQL SERVER indexing and view learning