Creating index

Question

Creating index

kapil_kk

SSC-Insane

Points: 21316
More actions
November 3, 2012 at 5:28 am

#269665

I want to create non clustered index on a view to check the limit that how many non clustered index can be created on a view. To do that I write a query but its not working and showing error:
declare @a int=2
while @a<2056
create nonclustered index ('testindex' + @a) on dbo.[item_vw]
(
order_no
)
set @a=@a+1
plz suggest..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

kapil_kk (11/3/2012)
I want to create non clustered index on a view to check the limit that how many non clustered index can be created on a view. To do that I write a query but its not working and showing error:
declare @a int=2
while @a<2056
create nonclustered index ('testindex' + @a) on dbo.[item_vw]
(
order_no
)
set @a=@a+1
plz suggest..

You'll need to change the whole CREATE statement to dynamic SQL and the execute the dynamic SQL.

--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)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Jeff,

I dont have any idea about dynamic SQL so can you plz help me in this...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

declare @a int=1

declare @lsql nvarchar(300)

set @lsql = ''

while @a<2056

begin

set @lsql = ''

set @lsql = 'create nonclustered index testindex_' + cast (@a as nvarchar(100)) + ' on dbo.[item_vw] (order_no) '

print @lsql

exec (@lsql )

set @a=@a+1

end i havent tried this but it should work.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sean Pearce SSCoach Points: 15999 More actions · Answer 4

kapil_kk (11/3/2012)
I want to create non clustered index on a view to check the limit that how many non clustered index can be created on a view.

Why?

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 5

Artoo22 (11/12/2012)
kapil_kk (11/3/2012)
I want to create non clustered index on a view to check the limit that how many non clustered index can be created on a view.
Why?

i think it's the difference between reading about the maximum number of indexes(999), and actually experiencing the errors you get when you create index 1000;

it makes you a better developer or DBA when you truly understand a boundary or limitation.

plus the headaches you go through creating an indexed view...I can definitely relate.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!