SQLserver中的索引

在 SQL Server 中，索引是一种数据库对象，用于提高数据库表中数据检索的效率。索引类似于书籍的目录，它允许数据库引擎快速找到数据行，而无需扫描整个表。以下是关于 SQL Server 索引的一些关键概念和操作：

索引的类型

1. 聚集索引（Clustered Index）：

   • 一个表只能有一个聚集索引。

   • 聚集索引决定了表中数据行的物理存储顺序。

   • 聚集索引可以显著提高查询性能，特别是对于大量数据的表。

2. 非聚集索引（Nonclustered Index）：

   • 一个表可以有多个非聚集索引。

   • 非聚集索引不改变数据行的物理存储顺序，而是在数据行的顶部创建一个指针结构。

   • 非聚集索引通常用于提高查询性能，特别是当查询不涉及表中的所有行时。

3. 包含列索引（Included Columns）：

   • 允许在索引中包含非键列，这些列被称为“包含列”。

   • 可以提高查询性能，因为某些查询可能不需要访问表中的所有列。

4. 复合索引（Composite Index）：

   • 索引多个列，可以是聚集索引或非聚集索引。

   • 索引列的顺序会影响查询性能。

5. 全文索引（Full-Text Index）：

   • 用于执行全文搜索查询。

   • 可以对文本数据进行复杂的搜索。

6. XML 索引：

   • 用于索引 XML 数据类型。

创建索引

创建聚集索引

CREATE CLUSTERED INDEX CI_LastName ON Employees(LastName);

创建非聚集索引

CREATE NONCLUSTERED INDEX NCI_LastName ON Employees(LastName);

创建包含列的非聚集索引

CREATE NONCLUSTERED INDEX NCI_LastName_FirstName ON Employees(LastName)
INCLUDE (FirstName);

创建复合索引

CREATE NONCLUSTERED INDEX NCI_LastName_FirstName ON Employees(LastName, FirstName);

管理索引

查看索引

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Employees');

重建索引

索引可能会因为数据的插入、删除和更新操作而变得碎片化，重建索引可以提高性能。

ALTER INDEX CI_LastName ON Employees REBUILD;

重新组织索引

重新组织索引是一种较轻量级的操作，用于减少索引碎片。

ALTER INDEX NCI_LastName ON Employees REORGANIZE;

删除索引

DROP INDEX NCI_LastName ON Employees;

注意事项

• 索引可以显著提高查询性能，但过多的索引会增加写操作的开销，因为每次数据更新时，索引也需要更新。

• 选择正确的索引类型和列对于优化性能至关重要。

• 定期维护索引，如重建或重新组织，以保持最佳性能。

• 在创建索引之前，考虑查询模式和表的使用情况，以确定哪些索引最有效。

聚集索引和非聚集索引的区别

聚集索引（Clustered Index）和非聚集索引（Nonclustered Index）是 SQL Server 中两种不同类型的索引，它们在数据存储和检索方面有着根本的区别。以下是它们的主要区别：

聚集索引（Clustered Index）

1. 数据存储方式：聚集索引决定了表中数据行的物理存储顺序。在聚集索引中，数据行是按照索引列的顺序存储的。

2. 唯一性：一个表只能有一个聚集索引，因为它定义了数据行的物理顺序。

3. 性能：聚集索引通常可以提供更快的数据检索速度，因为它直接指向数据行的物理位置。

4. 使用场景：当表中的大部分查询都基于一个特定的列或列组合时，聚集索引特别有用。

5. 更新开销：由于聚集索引定义了数据的物理存储顺序，因此对数据的插入、删除和更新操作可能会更昂贵，因为可能需要移动数据行来维护索引的顺序。

非聚集索引（Nonclustered Index）

1. 数据存储方式：非聚集索引不改变数据行的物理存储顺序。它在数据表的顶部创建一个索引结构，该结构包含索引列的值和指向数据行的指针。

2. 唯一性：一个表可以有多个非聚集索引，因为它们不定义数据的物理顺序。

3. 性能：非聚集索引可能需要额外的查找步骤来检索数据，因为首先需要查找索引，然后通过索引中的指针找到数据行。这可能会比聚集索引慢一些，但仍然可以显著提高查询性能。

4. 使用场景：当需要基于表中的多个列进行查询，且这些查询不涉及表中的所有行时，非聚集索引非常有用。

5. 更新开销：非聚集索引的更新开销通常比聚集索引小，因为它们不需要移动数据行来维护索引顺序。但是，它们需要维护索引结构本身。

包含列（Included Columns）

• 非聚集索引：在非聚集索引中，可以指定“包含列”，这些列不是索引键的一部分，但它们的值被存储在索引结构中。这可以减少对数据表的额外查找，从而提高查询性能。

总结

• 聚集索引：定义了数据行的物理存储顺序，一个表只能有一个聚集索引，适合于大多数查询都基于同一个列或列组合的情况。

• 非聚集索引：不改变数据行的物理存储顺序，一个表可以有多个非聚集索引，适合于需要基于多个列进行查询的情况。

SQLserver使用sql语句创建主键，外键，唯一约束，自增

在 SQL Server 中，可以通过 SQL 语句在创建表时或在现有表上添加约束，如主键（PRIMARY KEY）、外键（FOREIGN KEY）、唯一约束（UNIQUE）和自增（IDENTITY）。以下是如何使用 SQL 语句来实现这些约束的示例。

创建主键（PRIMARY KEY）

主键用于在表中定义一个或多个列，这些列的组合必须是唯一的，并且不能包含 NULL 值。

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

创建外键（FOREIGN KEY）

外键用于在两个表之间建立链接，确保引用的数据完整性。

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    EmployeeID INT,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

在这个例子中，Orders 表中的 EmployeeID 列引用了 Employees 表中的 EmployeeID 列。

创建唯一约束（UNIQUE）

唯一约束确保列中的所有值都是唯一的。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) UNIQUE
);

创建自增列（IDENTITY）

自增列用于自动生成唯一的值，通常用于主键。

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

在这个例子中，EmployeeID 列被设置为自增列，起始值为 1，每次增加 1。

在现有表上添加主键

ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

在现有表上添加外键

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Employees FOREIGN KEY (EmployeeID)
REFERENCES Employees(EmployeeID);

在现有表上添加唯一约束

ALTER TABLE Products
ADD CONSTRAINT UQ_Products_ProductName UNIQUE (ProductName);

在现有表上添加自增属性

-- 首先，如果列已经存在，你需要先删除它
ALTER TABLE Employees DROP COLUMN EmployeeID;
​
-- 然后，重新添加列并设置为自增
ALTER TABLE Employees
ADD EmployeeID INT IDENTITY(1,1);

请注意，自增属性只能在创建列时设置，不能在现有列上修改。如果你需要将现有列设置为自增，你必须先删除该列，然后重新创建它。

注意事项

• 在创建外键时，被引用的列（在本例中为 Employees 表的 EmployeeID 列）必须已经定义为主键或具有唯一约束。

• 在创建唯一约束时，被约束的列不能包含重复的值。

• 在设置自增属性时，你可以选择起始值和增量。如果不指定，SQL Server 将默认从 1 开始，增量为 1。

• 在添加或修改表结构时，确保考虑数据完整性和性能影响。