INFORMATION_SCHEMA Views and Indexed Views

Hi,
I wanted to write some stored procedures to help me manage my indexed views
in SQL2000/2008. Since I wanted to follow the advice to use the
INFORMATION_SCHEMA views instead of system tables/catalog views. I have
this query that will run in both 2000 and 2008 and it correctly finds my
indexed views:
select *
from sysobjects
where type = 'V'
and id in (select id from sysindexes);
However, if I run this query in either 2000 or 2008 , it returns nothing:
select * from INFORMATION_SCHEMA.VIEWS
where TABLE_NAME in (
select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
)
The SQL 2008 BOL states "Returns one row for each column that is constrained
as a key in the current database.", and SQL 2000 BOL state "Contains one row
for each column, in the current database, that is constrained as a key."
Does anyone have any sage advice on this topic, or should I post it as a
Connect issue for SQL 2008?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>
The INFORMATION_SCHEMA describes only the logical features of the database:
tables, columns, constraints. Not indexes because they are a physical
implementation construct and aren't part of standard SQL like the
INFORMATION_SCHEMA.
For index information you need sys.indexes and sys.index_columns, or
dbo.sysindexes and dbo.sysindexkeys. That's unless the index is one that
supports a constraint, in which case the same information will be in
INFORMATION_SCHEMA.
--
David Portas|||Someone will point out what's going on and for every issue you encounter
they'll tell you how you screwed up (though usually politely - unless you
get celko). I'll probably get blasted for this post. However, personally, I
think INFORMATION_SCHEMA sucks and that the SQL Server catalog tables are
far, far superior and thought out much better (esp. 2005).
While I'll blame Microsoft for the poor docs on it, I don't blame them for
the bulk of my issues with it as they just implimented the ANSI standard. I
just don't like it and don't think it was well thought out.
Jay
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
> However, if I run this query in either 2000 or 2008 , it returns nothing:
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>

INFORMATION_SCHEMA query question: constraint columns

Hi Folks:

I'm a little new to SQLServer, so please pardon my ignorance!

I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
TABLE_CONSTRAINTS. I'm looking for the views that will give me the list of
columns by constraint.

For instance, if Table1 has a unique key called Table1_UK01, I can find that
under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know the
columns in that UK constraint. I've tried
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this user
table doesn't seem to show up in either of those views.

Can anyone point me in the right direction? Any sample queries would be
tremendously appreciated. I'm going to be using this meta-data to
automatically generate quite a bundle of stored procs that do updates based
on finding rows via unique keys...

TIA,
DaveUnique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
constraint or an index? Use constraints and there shouldn't be a problem.
There is no physical difference between a unqiue constraint and a unique
index.

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<vpGdnZ-5heemC1Pd4p2dnA@.giganews.com>...
> Unique *constraints* will appear in both the CONSTRAINT_COLUMN_USAGE and
> KEY_COLUMN_USAGE views. Unique *indexes* however, will not. Did you create a
> constraint or an index? Use constraints and there shouldn't be a problem.
> There is no physical difference between a unqiue constraint and a unique
> index.

Yes, these are declared as constraints, not just indexes.

I think I've figured out the problem, but I don't know how to fix it.
The user tables are all owned by a user we created called "dw". The
docs say that these views return info about objects the current user
has access to. If I select current_user, I get "dbo". I notice that
the information_schema.constraint_column_usage only returns info about
constraints where the table is owned by dbo.

When I connect, I'm connecting (in Query Analyzer, for instance) as
user dw, but if I immediately select current_user, it shows me "dbo".
I'd assume if I can connect as "dw" rather than "dbo", I'll actually
see the constraint_column_usage meta-data for tables owned by "dw"
rather than "dbo".

So, how do I "get connected" as the user "dw" rather than "dbo".
Logging in as SQLServer authenticated user "dw" obviously isn't doing
the trick. Is there some sort of ALTER statement to change my
current_user? (This is SQLServer 7.0, btw).

TIA!
Dave|||Dave Sisk (dsisk@.nc.rr.com.0nospam0) writes:
> I'm a little new to SQLServer, so please pardon my ignorance!
> I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, and
> TABLE_CONSTRAINTS. I'm looking for the views that will give me the list
> of columns by constraint.
> For instance, if Table1 has a unique key called Table1_UK01, I can find
> that under INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to
> know the columns in that UK constraint. I've tried
> INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE and
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this
> user table doesn't seem to show up in either of those views.
> Can anyone point me in the right direction? Any sample queries would be
> tremendously appreciated. I'm going to be using this meta-data to
> automatically generate quite a bundle of stored procs that do updates
> based on finding rows via unique keys...

Rather than getting lost in the maze of the INFORMATION_SCHEMA views,
access the system tables directly. You will need to do that anyway if
you need information about indexes that are not constraints. Here is a
query:

SELECT i.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
AND ik.colid = c.colid
WHERE indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND indexproperty(i.id, i.name, 'IsStatistics') = 0
AND o.name = 'accountstats'
AND o.uid = USER_ID('dw')
ORDER BY i.name, ik.keyno

Gives you all indexes and their columns for this table. (It's possible
to constrain it to only unique constriaints, but I'm too lazy for that
now. Hint is that Unique constratins live in sysobjects too, and with
a parentobj = the object id for the table.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Information_Schema disappears?

Recently we had a development server on WIN2K running SQL 2K. We noticed that the Information_Schema views disappeared. We have no idea how. It appeared to be about the time most recent patches were run. However, patches were applied to many servers, and the others all have Information_Schema
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and reinstalled SQL2K. All patches were installed on both the operating system and sql
Information_Schema was present
We have not reinstalled
We have not rebooted
Information_Schema is now not present
Last week's queries against it now fail
Any ideas?It appears that the views are still in Master, but have owner dbo
They can only be accessed in Master, and don't have data for other db's
What would cause this
What can be done to correct
Thanks!|||Any ideas on this
It is truly vexing, and we'd appreciate some input here!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.

Information_Schema disappears?

Recently we had a development server on WIN2K running SQL 2K. We noticed t
hat the Information_Schema views disappeared. We have no idea how. It appea
red to be about the time most recent patches were run. However, patches were
applied to many servers, a
nd the others all have Information_Schema.
We reinstalled (needed an os upgrade) the OS bringing it up to Win2003, and
reinstalled SQL2K. All patches were installed on both the operating system
and sql.
Information_Schema was present.
We have not reinstalled.
We have not rebooted.
Information_Schema is now not present.
Last week's queries against it now fail.
Any ideas?It appears that the views are still in Master, but have owner dbo.
They can only be accessed in Master, and don't have data for other db's.
What would cause this?
What can be done to correct?
Thanks!|||Any ideas on this?
It is truly vexing, and we'd appreciate some input here!!
Thanks.|||I've never seen or heard about this. Assuming you have searched KB already,
this sounds like an MS Support case to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:E5C10653-0B4F-4136-AB59-51705B35FE2C@.microsoft.com...
> Any ideas on this?
> It is truly vexing, and we'd appreciate some input here!!
> Thanks.

Information Schema Views in SQL Server 2005

Hi

I am having a minor problem with SQL Server 2005.

I have a requirement that certain users (developers) should have visibility of certain schema information using either Management Studio, or from an application...

The problem is that when we do a query on the information schema views (from accounts that have db_DataReader and db_DataWriter and Public role membership) the column_default value is not returned, (a null value is returned) even if there is definately a default value constraint.

What permisions do I have to give these individuals, dbo is NOT an option as they should only have restriceted access to the Database.

I look forward to your answers.

Kind regards

Ronnie

As for as this one is queried within the schemaview you should either have access to the function
object_definition or the syscolums table, which you should have if you see the column names. (Don′t think that there is a more granular permission set than on the table)

object_definition(c.default_object_id)) AS COLUMN_DEFAULT,

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

The minimum permission required to view the default information for a table column is "ALTER" permission on the table.

Of course, ALTER also means that the principals with that permission can ALTER the table schema or even drop the table. If you wish to restrict that and want the principals to only view the table schema including the definition of the default constraint grant them "VIEW DEFINITION" permission on the table.

Please refer to the topic "Metadata visibilty configuration" in books online which explains this in greater detail.

http://msdn2.microsoft.com/en-us/library/ms187113.aspx

Hope that helps

Thanks

Asvin

|||Interesting point, I was afraid of metadata security in SQL 2k5, but why the hell did you connect it to ALTER ? This thing is not explained in the BOL article.

-Jens.|||

It is not always connected to ALTER. It depends on what kind of metadata you are looking for. If it's just the name or id of the table or procedure then any permission on the object is sufficient. We use a higher privilege like ALTER only when you need access to "business logic". By business logic I mean things like the body of the stored procedure, definition of a computed column, the definition of a default or check constraint.

Take a look at http://msdn2.microsoft.com/en-us/library/ms191507.aspx.

Indexing table-valued function?

I am using a multi-statement table-valued function to assemble data from several tables and views for a report. To do this, I INSERT data into the first few columns and then use UPDATEs to put data additional data into each row. Each UPDATE uses a WHERE criteria that identifies a unique row, based on the value of the first few columns.

The problem I'm having is that the UPDATEs are taking forever to execute. I believe the reason is that the temporary table that's created for the function is not indexed, so each row update requires a complete search of several columns.

In other situations I've been able to define one column as a primary key for the temporary table, but in this situation the primary key would have to consist of four columns, which doesn't seem to be allowed in the table definition for the function.

Is there any way to create indexes for the temporary tables that are created for multistatement table-valued functions? I think that would improve the UPDATE performance dramatically.

Thanks,

Lee Silverman
JackRabbit Sports

-Is it not possible to insert the data with one sql statement that would join all the needed tables together?

-Yes, temporary tables can be indexed just like regular tables.

Code Snippet

create unique clustered idex [IX_TestIndex] on #TmpTable (colA, colB, colC, colD)

Indexing on Views

1) We have SQL Server 2005 database on windows 2003 server.

