Why do we need nonclustered indexes? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Why do we need nonclustered indexes?

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2009-09-30 : 02:00:02

Hi,

My question is basically, why do we need nonclustered indexes if we already have a clustered index on a table?

To elaborate, I understand that in a clustered index the root, intermediate, and leaf nodes consists of data pages. I assume that because the leaf node is a data page, the data is immediately accessible when attempting to locate the record. This is why clustered indexes generally enhance the performance of the database (correct me if I'm wrong).

In a nonclustered index, the leaf node is not a data page, but a pointer to the record (file identifier, page number, and row number). If a clustered index exists on the table, it will use the clustered index value.

At this point, this is where things are not clear to me. My questions are:

1. If a nonclustered index automatically uses the clustered index value, why do we need a nonclustered index?

2. Wouldn't it be faster to locate the row(s) by utilizing a pointer to the actual record(s) rather than search through a list of pages? I'm guessing the answer to this is that it is easier to locate a group of records which are close in proximity, therefore a clustered index is faster.

T.I.A

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-30 : 02:43:33

[code]
AcctNo Lastname Firstname Nickname
4711 Meyer Frank Cheetah
5812 Adams Eugene BillYakHunter

clustered index on AcctNo

nonclustered index on Nickname (points to clustered index Acctno)

Question: Why do we need nonclustered indexes?
Answer is a question: How would you search for the Nickname via index WITHOUT nonclustered index?
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-30 : 03:04:46

quote:
Originally posted by under2811

1. If a nonclustered index automatically uses the clustered index value, why do we need a nonclustered index?

Same reason that indexes at the back of computer manuals are useful even though they just give the page number reference and not the information that's been looked for.

--
Gail Shaw
SQL Server MVP

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2009-09-30 : 07:01:23

How many non clustered index have on single table in DB..sometimes i got information that it should be 250 or 1 clustered index and 249 non clustered and sometimes i got 255???

which one is correct

T.I.A

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 07:32:12

Write a test case?

USE TempDB
GO

CREATE TABLE	#Temp
		(
			i INT NOT NULL
		)

DECLARE	@IndexNum SMALLINT = 0,
	@SQL VARCHAR(MAX)

WHILE @@ERROR = 0
	BEGIN
		SELECT	@IndexNum += 1,
			@SQL = 'CREATE NONCLUSTERED INDEX IX_' + CAST(@IndexNum AS VARCHAR(10)) + ' ON #Temp(i)'
		
		PRINT	@SQL
		EXEC	(@SQL)
	END

DROP TABLE	#Temp

N 56°04'39.26"
E 12°55'05.63"

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2009-09-30 : 08:28:58

Hi
I am getting below error.. :(

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 12
Must declare the scalar variable "@IndexNum".
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@SQL".
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@SQL".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 08:46:11

Which version of SQL Server are you using?

USE TempDB
GO

CREATE TABLE	#Temp
		(
			i INT NOT NULL
		)

DECLARE	@IndexNum SMALLINT,
	@SQL VARCHAR(MAX)

SET	@IndexNum = 0

WHILE @@ERROR = 0
	BEGIN
		SELECT	@IndexNum = @IndexNum + 1,
			@SQL = 'CREATE NONCLUSTERED INDEX IX_' + CAST(@IndexNum AS VARCHAR(10)) + ' ON #Temp(i)'
		
		PRINT	@SQL
		EXEC	(@SQL)
	END

DROP TABLE	#Temp

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 08:49:45

quote:
Originally posted by under2811

How many non clustered index have on single table in DB..sometimes i got information that it should be 250 or 1 clustered index and 249 non clustered and sometimes i got 255???

which one is correct

T.I.A

Read about Maximum Capacity Specifications in sql server help file

Madhivanan

Failing to plan is Planning to fail

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2009-09-30 : 08:57:22

[quote]Originally posted by Peso

Which version of SQL Server are you using?[code]

sql server 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 09:00:57

Then see my revised code posted 09/30/2009 : 08:46:11

N 56°04'39.26"
E 12°55'05.63"

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2009-09-30 : 09:22:36

Msg 1910, Level 16, State 1, Line 1
Could not create nonclustered index 'IX_250' because it exceeds the maximum of 249 allowed per table or view.

Thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 09:25:03

You're welcome.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources