Max function cannot be used in an indexed Views

All,
Does anyone know a workaround for the restriction to use max in an
indexed view? seems it really can help us to improve performance, but
we MUST use max to define the view...
any suggestions?
10x
GC
GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC
No way, and usully no need to do it either. My understanding of why is
as follows:
First reason, usually MAX and MIN can be selected from a proper index
with a quick index seek - there usually is no need to further speed it
up. Second reason, it would be not that simple to re-calculte MAX and
or MIN if you delete rows with that (MAX or MIN) value. So,
implementing that would be a lot of pain in the neck for little
practical advantage.
I think just using a proper index should be good enough under most
circustances.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/
sql

Max function cannot be used in an indexed Views

All,
Does anyone know a workaround for the restriction to use max in an
indexed view? seems it really can help us to improve performance, but
we MUST use max to define the view...
any suggestions?
10x
GCThere isn't. This is because indexed views are built in such a way, that
each individual transaction can be processed without having to rebuild
the entire indexed view. This is not possible if there is an aggregate
such as AVG, MIN, MAX.
You could build your own 'materialized view' and maintain it using
triggers on the base table. However, deletes or updates of the maximum
value of a group might have serious performance impact. Proper indexes
could mitigate this problem.
HTH,
Gert-Jan
GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC|||GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC
No way, and usully no need to do it either. My understanding of why is
as follows:
First reason, usually MAX and MIN can be selected from a proper index
with a quick index seek - there usually is no need to further speed it
up. Second reason, it would be not that simple to re-calculte MAX and
or MIN if you delete rows with that (MAX or MIN) value. So,
implementing that would be a lot of pain in the neck for little
practical advantage.
I think just using a proper index should be good enough under most
circustances.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||On 2 Jan 2007 05:01:25 -0800, GC wrote:
>All,
>Does anyone know a workaround for the restriction to use max in an
>indexed view? seems it really can help us to improve performance, but
>we MUST use max to define the view...
>any suggestions?
>10x
>GC
Hi GC,
I'm afraid you'll have to rol your own. You can duplicate the "under the
cover" implementation of an indexed view by creating a table to store
the results, and populating it by using INSERT INTO ... SELECT and then
the query you currently have in your view. Next, to keep it current, add
triggers on the base tables.
Simple example (untested) - note that the second update statement in the
trigger, the one that finds the new max if the old max gets deleted or
updated, can make the trigger very slow!!
CREATE TABLE dbo.TestTab
(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
CREATE VIEW dbo.TestView WITH SCHEMABINDING
AS
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE UNIQUE CLUSTERED INDEX TestIndex ON dbo.TestView(b);
go
DROP VIEW dbo.TestView;
go
CREATE TABLE dbo.TestView
(b int NOT NULL PRIMARY KEY,
MaxOfA int NOT NULL);
INSERT INTO dbo.TestView (b, MaxOfA)
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE TRIGGER TrgTestView1
ON TestTab FOR INSERT, UPDATE, DELETE
AS
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM inserted AS i
WHERE i.b = TestView.b)
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.b = TestView.b
AND i.a > TestView.MaxOfA);
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM TestTab AS t
WHERE t.b = TestView.b)
WHERE EXISTS (SELECT *
FROM deleted AS d
WHERE d.b = TestView.b
AND d.a = TestView.MaxOfA);
go
DROP TABLE TestView;
DROP TABLE TestTab;
go
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:54:05 +0100, Hugo Kornelis wrote:
(snip)
>Simple example (untested) - note that the second update statement in the
>trigger, the one that finds the new max if the old max gets deleted or
>updated, can make the trigger very slow!!
I just realized I forgot to put in code to add a row to TestView if a
new value for b is added, and code to remove a row if the last occurence
of a value is removed. Something like this:
INSERT INTO TestView (b, MaxOfA)
SELECT i.b, MAX(i.a)
FROM inserted AS i
WHERE NOT EXISTS
(SELECT *
FROM TestView AS t
WHERE t.b = i.b);
and
DELETE FROM TestView
WHERE EXISTS
(SELECT *
FROM deleted AS d
WHERE d.b = TestView.b)
AND NOT EXISTS
(SELECT *
FROM TestTab AS t
WHERE t.b = TestView.b);
The latter will, again, be slow.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Max function cannot be used in an indexed Views