2) What about columns in functions i.e. SUM(ABS(Col1)) OR SUM(Col2 * Col3). We create a view to select these and index on view on its computed AS column? Can we directly use vw1.AScol in query in place of these function expressions? Will the index be picked and how will cost improvement compare with that of index on normal columns.

3) Can statistics be used in such a situation?

its possible to put indexes on this columns...and it will be picked .... have a look at this link.:

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Indexing on Decryption Views

1) We have SQL Server 2005 on windows 2003 server.
2) In some columns of some tables, data is encrypted before being stored and indexes
have no meaning against these columns.
3) We have decryption views to select all columns of each & every table while decrypting
the encrypted ones.
4) All our code i.e. only queries is in stored procs. We use only decryption views in these
queries.
5) When encryted columns are involved in join or filter, query runs very slow and time outs
are occuring.
6) Indexes on decrypted columns will not work. Can indexes on decryption views against these
columns help? If so, how?
7) If so, please give an example creation of such an index on a view and its column OR post
a good link?

There are indexed views but, even if you could create an index on a view that decrypted the data, the decrypted data would be materialized and there would be no point in encrypting it in the first place.

You can create a variety of temporary tables into which you insert decrypted data which you could then index but SLOW gererally applies.

Query timeouts can be adjusted.

Indexes versus views

I am currently taking a class, SQL SErver 2000 Database Design and
Implementation (70-229) and have just finished reading about indexes. In
terms of their primary use, these seem closely related to 'views'. I am
unable to find any research on how to know when to use a 'view' versus an
'index'. I was disappointed that the text did not compare the two for
contrast. Can someone explain or direct me to information regarding their
differences?Indexes and views don't really have anything in common.
A view is a stored query that can be accessed like a table.
An index is sorted list of columns from your table (or indexed view, but
thats a different topic). This sorted list allows the database to quickly
locate a particular value in the indexed columns, and includes a pointer to
the actual row in the table. Indexes are used to speed up searches.
Unique indexes not only speed up searches but prevent duplicate values from
being inserted into the table.
A search on Google (or any good database book) will turn up much more in
depth explanations.
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
> I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||Did the instructor explain these two concepts? They are nothing at all
alike, and their use is quite different, so I would be surprised to see a
section comparing the two. It would be like having a section explaining the
difference between tables and SELECT statements.
An index is a physical structure that contains pointers to the data and
helps find the data you are looking for more quickly. In general, you don't
'use' an index. You create the indexes you need, and SQL Server chooses
whether or not to use them to help get to the data you are looking for.
A view is a way to save a SELECT statement so you don't have to retype it
every time, and can use the data returned by the view as if it were a table.
If there are indexes on the table that your view is based on, they can be
used exactly as if you were using the full underlying SELECT instead of
using the view.
You can also build indexes on views, but that is a whole separate big topic.
Have you asked the instructor this question?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>I am currently taking a class, SQL SErver 2000 Database Design and
> Implementation (70-229) and have just finished reading about indexes. In
> terms of their primary use, these seem closely related to 'views'. I am
> unable to find any research on how to know when to use a 'view' versus an
> 'index'. I was disappointed that the text did not compare the two for
> contrast. Can someone explain or direct me to information regarding their
> differences?|||There are indexes, there are views, and there are indexed views, so
lets take them one by one.
An index is used for accessing a table efficiently. It may also serve
to enforce a UNIQUE constraint. It is a physical, rather than logical
concept. Dropping an index may result in a query running slower, but
it should never change the results a query returns.
A view is, in contrast, strictly logical. The result set from any
SELECT command is (effectively) a table; a view simply provides a
convenient way to reference the results of a SELECT as if it were a
physical table.
As you can see, there is nothing about indexes and views that is
closely related. But, I suspect the source of your confusion is that
odd creation, the indexed view.
An indexed view is a trick. While a view is strictly a logical
construct, and indexed view is a sneaky way to force the view to
become a physical table internally. Like a real view, there is never
any question about it being kept in sync with the table(s) from which
it is derived.
The indexed view is a specialized tool used to optimize retrieval.
Indexed views can sometimes cause updates to the underlying table(s)
to become horribly slow, so they are not a tool one chooses to
implement lightly.
There are also some issues about how the optimizer treats them,
depending on the edition (Standard or Enterprise) running, but to get
started just try to understand the basics.
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
<apridgen@.discussions.microsoft.com> wrote:

>I am currently taking a class, SQL SErver 2000 Database Design and
>Implementation (70-229) and have just finished reading about indexes. In
>terms of their primary use, these seem closely related to 'views'. I am
>unable to find any research on how to know when to use a 'view' versus an
>'index'. I was disappointed that the text did not compare the two for
>contrast. Can someone explain or direct me to information regarding their
>differences?|||> I was disappointed that the text did not compare the two for
> contrast.
Well, this is kind of like expecting a car manual to compare anti-freeze and
carburetors, or seat belts and radios, for contrast. They are completely
different things, even though they are part of a bigger entity, as others
have and will explain(ed).
A|||Well, you see, that's the 'rub', I have no instructor...only a book and
online tutorial....sometimes concepts are hard to grasp with no one to
'bounce' questions off of.
I was about the 'views'. I incorrectly thought they were some
manipulation of the data (such as a logical file arrangement).
Thank you for the explanation...it really did help.
Some of these 'concepts' are really hard for me, as I have been using RPG400
FOREVER!!!
"Kalen Delaney" wrote:

> Did the instructor explain these two concepts? They are nothing at all
> alike, and their use is quite different, so I would be surprised to see a
> section comparing the two. It would be like having a section explaining th
e
> difference between tables and SELECT statements.
> An index is a physical structure that contains pointers to the data and
> helps find the data you are looking for more quickly. In general, you don'
t
> 'use' an index. You create the indexes you need, and SQL Server chooses
> whether or not to use them to help get to the data you are looking for.
> A view is a way to save a SELECT statement so you don't have to retype it
> every time, and can use the data returned by the view as if it were a tabl
e.
> If there are indexes on the table that your view is based on, they can be
> used exactly as if you were using the full underlying SELECT instead of
> using the view.
> You can also build indexes on views, but that is a whole separate big topi
c.
> Have you asked the instructor this question?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "a_pridgen" <apridgen@.discussions.microsoft.com> wrote in message
> news:8AAB6228-07DE-4881-A922-24DEA8DB53DE@.microsoft.com...
>
>|||Well, thanks so much for boosting my confidence.
I now remember why I hestitated before posting for help!
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, this is kind of like expecting a car manual to compare anti-freeze a
nd
> carburetors, or seat belts and radios, for contrast. They are completely
> different things, even though they are part of a bigger entity, as others
> have and will explain(ed).
> A
>
>|||Thanks for the help...when looking for 'help' I always hope for people
willing to share knowledge without being condescending.
"Roy Harvey" wrote:

> There are indexes, there are views, and there are indexed views, so
> lets take them one by one.
> An index is used for accessing a table efficiently. It may also serve
> to enforce a UNIQUE constraint. It is a physical, rather than logical
> concept. Dropping an index may result in a query running slower, but
> it should never change the results a query returns.
> A view is, in contrast, strictly logical. The result set from any
> SELECT command is (effectively) a table; a view simply provides a
> convenient way to reference the results of a SELECT as if it were a
> physical table.
> As you can see, there is nothing about indexes and views that is
> closely related. But, I suspect the source of your confusion is that
> odd creation, the indexed view.
> An indexed view is a trick. While a view is strictly a logical
> construct, and indexed view is a sneaky way to force the view to
> become a physical table internally. Like a real view, there is never
> any question about it being kept in sync with the table(s) from which
> it is derived.
> The indexed view is a specialized tool used to optimize retrieval.
> Indexed views can sometimes cause updates to the underlying table(s)
> to become horribly slow, so they are not a tool one chooses to
> implement lightly.
> There are also some issues about how the optimizer treats them,
> depending on the edition (Standard or Enterprise) running, but to get
> started just try to understand the basics.
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 27 Apr 2006 09:13:01 -0700, a_pridgen
> <apridgen@.discussions.microsoft.com> wrote:
>
>|||> Well, thanks so much for boosting my confidence.
> I now remember why I hestitated before posting for help!
Hey, you get what you pay for. Since other people had already explained the
concepts directly, I just thought I'd show an analogy of your expectations.
Sorry I bothered, because it seems my point was lost on you.

Indexed Views...on Tables in remote database on the same server..

Hi All,
part A: I have a thought and wanted to share it. Has anyone ever used
Indexed Views created in Database A where all the underlying referenced
objects are in Database B? Both Database A and B are on the same server.
Database A is a Log Shipping Standby Server that has Logs applied to it
every 15 minutes. I do not want the read only users to break synch. I want
them to connect to this (dummy) database B that has only Views and Stored
Procedures.
Part B: Can I then create the stored procedures in database B and point to
the views in B? Essentially Database B is a mask, we did this in other DBMS
systems and I was wondering if the same concept can be achieved here. I
guess one of the crucial questions is whether the benefits of an Index View
are still available across databases on the same server. Does the Query
Optimizer then travel to the other db to make decisions about query plans
etc. If it does, what happens if Database is in a restore mode? Are these
views cached somewhere so that the data is at least still available to the
users since the view will have a primary key defined on it?
There are a lot of questions about the characteristics of Indexed Views that
make it an interesting object, but I was wondering how it would behave in a
scenario where the underlying tables are in another database and that
database is a Read Only database that is a Log Shipping Standby Server.
Comments are welcome from experience and theory. Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0013_01C3E81F.1B25ACF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
To create an indexed view, you must use the WITH SCHEMABINDING OPTION. For
this option, you need to use two-part naming, which precludes their use
outside of the database in which they are created.
A stored proc can use 1 - 4 part naming, allowing you to call a stored proc
from outside the database and even outside the server.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Patrick Ikhifa" <ispi@.gte.net> wrote in message
news:eIP3MPC6DHA.2264@.tk2msftngp13.phx.gbl...
Hi All,
part A: I have a thought and wanted to share it. Has anyone ever used
Indexed Views created in Database A where all the underlying referenced
objects are in Database B? Both Database A and B are on the same server.
Database A is a Log Shipping Standby Server that has Logs applied to it
every 15 minutes. I do not want the read only users to break synch. I want
them to connect to this (dummy) database B that has only Views and Stored
Procedures.
Part B: Can I then create the stored procedures in database B and point to
the views in B? Essentially Database B is a mask, we did this in other DBMS
systems and I was wondering if the same concept can be achieved here. I
guess one of the crucial questions is whether the benefits of an Index View
are still available across databases on the same server. Does the Query
Optimizer then travel to the other db to make decisions about query plans
etc. If it does, what happens if Database is in a restore mode? Are these
views cached somewhere so that the data is at least still available to the
users since the view will have a primary key defined on it?
There are a lot of questions about the characteristics of Indexed Views that
make it an interesting object, but I was wondering how it would behave in a
scenario where the underlying tables are in another database and that
database is a Read Only database that is a Log Shipping Standby Server.
Comments are welcome from experience and theory. Thanks.
--=_NextPart_000_0013_01C3E81F.1B25ACF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

To create an indexed view, you must =use the WITH SCHEMABINDING OPTION. For this option, you need to use two-part =naming, which precludes their use outside of the database in which they are created.
A stored proc can use 1 - 4 part =naming, allowing you to call a stored proc from outside the database and even outside the =server.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Patrick Ikhifa" wrote in message news:eIP3MPC6DHA.2264=@.tk2msftngp13.phx.gbl...Hi All,part A: I have a thought and wanted to share it. Has anyone ever =usedIndexed Views created in Database A where all the underlying referencedobjects are in Database B? Both Database A and B are on =the same server.Database A is a Log Shipping Standby Server that has Logs =applied to itevery 15 minutes. I do not want the read only users to break =synch. I wantthem to connect to this (dummy) database B that has only Views =and StoredProcedures.Part B: Can I then create the stored =procedures in database B and point tothe views in B? Essentially Database B is a =mask, we did this in other DBMSsystems and I was wondering if the same =concept can be achieved here. Iguess one of the crucial questions is whether the =benefits of an Index Vieware still available across databases on the same =server. Does the QueryOptimizer then travel to the other db to make =decisions about query plansetc. If it does, what happens if Database is in a restore =mode? Are theseviews cached somewhere so that the data is at least still =available to theusers since the view will have a primary key defined on it?There are a lot of questions about the characteristics of =Indexed Views thatmake it an interesting object, but I was wondering how it =would behave in ascenario where the underlying tables are in another =database and thatdatabase is a Read Only database that is a Log Shipping Standby Server.Comments are welcome from experience and theory. Thanks.

---

--=_NextPart_000_0013_01C3E81F.1B25ACF0--

Indexed Views...on Tables in remote database on the same server..

Hi All,
part A: I have a thought and wanted to share it. Has anyone ever used
Indexed Views created in Database A where all the underlying referenced
objects are in Database B? Both Database A and B are on the same server.
Database A is a Log Shipping Standby Server that has Logs applied to it
every 15 minutes. I do not want the read only users to break synch. I want
them to connect to this (dummy) database B that has only Views and Stored
Procedures.
Part B: Can I then create the stored procedures in database B and point to
the views in B? Essentially Database B is a mask, we did this in other DBMS
systems and I was wondering if the same concept can be achieved here. I
guess one of the crucial questions is whether the benefits of an Index View
are still available across databases on the same server. Does the Query
Optimizer then travel to the other db to make decisions about query plans
etc. If it does, what happens if Database is in a restore mode? Are these
views cached somewhere so that the data is at least still available to the
users since the view will have a primary key defined on it?
There are a lot of questions about the characteristics of Indexed Views that
make it an interesting object, but I was wondering how it would behave in a
scenario where the underlying tables are in another database and that
database is a Read Only database that is a Log Shipping Standby Server.
Comments are welcome from experience and theory. Thanks.To create an indexed view, you must use the WITH SCHEMABINDING OPTION. For
this option, you need to use two-part naming, which precludes their use
outside of the database in which they are created.
A stored proc can use 1 - 4 part naming, allowing you to call a stored proc
from outside the database and even outside the server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Patrick Ikhifa" <ispi@.gte.net> wrote in message
news:eIP3MPC6DHA.2264@.tk2msftngp13.phx.gbl...
Hi All,
part A: I have a thought and wanted to share it. Has anyone ever used
Indexed Views created in Database A where all the underlying referenced
objects are in Database B? Both Database A and B are on the same server.
Database A is a Log Shipping Standby Server that has Logs applied to it
every 15 minutes. I do not want the read only users to break synch. I want
them to connect to this (dummy) database B that has only Views and Stored
Procedures.
Part B: Can I then create the stored procedures in database B and point to
the views in B? Essentially Database B is a mask, we did this in other DBMS
systems and I was wondering if the same concept can be achieved here. I
guess one of the crucial questions is whether the benefits of an Index View
are still available across databases on the same server. Does the Query
Optimizer then travel to the other db to make decisions about query plans
etc. If it does, what happens if Database is in a restore mode? Are these
views cached somewhere so that the data is at least still available to the
users since the view will have a primary key defined on it?
There are a lot of questions about the characteristics of Indexed Views that
make it an interesting object, but I was wondering how it would behave in a
scenario where the underlying tables are in another database and that
database is a Read Only database that is a Log Shipping Standby Server.
Comments are welcome from experience and theory. Thanks.

indexed views... how to set up?

I've heard SQL2K can have indexed views, but I havent seen any place to set
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/d...
xedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>

indexed views... how to set up?

I've heard SQL2K can have indexed views, but I havent seen any place to set
up the indexes (in the GUI)...
so, A) is it only scriptable and B) does it really help?
--
Eric Newton
eric.at.ensoft-software.com
www.ensoft-software.com
C#/ASP.net Solutions developerYes, it can. See the white paper
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/indexedviews1.asp
for details or SQL Server Books Online topics Designing an Indexed View and
Creating an Indexed View.
There's no special GUI for indexed views because it's really nothing more
than a creating regular view (see the white paper for
requirements/restrictions) and then creating a clustered index on that
view. I haven't tried it, but you should be able to use the normal GUI for
creating views and indexes in Enterprise Manager to do both of these tasks.
Whether or not they help depends, of course, on your situation. If you have
existing views that do a lot of table joins or aggregates data, then indexed
views may significantly improve the performance of those views. However,
you'll also be using more disk space because the result set of the view is
actually materialized and stored in the leaf level of the clustered index
just like a clustered index on a table. Plus the index will be maintained
whenever the underlying base table(s) are modified. The white paper goes
into more details on the pros and cons.
HTH,
Gail Erickson [MS]
SQL Server Doc Team
This posting is provided "AS IS" with no warranties, and confers no rights.
"Eric Newton" <eric@.ensoft-software.com> wrote in message
news:%233yGYMv%23DHA.1956@.TK2MSFTNGP10.phx.gbl...
> I've heard SQL2K can have indexed views, but I havent seen any place to
set
> up the indexes (in the GUI)...
> so, A) is it only scriptable and B) does it really help?
>
> --
> Eric Newton
> eric.at.ensoft-software.com
> www.ensoft-software.com
> C#/ASP.net Solutions developer
>

Indexed Views, Space Used.

Potentially stupid question but here goes:
Does an indexed view require extra space (due to data being copied) or
is it just a logical construct that uses existing table data?
Thanks, TFD.> Does an indexed view require extra space (due to data being copied) or
> is it just a logical construct that uses existing table data?
A standard view is a logical construct that is temporarily materialized when
a statement referencing the view is executed. An indexed view is
materialized and stored on disk at the time the clustered index is created
on the view. So, yes, it requires additional disk space to hold the
clustered index.
You might find this white paper useful
http://www.microsoft.com/technet/pr.../ipsql05iv.mspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137686494.821811.34620@.g47g2000cwa.googlegroups.com...
> Potentially stupid question but here goes:
> Does an indexed view require extra space (due to data being copied) or
> is it just a logical construct that uses existing table data?
> Thanks, TFD.
>|||> So, yes, it requires additional disk space to hold the clustered index.
And further to disk space, there is also additional I/O when you issue DML
against the base table (since it has to mirror those changes in the
materialized view(s)).
A|||Is there a way to determine the space used by a materialized view
(after the fact) like you can do for a table?|||Sure. You can use sp_spaceused for indexed views.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137710309.602103.184880@.z14g2000cwz.googlegroups.com...
> Is there a way to determine the space used by a materialized view
> (after the fact) like you can do for a table?
>|||CREATE VIEW V1
AS
SELECT a, SUM(b) AS Revenue
FROM MyTable
GROUP BY a
GO
sp_spaceused 'V1'
and the result comes back as
Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
Views do not have space allocated.
'|||Ummm, that's not an indexed view.
"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1137725398.306412.146190@.g43g2000cwa.googlegroups.com...
> CREATE VIEW V1
> AS
> SELECT a, SUM(b) AS Revenue
> FROM MyTable
> GROUP BY a
> GO
>
> sp_spaceused 'V1'
>
> and the result comes back as
> Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
> Views do not have space allocated.
> '
>|||Well, at least create the clustered index. :) As soon as it's created the
system procedure will work.
ML
http://milambda.blogspot.com/|||And further to that, you'll need to create the view WITH SCHEMABINDING
in order to create the clustered index on it, the clustered index must
be a *unique* clustered index, you must use 2 part names in the view
(i.e. you must specify the owner of "MyTable"), and the owner of the
view & the base tables referenced in the view must all be the same. For
example:
use tempdb;
go
create table dbo.SalesAmounts
(
InvoiceID int identity(1,1) primary key clustered,
SalesPerson varchar(10) not null,
Amount smallmoney not null
);
go
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 10.90);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 17.45);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Fred', 3.95);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Bill', 78.85);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Bill', 26.50);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 16.20);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 12.10);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 18.90);
insert into dbo.SalesAmounts (SalesPerson, Amount) values ('Jack', 9.95);
go
create view dbo.SalesAggregates with schemabinding
as
select SalesPerson, sum(Amount) as Revenue, count_big(*) as NumSales
from dbo.SalesAmounts
group by SalesPerson;
go
create unique clustered index UX_SalesAggregates_SalesPerson on dbo.SalesAgg
regates (SalesPerson);
go
select InvoiceID, SalesPerson, Amount from dbo.SalesAmounts;
select SalesPerson, Revenue, NumSales from dbo.SalesAggregates;
go
exec sp_spaceused 'dbo.SalesAmounts';
exec sp_spaceused 'dbo.SalesAggregates';
go
drop view dbo.SalesAggregates;
drop table dbo.SalesAmounts;
go
There are quite a few caveats and considerations around indexed views,
for more info see BOL
(http://msdn.microsoft.com/library/e...des_06_9jnb.asp).
*mike hodgson*
http://sqlnerd.blogspot.com
ML wrote:

>Well, at least create the clustered index. :) As soon as it's created the
>system procedure will work.
>
>ML
>--
>http://milambda.blogspot.com/
>

