CHỈ MỤC DỮ LiỆU

1. Indexes CHỈ MỤC DỮ LiỆU

2. Creating index Speeds up data access Enforces uniqueness of rows Donot creating index Consumes disk space Incurs overhead Creating Index or not ? • Primary and foreign keys • Frequently searched in ranges • Frequently accessed in sorted order • Seldom referenced in queries • Containing few unique values • Defined with bit, text, or image data types

3. Creating Index • Using the CREATE INDEX Statement USE library CREATE CLUSTERED INDEX cl_lastnameON library..member (lastname)

4. Creating Unique Indexes USE library CREATE UNIQUE INDEX title_ident ON title (title_no) title title_no title author synopsis 10 11 12 The Night-Born Lemon Walking Jack London Motojirou Henry David Thoreau ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ 12 Le Petit Prince Antoine de Saint-Exupery 12 Walking Henry David Thoreau ~ ~ ~ Duplicate key values are not allowed when a new row is added to the table

5. Creating Composite Indexes loan isbn copy_no title_no member_no out_date 342 342 343 5 10 4 35 35 35 3744 5278 3445 1998-01-06 1998-01-04 1998-01-04 USE library CREATE UNIQUE INDEX loan_ident ON loan (isbn, copy_no) Column 1 Column 2 Composite Key

6. Truy vấn không có index SELECT CustomerID, CustomerType FROM Sales.Customer_NoIndex WHERE CustomerID = 11001 use AdventureWorks SELECT * INTO Sales.Customer_NoIndex FROM Sales.Customer

7. Truy vấn có index SELECT CustomerID, CustomerType FROM Sales.Customer_Index WHERE CustomerID = 11001 SELECT * INTO Sales.Customer_Index FROM Sales.Customer GO CREATE INDEX Idx_CustomerID ON Sales.Customer_Index(CustomerID)

8. SELECT CustomerID, CustomerType FROM Sales.Customer_NoIndex WHERE CustomerID = 11001 SELECT CustomerID, CustomerType FROM Sales.Customer_Index WHERE CustomerID = 11001

9. 2 loại indexes • Clustered Indexes • The physical row order of the table and the order of rows in the index are the same • Each table can have only one clustered index • Non-clustered Indexes • Non-clustered indexes are the SQL server default • Existing non-clustered indexes are automatically rebuilt when: • An existing clustered index is dropped • A clustered index is created • The DROP_EXISTING option is used to change which columns define the clustered index