Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karan
karanlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert
|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegr oups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>
|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert
|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>
|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexing and partitioning of the tables.

We have 6 lac row records which will be increasing with time. So for
optimized performance please provide solution related to partitioning
and indexing of tables.
Is partitioning necessary for optimization of performance, keeping in
mind , future growth of records.
Karankaranlobana@.gmail.com wrote:
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
Disclaimer: haven't use partitioning in SQL 2005 myself - only on Oracle
instances.
It depends on the volume and the queries you do. If volume is rather high
and queries always include a specific narrow time range then it's likely
that your app will benefit from partitioning.
robert|||Forgive me but I don't know what "lac" means but please note that
partitioning in 2005 is primarily for data management not performance. That
does not mean that you can not get increased performance from a proper
partitioning scheme but that is usually not the primary goal. Partitioning
allow you to manage very large amounts of data much more easily and
efficient than if they were all in a single table or partition.
--
Andrew J. Kelly SQL MVP
<karanlobana@.gmail.com> wrote in message
news:1137392219.125403.291600@.g14g2000cwa.googlegroups.com...
> We have 6 lac row records which will be increasing with time. So for
> optimized performance please provide solution related to partitioning
> and indexing of tables.
> Is partitioning necessary for optimization of performance, keeping in
> mind , future growth of records.
> Karan
>|||Andrew J. Kelly wrote:
> Forgive me but I don't know what "lac" means but please note that
> partitioning in 2005 is primarily for data management not
> performance. That does not mean that you can not get increased
> performance from a proper partitioning scheme but that is usually not
> the primary goal. Partitioning allow you to manage very large amounts
> of data much more easily and efficient than if they were all in a
> single table or partition.
Oracle's optimizer restricts access to only some partitions if he can
determine from the table, partitioning set up and indexing that this is
more efficient. Does the optimizer in SQL 2005 not do this? I browsed
BOL but couldn't find anything definitive on the matter.
Kind regards
robert|||Absolutely. If the WHERE clause is such that it can determine based on the
partitioning scheme which partition(s) the data resides and the indexing is
aligned with the partitions it will only search those partitions. That is
and of itself does not guarantee better performance. It may or it may not.
It is just as fast to seek an individual row from a single partition out of
many as it is from one very large table, assuming it was properly indexed to
begin with. So it depends on exactly what they are doing and how.
--
Andrew J. Kelly SQL MVP
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eAQ0kzqGGHA.1124@.TK2MSFTNGP10.phx.gbl...
> Andrew J. Kelly wrote:
>> Forgive me but I don't know what "lac" means but please note that
>> partitioning in 2005 is primarily for data management not
>> performance. That does not mean that you can not get increased
>> performance from a proper partitioning scheme but that is usually not
>> the primary goal. Partitioning allow you to manage very large amounts
>> of data much more easily and efficient than if they were all in a
>> single table or partition.
> Oracle's optimizer restricts access to only some partitions if he can
> determine from the table, partitioning set up and indexing that this is
> more efficient. Does the optimizer in SQL 2005 not do this? I browsed
> BOL but couldn't find anything definitive on the matter.
> Kind regards
> robert
>|||Andrew J. Kelly wrote:
> Absolutely. If the WHERE clause is such that it can determine based
> on the partitioning scheme which partition(s) the data resides and
> the indexing is aligned with the partitions it will only search those
> partitions. That is and of itself does not guarantee better
> performance. It may or it may not. It is just as fast to seek an
> individual row from a single partition out of many as it is from one
> very large table, assuming it was properly indexed to begin with. So
> it depends on exactly what they are doing and how.
Thanks for the explanation and verifying that my expectations are met! Of
course performance in the end still depends on what query is actually
executed.
Cheers
robert

