SQL Index Statement

Introduction

 
In this chapter, we learned how to use a SQL Create Index statement with various options.  
 

SQL Create Index Statement

 
The SQL Create statement is used to create indexes in tables. Indexes are used to retrieve data from indexes rather than otherwise. Users can not see indexes, they are only used to speed up searches/queries in SQL.
 
It creates an index on a table. Duplicate values are allowed. It creates a relational index on a table or view. It's also called a rowstore index because it is either a clustered or nonclustered B-tree index. You can create a rowstore index before there is data in the table. Use a rowstore index to improve query performance, especially when the queries select from specific columns or require values to be sorted in a particular order in database tables.
 
Note
 
SQL Data Warehouse and Parallel Data Warehouse currently do not support unique constraints. Any examples referencing unique constraints are only applicable to SQL Server and SQL Database.
 
Syntax
  1. CREATE INDEX index_name    
  2. ON table_name (column1, column2, ...);   

Create a simple nonclustered rowstore index

 
The following examples create a nonclustered index on the OrderId column of the OrderDetails table. 
 
Syntax
  1. CREATE INDEX ProductId  ON orderDetails (OrderId);    
  2. CREATE INDEX ProductId  ON OrderDetails (OrderId DESC, OrderName  ASC, OrderAddress DESC);    
  3. CREATE INDEX ProductId ON OrderDetails  (OrderId);   

Create a simple nonclustered rowstore composite index

 
The following example creates a nonclustered composite index on the OrderName and OrderAddress columns of the OrderDetails table. 
 
Syntax 
  1. CREATE NONCLUSTERED INDEX ProductId ON OrderDetails  (OrderName, OrderAddress);   

Create an index on a table in another database

 
The following example creates a clustered index on theOrderId column of the OrderDetails table in the sample database.
 
Syntax  
  1. CREATE CLUSTERED INDEX ProductId ON OrderDetails (OrderId);   

Add a column to an index

 
The following example creates index IX_FF with two columns from the OrderDetails table The next statement rebuilds the index with one more column and keeps the existing name.
 
Syntax 
  1. CREATE INDEX ProductOrder ON OrderDetails  (OrderName ASC, OrderAddress ASC);    
  2.     
  3. -- Rebuild and add the OrganizationKey    
  4. CREATE INDEX ProductOrder ON dbo.OrderDetails (OrderName,OrderAddress, Orderdate DESC)    
  5.   WITH (DROP_EXISTING = ON);   

Create a unique nonclustered index 

 
Syntax
  1. CREATE UNIQUE INDEX ProductId ON OrderDtails(OrderId);   
The following query tests the uniqueness constraint by attempting to insert a row with the same value as that in an existing row.
 
The following example creates a unique nonclustered index on theOrderName column of the orderDetails of the table in the sample database. The index will enforce uniqueness on the data inserted into the OrderName column. 
 
Example
  1. SELECT  OrderName FROM OrderDetails  WHERE OrderName  = 'Apple';    
  2. GO    
  3.     
  4. INSERT INTO OrderDetails(OrderName, orderAddress,OrderDate)    
  5.   VALUES ('guava', 'NoidaSector150', GETDATE());   
Example 
 
 
The above query is created to select the OrderName from OrderDetails Table and where clause for OrderName='Apple' and Insert the OrderName, OrderAddress,  OrderDate.
 

How to use the IGNORE_DUP_KEY option?

 
The following example demonstrates the effect of the IGNORE_DUP_KEY option by inserting multiple rows into a temporary table first with the option set to On and again with the option set to Off. A single row is inserted into the ProductDetails table that will intentionally cause a duplicate value when the second multiple-row Insert statement is executed. A count of rows in the table returns the number of rows inserted.
 
