What is the logic behind it that maximum no of clustered index on any
table is 249?On Oct 23, 10:12 pm, vinod...@.gmail.com wrote:
> What is the logic behind it that maximum no of clustered index on any
> table is 249?
I'm assuming you are referring to either the 249 limit on maximum XML
indexes or the maximum non-clustered indexes. This link might be
helpful in explaining the reasoning behind the limit.
http://msdn2.microsoft.com/en-us/library/ms191497.aspx
Regards,
Enrique Martinez
Sr. Software Consultant
Showing posts with label clustered. Show all posts
Showing posts with label clustered. Show all posts
Monday, March 19, 2012
Sunday, February 19, 2012
help: indexed view question
I have created a unique clustered index on a view.
The view does a GROUP BY on 3 of the columns and
uses the COUNT_BIG aggregate function.
I used the following SET commands before creating the view and the index:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
I can insert and delete rows from the base table, and the indexed view is updated fine.
However, when a scheduled job does effectively the same thing (delete some rows, and insert some new rows) I get the following error:
Executed as user: NT AUTHORITY\SYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Why am I getting this error?
The same SET commands above are in the Transact-SQL code for the job before the delete and before the insert statements.
Thanks,
TomYES!!!!! I found a simple solution!!!
I simply put a GO after the SETs.
Although the code was not exactly in a stored procedure I suppose it acted like it was (the code was in a T-SQL script as one of the steps of a SQL Server agent job in Management Studio).
It may not sound like much, but this will help save 1 hour a month for an end user ... for probably 3-4 years!
Thanks Dan Guzman (SQL Server MVP) and Alex Kuznetsov!!!
:beer:
The view does a GROUP BY on 3 of the columns and
uses the COUNT_BIG aggregate function.
I used the following SET commands before creating the view and the index:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
I can insert and delete rows from the base table, and the indexed view is updated fine.
However, when a scheduled job does effectively the same thing (delete some rows, and insert some new rows) I get the following error:
Executed as user: NT AUTHORITY\SYSTEM. DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Why am I getting this error?
The same SET commands above are in the Transact-SQL code for the job before the delete and before the insert statements.
Thanks,
TomYES!!!!! I found a simple solution!!!
I simply put a GO after the SETs.
Although the code was not exactly in a stored procedure I suppose it acted like it was (the code was in a T-SQL script as one of the steps of a SQL Server agent job in Management Studio).
It may not sound like much, but this will help save 1 hour a month for an end user ... for probably 3-4 years!
Thanks Dan Guzman (SQL Server MVP) and Alex Kuznetsov!!!
:beer:
Subscribe to:
Posts (Atom)