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

influence the length of sub-tokens indexed in fuzzy look-up

can I influence the lenghth of sub-tokens that are indexed in fuzzy lookup? Is it just fixed as 4?

ThanksAt this time the size is fixed. There is a performance trade-off between Error Tolerant Index size and the length and number of sub-tokens indexed per record. Using a fixed length may cause errors in short tokens to be missed if there are no other tokens in common between the input and target records. One approach, if your reference table is small, is to set the Exhaustive property to True. This will make Fuzzy Lookup skip the ETI and compare against each and every record in the reference table. Again, this is an expensive operation for large ref tables, so you might consider only doing it if the input record has only one short token. Likewise, you might also create a view of your reference table that contains only records of short length and do the Exhaustive match on just the view. You could have this as a separate branch in your Data Flow pipeline and use the Conditional Split transform to direct only short input records down it.

Hope this helps,
-Kris

Indexing Word Docs

We currently have an application that OCRs a tif image and places the
recognized text in a SQL table.
The table is then indexed by the FTS service.
The app then allows you to search for any of the text and display the
corresponding tif image in a viewer.
I would also like to be able to search WORD docs for their contents using
the same catalog.
What is the proper manner to have the WORD docs indexed by the FTS service?
Do I need to extract the text from the WORD doc and store it in the table
much like the recognized text
from the OCR process?
Thanks
John,
What is the relationship between FTS and Indexing Service?
It looks like the Indexing Service maintains a catalog much the same as FTS.
We have support for WORD in our app already by storing the WORD doc in our
file warehouse on the file system.
We can display the .doc file in our viewer the same as a .tif image.
We currently don't have functionality to search for data in the WORD docs,
only text from the OCR process.
Since the WORD file is already stored in the file system and referenced by
our application, I was wondering about the feature that is titled "Full-text
Querying of File Data"
It looks like it uses the Index Service to allow searching for data in files
on the file system.
Wouldn't that work for my scenario?
It appears that when we want to search for data contained in a WORD doc, we
would use the SCOPE function in our query. Otherwise, we continue to search
for text from the OCR process.
Can you provide some insight?
Thanks
"John Kane" <jt-kane@.comcast.net> wrote in message
news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> Binder,
> What version of SQL Server (2000 or 7.0) and on what OS platform (NT4.0,
> Win2K, or Win2003) is it installed? Could you post the full output of
> SELECT @.@.version -- as this is helpful to answering your question.
> If you are using SQL Server 2000, you can use it's new feature (this
feature
> is not present in SQL 7.0) - from SQL Sever 2000 BOL title "Filtering
> Supported File Types". This feature allows you to store the binary version
> of the MS Word document and then in your table define a file extension
> column and populate it with the correct values ("doc" for MS Word
document)
> and then run a Full Population and then you can use the CONTAINS or
FREETEXT
> quires to FTS the contents of these files stored in a sql table>
> If you are using SQL Server 7.0, you will need to setup a process to
extract
> the MS Word text and then store this text in a TEXT column and the FT
Index[vbcol=seagreen]
> that column, much as you do for your OCR'ed data.
> Regards,
> John
>
> "Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
> news:eIXu546VEHA.2716@.tk2msftngp13.phx.gbl...
using[vbcol=seagreen]
> service?
table
>
|||System Parameters:
Windows 2000 Server
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>
|||Binder,
Q. What is the relationship between FTS and Indexing Service?
A. While they use the same underlying Microsoft Search Technology, they full
text index different servers. Indexing Service handles the server's files on
its local disk drive, while FTS (or really the "Micrsoft Search" service
[mssearch.exe]) full text indexes textaul (char, nvarchar, text, etc.)
columns in SQL Server tables. Yes, it seems to me that using the Indexing
Service, should work for you.
What is the name of your app? Does it support SQL Server 2000? If so, does
it support the storage of MS Word documents in columns that are defined with
the IMAGE datatype? Is the feature that is titled "Full-text Querying of
File Data", a feature of your app, or are you referring to the feature of
SQL Severer (version) ?
In addition to SQL Server's Full-text Search (FTS) component, you can also
define a "Linked Server" to the Indexing Service via using MSIDX, the "OLE
DB Provider for Microsoft Indexing Service". You would define this linked
server via sp_addlinkedserver. Below is an example from SQL Server 2000
Books Online:
G. Use the Microsoft OLE DB Provider for Indexing Service
This example creates a linked server and uses OPENQUERY to retrieve
information from both the linked server and the file system enabled for
Indexing Service.
EXEC sp_addlinkedserver FileSystem,
'Index Server',
'MSIDXS',
'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'yEmployees')
DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
(
id int NOT NULL,
lname varchar(30) NOT NULL,
fname varchar(30) NOT NULL,
salary money,
hiredate datetime
)
GO
INSERT yEmployees VALUES
(
10,
'Fuller',
'Andrew',
$60000,
'9/12/98'
)
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'DistribFiles')
DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles
AS
SELECT *
FROM OPENQUERY(FileSystem,
'SELECT Directory,
FileName,
DocAuthor,
Size,
Create,
Write
FROM SCOPE('' "c:\My Documents" '')
WHERE CONTAINS(''Distributed'') > 0
AND FileName LIKE ''%.doc%'' ')
WHERE DATEPART(yy, Write) = 1998
GO
SELECT *
FROM DistribFiles
GO
SELECT Directory,
FileName,
DocAuthor,
hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO
Regards,
John
"Binder" <rgondzur@.NO_SPAM_aicsoft.com> wrote in message
news:OcUOqQGWEHA.1012@.TK2MSFTNGP09.phx.gbl...
> John,
> What is the relationship between FTS and Indexing Service?
> It looks like the Indexing Service maintains a catalog much the same as
FTS.
> We have support for WORD in our app already by storing the WORD doc in our
> file warehouse on the file system.
> We can display the .doc file in our viewer the same as a .tif image.
> We currently don't have functionality to search for data in the WORD docs,
> only text from the OCR process.
> Since the WORD file is already stored in the file system and referenced by
> our application, I was wondering about the feature that is titled
"Full-text
> Querying of File Data"
> It looks like it uses the Index Service to allow searching for data in
files
> on the file system.
> Wouldn't that work for my scenario?
> It appears that when we want to search for data contained in a WORD doc,
we
> would use the SCOPE function in our query. Otherwise, we continue to
search[vbcol=seagreen]
> for text from the OCR process.
> Can you provide some insight?
> Thanks
>
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:O7TiL6BWEHA.2928@.tk2msftngp13.phx.gbl...
> feature
version
> document)
> FREETEXT
> extract
> Index
> using
> table
>