All,
Does anyone know a workaround for the restriction to use max in an
indexed view? seems it really can help us to improve performance, but
we MUST use max to define the view...
any suggestions?
10x
GCThere isn't. This is because indexed views are built in such a way, that
each individual transaction can be processed without having to rebuild
the entire indexed view. This is not possible if there is an aggregate
such as AVG, MIN, MAX.
You could build your own 'materialized view' and maintain it using
triggers on the base table. However, deletes or updates of the maximum
value of a group might have serious performance impact. Proper indexes
could mitigate this problem.
HTH,
Gert-Jan
GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC|||GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC
No way, and usully no need to do it either. My understanding of why is
as follows:
First reason, usually MAX and MIN can be selected from a proper index
with a quick index seek - there usually is no need to further speed it
up. Second reason, it would be not that simple to re-calculte MAX and
or MIN if you delete rows with that (MAX or MIN) value. So,
implementing that would be a lot of pain in the neck for little
practical advantage.
I think just using a proper index should be good enough under most
circustances.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||On 2 Jan 2007 05:01:25 -0800, GC wrote:

>All,
>Does anyone know a workaround for the restriction to use max in an
>indexed view? seems it really can help us to improve performance, but
>we MUST use max to define the view...
>any suggestions?
>10x
>GC
Hi GC,
I'm afraid you'll have to rol your own. You can duplicate the "under the
cover" implementation of an indexed view by creating a table to store
the results, and populating it by using INSERT INTO ... SELECT and then
the query you currently have in your view. Next, to keep it current, add
triggers on the base tables.
Simple example (untested) - note that the second update statement in the
trigger, the one that finds the new max if the old max gets deleted or
updated, can make the trigger very slow!!
CREATE TABLE dbo.TestTab
(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
CREATE VIEW dbo.TestView WITH SCHEMABINDING
AS
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE UNIQUE CLUSTERED INDEX TestIndex ON dbo.TestView(b);
go
DROP VIEW dbo.TestView;
go
CREATE TABLE dbo.TestView
(b int NOT NULL PRIMARY KEY,
MaxOfA int NOT NULL);
INSERT INTO dbo.TestView (b, MaxOfA)
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE TRIGGER TrgTestView1
ON TestTab FOR INSERT, UPDATE, DELETE
AS
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM inserted AS i
WHERE i.b = TestView.b)
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.b = TestView.b
AND i.a > TestView.MaxOfA);
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM TestTab AS t
WHERE t.b = TestView.b)
WHERE EXISTS (SELECT *
FROM deleted AS d
WHERE d.b = TestView.b
AND d.a = TestView.MaxOfA);
go
DROP TABLE TestView;
DROP TABLE TestTab;
go
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:54:05 +0100, Hugo Kornelis wrote:
(snip)
>Simple example (untested) - note that the second update statement in the
>trigger, the one that finds the new max if the old max gets deleted or
>updated, can make the trigger very slow!!
I just realized I forgot to put in code to add a row to TestView if a
new value for b is added, and code to remove a row if the last occurence
of a value is removed. Something like this:
INSERT INTO TestView (b, MaxOfA)
SELECT i.b, MAX(i.a)
FROM inserted AS i
WHERE NOT EXISTS
(SELECT *
FROM TestView AS t
WHERE t.b = i.b);
and
DELETE FROM TestView
WHERE EXISTS
(SELECT *
FROM deleted AS d
WHERE d.b = TestView.b)
AND NOT EXISTS
(SELECT *
FROM TestTab AS t
WHERE t.b = TestView.b);
The latter will, again, be slow.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
Stefan
Stefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan
|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
StefanStefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Max columns in subquery VIEW

When I try to query a view in a subquery, I experience
problems if the view returns more than 63 columns.
Ex: SELECT * FROM (SELECT * FROM X_View)
If X_View returns more than 63 fields, the result of the
entire query is empty.
Any hotfix/quickfix for this?
StefanStefan
What do you mean by 'empty' ? If you return 62 columns does it mean that
you will get not empty result?
Also , with your code it will be generate a syntax error . Add alias to the
subquery.
SELECT * FROM (SELECT * FROM X_View) AS d
"Stefan Nilsson" <sen@.syscomworld.com> wrote in message
news:203b01c42776$8472b270$a301280a@.phx.gbl...
> When I try to query a view in a subquery, I experience
> problems if the view returns more than 63 columns.
> Ex: SELECT * FROM (SELECT * FROM X_View)
> If X_View returns more than 63 fields, the result of the
> entire query is empty.
> Any hotfix/quickfix for this?
> Stefan|||Jeps .. if the VIEW returns 62 or 63 columns, everything works just
fine.
(Sorry ... I forgot to copy the alias part)
Stefan
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!sql

matrix showing wrong totals

Going bonkers here. I have a matrix that displays total referrals by month
for the year. Two months, Feb and April are off by 1 when I view the matrix,
but other months show correct amt. when just doing a simple query with same
criteria, the dataset is correct. I've looked at the data countless times
but cannot deterine where the issue is. Possible matrix problem? anyone seem
results llike this?
HELP!more food for thought. I changed the query a bit. rather than selecting all
companies who referred to us, I selected just one. Results were accurate with
just one referrer. I'm still perplexed though...
"Brian L" wrote:
> Going bonkers here. I have a matrix that displays total referrals by month
> for the year. Two months, Feb and April are off by 1 when I view the matrix,
> but other months show correct amt. when just doing a simple query with same
> criteria, the dataset is correct. I've looked at the data countless times
> but cannot deterine where the issue is. Possible matrix problem? anyone seem
> results llike this?
> HELP!|||are you using olap or relational?
sounds like a standard olap situation where you need to write some
crazy-ass MDX statement
-Aaron
Brian L wrote:
> more food for thought. I changed the query a bit. rather than selecting all
> companies who referred to us, I selected just one. Results were accurate with
> just one referrer. I'm still perplexed though...
> "Brian L" wrote:
> > Going bonkers here. I have a matrix that displays total referrals by month
> > for the year. Two months, Feb and April are off by 1 when I view the matrix,
> > but other months show correct amt. when just doing a simple query with same
> > criteria, the dataset is correct. I've looked at the data countless times
> > but cannot deterine where the issue is. Possible matrix problem? anyone seem
> > results llike this?
> >
> > HELP!

Matrix Question

I have a view that gives me the following information from some tables.

Cust # Cust Name Date Order Type

001 John Doe 20070401 OR1

002 Miss Doe 20070401 OR2

001 John Doe 20070402 OR2

002 Miss Doe 20070402 OR2

What I would like to do is set up a matrix type report. The report is by the last 6 rolling order dates, with some % columns. So two row examples might be

Cust# Cust Name 20070401 20070402 % (of last x = OR1) %(of last x = OR2)

001 John Doe OR1 OR2 50% 50%

002 Miss Doe OR2 OR2 0% 100%

Another column just like the last two % based off a 3rd type, and finally a total % column that simply adds the 3 columns up (should always equal 100%, just an error check)

First, can this be done with a matrix? I tried a table but it lists cust# twice, but I can be doing it wrong. I am ok doing this within the query if need be, if someone gives me a hint how

Thanks,

When you tried the table, did you set a row group on the customer number?|||Yes, and it will not give me a column for each date in table form.

matrix

hey there

Can anyone direct me to explicit examples of a matrix.

eg I would like to see the layout view // then preview

I am using Visual Studio.net 2003 and Reporting Services

thanks

jewel

You can install the AdventureWorks sample reports during the setup installation of Reporting Services. The "Company Sales" sample report provides a matrix layout.

You can also take a look at this article: http://www.gotreportviewer.com/matrices/index.html
While that how-to article is targeted at the ReportViewer controls shipped in VS 2005, most of it still applies for Reporting Services 2000 / Report designer in VS 2003.

Also RS Books Online contains information about matrix reports, e.g.: http://msdn2.microsoft.com/en-us/library/ms157334.aspx

-- Robert

Materialized views

