Click here to Skip to main content
15,903,203 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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

SQL
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()));


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
Posted
Updated 9-Jun-16 2:06am
v2

1 solution

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.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900