Indexing image - file size limit?

According to the Books Online information on full-text indexing, there
are limits on the size of a file that can be indexed in an image
column: 16MB filesize, 256 KB of filtered text. I've exceeded those
limits in my testing (with Word docs), and still appear to be able to
access information in those files with CONTAINS. Is the documentation
out of date? Are there only certain conditions under which those limits
apply? The word I'm searching for appears only at the end of the test
document, so it's not indexing only the first part of the file...
Since it seems to be a common question here, this is my @.@.version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
And, just for clarity, I don't have any problem with SQL Server
indexing more than I had planned on, I just don't want any surprises
down the road.
Thanks for any ideas you have,
Joel
Last time I tested, when the hard limit was exceeded the remaining content
was not indexed.
So if you index a document containing more than 256k of text, and then put
the word rats at the end, and then tried to search on the word rats, you
would not get hits to this row, if the word rats did not occur in the first
256k of text.
One question for you is did these word docs contains any images? Images will
not be indexed, and can swell the document size, without pushing you over
the 256 k limit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105371120.743181.194610@.c13g2000cwb.googlegr oups.com...
> According to the Books Online information on full-text indexing, there
> are limits on the size of a file that can be indexed in an image
> column: 16MB filesize, 256 KB of filtered text. I've exceeded those
> limits in my testing (with Word docs), and still appear to be able to
> access information in those files with CONTAINS. Is the documentation
> out of date? Are there only certain conditions under which those limits
> apply? The word I'm searching for appears only at the end of the test
> document, so it's not indexing only the first part of the file...
> Since it seems to be a common question here, this is my @.@.version:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: )
> And, just for clarity, I don't have any problem with SQL Server
> indexing more than I had planned on, I just don't want any surprises
> down the road.
> Thanks for any ideas you have,
> Joel
>
|||No, the documents that are confusing me did not have any images. They
were just a bunch of text, pasted repeatedly. I ran them through
filtdump, to make sure they really did have more than 256K of text. The
test you describe is exactly what I did--I put words at the very end of
the document that I was sure weren't in the document before, and once
the catalog rebuilt, I searched for them, and found them.
Thanks,
Joel
|||Let me try this myself. I did try this several years ago so this may have
changed with a recent sp.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||Joel,
Q. Is the documentation out of date?
A. Actually, it is wrong as there is a DOC bug filed for this limited in the
BOL title "Filtering Supported File Types" - "Note For full-text indexing,
a document must be less than 16 megabytes (MB) in size and must not contain
more than 256 kilobytes (KB) of filtered text" and this limit can be
over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search May
Not Return Any Hits If It Fails to Index a File" at
http://support.microsoft.com/default...;en-us;308771. and the
FilterProcessMemoryQuota registry key value. However, you should be careful
in making adjustments to this registry key and incrementally increase it
based upon your server's memory and avg. file sizes.
Q. Are there only certain conditions under which those limits apply?
A. Not specific conditions, but you should ensure that you have enough disk
free space (at least always 15% free) at all times where you have your FT
Catalog folder located as temp. files are written out as needed for the
processing of large files at the same location.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
> No, the documents that are confusing me did not have any images. They
> were just a bunch of text, pasted repeatedly. I ran them through
> filtdump, to make sure they really did have more than 256K of text. The
> test you describe is exactly what I did--I put words at the very end of
> the document that I was sure weren't in the document before, and once
> the catalog rebuilt, I searched for them, and found them.
> Thanks,
> Joel
>
|||I'm not entirely sure I'm clear. If I'm reading that article right, it
looks like there is still some point at which indexing a document will
fail due to lack of memory. However, that point cannot be determined by
examining the file size of the document. Is that accurate?
Thanks,
Joel
John Kane wrote:
> Joel,
> Q. Is the documentation out of date?
> A. Actually, it is wrong as there is a DOC bug filed for this limited
in the
> BOL title "Filtering Supported File Types" - "Note For full-text
indexing,
> a document must be less than 16 megabytes (MB) in size and must not
contain
> more than 256 kilobytes (KB) of filtered text" and this limit can be
> over-ridden via KB article: 308771 (Q308771) "PRB: A Full-Text Search
May
> Not Return Any Hits If It Fails to Index a File" at
> http://support.microsoft.com/default...;en-us;308771. and
the
> FilterProcessMemoryQuota registry key value. However, you should be
careful
> in making adjustments to this registry key and incrementally increase
it
> based upon your server's memory and avg. file sizes.
> Q. Are there only certain conditions under which those limits apply?
> A. Not specific conditions, but you should ensure that you have
enough disk
> free space (at least always 15% free) at all times where you have
your FT
> Catalog folder located as temp. files are written out as needed for
the[vbcol=seagreen]
> processing of large files at the same location.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
They[vbcol=seagreen]
The[vbcol=seagreen]
end of[vbcol=seagreen]
once[vbcol=seagreen]
|||You're welcome, Joel,
Yea, the RESOLUTION section states "Unfortunately, there is no way to
calculate directly from the size of the document to be full-text indexed how
much memory the filter process needs. The memory quota only exists to
protect against badly written filters, and they do spike to large amounts if
some bogus size contains a negative number. The quota itself can be made
larger, as long as it is finite. "
While no upper limit size for documents to be FT Indexed is documented, you
can increase the amount of text to be indexed by modifying the
FilterProcessMemoryQuota registry key value and you need to test your
documents on your server to get a feel for what is the "finite" limit and
monitor the server's application event log for "Microsoft Search" source
events for very large files that fail.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<nospamforjoel@.yahoo.com> wrote in message
news:1105452953.633306.318410@.f14g2000cwb.googlegr oups.com...
> I'm not entirely sure I'm clear. If I'm reading that article right, it
> looks like there is still some point at which indexing a document will
> fail due to lack of memory. However, that point cannot be determined by
> examining the file size of the document. Is that accurate?
> Thanks,
> Joel
> John Kane wrote:
> in the
> indexing,
> contain
> May
> the
> careful
> it
> enough disk
> your FT
> the
> They
> The
> end of
> once
>
|||I just tried it again. I indexed a 32 Mg text and a 16 Mg word doc and have
confirmed that at least first 256 k of extracted text is indexed, but that
tokens at the end of the documents are not. Any textual data after this 256k
boundary appears to be ignored.
I have the same version of SQL Server as you, only I am running on Win2k.
Let me try with Win2003.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u6I6P509EHA.3376@.TK2MSFTNGP12.phx.gbl...
> Let me try this myself. I did try this several years ago so this may have
> changed with a recent sp.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> <nospamforjoel@.yahoo.com> wrote in message
> news:1105386126.803682.118060@.c13g2000cwb.googlegr oups.com...
>

indexes

Anyone can tell me the fast way to find out which columns
of the tables are indexed? Thanks.Either query sysindexes and sysindexkeys. Or use sp_helpindex.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill" <fei0405@.yahoo.com> wrote in message news:019301c49b46$23315c70$a301280a@.phx.gbl...
> Anyone can tell me the fast way to find out which columns
> of the tables are indexed? Thanks.

indexes

Anyone can tell me the fast way to find out which columns
of the tables are indexed? Thanks.
Either query sysindexes and sysindexkeys. Or use sp_helpindex.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bill" <fei0405@.yahoo.com> wrote in message news:019301c49b46$23315c70$a301280a@.phx.gbl...
> Anyone can tell me the fast way to find out which columns
> of the tables are indexed? Thanks.

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