Indexed Views with Self-Joins

I realize that SQL Server doesn't support indexed views that have
self-joins, but are there any reasonable work-arounds to this?
In the following view definition, the LawLink table contains IDs for a
lawyer/lawfirm pair, and the Profile table contains one record for each
lawyer and one record for each law firm. (ProfileID is the primary key of
the Profile table, and ProfileIDs are unique across all lawyers and law
firms.)
CREATE VIEW EntityLawLink WITH SCHEMABINDING
AS
SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
COUNT_BIG(*) AS Frequency
FROM dbo.LawLink LL
INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
GROUP BY L.EntityID, F.EntityID
GO
The problem is that SQL Server sees this as a self-join because the Profile
table is referenced twice. When I issue the command:
CREATE UNIQUE CLUSTERED INDEX Test
ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
GO
SQL Server responds: Index cannot be created ... because the view contains
a self-join on 'dbo.Profile'.
I don't really understand why SQL Server has this restriction. I suppose
that the Profile table could be seen as indirectly referencing itself
through the LawLink table, but I had assumed that the self-referencing
restriction had to do with a table directly referencing itself. Why should
SQL Server care that the table is used twice?
If I split the Profile table into two tables (one for lawyers and one for
law firms) then everything works fine. Unfortunately, that's not practical
in this circumstance. Can anyone suggest any other alternatives?
Thanks,
Mark
Mark,
Yes, I was disappointed as well to see that no-self join included not
joining to the same table twice. (!!!)
Here is a possible workaround:
1. You will need another table, perhaps LawFirmProfile.
2. You do not want or need that table for another other purpose than this
indexed view.
3. Create UPD,INS,DEL triggers for the Profile table.
All these triggers will do is keep the LawFirmProfile table up-to-date
with what is in Profile.
4. Build your indexed view with a join to Profile (for lawyers) and
LawFirmProfile (for firms.)
Perhaps another question to ask is whether the indexed view is really giving
you enough boost to make it worth fooling with. We backed off on indexed
view (not all of them) once we understood the limitations.
FWIW,
Russell Fields
"Mark Pauker" <mpauker@.optonline.net> wrote in message
news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
> I realize that SQL Server doesn't support indexed views that have
> self-joins, but are there any reasonable work-arounds to this?
> In the following view definition, the LawLink table contains IDs for a
> lawyer/lawfirm pair, and the Profile table contains one record for each
> lawyer and one record for each law firm. (ProfileID is the primary key of
> the Profile table, and ProfileIDs are unique across all lawyers and law
> firms.)
> CREATE VIEW EntityLawLink WITH SCHEMABINDING
> AS
> SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
> COUNT_BIG(*) AS Frequency
> FROM dbo.LawLink LL
> INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
> INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
> GROUP BY L.EntityID, F.EntityID
> GO
> The problem is that SQL Server sees this as a self-join because the
Profile
> table is referenced twice. When I issue the command:
> CREATE UNIQUE CLUSTERED INDEX Test
> ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
> GO
> SQL Server responds: Index cannot be created ... because the view
contains
> a self-join on 'dbo.Profile'.
> I don't really understand why SQL Server has this restriction. I suppose
> that the Profile table could be seen as indirectly referencing itself
> through the LawLink table, but I had assumed that the self-referencing
> restriction had to do with a table directly referencing itself. Why
should
> SQL Server care that the table is used twice?
> If I split the Profile table into two tables (one for lawyers and one for
> law firms) then everything works fine. Unfortunately, that's not
practical
> in this circumstance. Can anyone suggest any other alternatives?
> Thanks,
> Mark
>
|||Thanks for your thoughts. I contemplated this, but having a trigger to
update a table that's used in an indexed view will likely lead to
unacceptable performance bottlenecks.
I also looked into physically separating the Profile table into 2 separate
tables and using a UNION ALL view to create a logical Profile table. The
problem is that the performance characteristics of the Profile view were not
acceptable. (I can't create a partitioned view at this level, so most
queries take at least twice as long to complete.)
Regarding the question of whether or not the indexed view is worth it in
this case, the query appears to run about 30 times faster using the view.
Well worth spending some extra time to see if there's a reasonable
workaround. Of course, I was hoping that there might simply be a different
way of creating the view without having to build temporary constructs.
-- Mark
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23IiXjzIUEHA.712@.TK2MSFTNGP11.phx.gbl...
> Mark,
> Yes, I was disappointed as well to see that no-self join included not
> joining to the same table twice. (!!!)
> Here is a possible workaround:
> 1. You will need another table, perhaps LawFirmProfile.
> 2. You do not want or need that table for another other purpose than this
> indexed view.
> 3. Create UPD,INS,DEL triggers for the Profile table.
> All these triggers will do is keep the LawFirmProfile table
up-to-date
> with what is in Profile.
> 4. Build your indexed view with a join to Profile (for lawyers) and
> LawFirmProfile (for firms.)
> Perhaps another question to ask is whether the indexed view is really
giving[vbcol=seagreen]
> you enough boost to make it worth fooling with. We backed off on indexed
> view (not all of them) once we understood the limitations.
> FWIW,
> Russell Fields
> "Mark Pauker" <mpauker@.optonline.net> wrote in message
> news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
> Profile
> contains
suppose[vbcol=seagreen]
> should
for
> practical
>

Indexed Views with Self-Joins

I realize that SQL Server doesn't support indexed views that have
self-joins, but are there any reasonable work-arounds to this?
In the following view definition, the LawLink table contains IDs for a
lawyer/lawfirm pair, and the Profile table contains one record for each
lawyer and one record for each law firm. (ProfileID is the primary key of
the Profile table, and ProfileIDs are unique across all lawyers and law
firms.)
CREATE VIEW EntityLawLink WITH SCHEMABINDING
AS
SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
COUNT_BIG(*) AS Frequency
FROM dbo.LawLink LL
INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
GROUP BY L.EntityID, F.EntityID
GO
The problem is that SQL Server sees this as a self-join because the Profile
table is referenced twice. When I issue the command:
CREATE UNIQUE CLUSTERED INDEX Test
ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
GO
SQL Server responds: Index cannot be created ... because the view contains
a self-join on 'dbo.Profile'.
I don't really understand why SQL Server has this restriction. I suppose
that the Profile table could be seen as indirectly referencing itself
through the LawLink table, but I had assumed that the self-referencing
restriction had to do with a table directly referencing itself. Why should
SQL Server care that the table is used twice?
If I split the Profile table into two tables (one for lawyers and one for
law firms) then everything works fine. Unfortunately, that's not practical
in this circumstance. Can anyone suggest any other alternatives?
Thanks,
MarkMark,
Yes, I was disappointed as well to see that no-self join included not
joining to the same table twice. (!!!)
Here is a possible workaround:
1. You will need another table, perhaps LawFirmProfile.
2. You do not want or need that table for another other purpose than this
indexed view.
3. Create UPD,INS,DEL triggers for the Profile table.
All these triggers will do is keep the LawFirmProfile table up-to-date
with what is in Profile.
4. Build your indexed view with a join to Profile (for lawyers) and
LawFirmProfile (for firms.)
Perhaps another question to ask is whether the indexed view is really giving
you enough boost to make it worth fooling with. We backed off on indexed
view (not all of them) once we understood the limitations.
FWIW,
Russell Fields
"Mark Pauker" <mpauker@.optonline.net> wrote in message
news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
> I realize that SQL Server doesn't support indexed views that have
> self-joins, but are there any reasonable work-arounds to this?
> In the following view definition, the LawLink table contains IDs for a
> lawyer/lawfirm pair, and the Profile table contains one record for each
> lawyer and one record for each law firm. (ProfileID is the primary key of
> the Profile table, and ProfileIDs are unique across all lawyers and law
> firms.)
> CREATE VIEW EntityLawLink WITH SCHEMABINDING
> AS
> SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
> COUNT_BIG(*) AS Frequency
> FROM dbo.LawLink LL
> INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
> INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
> GROUP BY L.EntityID, F.EntityID
> GO
> The problem is that SQL Server sees this as a self-join because the
Profile
> table is referenced twice. When I issue the command:
> CREATE UNIQUE CLUSTERED INDEX Test
> ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
> GO
> SQL Server responds: Index cannot be created ... because the view
contains
> a self-join on 'dbo.Profile'.
> I don't really understand why SQL Server has this restriction. I suppose
> that the Profile table could be seen as indirectly referencing itself
> through the LawLink table, but I had assumed that the self-referencing
> restriction had to do with a table directly referencing itself. Why
should
> SQL Server care that the table is used twice?
> If I split the Profile table into two tables (one for lawyers and one for
> law firms) then everything works fine. Unfortunately, that's not
practical
> in this circumstance. Can anyone suggest any other alternatives?
> Thanks,
> Mark
>|||Thanks for your thoughts. I contemplated this, but having a trigger to
update a table that's used in an indexed view will likely lead to
unacceptable performance bottlenecks.
I also looked into physically separating the Profile table into 2 separate
tables and using a UNION ALL view to create a logical Profile table. The
problem is that the performance characteristics of the Profile view were not
acceptable. (I can't create a partitioned view at this level, so most
queries take at least twice as long to complete.)
Regarding the question of whether or not the indexed view is worth it in
this case, the query appears to run about 30 times faster using the view.
Well worth spending some extra time to see if there's a reasonable
workaround. Of course, I was hoping that there might simply be a different
way of creating the view without having to build temporary constructs.
-- Mark
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23IiXjzIUEHA.712@.TK2MSFTNGP11.phx.gbl...
> Mark,
> Yes, I was disappointed as well to see that no-self join included not
> joining to the same table twice. (!!!)
> Here is a possible workaround:
> 1. You will need another table, perhaps LawFirmProfile.
> 2. You do not want or need that table for another other purpose than this
> indexed view.
> 3. Create UPD,INS,DEL triggers for the Profile table.
> All these triggers will do is keep the LawFirmProfile table
up-to-date
> with what is in Profile.
> 4. Build your indexed view with a join to Profile (for lawyers) and
> LawFirmProfile (for firms.)
> Perhaps another question to ask is whether the indexed view is really
giving
> you enough boost to make it worth fooling with. We backed off on indexed
> view (not all of them) once we understood the limitations.
> FWIW,
> Russell Fields
> "Mark Pauker" <mpauker@.optonline.net> wrote in message
> news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
> > I realize that SQL Server doesn't support indexed views that have
> > self-joins, but are there any reasonable work-arounds to this?
> >
> > In the following view definition, the LawLink table contains IDs for a
> > lawyer/lawfirm pair, and the Profile table contains one record for each
> > lawyer and one record for each law firm. (ProfileID is the primary key
of
> > the Profile table, and ProfileIDs are unique across all lawyers and law
> > firms.)
> >
> > CREATE VIEW EntityLawLink WITH SCHEMABINDING
> > AS
> > SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
> > COUNT_BIG(*) AS Frequency
> > FROM dbo.LawLink LL
> > INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
> > INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
> > GROUP BY L.EntityID, F.EntityID
> > GO
> >
> > The problem is that SQL Server sees this as a self-join because the
> Profile
> > table is referenced twice. When I issue the command:
> >
> > CREATE UNIQUE CLUSTERED INDEX Test
> > ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
> > GO
> >
> > SQL Server responds: Index cannot be created ... because the view
> contains
> > a self-join on 'dbo.Profile'.
> >
> > I don't really understand why SQL Server has this restriction. I
suppose
> > that the Profile table could be seen as indirectly referencing itself
> > through the LawLink table, but I had assumed that the self-referencing
> > restriction had to do with a table directly referencing itself. Why
> should
> > SQL Server care that the table is used twice?
> >
> > If I split the Profile table into two tables (one for lawyers and one
for
> > law firms) then everything works fine. Unfortunately, that's not
> practical
> > in this circumstance. Can anyone suggest any other alternatives?
> >
> > Thanks,
> > Mark
> >
> >
>

