SQL PROGRAMMING

Câu lệnh INDEX trong SQL

Câu lệnh INDEX trong SQL

Giới thiệu

Trong chương này, chúng ta đã học cách sử dụng câu lệnh SQL Create Index với nhiều tùy chọn khác nhau.

Câu lệnh Create Index Statement

Câu lệnh Create SQL được sử dụng để tạo chỉ mục trong bảng. Các chỉ mục được sử dụng để truy xuất dữ liệu từ các chỉ mục chứ không phải cách khác. Người dùng không thể nhìn thấy các chỉ mục, chúng chỉ được sử dụng để tăng tốc độ tìm kiếm/truy vấn trong SQL.
Nó tạo một chỉ mục trên một bảng. Các giá trị trùng lặp được cho phép. Nó tạo ra một chỉ mục quan hệ trên một bảng hoặc dạng xem. Nó còn được gọi là chỉ mục rowstore vì nó là chỉ mục cây B được nhóm hoặc không được nhóm. Bạn có thể tạo chỉ mục rowstore trước khi có dữ liệu trong bảng. Sử dụng chỉ mục rowstore để cải thiện hiệu suất truy vấn, đặc biệt khi truy vấn chọn từ các cột cụ thể hoặc yêu cầu các giá trị được sắp xếp theo thứ tự cụ thể trong bảng cơ sở dữ liệu.

Ghi chú

Kho dữ liệu SQL và Kho dữ liệu song song hiện không hỗ trợ các ràng buộc duy nhất. Mọi ví dụ tham chiếu đến các ràng buộc duy nhất chỉ áp dụng cho SQL Server và Cơ sở dữ liệu SQL.

Cú pháp
  1. CREATE INDEX index_name
  2. ON table_name (column1, column2, …);

Tạo một chỉ mục hàng lưu trữ không bao gồm đơn giản

Các ví dụ sau tạo một chỉ mục không bao gồm trên cột OrderId của bảng OrderDetails.
Cú pháp
  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);

Tạo một chỉ mục tổng hợp hàng lưu trữ không bao gồm đơn giản

Ví dụ sau đây tạo một chỉ mục tổng hợp không bao gồm các cột OrderName và OrderAddress của bảng OrderDetails.
 
Cú pháp 
  1. CREATE NONCLUSTERED INDEX ProductId ON OrderDetails  (OrderName, OrderAddress);

Tạo một chỉ mục trên một bảng trong cơ sở dữ liệu khác

Ví dụ sau đây tạo một chỉ mục nhóm trên cột OrderId của bảng OrderDetails  trong cơ sở dữ liệu mẫu.
Cú pháp  
  1. CREATE CLUSTERED INDEX ProductId ON OrderDetails (OrderId);

Thêm một cột vào một chỉ mục

Ví dụ sau tạo chỉ mục IX_FF với hai cột từ bảng OrderDetails. Câu lệnh tiếp theo sẽ xây dựng lại chỉ mục với một cột nữa và giữ nguyên tên hiện có.
Cú pháp 
  1. CREATE INDEX ProductOrder ON OrderDetails  (OrderName ASC, OrderAddress ASC);
  3. — Rebuild and add the OrganizationKey  
  4. CREATE INDEX ProductOrder ON dbo.OrderDetails (OrderName,OrderAddress, Orderdate DESC)
  5.   WITH (DROP_EXISTING = ON);

Tạo một chỉ mục không bao gồm duy nhất 

Cú pháp
  1. CREATE UNIQUE INDEX ProductId ON OrderDtails(OrderId);
Truy vấn sau đây kiểm tra ràng buộc về tính duy nhất bằng cách cố gắng chèn một hàng có cùng giá trị với giá trị đó vào một hàng hiện có.
Ví dụ sau đây tạo một chỉ mục không nhóm duy nhất trên cột OrderName của orderDetails của bảng trong cơ sở dữ liệu mẫu. Chỉ mục sẽ thực thi tính duy nhất trên dữ liệu được chèn vào cột OrderName.
Ví dụ
  1. SELECT  OrderName FROM OrderDetails  WHERE OrderName  = ‘Apple’;
  2. GO
  4. INSERT INTO OrderDetails(OrderName, orderAddress,OrderDate)
  5.   VALUES (‘guava’, ‘NoidaSector150’, GETDATE());
Ví dụ 
Truy vấn trên được tạo để chọn OrderName từ Bảng OrderDetails và mệnh đề where cho OrderName=’Apple’ và Chèn OrderName, OrderAddress, OrderDate.

Làm cách nào để sử dụng tùy chọn IGNORE_DUP_KEY?

Ví dụ sau minh họa tác dụng của tùy chọn IGNORE_DUP_KEY bằng cách chèn nhiều hàng vào một bảng tạm thời trước tiên với tùy chọn được đặt thành On và một lần nữa với tùy chọn được đặt thành Off. Một hàng duy nhất được chèn vào bảng ProductDetails sẽ cố ý gây ra một giá trị trùng lặp khi câu lệnh Insert nhiều hàng thứ hai được thực thi. Số lượng hàng trong bảng trả về số hàng được chèn.
Cú pháp 
  1. CREATE TABLE ProductDetails (ProductId INT, ProductName NVARCHAR(10),ProductAddres NVARCHAR(50), ProductCity DATETIME);
  2. GO
  4. CREATE UNIQUE INDEX AK_Index ON ProductDetails(ProductAddres)
  5.   WITH (IGNORE_DUP_KEY = ON);
  6. GO
  8. INSERT INTO ProductDetails VALUES (N‘Mnago’, N‘Apple’, GETDATE());
  9. INSERT INTO ProductDetails SELECT * FROM OrderDetails;
  10. GO
  12. SELECT COUNT(*) AS [Number of rows] FROM ProductDetails;
  13. GO
  15. DROP TABLE ProductDetails;
  16. GO
  • Truy vấn trên sẽ tạo một bảng có tên ProductDetails
  • Lưu ý rằng các hàng được chèn từ bảng ProductDetails không vi phạm ràng buộc về tính duy nhất đã được chèn thành công. Một cảnh báo đã được đưa ra và hàng trùng lặp đã bị bỏ qua, nhưng toàn bộ giao dịch không được khôi phục.
  • Các câu lệnh tương tự được thực hiện lại, nhưng với IGNORE_DUP_KEY được đặt thành OFF.

Sử dụng DROP_EXISTING để xóa và tạo lại chỉ mục

Ví dụ sau loại bỏ và tạo lại một chỉ mục hiện có trên cột OrderId của bảng OrderDetails trong cơ sở dữ liệu mẫu bằng cách sử dụng tùy chọn DROP_EXISTING. Các tùy chọn FILLFACTOR và PAD_INDEX cũng được đặt.
Cú pháp 
  1. CREATE NONCLUSTERED INDEX OrderName
  2.   ON OrderDetails (OrderId)
  3.     WITH (FILLFACTOR = 80,
  4.       PAD_INDEX = ON,
  5.       DROP_EXISTING = ON);
  6. GO

Làm cách nào để tạo chỉ mục trên câu lệnh xem? 

Ví dụ sau tạo một dạng xem và một chỉ mục trên dạng xem đó. Hai truy vấn được bao gồm sử dụng chế độ xem được lập chỉ mục.
 
Cú pháp 
  1. SET NUMERIC_ROUNDABORT OFF;
  2. SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  3.   QUOTED_IDENTIFIER, ANSI_NULLS ON;
  4. GO
  6. — Create view with schemabinding  
  7. IF OBJECT_ID (‘Sales.vOrders’, ‘view’) IS NOT NULL
  8.   DROP VIEW Sales.vOrders;
  9. GO
  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
  21. — Create an index on the view  
  22. CREATE UNIQUE CLUSTERED INDEX IDX_V1
  23.   ON Sales.vOrders (OrderDate, ProductID);
  24. GO
  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
  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
  • Truy vấn trên tạo một chỉ mục với câu lệnh cột (không phải khóa) được bao gồm
  • Ví dụ sau tạo một chỉ mục không bao gồm một cột chính (OrderId) và ba cột không khóa (tên đơn hàng, địa chỉ đơn hàng, ngày đặt hàng)
  • Sau đây là một truy vấn được bao phủ bởi chỉ mục. Để hiển thị trình tối ưu hóa đã chọn, trên menu truy vấn trong studio quản lý máy chủ SQL, hãy chọn “display” actual execution plan” trước khi thực hiện truy vấn.
Cú pháp  
  1. CREATE NONCLUSTERED INDEX  New_OrderDetails
  2.       ON OrderDetails (OrderId)
  3.       INCLUDE (OrderName, OrderAddress, OrderDate);
  4.     GO
  6.     SELECT OrderName,orderAddress,OrderDate
  7.     FROM OrderDetails
  8.     WHERE OrderId BETWEEN 8 and 10;
  9.     GO