Hello,
Do you know how can i create a materialized view in sqlserver like oracle.
Because i want to store in a table the result of a query.You can create a view of the query you have and then create a clustered index on that view. This will make SQL Server store the data physically.|||This smells more like a simple SELECT INTO (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_03_8nad.asp) to me.

-PatP

Materialized Views

I'm having a problem creating a materialized view. Here's the view
definition:
Create View audit_Report
WITH SCHEMABINDING
AS
select a.audit_id, a.status_id, a.audit_date, a.reference_name...
from dbo.audits a
join dbo.references r
on a.reference_id=r.reference_id
The view is created successfully.
I then tried to create a clustered index:
Create clustered index ar_idx on audit_report(audit_id)
and got this result:
When the audit table was created the following Set options were set to off:
ANSI_NULLS
Is there anyway of getting around this? The table has info in it, so I
don't want to drop it and re-create it.MAS wrote:
> I'm having a problem creating a materialized view. Here's the view
> definition:
> Create View audit_Report
> WITH SCHEMABINDING
> AS
> select a.audit_id, a.status_id, a.audit_date, a.reference_name...
> from dbo.audits a
> join dbo.references r
> on a.reference_id=r.reference_id
> The view is created successfully.
> I then tried to create a clustered index:
> Create clustered index ar_idx on audit_report(audit_id)
> and got this result:
> When the audit table was created the following Set options were set to off
:
> ANSI_NULLS
> Is there anyway of getting around this? The table has info in it, so I
> don't want to drop it and re-create it.
AFAIK the only solution is to SET ANSI_NULLS ON and then re-create the
table. Always leave ANSI_NULLS set to ON.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

materialized view vs. denormalized table

sql2k sp3
Whats the difference between these two? Isn't a
materialized view nothing more than a denormalized table?
Dont you need to update/ repopulate them both if data in
the underlying table changes?
TIA, ChrisR
ChrisR,
A materialized view (i.e. Indexed View) is a denormalized table that the
system keeps up-to-date for you. So, no you would not have to repopulate
it.
Having said that, there are several rules that you have to follow to get an
Indexed View created, so you should carefully read the restrictions in the
Books Online.
Russell Fields
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR
|||A denormalized table is simply a table that doesn't follow the 3nf
standards. This happens a lot to improve efficiency of the queries at the
cost of storage space.
So a denormalized table may have duplicate columns, data or other items in
it that violate the normal forms.
A materialized view however is simply a view that has been created on a
table(s) that has then had a clustered index created for that view. SQL
Server stores the clustered index on the view. This can make queries much
faster. This is especially true in instances where there is a large amount
of calculations or aggregations going on in the query itself.
You do not have to repopulate the clustered index as data is modified in the
base tables. This happens automagically. =)
There is more to learn about this topic. Check out materialized views in
the Books Online.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR
|||The cost of SQL Serve maintaining an indexed view is fairly steep, compared
to you doing the work yourself. However if you denormalize you may have to
change apps as well, which would not be required for an indexed view... Also
the optimizer will automatically choose the indexed view ONLY if you are
running the Enterprise edition. In the standard edition you may create an
indexed view, but it will only be used when someone references the view
name...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR

materialized view vs. denormalized table

sql2k sp3
Whats the difference between these two? Isn't a
materialized view nothing more than a denormalized table?
Dont you need to update/ repopulate them both if data in
the underlying table changes?
TIA, ChrisRChrisR,
A materialized view (i.e. Indexed View) is a denormalized table that the
system keeps up-to-date for you. So, no you would not have to repopulate
it.
Having said that, there are several rules that you have to follow to get an
Indexed View created, so you should carefully read the restrictions in the
Books Online.
Russell Fields
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR|||A denormalized table is simply a table that doesn't follow the 3nf
standards. This happens a lot to improve efficiency of the queries at the
cost of storage space.
So a denormalized table may have duplicate columns, data or other items in
it that violate the normal forms.
A materialized view however is simply a view that has been created on a
table(s) that has then had a clustered index created for that view. SQL
Server stores the clustered index on the view. This can make queries much
faster. This is especially true in instances where there is a large amount
of calculations or aggregations going on in the query itself.
You do not have to repopulate the clustered index as data is modified in the
base tables. This happens automagically. =)
There is more to learn about this topic. Check out materialized views in
the Books Online.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR|||The cost of SQL Serve maintaining an indexed view is fairly steep, compared
to you doing the work yourself. However if you denormalize you may have to
change apps as well, which would not be required for an indexed view... Also
the optimizer will automatically choose the indexed view ONLY if you are
running the Enterprise edition. In the standard edition you may create an
indexed view, but it will only be used when someone references the view
name...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:799d01c49512$2e6bc060$a301280a@.phx.gbl...
> sql2k sp3
> Whats the difference between these two? Isn't a
> materialized view nothing more than a denormalized table?
> Dont you need to update/ repopulate them both if data in
> the underlying table changes?
>
> TIA, ChrisR