Indexed Views with Self-Joins

I realize that SQL Server doesn't support indexed views that have
self-joins, but are there any reasonable work-arounds to this?
In the following view definition, the LawLink table contains IDs for a
lawyer/lawfirm pair, and the Profile table contains one record for each
lawyer and one record for each law firm. (ProfileID is the primary key of
the Profile table, and ProfileIDs are unique across all lawyers and law
firms.)
CREATE VIEW EntityLawLink WITH SCHEMABINDING
AS
SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
COUNT_BIG(*) AS Frequency
FROM dbo.LawLink LL
INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
GROUP BY L.EntityID, F.EntityID
GO
The problem is that SQL Server sees this as a self-join because the Profile
table is referenced twice. When I issue the command:
CREATE UNIQUE CLUSTERED INDEX Test
ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
GO
SQL Server responds: Index cannot be created ... because the view contains
a self-join on 'dbo.Profile'.
I don't really understand why SQL Server has this restriction. I suppose
that the Profile table could be seen as indirectly referencing itself
through the LawLink table, but I had assumed that the self-referencing
restriction had to do with a table directly referencing itself. Why should
SQL Server care that the table is used twice?
If I split the Profile table into two tables (one for lawyers and one for
law firms) then everything works fine. Unfortunately, that's not practical
in this circumstance. Can anyone suggest any other alternatives?
Thanks,
MarkMark,
Yes, I was disappointed as well to see that no-self join included not
joining to the same table twice. (!!!)
Here is a possible workaround:
1. You will need another table, perhaps LawFirmProfile.
2. You do not want or need that table for another other purpose than this
indexed view.
3. Create UPD,INS,DEL triggers for the Profile table.
All these triggers will do is keep the LawFirmProfile table up-to-date
with what is in Profile.
4. Build your indexed view with a join to Profile (for lawyers) and
LawFirmProfile (for firms.)
Perhaps another question to ask is whether the indexed view is really giving
you enough boost to make it worth fooling with. We backed off on indexed
view (not all of them) once we understood the limitations.
FWIW,
Russell Fields
"Mark Pauker" <mpauker@.optonline.net> wrote in message
news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
> I realize that SQL Server doesn't support indexed views that have
> self-joins, but are there any reasonable work-arounds to this?
> In the following view definition, the LawLink table contains IDs for a
> lawyer/lawfirm pair, and the Profile table contains one record for each
> lawyer and one record for each law firm. (ProfileID is the primary key of
> the Profile table, and ProfileIDs are unique across all lawyers and law
> firms.)
> CREATE VIEW EntityLawLink WITH SCHEMABINDING
> AS
> SELECT L.EntityID AS LawyerEntityID, F.EntityID AS LawFirmEntityID,
> COUNT_BIG(*) AS Frequency
> FROM dbo.LawLink LL
> INNER JOIN dbo.Profile L ON L.ProfileID = LL.LawyerProfileID
> INNER JOIN dbo.Profile F ON F.ProfileID = LL.LawFirmProfileID
> GROUP BY L.EntityID, F.EntityID
> GO
> The problem is that SQL Server sees this as a self-join because the
Profile
> table is referenced twice. When I issue the command:
> CREATE UNIQUE CLUSTERED INDEX Test
> ON EntityLawLink (LawyerEntityID, LawFirmEntityID)
> GO
> SQL Server responds: Index cannot be created ... because the view
contains
> a self-join on 'dbo.Profile'.
> I don't really understand why SQL Server has this restriction. I suppose
> that the Profile table could be seen as indirectly referencing itself
> through the LawLink table, but I had assumed that the self-referencing
> restriction had to do with a table directly referencing itself. Why
should
> SQL Server care that the table is used twice?
> If I split the Profile table into two tables (one for lawyers and one for
> law firms) then everything works fine. Unfortunately, that's not
practical
> in this circumstance. Can anyone suggest any other alternatives?
> Thanks,
> Mark
>|||Thanks for your thoughts. I contemplated this, but having a trigger to
update a table that's used in an indexed view will likely lead to
unacceptable performance bottlenecks.
I also looked into physically separating the Profile table into 2 separate
tables and using a UNION ALL view to create a logical Profile table. The
problem is that the performance characteristics of the Profile view were not
acceptable. (I can't create a partitioned view at this level, so most
queries take at least twice as long to complete.)
Regarding the question of whether or not the indexed view is worth it in
this case, the query appears to run about 30 times faster using the view.
Well worth spending some extra time to see if there's a reasonable
workaround. Of course, I was hoping that there might simply be a different
way of creating the view without having to build temporary constructs.
-- Mark
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23IiXjzIUEHA.712@.TK2MSFTNGP11.phx.gbl...
> Mark,
> Yes, I was disappointed as well to see that no-self join included not
> joining to the same table twice. (!!!)
> Here is a possible workaround:
> 1. You will need another table, perhaps LawFirmProfile.
> 2. You do not want or need that table for another other purpose than this
> indexed view.
> 3. Create UPD,INS,DEL triggers for the Profile table.
> All these triggers will do is keep the LawFirmProfile table
up-to-date
> with what is in Profile.
> 4. Build your indexed view with a join to Profile (for lawyers) and
> LawFirmProfile (for firms.)
> Perhaps another question to ask is whether the indexed view is really
giving
> you enough boost to make it worth fooling with. We backed off on indexed
> view (not all of them) once we understood the limitations.
> FWIW,
> Russell Fields
> "Mark Pauker" <mpauker@.optonline.net> wrote in message
> news:u8fTFGAUEHA.704@.TK2MSFTNGP09.phx.gbl...
of[vbcol=seagreen]
> Profile
> contains
suppose[vbcol=seagreen]
> should
for[vbcol=seagreen]
> practical
>

Indexed Views with aggregated awareness