Ví dụ  
Truy vấn trên sẽ tạo bảng NONCLUSTER INDEX ON OrderDetails và thêm OrderName, OrderAddress, OrderDate và chọn cộtName OrderName, orderAddress, OrderDate từ bảng OrderDetails và câu lệnh Where OrderId giữa 8 đến 10.

Làm cách nào để tạo một câu lệnh chỉ mục được phân vùng?

Ví dụ sau đây tạo một chỉ mục được phân vùng không bao gồm trên Product_Details, một lược đồ phân vùng hiện có trong cơ sở dữ liệu mẫu. Ví dụ này giả sử mẫu chỉ mục được phân vùng đã được cài đặt.
Cú pháp 
  1. CREATE NONCLUSTERED INDEX Product_Details
  2.   ON  OrderDetails (OrderId)
  3. GO

Làm cách nào để tạo một câu lệnh chỉ mục được lọc? 

Ví dụ sau tạo một chỉ mục được lọc trên bảng OrderDetails trong cơ sở dữ liệu mẫu. Vị từ bộ lọc có thể bao gồm các cột không phải là cột chính trong chỉ mục được lọc. Vị từ trong ví dụ này chỉ chọn những hàng có EndDate không phải là NULL.
Cú pháp 
  1. CREATE NONCLUSTERED INDEX Product_List
  2.   ON OrderDetails (OrderId, OrderName, OrderAddress)
  3.   WHERE OrderDate IS NOT NULL;

Làm cách nào để tạo một câu lệnh chỉ mục nén?

Ví dụ sau tạo một chỉ mục trên một bảng không được phân vùng bằng cách sử dụng tính năng nén hàng.
Cú pháp 
  1. CREATE NONCLUSTERED INDEX ProductDetails
  2.   ON OrderDetails (OrderName)
  3.   WITH (DATA_COMPRESSION = ROW);
  4. GO
Ví dụ sau tạo một chỉ mục trên một bảng được phân vùng bằng cách sử dụng tính năng nén hàng trên tất cả các phân vùng của chỉ mục.
Cú pháp 
  1. CREATE CLUSTERED INDEX Product_Details
  2.   ON OrderDetails  (OrderId)
  3.   WITH (DATA_COMPRESSION = ROW);
  4. GO
Ví dụ sau tạo một chỉ mục trên bảng được phân vùng bằng cách sử dụng nén trang trên phân vùng 1 của chỉ mục và nén hàng trên phân vùng  2  đến  4  của chỉ mục.
Cú pháp 
  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 và abort các hoạt động lập chỉ mục có thể tiếp tục
Cú pháp 
  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);
  4. — Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.  
  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);
  9. — Pause a running resumable online index creation  
  10. ALTER INDEX EmpId ON employeDetail PAUSE;
  11. ALTER INDEX EmpName ON EmployeeDetail PAUSE;
  13. — Resume a paused online index creation  
  14. ALTER INDEX EmpId ON employeDetail RESUME;
  15. ALTER INDEX EmpName ON EmployeeDetail RESUME;
  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;
Truy vấn trên sẽ tạo Index OrderId từ tên bảng EmployeeDetail từ EmpName với Online=on, Resumable= on, MAX_DURATION=240, và ALTER INDEX để thêm tên cột EmpId, EmpName trên tên bảng EmployeeDetail tên PAUSE, RESUME, ABORT.

Làm cách nào để tạo một chỉ mục không bao gồm trên một bảng trong cơ sở dữ liệu hiện tại? 

Ví dụ sau tạo một chỉ mục không nhóm trên cột OrderId của bảng OrderDetails.
Cú pháp
  1. CREATE INDEX OrderName
  2.   ON OrderDetails  (OrderID);

Làm cách nào để tạo một chỉ mục nhóm trên một bảng trong cơ sở dữ liệu khác?

Ví dụ sau tạo một chỉ mục không nhóm trên cột OrderId của bảng OrderDetails.
Cú pháp 
  1. CREATE INDEX NewOrderDetails
  2.   ON OrderDetails(OrderId);

Làm cách nào để tạo một chỉ mục nhóm trên một bảng trong cơ sở dữ liệu khác?

Ví dụ sau tạo một chỉ mục không nhóm trên cột OrderName của bảng OrderDetails.
Cú pháp
  1. CREATE CLUSTERED INDEX Order_Id
  2.   ON OrderDetail  (OrerName);

Bản tóm tắt

Trong chương tiếp theo, chúng ta sẽ học cách sử dụng câu lệnh SQL UNIQUE với nhiều tùy chọn khác nhau.
(Source: https://www.c-sharpcorner.com/)

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *