While you have your book open... is it true that clustered index views are only supported in the enterprise edition of SQL server?
On 8/24/05, Adrian Lynch <[EMAIL PROTECTED]> wrote: > Good question! I was just reading about it today while studying for MS's > 70-229 exam, so here goes nothing... > > Taking an example from the book: > > CREATE VIEW MyView WITH SCHEMABINDING AS > SELECT MyColumn, MyOtherColumn > FROM dbo.MyTable > WHERE MyColumn = 'Some Value' > > Some notes on creating indexed views: > - The view must specify SCHEMABINDING > - You must use owner.object syntax > - You can't use * (ALL) > - You can't have derived tables > - No UNION, OUTTER JOIN, sub queries or self joins > - No TOP or ORDER BY > - No COUNT(*), instead use COUNT_BIG(*), although I'm not sure if it's the > use of the * or COUNT() that's the problem > - No aggregate functions AVG, MAX, MIN > - The following need also be true: > - NUMERIC_ROUNDABORT set to OFF > - ANSI_NULLS set to ON > - ANSI_PADDING set to ON > - ARITHABORT set to ON > - CONCAT_NULL_YIELDS_NULL set to ON > - QUOTED_IDENTIFIER set to ON > - The first index created on the view must be unique clustered > - There after non-clustered can be added > - Removing the clustered index will remove the non-clustered indexes > > Then to create the index: > > CREATE CLUSTERED INDEX MyClusteredIndex ON MyView (MyColumn, MyOtherColumn) > > Now how's that for simplicity?! > > Probably of no help to you but it's revision for me :OD > > Ade > > -----Original Message----- > From: Ian Skinner [mailto:[EMAIL PROTECTED] > Sent: 24 August 2005 22:57 > To: CF-Talk > Subject: RE: SQL/Oracle Indexing a temporary view. > > > Adrian > > Can you share what that SQL might look like, under the hope that Oracle may > use the same syntax. Not a large hope, but hope none the less. > > I looked up the Oracle (8i) CREATE INDEX documentation and quickly got very > lost on all the variations and convolutions. > > > -------------- > Ian Skinner > Web Programmer > BloodSource > www.BloodSource.org > Sacramento, CA > > "C code. C code run. Run code run. Please!" > - Cynthia Dunning > > Confidentiality Notice: This message including any > attachments is for the sole use of the intended > recipient(s) and may contain confidential and privileged > information. Any unauthorized review, use, disclosure or > distribution is prohibited. If you are not the > intended recipient, please contact the sender and > delete any copies of this message. > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:216287 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