Syntax 
  1. CREATE TABLE ProductDetails (ProductId INT, ProductName NVARCHAR(10),ProductAddres NVARCHAR(50), ProductCity DATETIME);    
  2. GO    
  3.     
  4. CREATE UNIQUE INDEX AK_Index ON ProductDetails(ProductAddres)    
  5.   WITH (IGNORE_DUP_KEY = ON);    
  6. GO    
  7.     
  8. INSERT INTO ProductDetails VALUES (N'Mnago', N'Apple', GETDATE());    
  9. INSERT INTO ProductDetails SELECT * FROM OrderDetails;    
  10. GO    
  11.     
  12. SELECT COUNT(*) AS [Number of rows] FROM ProductDetails;    
  13. GO    
  14.     
  15. DROP TABLE ProductDetails;    
  16. GO   
  • The above query will create a table named ProductDetails
  • Notice that the rows inserted from the ProductDetails table that did not violate the uniqueness constraint were successfully inserted. A warning was issued and the duplicate row ignored, but the entire transaction was not rolled back.
  • The same statements are executed again, but with IGNORE_DUP_KEY set to OFF.  

Using DROP_EXISTING to drop and re-create an index

 
The following example drops and re-creates an existing index on the OrderId column of the OrderDetails table in the sample database by using the DROP_EXISTING option. The options FILLFACTOR and PAD_INDEX are also set.
 
Syntax 
  1. CREATE NONCLUSTERED INDEX OrderName    
  2.   ON OrderDetails (OrderId)    
  3.     WITH (FILLFACTOR = 80,    
  4.       PAD_INDEX = ON,    
  5.       DROP_EXISTING = ON);    
  6. GO  

How to create an index on a view statement? 

 
The following example creates a view and an index on that view. Two queries are included that use the indexed view.
 
Syntax 
  1. SET NUMERIC_ROUNDABORT OFF;    
  2. SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,    
  3.   QUOTED_IDENTIFIER, ANSI_NULLS ON;    
  4. GO    
  5.     
  6. -- Create view with schemabinding    
  7. IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL    
  8.   DROP VIEW Sales.vOrders;    
  9. GO    
  10.     
  11. CREATE VIEW Product.vOrders    
  12.   WITH SCHEMABINDING    
  13. AS    
  14.   SELECT SUM( * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,    
  15.     OrderDate, ProductID, COUNT_BIG(*) AS COUNT    
  16.   FROM Sales.SalesOrderDetail AS od, SalesOrderHeader AS o    
  17.   WHERE od.SalesOrderID = o.SalesOrderID    
  18.   GROUP BY OrderDate, ProductID;    
  19. GO    
  20.     
  21. -- Create an index on the view    
  22. CREATE UNIQUE CLUSTERED INDEX IDX_V1    
  23.   ON Sales.vOrders (OrderDate, ProductID);    
  24. GO    
  25.     
  26. -- This query can use the indexed view even though the view is    
  27. -- not specified in the FROM clause.    
  28. SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,    
  29.   OrderDate, ProductID    
  30. FROM Sales.SalesOrderDetail AS od    
  31.   JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID    
  32.     AND ProductID BETWEEN 500 AND 600    
  33.     AND OrderDate >= CONVERT(DATETIME, '11/01/2020', 104)    
  34. GROUP BY OrderDate, ProductID    
  35. ORDER BY Rev DESC;    
  36. GO    
  37.     
  38. -- This query can use the above indexed view    
  39. SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev    
  40. FROM Sales.SalesOrderDetail AS od    
  41.   JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID    
  42.     AND DATEPART(mm, OrderDate) = 3    
  43.   AND DATEPART(yy, OrderDate) = 2020    
  44. GROUP BY OrderDate    
  45. ORDER BY OrderDate ASC;    
  46. GO    
  • The above query creates an index with included (non-key) columns statement
  • The following example creates a nonclustered index with one key column (OrderId) and three non-key columns (ordername, orderaddress, orderdate)
  • A query that is covered by the index follows. To display selected optimizer, on the query menu in SQL server management studio, select  "display" actual execution plan" before executing a query.
Syntax  
  1.     CREATE NONCLUSTERED INDEX  New_OrderDetails    
  2.       ON OrderDetails (OrderId)    
  3.       INCLUDE (OrderName, OrderAddress, OrderDate);    
  4.     GO    
  5.         
  6.     SELECT OrderName,orderAddress,OrderDate     
  7.     FROM OrderDetails     
  8.     WHERE OrderId BETWEEN 8 and 10;    
  9.     GO   
Example  
 
 
The above query will create NONCLUSTER INDEX ON OrderDetails table and add the OrderName, OrderAddress, OrderDate and select the columnName OrderName,  orderAddress, OrderDate from OrderDetails Table and Where statement OrderId Between 8 To 10. 
 

How to Create a partitioned index statement?

 
The following example creates a nonclustered partitioned index on Product_Details, an existing partition scheme in the sample database. This example assumes the partitioned index sample has been installed.
 
Syntax 
  1. CREATE NONCLUSTERED INDEX Product_Details    
  2.   ON  OrderDetails (OrderId)    
  3. GO  

How to create a filtered index statement? 

 
The following example creates a filtered index on the OrderDetails table in the sample database. The filter predicate can include columns that are not key columns in the filtered index. The predicate in this example selects only the rows where EndDate is non-NULL. 
 
Syntax 
  1. CREATE NONCLUSTERED INDEX Product_List     
  2.   ON OrderDetails (OrderId, OrderName, OrderAddress)    
  3.   WHERE OrderDate IS NOT NULL;   

How to create a compressed index statement?

 
The following example creates an index on a nonpartitioned table by using row compression.
 
Syntax 
  1. CREATE NONCLUSTERED INDEX ProductDetails    
  2.   ON OrderDetails (OrderName)    
  3.   WITH (DATA_COMPRESSION = ROW);    
  4. GO   
The following example creates an index on a partitioned table by using row compression on all partitions of the index.
 
Syntax 
  1. CREATE CLUSTERED INDEX Product_Details    
  2.   ON OrderDetails  (OrderId)    
  3.   WITH (DATA_COMPRESSION = ROW);    
  4. GO  
The following example creates an index on a partitioned table by using page compression on partition 1 of the index and row compression on partitions 2 through 4 of the index.
 
Syntax 
  1. CREATE CLUSTERED INDEX Product_Details    
  2.   ON OrderDetails  (OrderId)    
  3.   WITH (    
  4.     DATA_COMPRESSION = PAGE ON PARTITIONS(1),    
  5.     DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)    
  6.   );    
  7. GO   
Create, resume, pause, and abort resumable index operations
 
Syntax 
  1. -- Execute a resumable online index create statement with MAXDOP=1    
  2. CREATE INDEX OrderId  ON OrderDetails (OrderId) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);    
  3.     
  4. -- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.    
  5.     
  6. -- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index creates operation is paused.    
  7. CREATE INDEX OrderId  ON EmployeeDetail  (EmpName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);    
  8.     
  9. -- Pause a running resumable online index creation    
  10. ALTER INDEX EmpId ON employeDetail PAUSE;    
  11. ALTER INDEX EmpName ON EmployeeDetail PAUSE;    
  12.     
  13. -- Resume a paused online index creation    
  14. ALTER INDEX EmpId ON employeDetail RESUME;    
  15. ALTER INDEX EmpName ON EmployeeDetail RESUME;    
  16.     
  17. -- Abort resumable index create an operation which is running or paused    
  18. ALTER INDEX EmpId ON employeDetail ABORT;    
  19. ALTER INDEX EmpName ON EmployeeDetail ABORT;    
The above query will create Index OrderId from EmployeeDetail table name from EmpName with Online=on, Resumable= on, MAX_DURATION=240, And the ALTER INDEX to add column name EmpId, EmpName on EmployeeDetail Table name PAUSE, RESUME, ABORT statement.
 

How to create a nonclustered index on a table in the current database? 

 
The following example creates a nonclustered index on the OrderId column of the OrderDetails table. 
 
Syntax 
  1. CREATE INDEX OrderName  
  2.   ON OrderDetails  (OrderID);   

How to create a clustered index on a table in another database?

 
The following example creates a nonclustered index on the OrderId column of the OrderDetails table.
 
Syntax 
  1. CREATE INDEX NewOrderDetails    
  2.   ON OrderDetails(OrderId);   

How to create a clustered index on a table in another database?

 
The following example creates a nonclustered index on the OrderName column of the OrderDetails table.
 
Syntax
  1. CREATE CLUSTERED INDEX Order_Id    
  2.   ON OrderDetail  (OrerName);   

Summary

 
In the next chapter, we will learn how to use a SQL UNIQUE statement with various options.
Author
Naresh Beniwal
Editor 7.1k 1.5m
Next » SQL UNIQUE Constraint

©2024 C# Corner.