Materialized view or table function in SQL 2005

Hi,
Please advise whether SQL 2005 has the smiliar function as belows:
Materialized View in Oracle
Materialized Query Tables in DB2
Thank you.
Best Regards,
Lynn
Im not sure since I dont work with either Oracle or DB2 but I believe
indexed view would be something like that.
MC
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn
|||The purpose of this function is that the view or table is not repopulated the
data when it's queried. Usually, the data is already stored in that kind of
table when the original table is updated. When this kind of view/table is
queried, the data already exists without parsing the query to original table
and repopulated the table again. Indexed view in SQL 2005 don't have this
function.
Anyway, thanks.
Best Regards,
Lynn
"MC" wrote:

> Im not sure since I dont work with either Oracle or DB2 but I believe
> indexed view would be something like that.
>
> MC
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
>
>
|||indexed views store the aggregate result of a query (group by something
queries) and when the source table change, the view content is updated too.
like a table, you can create an index on it.
for example, if you always want to sum the sales by product, the indexed
view will contains the result of this grouping with an index on the product
column. when the source table is updated the view is updated too at the same
time so the total by product contains the new total.
when a user ask for the total of sales by product (or the sales for a group
of products or all the products) SQL server will use the indexed views
instead of scanning the big source table.
so its exactly the result you looking for.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:56067DCB-847F-4242-9B64-59463B9B79D6@.microsoft.com...[vbcol=seagreen]
> The purpose of this function is that the view or table is not repopulated
> the
> data when it's queried. Usually, the data is already stored in that kind
> of
> table when the original table is updated. When this kind of view/table is
> queried, the data already exists without parsing the query to original
> table
> and repopulated the table again. Indexed view in SQL 2005 don't have this
> function.
> Anyway, thanks.
> --
> Best Regards,
> Lynn
>
> "MC" wrote:
|||Lynn,
"On commit" materialized views in Oracle are conceptually the same as schema
bound views in SQL Server which have had a unique clustered index applied
prior to any other index. The Oracle materialized view grew out of the
snapshot functionality and has abilities like stale tolerance that do not
appear to be part of SQL Server Indexed Views.
The usage of indexed views can drastically increase the performace of select
queries at the cost insert, update, and delete. The following is a
reasonable introductory article which covers the concept:
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx
Luke
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn

Materialized view or table function in SQL 2005

Hi,
Please advise whether SQL 2005 has the smiliar function as belows:
Materialized View in Oracle
Materialized Query Tables in DB2
Thank you.
--
Best Regards,
LynnIm not sure since I dont work with either Oracle or DB2 but I believe
indexed view would be something like that.
MC
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn|||The purpose of this function is that the view or table is not repopulated th
e
data when it's queried. Usually, the data is already stored in that kind of
table when the original table is updated. When this kind of view/table is
queried, the data already exists without parsing the query to original table
and repopulated the table again. Indexed view in SQL 2005 don't have this
function.
Anyway, thanks.
--
Best Regards,
Lynn
"MC" wrote:

> Im not sure since I dont work with either Oracle or DB2 but I believe
> indexed view would be something like that.
>
> MC
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
>
>|||indexed views store the aggregate result of a query (group by something
queries) and when the source table change, the view content is updated too.
like a table, you can create an index on it.
for example, if you always want to sum the sales by product, the indexed
view will contains the result of this grouping with an index on the product
column. when the source table is updated the view is updated too at the same
time so the total by product contains the new total.
when a user ask for the total of sales by product (or the sales for a group
of products or all the products) SQL server will use the indexed views
instead of scanning the big source table.
so its exactly the result you looking for.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:56067DCB-847F-4242-9B64-59463B9B79D6@.microsoft.com...[vbcol=seagreen]
> The purpose of this function is that the view or table is not repopulated
> the
> data when it's queried. Usually, the data is already stored in that kind
> of
> table when the original table is updated. When this kind of view/table is
> queried, the data already exists without parsing the query to original
> table
> and repopulated the table again. Indexed view in SQL 2005 don't have this
> function.
> Anyway, thanks.
> --
> Best Regards,
> Lynn
>
> "MC" wrote:
>|||Lynn,
"On commit" materialized views in Oracle are conceptually the same as schema
bound views in SQL Server which have had a unique clustered index applied
prior to any other index. The Oracle materialized view grew out of the
snapshot functionality and has abilities like stale tolerance that do not
appear to be part of SQL Server Indexed Views.
The usage of indexed views can drastically increase the performace of select
queries at the cost insert, update, and delete. The following is a
reasonable introductory article which covers the concept:
http://www.microsoft.com/technet/pr...5/impprfiv.mspx
Luke
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:0E95254A-1C42-49D7-BE78-4F64C93CD18A@.microsoft.com...
> Hi,
> Please advise whether SQL 2005 has the smiliar function as belows:
> Materialized View in Oracle
> Materialized Query Tables in DB2
> Thank you.
> --
> Best Regards,
> Lynn

Materialized View Error 8908

Microsoft SQL Server 2005 - 9.00.1187.07

dbcc checkdb is failing with an interesting message:

Msg 8908, Level 16, State 1, Line 1

Indexed view 'BritishEnglishMV' (object ID 226099846) does not contain all rows that the view definition produces. Refer to Books Online for more information on this error. This does not necessarily represent an integrity issue with the data in this database.

The data materialized in the indexed view is exactly the same as the data in the underlying tables...
Books online has no info on this error.
Rebuilding the index fixes the problem.

This warning is produced if the indexed view does not 100% match the "newly generated" indexed view. This may happen in cases when there are updates performed on the underlying tables.
I will use an example to explain. If a view contains for examle an aggregation SUM, then inserting of new value to underlying table will add a new value to this sum. If the SUM was produced originally from a sequence of numbers, say a1, a2, ..., an, and the new inserted value is bb, then updating the indexed view means
(a1+a2+a3+...+an) + bb while recalculating the indexed view may prform the sum in different order.
We are still working on providing more information about warnings and errors we generate. This should improve substantially by the time we ship the final release of SQL Server 2005.

Lubor Kollar

materialized view equivalent ?

Hi,
Oracle query :
create materialized view view1 as select *from test
Is there any equivalent for the above query in SQL Server (specifically for "materialized views")
Please advice,
Thanks,
SamNot yet, at least not yet officially ;)|||Oracle query :

create materialized view view1 as select *from test


OK, I'll bite. What in the world is a materialized view? :confused:

It's not pulling a rabbit out of a hat, is it?|||Check out Indexed Views (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag2k/html/IndexedViews.asp). They aren't quite the same as materialized views (unless you can index every column), but they get PFC (within a Pentium Floating-point Calculation) of a materialized view.

-PatP|||Thanks Pat!|||PFC (within a Pentium Floating-point Calculation) that's funny!!!|||that's funny!!!Only if you have the background to appreciate the joke. Most people today (even the geeks) wouldn't have a clue why we think it is funny.

-PatP|||it's funny on two completely different levels

first, it actually stands for "pretty darned close"

second, well, i won't spoil it for ya :)

remember the bug in the windows 3.1 calculator? i still have that executable somewhere...|||Fubar... and Darned close at that!

Heck, I remember the original problems with die 510 of the P-60 (the one that incorporated the 80387 FPU onto the 81587 CPU to produce the first "single chip" CPU/FPU in the Intel line). It was the first chip that we knew of that had what Andy later called the "floating-point anomoly".

