question

Jenmar22 avatar image
Jenmar22 asked

Creating an index on an existing view

I am attempting to create a unique clustered index on an existing view that I previously created. My syntax is as follows; CREATE UNIQUE CLUSTERED INDEX Product ON dbo.CURRENT PRODUCT LIST (ProductName); GO I am getting the following error message; Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'CURRENT'. Can anyone correct my syntax so that this will work? Thank you in advance!
indexviewsyntax
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Jeff Moden avatar image
Jeff Moden answered
You need to read up on "Identifiers" in Books Online. You need to properly delimit identifiers that have "illegal" characters in them like spaces, which is exactly what you have in the code you posted. Wrap the table or view name in brackets to allow for such things. Open up "Books Online" and read about "Identifiers". As a bit of sidebar, adding an index to a view isn't a trivial task. There are more that 3 dozen restrictions for creating an Indexed View and there's a 99.9% probability that your current view violates at least one of the those restrictions. I strongly recommend that you curl up with a nice hot-tottie and read in Books Online about what you're getting into. If you don't actually know what "Books Online" is, it's the help system that you install with SQL Server. If you installed it, you can easily get there just by pressing the {f1} key while in SSMS. If you didn't or won't install it, then do a search on the internet for "Indexed Views SQL Server" and it should take you to the online version.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.