SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 View vs Index View...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jscot
Posting Yak Master

106 Posts

Posted - 02/24/2013 :  22:59:57  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/24/2013 :  23:38:46  Show Profile  Reply with Quote
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 follow

see here for full list

http://msdn.microsoft.com/en-us/library/ms191432.aspx#Restrictions

we 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

jscot
Posting Yak Master

106 Posts

Posted - 02/24/2013 :  23:53:02  Show Profile  Reply with Quote
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....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/25/2013 :  00:00:41  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
371 Posts

Posted - 02/27/2013 :  17:55:35  Show Profile  Reply with Quote
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.

Edited by - ScottPletcher on 02/27/2013 17:56:07
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000