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:216285
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
