Using indexes

Index architecture clustered nonclustered CREATE INDEX (T-SQL) Useful tips Index options Spatial indexes

1

Index architecture

2

Index architecture

3

Index architecture (clustered)

4

Index architecture (nonclustered)

5

CREATE INDEX (T-SQL)

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 { partition_scheme_name ( column_name ) | filegroup_name | default } ] [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ] ![ ; ]

6

CREATE INDEX (T-SQL)

<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 | DATA_COMPRESSION = { NONE | ROW | PAGE} [ ON PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) ] }

7

Useful tips

Consider using a clustered index for queries that do the following: !• Return a range of values by using operators such as BETWEEN, >, >=, <, and <=. • Return large result sets. • Use JOIN clauses; typically these are foreign key columns. • Use ORDER BY, or GROUP BY clauses. !Consider columns that have one or more of the following attributes: !• Are unique or contain many distinct values • Are accessed sequentially • Defined as IDENTITY because the column is guaranteed to be unique within the table. • Used frequently to sort the data retrieved from a table. !Clustered indexes are not a good choice for the following attributes: !• Columns that undergo frequent changes • Wide keys

8

Useful tips

Consider the characteristics of the database when designing nonclustered indexes. !• Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes. • Online Transaction Processing applications and databases that contain heavily updated tables should avoid over-indexing. Additionally, indexes should be narrow, that is, with as few columns as possible. !Consider using a nonclustered index for queries that have the following attributes: !• Use JOIN or GROUP BY clauses. • Queries that do not return large result sets. • Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches. !Consider columns that have one or more of these attributes: !• Cover the query. • Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

9

Useful tipsQuery in which the column predicate is

one of these

Query description and example Index to consider

Exact match to a specific value

Searches for an exact match in which the query uses the WHERE clause to specify a column entry with a specific value. For example: !SELECT Id, [Login], Email FROM Users WHERE Id = 202

Nonclustered or clustered index on the Id column.

Exact match to a value in an IN (x,y,z) list

Searches for an exact match to a value in a specified list of values. For example: !SELECT Id, [Login], Email FROM Users WHERE Id IN (603, 658, 1371)

Nonclustered or clustered index on the Id column.

Range of values

Searches for a range of values in which the query specifies any entry that has a value between two values. For example: !SELECT Id, UserId, CreateDate, Content FROM Posts WHERE CreateDate >= '2013-03-01 12:00' AND CreateDate <= '2013-03-01 12:30‘ !Or !SELECT Id, UserId, CreateDate, Content FROM Posts WHERE CreateDate BETWEEN '2013-03-01 12:00' AND '2013-03-01 12:30'

Clustered or nonclustered index on the CreateDate column.

Join between tables

Searches for rows in a table that match a row in another table based on a join predicate. For example: !SELECT u.Id, u.[Login], p.Content FROM Users u JOIN Posts p ON u.Id = p.UserId WHERE u.Id = 202

Nonclustered or clustered index on the Id and UserId columns.

10

Useful tipsQuery in which the column predicate is

one of these

Query description and example Index to consider

LIKE comparison

Searches for matching rows that start with a specific character string such as 'abc%'. For example: !SELECT Id, [Login], Email FROM Users WHERE [Login] LIKE 'ma%'

Nonclustered or clustered index on the Login column.

Sorted or aggregated

Requires an implicit or explicit sort order or an aggregation (GROUP BY). For example: !SELECT p.Id, p.UserId, u.[Login], p.Content FROM Posts p JOIN Users u ON p.UserId = u.Id ORDER BY p.UserId, p.CreateDate

Nonclustered or clustered index on the sorted or aggregated column. For sort columns, consider specifying the ASC or DESC order of

PRIMARY KEY or UNIQUE constraint

Searches for duplicates of new index key values in insert and update operations, to enforce PRIMARY KEY and UNIQUE constraints. For example: !INSERT Users ([Login], [Password], Email) VALUES ('xdfyht', 'dh4G57hn1', 'xdfyht@gmail.com')

Clustered or nonclustered index on the column or columns defined in the constraint.

UPDATE or DELETE operation in a PRIMARY KEY/FOREIGN KEY relationship

Searches for rows in an update or delete operation in which the column participates in a PRIMARY KEY/FOREIGN KEY relationship, with or without the CASCADE option.

Nonclustered or clustered index on the foreign key column.

Column is in the select list but not in the predicate.

Contains one or more columns in the select list that are not used for searching and lookups. For example: !SELECT UserId, CreateDate, Content FROM Posts WHERE UserId = 202 ORDER BY CreateDate DESC

Nonclustered index with Content specified in the INCLUDE clause.

11

Index options (fill factor)

12

Index options (included columns)

13

Index options (filtered)

Allowed in expressions Views Filtered indexes

Computed columns Yes No

Joins Yes No

Multiple tables Yes No

Simple comparison logic in a predicate Yes Yes

Complex logic in a predicate Yes No

Views vs. Filtered Indexes

14

Spatial indexes

15

Spatial indexes

16

Spatial indexes

17

Spatial indexes

18

Spatial indexes (conditions)

• geometry1.STContains(geometry2) = 1 • geometry1.STDistance(geometry2) < @r • geometry1.STDistance(geometry2) <= @r • geometry1.STEquals(geometry2) = 1 • geometry1.STIntersects(geometry2) = 1 • geometry1.STOverlaps(geometry2) = 1 • geometry1.STTouches(geometry2) = 1 • geometry1.STWithin(geometry2) = 1

• geography1.STEquals(geography2) = 1 • geography1.STDistance(geography2) < @r • geography1.STDistance(geography2) <= @r • geography1.STIntersects(geography2) = 1

19

about:me

http://unknowntransfer.blogspot.com akor@ciklum.com Skype: unknowntransfer

20