I don't think that the bug you are refering to was caused by software (although the 3.1 calculator had plenty of those too). I think that was a math problem that was a manifestation of the FPU problems on the early Pentium chips. If so, the problem hit anything doing Floating Point math, including Excel, 1-2-3, Calc-star, etc. It also caused havok with Autocad and other related CAD software.

-PatP

Matching a Views columns to its underlying tables columns

Hello,

Using SQL Server 2000, I'm trying to put together a query that will
tell me the following information about a view:
The View Name
The names of the View's columns
The names of the source tables used in the view
The names of the columns that are used from the source tables

Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
below, which gives me the View Name, Source Table Name, and Source
Column Name. And I can easily enough get the View columns from the
syscolumns table. The problem is that I haven't figured out how to
link a source column name to a view column name. Any help would be
appreciated.

Gary

select
v_obj.name as ViewName,
t_obj.name as SourceTable,
t_col.name as SourceColumn
from
sysobjects t_obj,
sysobjects v_obj,
sysdepends dep,
syscolumns t_col
where
v_obj.xtype = 'V'
and dep.id = v_obj.id
and dep.depid = t_obj.id
and t_obj.id = t_col.id
and dep.depnumber = t_col.colid
order by
v_obj.name,
t_obj.name,
t_col.namegaryderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> Hello,
> Using SQL Server 2000, I'm trying to put together a query that will
> tell me the following information about a view:
> The View Name
> The names of the View's columns
> The names of the source tables used in the view
> The names of the columns that are used from the source tables
> Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> below, which gives me the View Name, Source Table Name, and Source
> Column Name. And I can easily enough get the View columns from the
> syscolumns table. The problem is that I haven't figured out how to
> link a source column name to a view column name. Any help would be
> appreciated.
> Gary
>
> select
> v_obj.name as ViewName,
> t_obj.name as SourceTable,
> t_col.name as SourceColumn
> from
> sysobjects t_obj,
> sysobjects v_obj,
> sysdepends dep,
> syscolumns t_col
> where
> v_obj.xtype = 'V'
> and dep.id = v_obj.id
> and dep.depid = t_obj.id
> and t_obj.id = t_col.id
> and dep.depnumber = t_col.colid
> order by
> v_obj.name,
> t_obj.name,
> t_col.name

I don't believe that this information is available - sysdepends
records that the dependency exists, but not exactly what the
dependency is. The mapping of view to table columns could be 1:N or
M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
worth the effort to try and capture the detailed column mapping.

Simon|||Simon,

Thanks for the information, even though it wasn't what I wanted to hear.

Gary

sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0311060041.35542cec@.posting.google.com>...
> garyderousse@.yahoo.com (Gary DeRousse) wrote in message news:<9ce1cc62.0311051041.2dd0f428@.posting.google.com>...
> > Hello,
> > Using SQL Server 2000, I'm trying to put together a query that will
> > tell me the following information about a view:
> > The View Name
> > The names of the View's columns
> > The names of the source tables used in the view
> > The names of the columns that are used from the source tables
> > Borrowing code from the VIEW_COLUMN_USAGE view, I've got the code
> > below, which gives me the View Name, Source Table Name, and Source
> > Column Name. And I can easily enough get the View columns from the
> > syscolumns table. The problem is that I haven't figured out how to
> > link a source column name to a view column name. Any help would be
> > appreciated.
> > Gary
> > select
> > v_obj.name as ViewName,
> > t_obj.name as SourceTable,
> > t_col.name as SourceColumn
> > from
> > sysobjects t_obj,
> > sysobjects v_obj,
> > sysdepends dep,
> > syscolumns t_col
> > where
> > v_obj.xtype = 'V'
> > and dep.id = v_obj.id
> > and dep.depid = t_obj.id
> > and t_obj.id = t_col.id
> > and dep.depnumber = t_col.colid
> > order by
> > v_obj.name,
> > t_obj.name,
> > t_col.name
> I don't believe that this information is available - sysdepends
> records that the dependency exists, but not exactly what the
> dependency is. The mapping of view to table columns could be 1:N or
> M:N (or 1:0, in fact), so I would guess that MS decided that it wasn't
> worth the effort to try and capture the detailed column mapping.
> Simon