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 |
|
jscot
Posting Yak Master
106 Posts |
 Posted - 2013-02-24 : 22:59:57
|
| Hi Gurus. I know i can Google it and i can get the answers however i would like to know your thoughts. Please Correct me if i am wrong.If i am not wrong we can't create Index (Clustered or non clustered ) on VIEW. Am i right?What is the difference View & Indexed View?This question always click in my mind and i would like to clear it. Let say i have table with any Primary key and Clustered Index. Is we can create a Non Clustered Index on that table?Thank You in advance.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-24 : 23:38:46
|
| yep..you can index a view main difference is Indexed view persists ie field values are stored whereas normal view is a virtual table ie only definition exists not actual data.For a view to be made indexed, there are few additional rules it has to followsee here for full listhttp://msdn.microsoft.com/en-us/library/ms191432.aspx#Restrictionswe can as many non clustered index as we want on table with clustered index. Only thing to note is good amount thoughts have to be given before we add an index to make sure that query actually benefit out of it. Also remember addition of each index will add up to space for storing index information and also required modifications to be performed for each DML operation on main table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
jscot
Posting Yak Master
106 Posts |
Posted - 2013-02-24 : 23:53:02
|
| Visakh, Thanks for reply. From your reply i have two questions. 1) There is two different thing View and Index View am i right? And we can create a Index (Clustered or Non Clustered Index) on Indexed View Not on view. Yes Or Not?2) If the table Don't have Clustered Index, we can't create Non Clustered Index on it Yes Or No?Thank You.... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-25 : 00:00:41
|
| 1. Not fully correct. On a view when you add an index it becomes indexed view and value gets stored physically. But for doing that view has to follow certain additional rules as per link i posted. All views cannot be made indexed and stored physically so far as they dont follow all the above rules.2. Nope..you can still create a nonclustered index on table without clustered index. The only difference in that case is Non clustered index points to row identifier (RID) stored internally to find out record rather than using clustered index if one was present.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-02-27 : 17:55:35
|
| 1A) There is two different thing View and Index View am i right? Yes, they're different, because, as noted earlier: a view is not physically stored, and an indexed view is.1B) And we can create a Index (Clustered or Non Clustered Index) on Indexed View Not on view. Yes Or Not?You can create an index on a view: that's how an "indexed view" is created.The first index you create on a view must be a unique, clustered index. After that clus index is built, you can build nonclustered index(es) on that view also. |
|
|
|
|
|
|
View vs Index View...