1 / 13

SQL Server Indexes

SQL Server Indexes. Indexes. Overview. Indexes are used to help speed search results in a database. A careful use of indexes can greatly improve search speeds. Over use of indexes, however, can painfully slow database inserts and updates. Types of indexes. Clustered Indexes

conroy
Download Presentation

SQL Server Indexes

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Indexes Indexes

  2. Overview Indexes are used to help speed search results in a database. A careful use of indexes can greatly improve search speeds. Over use of indexes, however, can painfully slow database inserts and updates.

  3. Types of indexes • Clustered Indexes • Non Clustered Indexes • Standard non clustered • Unique • Filtered • Composite (more than one field)

  4. Clustered Indexes A clustered index physically reorders the table contents Clustered indexes are usually assigned to a tables primary key There can only be one clustered index per table. CREATE CLUSTERED INDEX ix_CustomerKeyON Customer(CustomerKey)

  5. Non Clustered Indexes Non Clustered indexes form a separate structure called a B-Tree that greatly speeds searches. Instead of going through thousands or millions of rows to find a value, the B-tree enables a search using only a very few records. Indexes can slow INSERTS, UPDATES and DELETES because the B-Tree index must be rebuilt each time

  6. B-Tree Structure

  7. Syntax for Standard Non Clustered Index CREATE NONCLUSTERED INDEX Ix_LastNameON Customer(LastName) The term NONCLUSTERED is optional

  8. Unique, Composite, and Filtered Indexes CREATE UNIQUE INDEX ix_EmailON Customer(CustomerEmail) CREATE INDEX ix_AddressONCustomerAddress(City, State, Zip) CREATE INDEX ix_ApartmentONCustomerAddress(Apartment) WHERE Apartment IS NOT NULL

  9. Columnstore Indexes This is a new type of index available only in Enterprise Editions of SQL Server 2012 or later. Instead of storing the data rows contiguously across pages it stores the data in columns contiguously across pages. These are best used in data warehousing or read only databases

  10. Forcing an index The database management system query optimization system will ignore indexes in tables with few rows. It is more efficient to skip the index with under 10,000 rows or so, but you can force an index: SELECT * FROM Employee WHEREEmployeeName='John Smith' WITH (NOLOCK, INDEX(ix_EmployeeName))

  11. Enabling,Disabling and Dropping Indexes ALTER INDEX ix_EmployeeNameON Employee DISABLE ALTER INDEX ix_EmployeeNameON Employee REBUILD DROP INDEX ix_EmployeeNameON Employee

  12. Testing Queries SQL Server contains tools for testing and comparing query results

  13. Best Practices Indexes should be applied on Columns used often in searches. Foreign keys make good candidates for indexes It takes careful testing to be sure where to place indexes

More Related