Please correct me if I'm wrong here but what's the purpose of sql server
indexed views.. I mean I don't mean to go off on rant here and please
understand this is not a post to discredited ms sql server 2005 at all, I'm
just trying to get some clarification that's all.
ok.. with that said. from what I understand in a nut shell about 2005
indexed views is when the view is persisted ALL objects with in the view are
persisted.. which is to be expected.. what I found to my surprises is how sq
l
server seems to perform aggregated awareness only when all element with the
view are present. in other words if you present any other elements ( like a
another dimensional table join that is at the same level of aggregation that
the view is at) currently at seems to break the optimal query plan that I
would expect the optimizer to take.
example:
1. base detail table called table_detail a has 1 million records in it.
create table tbl_detail
( client_id into, invoicedate, sales money)
2. an indexed view called mv_summary is created over tbl_detail table
create view mv_summary as
select client_id , datepart(yyyy,invoicedate) as invoiceyear, sum(sales) as
sales
go
create unique clustered index cidx_yearclient on ,mv_summary(invoiceyear,
client_id)
go
3. lets say the mv_summary view is at lower level of aggregation, now the
mv_summary has a 1000 aggregated record from the detail table.
any select statements ran against the mv_summary view directly the plan runs
as expected. even if you were to query the tbl_detail at the aggregation
level of the view the plan result returns mv_summary as expected via sql
server arrogation awareness. though once you introduce an dimensional table
is at the same level of aggregation to the query ( like a dimensional join )
ok this is were this get weird.
the plan goes to the tbl_detail not the indexed view as I would expect it to
.
basically what I attempted to do was filter on an dimensional descriptive
element via join and where clause.
example :
select client_id , sum(sales) as sales
from tbl_detail
inner join client_dimension
on
tbl_detail.client_id = client_dimension.client_id
where client_dimension.client_description 'test'
turns out what I have been able to come up with is ALL elements that you
need to filter join select on HAS to been in the indexed view... which seems
to been an issue.. that mean ALL data including dimensional data has to be
persisted again...
any thoughts on how I can use indexed views in a aggregation aware
environment without have to store ALL possible filterable elements in the
view.
thanks!!!In theory it ought to work fine (ie. use the indexed view in the query
plan) IFF the optimiser determines that that's the most efficient way to
return the data (although I've had cases where querying the base table
was so quick that the optimiser decided to chose it over a corresponding
indexed view anyway and not waste it's time evaluating query plans
against the indexed view).
The T-SQL code you post looks rather incomplete. Among other things,
shouldn't there be at least a GROUP BY in your view? What I'm trying to
get at is the SUM() aggregates in the view, are they usable in your
other query or are you grouping the rows into different groups when
calculating your SUM() aggregates? I'm guessing if you summed the sales
column from your view for all invoice years for a particular client_id
you'd get the same figure as summing the sales figure for that client_id
in the base table wouldn't you? Have I correctly guessed the grouping
you've used in the view?
If that's true then perhaps the optimiser thinks it's harder to join the
materialised view data to the client_dimension table than it is to join
the base tbl_detail table to that client_dimension table. I notice the
clustered index you create on the view has the invoiceyear column 1st
(and the client_id column 2nd), which would make it rather nasty to join
to client_dimension. I assume tbl_detail & client_dimension both have
nice indexes (maybe even clustered indexes) where client_id is the 1st
column in those indexes; if so, then a join between tbl_detail and
client_dimension would probably be more efficient than between
mv_summary and client_dimension (and so the query optimiser would
probably pick a join with the base table rather than the clustered index
on the view).
I'd check the execution plans, try changing the clustered unique index
on mv_summary so that client_id is the first column in the index and
make sure the grouping in the view & the later query are "compatible".
The bottom line is, in theory, it ought to work fine but there's
probably just something a bit off with your scenario/implementation.
*mike hodgson*
http://sqlnerd.blogspot.com
Eric wrote:

>Please correct me if I'm wrong here but what's the purpose of sql server
>indexed views.. I mean I don't mean to go off on rant here and please
>understand this is not a post to discredited ms sql server 2005 at all, I'm
>just trying to get some clarification that's all.
>ok.. with that said. from what I understand in a nut shell about 2005
>indexed views is when the view is persisted ALL objects with in the view ar
e
>persisted.. which is to be expected.. what I found to my surprises is how s
ql
>server seems to perform aggregated awareness only when all element with the
>view are present. in other words if you present any other elements ( like a
>another dimensional table join that is at the same level of aggregation tha
t
>the view is at) currently at seems to break the optimal query plan that I
>would expect the optimizer to take.
>example:
>1. base detail table called table_detail a has 1 million records in it.
>
>create table tbl_detail
>( client_id into, invoicedate, sales money)
>
>2. an indexed view called mv_summary is created over tbl_detail table
>
>create view mv_summary as
>select client_id , datepart(yyyy,invoicedate) as invoiceyear, sum(sales) as
>sales
>go
>create unique clustered index cidx_yearclient on ,mv_summary(invoiceyear,
>client_id)
>go
>
>3. lets say the mv_summary view is at lower level of aggregation, now the
>mv_summary has a 1000 aggregated record from the detail table.
>any select statements ran against the mv_summary view directly the plan run
s
>as expected. even if you were to query the tbl_detail at the aggregation
>level of the view the plan result returns mv_summary as expected via sql
>server arrogation awareness. though once you introduce an dimensional table
>is at the same level of aggregation to the query ( like a dimensional join
)
>ok this is were this get weird.
>the plan goes to the tbl_detail not the indexed view as I would expect it t
o.
>basically what I attempted to do was filter on an dimensional descriptive
>element via join and where clause.
>example :
>
>select client_id , sum(sales) as sales
>from tbl_detail
>inner join client_dimension
>on
>tbl_detail.client_id = client_dimension.client_id
>where client_dimension.client_description 'test'
>
>turns out what I have been able to come up with is ALL elements that you
>need to filter join select on HAS to been in the indexed view... which seem
s
>to been an issue.. that mean ALL data including dimensional data has to be
>persisted again...
>any thoughts on how I can use indexed views in a aggregation aware
>environment without have to store ALL possible filterable elements in the
>view.
>thanks!!!
>
>|||Eric,
have you tried to change the order of columns in the clustered index to
create unique clustered index cidx_yearclient on mv_summary(client_id,
invoiceyear)
and see if that helps?|||Hi Mike--
Ya sorry bout that... not only was my code incomplete but my grammar went to
hell as well... LOL! Typing when you’re running on 2 hours of sleep in a 7
2
hour window might do that to ya...
At any rate.
You are correct in my sample code there should be a group by (and is in my
testing) somehow i forgot to place in the post.
From what I have been able to devise is... it seems like the optimizer needs
to have any of all objects within the indexed view... Not just joined to
validate the objects binding prior to using it as a valid source for
aggregation. in other words all of the element that you may want to filter o
n
HAVE to be stored within the view...right? if that’s the case you would be
better off using the view strictly has a single source and never counting on
the optimizer performing the aggregation awareness.
For example:
Lets say I wanted the optimizer to use the indexed view based on a date
range. When I create the indexed view based on the first date of the month
for each grouping
Example: tbl_detail
client id invoice_date sales
a0000001 1/3/2005 10
a0000001 1/4/2005 30
a0000001 1/5/2005 20
a0000001 1/7/2005 50
gets resolved to : mv_summary
client id invoice_date sales
a0000001 1/1/2005 110
If I were to run a query against the tbl_detail table with the invoice_date
of between 1/1/2005 and 1/31/2005 it should be intelligent enough to know
that it would be more efficient to get the pre-aggregated data from the
indexed view verses pulling it from the table then aggregating it. (
obviously the given example is small but extrapolate it by a couple hundred
million and I believe it would make a difference)
Any thoughts on what might be the best approach to use indexed view in an
date aggregation aware scenario? basically I want to have a single source (
tbl_detail) that all querys are pointed to and based on the aggregation leve
l
and date constraints would determine which alternate data source would be
used ( like an indexed view summary) oracle has a function called " date
folding" that resolves a date constraint and re-writes the date portion of
the where clause to conform to the best element constraint based on the
finite or range of the date and aggregation level of the data.
Example:
invoice_date of 1/1/2005 through 1/31/2005 at the client level would be
resolved to the month of January and then...( not in the prior example code.
.
but invoice_date would be replaced with month instead) ... it would apply th
e
month code to the month column in the summary.
Please let me know if am not making since...
Oh, BTW I tried switching the date and client in the clustered index …
unfortunately with not avail)
Thanks
Eric
"Mike Hodgson" wrote:

> In theory it ought to work fine (ie. use the indexed view in the query
> plan) IFF the optimiser determines that that's the most efficient way to
> return the data (although I've had cases where querying the base table
> was so quick that the optimiser decided to chose it over a corresponding
> indexed view anyway and not waste it's time evaluating query plans
> against the indexed view).
> The T-SQL code you post looks rather incomplete. Among other things,
> shouldn't there be at least a GROUP BY in your view? What I'm trying to
> get at is the SUM() aggregates in the view, are they usable in your
> other query or are you grouping the rows into different groups when
> calculating your SUM() aggregates? I'm guessing if you summed the sales
> column from your view for all invoice years for a particular client_id
> you'd get the same figure as summing the sales figure for that client_id
> in the base table wouldn't you? Have I correctly guessed the grouping
> you've used in the view?
> If that's true then perhaps the optimiser thinks it's harder to join the
> materialised view data to the client_dimension table than it is to join
> the base tbl_detail table to that client_dimension table. I notice the
> clustered index you create on the view has the invoiceyear column 1st
> (and the client_id column 2nd), which would make it rather nasty to join
> to client_dimension. I assume tbl_detail & client_dimension both have
> nice indexes (maybe even clustered indexes) where client_id is the 1st
> column in those indexes; if so, then a join between tbl_detail and
> client_dimension would probably be more efficient than between
> mv_summary and client_dimension (and so the query optimiser would
> probably pick a join with the base table rather than the clustered index
> on the view).
> I'd check the execution plans, try changing the clustered unique index
> on mv_summary so that client_id is the first column in the index and
> make sure the grouping in the view & the later query are "compatible".
> The bottom line is, in theory, it ought to work fine but there's
> probably just something a bit off with your scenario/implementation.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Eric wrote:
>
>|||Hi Alexander --
Yep, unfortunately it didn't seem to change the result... It still pulled
from the detail.
Thanks
Eric
"Alexander Kuznetsov" wrote:

