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

CREATE INDEX index_name
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

CREATE INDEX ProductId ON orderDetails (OrderId);
CREATE INDEX ProductId ON OrderDetails (OrderId DESC, OrderName ASC, OrderAddress DESC);
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

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

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

CREATE INDEX ProductOrder ON OrderDetails (OrderName ASC, OrderAddress ASC);
— Rebuild and add the OrganizationKey
CREATE INDEX ProductOrder ON dbo.OrderDetails (OrderName,OrderAddress, Orderdate DESC)
WITH (DROP_EXISTING = ON);

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

Cú pháp

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ụ

SELECT OrderName FROM OrderDetails WHERE OrderName = ‘Apple’;
GO
INSERT INTO OrderDetails(OrderName, orderAddress,OrderDate)
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

CREATE TABLE ProductDetails (ProductId INT, ProductName NVARCHAR(10),ProductAddres NVARCHAR(50), ProductCity DATETIME);
GO
CREATE UNIQUE INDEX AK_Index ON ProductDetails(ProductAddres)
WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO ProductDetails VALUES (N‘Mnago’, N‘Apple’, GETDATE());
INSERT INTO ProductDetails SELECT * FROM OrderDetails;
GO
SELECT COUNT(*) AS [Number of rows] FROM ProductDetails;
GO
DROP TABLE ProductDetails;
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

CREATE NONCLUSTERED INDEX OrderName
ON OrderDetails (OrderId)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON,
DROP_EXISTING = ON);
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

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
— Create view with schemabinding
IF OBJECT_ID (‘Sales.vOrders’, ‘view’) IS NOT NULL
DROP VIEW Sales.vOrders;
GO
CREATE VIEW Product.vOrders
WITH SCHEMABINDING
AS
SELECT SUM( * OrderQty * (1.00 – UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
— Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO
— This query can use the indexed view even though the view is
— not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 – UnitPriceDiscount)) AS Rev,
OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
AND ProductID BETWEEN 500 AND 600
AND OrderDate >= CONVERT(DATETIME, ’11/01/2020′, 104)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
— This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 – UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
AND DATEPART(mm, OrderDate) = 3
AND DATEPART(yy, OrderDate) = 2020
GROUP BY OrderDate
ORDER BY OrderDate ASC;
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

CREATE NONCLUSTERED INDEX New_OrderDetails
ON OrderDetails (OrderId)
INCLUDE (OrderName, OrderAddress, OrderDate);
GO
SELECT OrderName,orderAddress,OrderDate
FROM OrderDetails
WHERE OrderId BETWEEN 8 and 10;
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

CREATE NONCLUSTERED INDEX Product_Details
ON OrderDetails (OrderId)
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

CREATE NONCLUSTERED INDEX Product_List
ON OrderDetails (OrderId, OrderName, OrderAddress)
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

CREATE NONCLUSTERED INDEX ProductDetails
ON OrderDetails (OrderName)
WITH (DATA_COMPRESSION = ROW);
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

CREATE CLUSTERED INDEX Product_Details
ON OrderDetails (OrderId)
WITH (DATA_COMPRESSION = ROW);
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

CREATE CLUSTERED INDEX Product_Details
ON OrderDetails (OrderId)
WITH (
DATA_COMPRESSION = PAGE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
);
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

— Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX OrderId ON OrderDetails (OrderId) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);
— Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.
— 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.
CREATE INDEX OrderId ON EmployeeDetail (EmpName) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);
— Pause a running resumable online index creation
ALTER INDEX EmpId ON employeDetail PAUSE;
ALTER INDEX EmpName ON EmployeeDetail PAUSE;
— Resume a paused online index creation
ALTER INDEX EmpId ON employeDetail RESUME;
ALTER INDEX EmpName ON EmployeeDetail RESUME;
— Abort resumable index create an operation which is running or paused
ALTER INDEX EmpId ON employeDetail ABORT;
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

CREATE INDEX OrderName
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

CREATE INDEX NewOrderDetails
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

CREATE CLUSTERED INDEX Order_Id
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/)

SQL PROGRAMMING

Chương trình giáo dục

Câu lệnh INDEX trong SQL

Câu lệnh INDEX trong SQL

Giới thiệu

Câu lệnh Create Index Statement

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

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

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

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

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

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

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

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

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

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

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

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?

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?

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?

Bản tóm tắt

Để lại một bình luận Hủy