why don't we have two clustered index in a table?

Question

Post reply

why don't we have two clustered index in a table?

thava

SSCrazy

Points: 2275
More actions
September 7, 2014 at 8:05 pm

#299152

Hi pros

i think yesterday is not my day,i went to an interview, the interviewer asked me some questions hopefully, i found the answers for the other things, but for the following one i am not able to get it, even though he gave me a answer i have some doubt on it,

ok here is the question

Q: we have more than one non clustered index in one table. Then, Why don't we have two clustered index in a table?:rolleyes::w00t:

I told him that it is to uniquely identify a row in a table (seriously i don't know the answer, but an interview point of view seems this is a basic question so i answered something), where as in non clustered index it depend upon the data

Q: Is there any other reason?:exclamation::crying:

well i am lost at this point, so i want the answer from him, he told me that, in cluster index the datas are stored where as in non clustered index the pointers are saved

:Wow: pointers, what kind of pointers are stored in non clustered index?

then what is the purpose of include statement in the create index statement?

( i don't want to argue with him, i want to discuss this , i think this is the right place), Actually is there any particular reason for having only one clustered index?is the answer is right?

please some one answer me or point me a link to this qustion?

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 1

In short: A clustered index can be thought of as the structure of the table, it can only have one structure at the time. In other words, it determines the physical order of the data in the table. A non-clustered index is stored separately from the physical data and therefore there can be many non-clustered indexes on a single table.

😎

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Apologies for the brutal honesty but, if you're interviewing for the position of DBA or Database Developer, you either need to hit the books hard or consider another career.

If you're interviewing for the position of a Web or other "front end" developer, you should curl up with a copy of "Books Online" (the "help" system that comes with SQL Server and learn as much as you can about indexes and the structure of indexes. It'll not only help you with interviews, it'll help you at your job... a lot.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

Orlando Colamatteo

SSC Guru

Points: 182276

September 8, 2014 at 12:04 am

#1743768

Here is a great place to start learning about indexes in SQL Server, from the beginning:

Stairway to SQL Server Indexes: Level 1, Introduction to Indexes[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

thava SSCrazy Points: 2275 More actions · Answer 4

Hi jeff;

Thanks, for the info, but switching my career is not possible, I think i know little bit of the clustered index and non clustered index, to me a clustered index is a binary tree with it leaf node contains data, where as a non clustered index is also a binary tree with index pages as its leaf node,

i am stumped there because of the question the interviewer asked?, May be i think i am not clear in my question there?

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 5

thava (9/8/2014)

Hi jeff;

Thanks, for the info, but switching my career is not possible, I think i know little bit of the clustered index and non clustered index, to me a clustered index is a binary tree with it leaf node contains data, where as a non clustered index is also a binary tree with index pages as its leaf node,

i am stumped there because of the question the interviewer asked?, May be i think i am not clear in my question there?

In SQL Server, the indexes are not binary trees (at most 2 children for a node), but balanced B-trees[/url].

Simply put: a clustered index IS the table. It organizes the data itself in a certain way.

Thus, you can have only one clustered index per table.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

thava (9/7/2014)

I told him that it is to uniquely identify a row in a table

Not at all, a clustered index doesn't have to be unique

(seriously i don't know the answer, but an interview point of view seems this is a basic question so i answered something)

If you'd been interviewing with me, that would have been the end of the interview right there. If you don't know something, admit that you don't know. Making stuff up is not going to impress the interviewer.

One thing I look for in people I interview is a willingness to admit when they're over their head and if someone makes stuff up when they don't know, I can't trust them to do so

:Wow: pointers, what kind of pointers are stored in non clustered index?

Pointers to the actual row (which is part of the clustered index)

then what is the purpose of include statement in the create index statement?

To add additional non-key columns to the index leaf level.

Actually is there any particular reason for having only one clustered index?

Yes, the clustered index is the table itself. Since the table only exists once, there's only one clustered index for it

is the answer is right?

His answer is.

to me a clustered index is a binary tree with it leaf node contains data, where as a non clustered index is also a binary tree with index pages as its leaf node,

Indexes are no binary trees. That would be remarkably inefficient.

http://qa.sqlservercentral.com/articles/Indexing/68439/

http://qa.sqlservercentral.com/articles/Indexing/68563/

http://qa.sqlservercentral.com/articles/Indexing/68636/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

thava SSCrazy Points: 2275 More actions · Answer 7

thava

SSCrazy

Points: 2275

September 8, 2014 at 1:52 am

#1743790

Thank you gail.

humbly accept all your advice?

Next time, i will try to do it in a better way.

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

I'm going to pile on with Jeff and strongly suggest you grab a couple of books and get the fundamentals more firmly entrenched in your head. Clustered and non-clustered indexes and how they work together should be pretty much a given for a DBA, database developer or data architect. And, I'm with Gail, I'd prefer the answer, "I don't know" to made up stuff. Better even than that, "I don't know, but here's how I'd find out..." would make me much more comfortable.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

EdVassie SSC Guru Points: 60312 More actions · Answer 9

Don't forget that SQL Server imposes the limit that a given table can only have one clustered index, but this limitation does not exist in all DBMSs. So when you answer the question, make sure you and the interviewer are thinking about the same DBMS.

Since 2004 DB2 has Multi-dimensional clustering, which allows multiple clustered indexes for a table. For many SQL Server folks, this seems an impossibility - how can a row be stored only once but the table physically clustered in multiple dimensions.

Consider a table with dimensions of shape, colour and size. In SQL Server we can choose one of these dimensions as the cluster index, and the remaining dimensions have to be dealt with using a non-cluster index.

DB2 handles multi-dimensional clustering by dedicating an entire extent to a given intersection of attributes. In the example above, one (or more) extent will be dedicated to square red large rows, and another extent dedicated to round blue small rows. If a given intersection of attributes does not exist, no extent is dedicated to it. The order of extents within the data file is not relevant in both DB2 and SQL Server. The end result in DB2 is that a row is stored only once but the table is physically clustered in multiple dimensions.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara