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.
| Author |
Topic |
|
iamsmahd
Yak Posting Veteran
55 Posts |
 Posted - 2005-09-29 : 00:57:42
|
| hi there...suppose I have a table (t1) which it has 3 columns (col1, col2, col3).now, on the table, I choose col1 as primary key (so clustered index) and also col2 as another index (non clustered.)Now, i wanna to know that if i create a view (v1) that contains col2, and col3...is it correct that my view (v1) has an index because of using col2?please tell me your ideas.....thank yousonia |
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-29 : 01:03:21
|
| No, you must explicitly create an index on the view. See SQL Server Books Online for details on what's required to create an indexed view (SKU requirements, SCHEMABINDING, view defintion, etc.).Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-29 : 01:07:03
|
| Ryan,I'm curious to know if there is a benefit to having an index on a view JUST for a column that is also indexed on the underlying table? (Coz I've never thought to do that!)ThanksKristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-09-29 : 01:26:10
|
| Ryan, If the column of a table has index, then why cant the View use that index if that column is part of that view?MadhivananFailing to plan is Planning to fail |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-09-29 : 02:02:35
|
| There appears to be a small comms issue...Ryan explicitly answered the poster. The view does not HAVE an index. But it will USE the table index.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-29 : 02:24:25
|
| Yup, that's how I read it David, but I'm still curious to know if there would be a benefit (from a technical viewpoint, if not for use in the real world!)Kristen |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-29 : 11:18:22
|
quote:
Originally posted by Kristen
Ryan,I'm curious to know if there is a benefit to having an index on a view JUST for a column that is also indexed on the underlying table? (Coz I've never thought to do that!)ThanksKristen
I can't think of any scenarios where this would help, to be honest. If the view isn't any more complex than a simple column projection from the base table (i.e., 'select c2 from foo'), then it doesn't strike me as very useful.Thanks,----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
ryanston
Microsoft SQL Server Product Team
89 Posts |
Posted - 2005-09-29 : 11:19:59
|
quote:
Originally posted by madhivanan
Ryan, If the column of a table has index, then why cant the View use that index if that column is part of that view?MadhivananFailing to plan is Planning to fail
David seems to have cleared things up for me -- thanks David  The optimizer will simply "out-smart" your query and resolve the view to its parts: rather than select * from view, where view is defined as something like 'select c2 from base_table', the optimizer will actually execute 'select c2 from base_table'.----------------------Ryan StonecipherDeveloper, Microsoft SQL Server Storage Engine, DBCC(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-29 : 12:11:54
|
| That's good to hear. I was not looking forward to rewriting all our views for "yet more performance" if there was a Smart Wrinkle to be had Thanks chaps,Kristen |
|
|
|
|
|
|
|
|
table, index, view