August 8, 2003 at 4:29 pm
Can you put an index on a column in a view?
Thanks
Aurora
August 8, 2003 at 9:45 pm
No, you put the index on the column in the underlying table.
You CAN create indexed views (which materialises the view), but you need Enterprise Edition and a number of ANSI SET options in effect, the view must comply with a bunch of requirements, and the first index must be clustered unique.
Cheers,
- Mark
Cheers,
- Mark
August 10, 2003 at 6:43 pm
Actually indexed views are a new feature of SQL Server 2000. I haven't actually used it, but it looks like the overhead would be significant. In essence creates a table for the view that it then indexes. Whenever the underlying tables in the view are changed, the data in the new table is changed to remain current. Take a look in books online in the index entry "Indexed Views" if you're running SQL Server 2000...
August 11, 2003 at 6:07 am
In some cases it can make a lot of difference - you're doing the work once rather than each time the query is run.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply