Indexed view over partitioned table

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Indexed view over partitioned table

Author

Topic

datamonkey
Starting Member

29 Posts

Posted - 2006-05-09 : 16:06:26

Hello all, I was wondering if anyone as found any information on weather or not you can place a index view over a partitioning scheme in SQL server 2005.

I have successfully setup a 89 partitioned scheme in SQL 2005 (that works well) though I also need to build a materialized view over that newly partitioned data. Oddly I continue to receive an error when I attempt to place the clustered index on the view.

ERROR:
TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for Index 'cidx_client_monthcode'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Index&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot create the clustered index 'cidx_client_monthcode' on view 'Partition_DB.dbo.mv_SummarybyClientAndMonth' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list. (Microsoft SQL Server, Error: 8668)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=8668&LinkId=20476

Any thoughts

Thanks
datamonkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources