什么是sqlserver中的集群和非集群索引，为什么我们使用它以及如何使用它？ [英] What is cluster and noncluster index in sqlserver why we use and how to use it ?

问题描述

大家好我被困在群集和非群集索引中我读了不同的关于它的文章，但我仍然不清楚它请帮我清除它



i创建了一个表格，如

hello guys i am stuck in cluster and non cluster index i am read different articals about it but still i am not clear on it please help me to clear it

i have create one table like

create table Student (SId int not null identity(1,1) primary key,Firstname nvarchar(50),Lastname nvarchar(50),Gender nvarchar(6),MobileNo decimal(11,0),Address nvarchar(300),City nvarchar(50),DOB datetime, created DATETIME not null DEFAULT(getdate()));

我是什么尝试过：



i有一个学生表我已经为下面给出的集群和非集群索引写了查询



在学生（名字）上创建聚集索引Student_ind;



当我在查询上运行时我显示错误：无法创建多个聚簇索引在桌上'学生'。在创建另一个之前删除现有的聚集索引PK__Student__CA195950D791869D。



但是我第一次创建集群然后出现此错误的原因



在学生上创建索引Student_no_ind（已创建）



执行时我已使用此命令

exec SP_HELP学生



i也得到输出但我不明白它是什么类型的输出

What I have tried:

i have a Student Table i have wrote query for cluster and non cluster index given below

create clustered index Student_ind on Student(Firstname);

when i am run above query i have shown a error :Cannot create more than one clustered index on table 'Student'. Drop the existing clustered index 'PK__Student__CA195950D791869D' before creating another.

but i have create cluster first time then why this error occur

create index Student_no_ind on Student(created)

for execution i have used this command
exec SP_HELP Student

i have get output also but i dont understand it what kind of output it is

推荐答案

实际上是一个聚集索引定义记录在磁盘上的存储顺序;因此，您只能拥有一个聚簇索引。非聚集索引存储在磁盘的单独部分上，并指向磁盘上的实际记录，这就是为什么您可以拥有多个非聚集索引的原因。



您的错误应该非常清楚：无法在表'Student'上创建多个聚簇索引。在创建另一个之前删除现有的聚簇索引'PK__Student__CA195950D791869D'。



您已经有了聚簇索引，它是您的主键。因为它具有奇怪的名称，这意味着您没有手动创建它，但是由于创建了主键，因此为您创建了聚簇索引。

A clustered index actually defines what order the records are stored on disk; hence you can only have one clustered index. A non-clustered index is stored on a separate part of the disk and points to the actual records on disk which is why you can have more than one non-clustered index.

Your error should be pretty clear: "Cannot create more than one clustered index on table 'Student'. Drop the existing clustered index 'PK__Student__CA195950D791869D' before creating another."

You already have a clustered index, it is your primary key. Because it has that weird name it means you did not create it manually, but by virtue of creating your primary key the clustered index was created for you.

这篇关于什么是sqlserver中的集群和非集群索引，为什么我们使用它以及如何使用它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！