Creating index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Creating index

Creating index

Rate Topic

Display Mode

Topic Options

Author

Message

kapil_kk

Posted Saturday, November 03, 2012 5:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1380699

Jeff Moden

Posted Saturday, November 03, 2012 10:03 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 32,923, Visits: 26,811

kapil_kk (11/3/2012)

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1380718

kapil_kk

Posted Sunday, November 04, 2012 11:23 PM

Ten Centuries

Group: General Forum Members
Last Login: Sunday, May 19, 2013 11:16 PM
Points: 1,061, Visits: 1,151

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

Post #1380881

Bhuvnesh

Posted Monday, November 05, 2012 12:29 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1380899

Sean Pearce

Posted Monday, November 12, 2012 3:49 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 2:27 AM
Points: 350, Visits: 1,340

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?

http://thesqlguy.blogspot.com/

Post #1383594

Lowell

Posted Monday, November 12, 2012 5:21 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:41 PM
Points: 11,648, Visits: 27,760

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1383626

« Prev Topic | Next Topic »

Permissions