Indexed Views over a Partitioning scheme table

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
Eric"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> 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.
>
Yes.
drop view iv_T
drop table t
drop partition scheme ps1
drop partition function pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ( [primary] );
create table t(id int primary key, description varchar(50) null)
on ps1(id)
go
insert into t(id,description)
values (1,'a')
insert into t(id,description)
values (2,null)
go
create view iv_T
with schemabinding
as
select id, description
from dbo.t where description is not null
go
create unique clustered index ix_iv_t
on iv_T(id)
on [primary]
go
select * from t
David|||Hi David, thanks for the quick reply.
Oddly your syntax isn't much different then what I hadâ?¦ Though I did notice
that you had used a single file group. (Though that shouldn't make a
difference right?)
Thanks
Eric
"David Browne" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> > 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.
> >
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||Sorry in addition, I have a calculation in my view that needs to be there
seeing that the view is being generated at a higher level then the partition
tables grain level.
Here is the code to reproduce the error:
--start code
drop view iv_T
drop table t
drop PARTITION SCHEME ps1
drop PARTITION FUNCTION pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000,10000,100000,1000000,10000000);
go
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ([primary])
go
create table t(id int primary key, description varchar(50) null, amt int null)
on ps1(id)
insert into t(id,description,amt)
values (1,'a', 100)
insert into t(id,description,amt)
values (200,'a', 100)
insert into t(id,description,amt)
values (3000,'b', 100)
insert into t(id,description,amt)
values (40000,'b', 100)
insert into t(id,description,amt)
values (500000,'c', 100)
go
create view iv_T
with schemabinding
as
select description, isnull(sum(amt),0) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
go
--end code
thanks
eric
"David Browne" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> > 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.
> >
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
> Sorry in addition, I have a calculation in my view that needs to be there
> seeing that the view is being generated at a higher level then the
> partition
> tables grain level.
> Here is the code to reproduce the error:
> --start code
> drop view iv_T
> drop table t
> drop PARTITION SCHEME ps1
> drop PARTITION FUNCTION pf1
> go
>
Try this eqivilent formulation:
create view iv_T
with schemabinding
as
select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
David|||Wow! I think I won the dummy of the year award... :)
That was it!
Do you realize how many hours I sat here trying to figure this out? Lets
just say Iâ'm on my 12 red bull and Iâ'm starting to get a sun burn from my
monitorâ?¦LOL.
Thanks David
Eric
"David Browne" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
> > Sorry in addition, I have a calculation in my view that needs to be there
> > seeing that the view is being generated at a higher level then the
> > partition
> > tables grain level.
> >
> > Here is the code to reproduce the error:
> > --start code
> > drop view iv_T
> > drop table t
> > drop PARTITION SCHEME ps1
> > drop PARTITION FUNCTION pf1
> > go
> >
>
> Try this eqivilent formulation:
> create view iv_T
> with schemabinding
> as
> select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
> from dbo.t where description is not null
> group by description
> go
> create unique clustered index ix_iv_t
> on iv_T(description)
> on [primary]
> David
>
>

Indexed Views over a Partitioning scheme table

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?Prod...ex&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?Prod...68&LinkId=20476
Any thoughts
Thanks
Eric"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> 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.
>
Yes.
drop view iv_T
drop table t
drop partition scheme ps1
drop partition function pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ( [primary] );
create table t(id int primary key, description varchar(50) null)
on ps1(id)
go
insert into t(id,description)
values (1,'a')
insert into t(id,description)
values (2,null)
go
create view iv_T
with schemabinding
as
select id, description
from dbo.t where description is not null
go
create unique clustered index ix_iv_t
on iv_T(id)
on [primary]
go
select * from t
David|||Hi David, thanks for the quick reply.
Oddly your syntax isn't much different then what I had… Though I did notic
e
that you had used a single file group. (Though that shouldn't make a
difference right?)
Thanks
Eric
"David Browne" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||Sorry in addition, I have a calculation in my view that needs to be there
seeing that the view is being generated at a higher level then the partition
tables grain level.
Here is the code to reproduce the error:
--start code
drop view iv_T
drop table t
drop PARTITION SCHEME ps1
drop PARTITION FUNCTION pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000,10000,100000,1000000,10000000);
go
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ([primary])
go
create table t(id int primary key, description varchar(50) null, amt int nul
l)
on ps1(id)
insert into t(id,description,amt)
values (1,'a', 100)
insert into t(id,description,amt)
values (200,'a', 100)
insert into t(id,description,amt)
values (3000,'b', 100)
insert into t(id,description,amt)
values (40000,'b', 100)
insert into t(id,description,amt)
values (500000,'c', 100)
go
create view iv_T
with schemabinding
as
select description, isnull(sum(amt),0) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
go
--end code
thanks
eric
"David Browne" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
> Sorry in addition, I have a calculation in my view that needs to be there
> seeing that the view is being generated at a higher level then the
> partition
> tables grain level.
> Here is the code to reproduce the error:
> --start code
> drop view iv_T
> drop table t
> drop PARTITION SCHEME ps1
> drop PARTITION FUNCTION pf1
> go
>
Try this eqivilent formulation:
create view iv_T
with schemabinding
as
select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
David|||Wow! I think I won the dummy of the year award...
That was it!
Do you realize how many hours I sat here trying to figure this out? Lets
just say I’m on my 12 red bull and I’m starting to get a sun burn from m
y
monitor…LOL.
Thanks David
Eric
"David Browne" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
>
> Try this eqivilent formulation:
> create view iv_T
> with schemabinding
> as
> select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
> from dbo.t where description is not null
> group by description
> go
> create unique clustered index ix_iv_t
> on iv_T(description)
> on [primary]
> David
>
>