> Eric,
> have you tried to change the order of columns in the clustered index to
> create unique clustered index cidx_yearclient on mv_summary(client_id,
> invoiceyear)
> and see if that helps?
>|||Whats the query that you are using to access the index view?... Try
retrieving the only columns - invoiceyear, client_id in your query and see
the execution plan.. It will be fetching the results directly from the
indexed view rather than from the base table..
Jayesh
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:1B096C2E-A43C-4F32-B12A-BDF09339FC7F@.microsoft.com...
> Please correct me if I'm wrong here but what's the purpose of sql server
> indexed views.. I mean I don't mean to go off on rant here and please
> understand this is not a post to discredited ms sql server 2005 at all,
> I'm
> just trying to get some clarification that's all.
> ok.. with that said. from what I understand in a nut shell about 2005
> indexed views is when the view is persisted ALL objects with in the view
> are
> persisted.. which is to be expected.. what I found to my surprises is how
> sql
> server seems to perform aggregated awareness only when all element with
> the
> view are present. in other words if you present any other elements ( like
> a
> another dimensional table join that is at the same level of aggregation
> that
> the view is at) currently at seems to break the optimal query plan that I
> would expect the optimizer to take.
> example:
> 1. base detail table called table_detail a has 1 million records in it.
>
> create table tbl_detail
> ( client_id into, invoicedate, sales money)
>
> 2. an indexed view called mv_summary is created over tbl_detail table
>
> create view mv_summary as
> select client_id , datepart(yyyy,invoicedate) as invoiceyear, sum(sales)
> as
> sales
> go
> create unique clustered index cidx_yearclient on ,mv_summary(invoiceyear,
> client_id)
> go
>
> 3. lets say the mv_summary view is at lower level of aggregation, now the
> mv_summary has a 1000 aggregated record from the detail table.
> any select statements ran against the mv_summary view directly the plan
> runs
> as expected. even if you were to query the tbl_detail at the aggregation
> level of the view the plan result returns mv_summary as expected via sql
> server arrogation awareness. though once you introduce an dimensional
> table
> is at the same level of aggregation to the query ( like a dimensional
> join )
> ok this is were this get weird.
> the plan goes to the tbl_detail not the indexed view as I would expect it
> to.
> basically what I attempted to do was filter on an dimensional descriptive
> element via join and where clause.
> example :
>
> select client_id , sum(sales) as sales
> from tbl_detail
> inner join client_dimension
> on
> tbl_detail.client_id = client_dimension.client_id
> where client_dimension.client_description 'test'
>
> turns out what I have been able to come up with is ALL elements that you
> need to filter join select on HAS to been in the indexed view... which
> seems
> to been an issue.. that mean ALL data including dimensional data has to be
> persisted again...
> any thoughts on how I can use indexed views in a aggregation aware
> environment without have to store ALL possible filterable elements in the
> view.
> thanks!!!
>|||Ok, I have written a script to reproduce the issue I am seeing...
(Though I was able to get SQL to pull from the indexed view with a
constraint on a joining dimensional table by removing the joining table from
the indexed view...) though that does seem odd to me why that won't work.
as far as the time awareness aggregation
it seems as if the analyzer is not wanting to honor the time_dim join
against the indexed view.
in a nut shell I am attempting to get the analyzer to query against the
summary indexed view based on a date constraint.
The thought behind this is a user would be able to build a query against the
lowest level of aggregation (the detail table "tbl_detail") and based on the
level of aggregation and the date constraints it would choose the most
appropriate table of indexed view to used to resolve the result set.
(Thinking that if a user had asked for a full years worth of data at the
client level (found in the index view "mv_summary" ) it would be more
efficient to pull process maybe 12 I/Os verses 12 million I/O s ) "That woul
d
make total since to me... in fact it does do that on the 1st query in my
example: " though once I introduce time into the equalization it go after th
e
detail table every time.
hmmm... the has to be a way to do this... maybe I just not seeing it... I
just can't accept that Microsoft would release a product that would allow yo
u
to perform aggregation one way but not another... especially on one such a
useful as date...
Any thoughts?
Thanks
Eric
/* code start : */
--drop sample objects
drop view mv_summary
drop table tbl_detail
drop table time_dim
drop table client_dim
--create detail table
create table tbl_detail(
ID int identity(1,1),
invoice_date datetime,
invoice_month as CASE
WHEN cast(datepart(mm,invoice_date) as varchar(2)) < = 9 THEN
cast(datepart(yyyy,invoice_date)as varchar(4)) + '0' +
cast(datepart(mm,invoice_date)as varchar(2))
ELSE cast(datepart(yyyy,invoice_date)as varchar(4)) +
cast(datepart(mm,invoice_date) as varchar(2))
END,
month_begin_date as dateadd(month,datediff(month,0,[invoice_date]),0),
client_id varchar(10),
sales money not null)
go
--create time dimension
create table time_dim(
date_number datetime ,
month_begin_date as dateadd(month,datediff(month,0,date_numb
er),0),
month_code as CASE
WHEN cast(datepart(mm,date_number) as varchar(2)) < = 9 THEN
cast(datepart(yyyy,date_number)as varchar(4)) + '0' +
cast(datepart(mm,date_number)as varchar(2))
ELSE cast(datepart(yyyy,date_number)as varchar(4)) +
cast(datepart(mm,date_number) as varchar(2))
END)
go
--create client list
create table client_dim (client_id varchar(10), client_desc varchar(20),
usedflag bit null)
insert into client_dim(client_id,client_desc)values(
'A0000001','A0000001TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000002','A0000002TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000003','A0000003TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000004','A0000004TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000005','A0000005TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000006','A0000006TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000007','A0000007TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000008','A0000008TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000009','A0000009TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000010','A0000010TEST
')
go
--create index
create index idx_client_id on client_dim (client_id)
go
--loop through each client and build a random list of data.
declare @.loopcnt int
set @.loopcnt = 0
declare @.clientloopcnt int
set @.clientloopcnt = 0
declare @.clientid varchar(10)
declare @.nextdate datetime
set @.nextdate = getdate()
--populate detail table
while @.loopcnt < 1--0000
begin
insert into tbl_detail ( invoice_date, client_id, sales)
select
cast(cast(getdate() as int) -115* rand(cast(cast(newid() as binary(8))
as int))as datetime) as invoice_date,
client_id,
cast(cast(100 as int) -115* rand(cast(cast(newid() as binary(8)) as
int))as money)
from client_dim --where usedflag is not null
set @.loopcnt = @.loopcnt + 1
print 'The loop counter is ' + cast(@.loopcnt as char)
--update the client as being complete...
set @.clientloopcnt = @.clientloopcnt + 1
end
--populate time dimension
while @.loopcnt < 365
begin
insert into time_dim (date_number)
values (@.nextdate)
set @.nextdate = @.nextdate + 1
set @.loopcnt = @.loopcnt + 1
print 'The loop counter is ' + cast(@.loopcnt as char)
--update the client as being complete...
end
go
go
--create indexed view
create view mv_summary with schemabinding
as
select a.month_begin_date, a.client_id, sum(a.sales) as sales, count_big(*)
as RC
from dbo.tbl_detail a
group by a.month_begin_date ,a.client_id
go
create unique clustered index cidx_client_invoice_date on mv_summary(
client_id, month_begin_date)
go
--this query invokes the summary as expected...
select a.month_begin_date , b.client_id , b.client_desc , sum(sales) as sale
s
from dbo.tbl_detail a
inner join dbo.client_dim b
on
a.client_id = b.client_id
where b.client_desc = 'A0000001TEST'
group by a.month_begin_date, b.client_id , b.client_desc
--this query does NOT invokes the summary... though not sure why...
select a.month_begin_date , b.client_id , b.client_desc , sum(sales) as sale
s
from dbo.tbl_detail a
inner join dbo.client_dim b
on
a.client_id = b.client_id
inner join time_dim t
on
t.month_begin_date = a.month_begin_date
where b.client_desc = 'A0000001TEST' and t.date_number between '6/1/2006'
and '6/30/2006'
group by a.month_begin_date, b.client_id , b.client_desc
/* code end: */
"Mike Hodgson" wrote:

> In theory it ought to work fine (ie. use the indexed view in the query
> plan) IFF the optimiser determines that that's the most efficient way to
> return the data (although I've had cases where querying the base table
> was so quick that the optimiser decided to chose it over a corresponding
> indexed view anyway and not waste it's time evaluating query plans
> against the indexed view).
> The T-SQL code you post looks rather incomplete. Among other things,
> shouldn't there be at least a GROUP BY in your view? What I'm trying to
> get at is the SUM() aggregates in the view, are they usable in your
> other query or are you grouping the rows into different groups when
> calculating your SUM() aggregates? I'm guessing if you summed the sales
> column from your view for all invoice years for a particular client_id
> you'd get the same figure as summing the sales figure for that client_id
> in the base table wouldn't you? Have I correctly guessed the grouping
> you've used in the view?
> If that's true then perhaps the optimiser thinks it's harder to join the
> materialised view data to the client_dimension table than it is to join
> the base tbl_detail table to that client_dimension table. I notice the
> clustered index you create on the view has the invoiceyear column 1st
> (and the client_id column 2nd), which would make it rather nasty to join
> to client_dimension. I assume tbl_detail & client_dimension both have
> nice indexes (maybe even clustered indexes) where client_id is the 1st
> column in those indexes; if so, then a join between tbl_detail and
> client_dimension would probably be more efficient than between
> mv_summary and client_dimension (and so the query optimiser would
> probably pick a join with the base table rather than the clustered index
> on the view).
> I'd check the execution plans, try changing the clustered unique index
> on mv_summary so that client_id is the first column in the index and
> make sure the grouping in the view & the later query are "compatible".
> The bottom line is, in theory, it ought to work fine but there's
> probably just something a bit off with your scenario/implementation.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Eric wrote:
>
>|||Ok, I have written a script to reproduce the issue I am seeing...
(Though I was able to get SQL to pull from the indexed view with a
constraint on a joining dimensional table by removing the joining table from
the indexed view...) though that does seem odd to me why that won't work.
as far as the time awareness aggregation
it seems as if the analyzer is not wanting to honor the time_dim join
against the indexed view.
in a nut shell I am attempting to get the analyzer to query against the
summary indexed view based on a date constraint.
The thought behind this is a user would be able to build a query against the
lowest level of aggregation (the detail table "tbl_detail") and based on the
level of aggregation and the date constraints it would choose the most
appropriate table of indexed view to used to resolve the result set.
(Thinking that if a user had asked for a full years worth of data at the
client level (found in the index view "mv_summary" ) it would be more
efficient to pull process maybe 12 I/Os verses 12 million I/O s ) "That woul
d
make total since to me... in fact it does do that on the 1st query in my
example: " though once I introduce time into the equalization it go after th
e
detail table every time.
hmmm... the has to be a way to do this... maybe I just not seeing it... I
just can't accept that Microsoft would release a product that would allow yo
u
to perform aggregation one way but not another... especially on one such a
useful as date...
Any thoughts?
Thanks
Eric
/* code start : */
--drop sample objects
drop view mv_summary
drop table tbl_detail
drop table time_dim
drop table client_dim
--create detail table
create table tbl_detail(
ID int identity(1,1),
invoice_date datetime,
invoice_month as CASE
WHEN cast(datepart(mm,invoice_date) as varchar(2)) < = 9 THEN
cast(datepart(yyyy,invoice_date)as varchar(4)) + '0' +
cast(datepart(mm,invoice_date)as varchar(2))
ELSE cast(datepart(yyyy,invoice_date)as varchar(4)) +
cast(datepart(mm,invoice_date) as varchar(2))
END,
month_begin_date as dateadd(month,datediff(month,0,[invoice_date]),0),
client_id varchar(10),
sales money not null)
go
--create time dimension
create table time_dim(
date_number datetime ,
month_begin_date as dateadd(month,datediff(month,0,date_numb
er),0),
month_code as CASE
WHEN cast(datepart(mm,date_number) as varchar(2)) < = 9 THEN
cast(datepart(yyyy,date_number)as varchar(4)) + '0' +
cast(datepart(mm,date_number)as varchar(2))
ELSE cast(datepart(yyyy,date_number)as varchar(4)) +
cast(datepart(mm,date_number) as varchar(2))
END)
go
--create client list
create table client_dim (client_id varchar(10), client_desc varchar(20),
usedflag bit null)
insert into client_dim(client_id,client_desc)values(
'A0000001','A0000001TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000002','A0000002TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000003','A0000003TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000004','A0000004TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000005','A0000005TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000006','A0000006TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000007','A0000007TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000008','A0000008TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000009','A0000009TEST
')
insert into client_dim(client_id,client_desc)values(
'A0000010','A0000010TEST
')
go
--create index
create index idx_client_id on client_dim (client_id)
go
--loop through each client and build a random list of data.
declare @.loopcnt int
set @.loopcnt = 0
declare @.clientloopcnt int
set @.clientloopcnt = 0
declare @.clientid varchar(10)
declare @.nextdate datetime
set @.nextdate = getdate()
--populate detail table
while @.loopcnt < 1--0000
begin
insert into tbl_detail ( invoice_date, client_id, sales)
select
cast(cast(getdate() as int) -115* rand(cast(cast(newid() as binary(8))
as int))as datetime) as invoice_date,
client_id,
cast(cast(100 as int) -115* rand(cast(cast(newid() as binary(8)) as
int))as money)
from client_dim --where usedflag is not null
set @.loopcnt = @.loopcnt + 1
print 'The loop counter is ' + cast(@.loopcnt as char)
--update the client as being complete...
set @.clientloopcnt = @.clientloopcnt + 1
end
--populate time dimension
while @.loopcnt < 365
begin
insert into time_dim (date_number)
values (@.nextdate)
set @.nextdate = @.nextdate + 1
set @.loopcnt = @.loopcnt + 1
print 'The loop counter is ' + cast(@.loopcnt as char)
--update the client as being complete...
end
go
go
--create indexed view
create view mv_summary with schemabinding
as
select a.month_begin_date, a.client_id, sum(a.sales) as sales, count_big(*)
as RC
from dbo.tbl_detail a
group by a.month_begin_date ,a.client_id
go
create unique clustered index cidx_client_invoice_date on mv_summary(
client_id, month_begin_date)
go
--this query invokes the summary as expected...
select a.month_begin_date , b.client_id , b.client_desc , sum(sales) as sale
s
from dbo.tbl_detail a
inner join dbo.client_dim b
on
a.client_id = b.client_id
where b.client_desc = 'A0000001TEST'
group by a.month_begin_date, b.client_id , b.client_desc
--this query does NOT invokes the summary... though not sure why...
select a.month_begin_date , b.client_id , b.client_desc , sum(sales) as sale
s
from dbo.tbl_detail a
inner join dbo.client_dim b
on
a.client_id = b.client_id
inner join time_dim t
on
t.month_begin_date = a.month_begin_date
where b.client_desc = 'A0000001TEST' and t.date_number between '6/1/2006'
and '6/30/2006'
group by a.month_begin_date, b.client_id , b.client_desc
/* code end: */
"Mike Hodgson" wrote:

> In theory it ought to work fine (ie. use the indexed view in the query
> plan) IFF the optimiser determines that that's the most efficient way to
> return the data (although I've had cases where querying the base table
> was so quick that the optimiser decided to chose it over a corresponding
> indexed view anyway and not waste it's time evaluating query plans
> against the indexed view).
> The T-SQL code you post looks rather incomplete. Among other things,
> shouldn't there be at least a GROUP BY in your view? What I'm trying to
> get at is the SUM() aggregates in the view, are they usable in your
> other query or are you grouping the rows into different groups when
> calculating your SUM() aggregates? I'm guessing if you summed the sales
> column from your view for all invoice years for a particular client_id
> you'd get the same figure as summing the sales figure for that client_id
> in the base table wouldn't you? Have I correctly guessed the grouping
> you've used in the view?
> If that's true then perhaps the optimiser thinks it's harder to join the
> materialised view data to the client_dimension table than it is to join
> the base tbl_detail table to that client_dimension table. I notice the
> clustered index you create on the view has the invoiceyear column 1st
> (and the client_id column 2nd), which would make it rather nasty to join
> to client_dimension. I assume tbl_detail & client_dimension both have
> nice indexes (maybe even clustered indexes) where client_id is the 1st
> column in those indexes; if so, then a join between tbl_detail and
> client_dimension would probably be more efficient than between
> mv_summary and client_dimension (and so the query optimiser would
> probably pick a join with the base table rather than the clustered index
> on the view).
> I'd check the execution plans, try changing the clustered unique index
> on mv_summary so that client_id is the first column in the index and
> make sure the grouping in the view & the later query are "compatible".
> The bottom line is, in theory, it ought to work fine but there's
> probably just something a bit off with your scenario/implementation.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Eric wrote:
>
>|||Does anyone have any ideas as to why this might be happening.
"Eric" wrote:

> Please correct me if I'm wrong here but what's the purpose of sql server
> indexed views.. I mean I don't mean to go off on rant here and please
> understand this is not a post to discredited ms sql server 2005 at all, I'
m
> just trying to get some clarification that's all.
> ok.. with that said. from what I understand in a nut shell about 2005
> indexed views is when the view is persisted ALL objects with in the view a
re
> persisted.. which is to be expected.. what I found to my surprises is how
sql
> server seems to perform aggregated awareness only when all element with th
e
> view are present. in other words if you present any other elements ( like
a
> another dimensional table join that is at the same level of aggregation th
at
> the view is at) currently at seems to break the optimal query plan that I
> would expect the optimizer to take.
> example:
> 1. base detail table called table_detail a has 1 million records in it.
>
> create table tbl_detail
> ( client_id into, invoicedate, sales money)
>
> 2. an indexed view called mv_summary is created over tbl_detail table
>
> create view mv_summary as
> select client_id , datepart(yyyy,invoicedate) as invoiceyear, sum(sales) a
s
> sales
> go
> create unique clustered index cidx_yearclient on ,mv_summary(invoiceyear,
> client_id)
> go
>
> 3. lets say the mv_summary view is at lower level of aggregation, now the
> mv_summary has a 1000 aggregated record from the detail table.
> any select statements ran against the mv_summary view directly the plan ru
ns
> as expected. even if you were to query the tbl_detail at the aggregation
> level of the view the plan result returns mv_summary as expected via sql
> server arrogation awareness. though once you introduce an dimensional tabl
e
> is at the same level of aggregation to the query ( like a dimensional join
)
> ok this is were this get weird.
> the plan goes to the tbl_detail not the indexed view as I would expect it
to.
> basically what I attempted to do was filter on an dimensional descriptive
> element via join and where clause.
> example :
>
> select client_id , sum(sales) as sales
> from tbl_detail
> inner join client_dimension
> on
> tbl_detail.client_id = client_dimension.client_id
> where client_dimension.client_description 'test'
>
> turns out what I have been able to come up with is ALL elements that you
> need to filter join select on HAS to been in the indexed view... which see
ms
> to been an issue.. that mean ALL data including dimensional data has to be
> persisted again...
> any thoughts on how I can use indexed views in a aggregation aware
> environment without have to store ALL possible filterable elements in the
> view.
> thanks!!!
>|||I have one possible reason (which is merely a guess), and a speculation.
For a simple example where the cost difference (in absolute time) is not
that big (like the one in your repro script), the optimizer might not do
a full optimize, but stop searching for better query plans when a so
called 'obvious' plan is found. This mechanism is used to avoid
'wasting' time on searching for better plans that may never be found,
and instead to start executing immediately.
The speculation is, that Microsoft might not have invested enough effort
in analyzing if an indexed view might benefit the query. In your case,
the indexed view option is simply missed, because using it makes the
query definitely more efficient. Note that indexed views introduced as
'recently' as SQL Server 2000. IMO, its introduction this was mostly a
commercial statement, claiming that Microsoft was no longer behind
Oracle and IBM (with regard to this feature).
I can't tell if there were any improvements to indexed views (or their
use) in SQL Server 2005, but I can tell that I have not seen any
documentation suggesting there were any improvements. If someone has
seen such documentation, I would be very interested.
HTH,
Gert-Jan
Eric wrote:[vbcol=seagreen]
> Does anyone have any ideas as to why this might be happening.
> "Eric" wrote:
>