Hi,
Is there workaround to use MAX aggregate funcion in indexed views?
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola MilicNo there is not, and the reason is simple: when you modify a table, the
database engine is supposed to modify the indexed view based on only
the modified row and the relevant row in the view, not accessing
anything else. This is necessarty to minimize lock contention. What
happens if you delete the only one row with the MAX value? You will
have to access at least an index to get the smaller MAX value. That may
introduce lock contention and deadlocks, and it will complicate the
implementation, so it's not supported.
With SUM, it's a different story: when you delete a row, you decrement
the corresponding BIG_COUNT, and either decrement the SUM, or delete
the row from the indexed view altogether - no need to access anything
else...
Showing posts with label views. Show all posts
Showing posts with label views. Show all posts
Wednesday, March 28, 2012
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
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
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
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
Wednesday, March 7, 2012
Materialized Views?
I'm a little new to SQL Server but I recall using Materialized Views in
Oracle. Does SQL Server have anything like it?
Thanks,
G
SQL Server 2000 introduced Indexed Views, which are basically the same
thing.
You can read about them in Books Online, and then come back and ask for any
clarification or elaboration you need.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G. Dean Blake" <Dean@.nospam.com> wrote in message
news:etioX$ebEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I'm a little new to SQL Server but I recall using Materialized Views in
> Oracle. Does SQL Server have anything like it?
> Thanks,
> G
>
Oracle. Does SQL Server have anything like it?
Thanks,
G
SQL Server 2000 introduced Indexed Views, which are basically the same
thing.
You can read about them in Books Online, and then come back and ask for any
clarification or elaboration you need.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G. Dean Blake" <Dean@.nospam.com> wrote in message
news:etioX$ebEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I'm a little new to SQL Server but I recall using Materialized Views in
> Oracle. Does SQL Server have anything like it?
> Thanks,
> G
>
Materialized Views?
I'm a little new to SQL Server but I recall using Materialized Views in
Oracle. Does SQL Server have anything like it?
Thanks,
GSQL Server 2000 introduced Indexed Views, which are basically the same
thing.
You can read about them in Books Online, and then come back and ask for any
clarification or elaboration you need.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G. Dean Blake" <Dean@.nospam.com> wrote in message
news:etioX$ebEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I'm a little new to SQL Server but I recall using Materialized Views in
> Oracle. Does SQL Server have anything like it?
> Thanks,
> G
>
Oracle. Does SQL Server have anything like it?
Thanks,
GSQL Server 2000 introduced Indexed Views, which are basically the same
thing.
You can read about them in Books Online, and then come back and ask for any
clarification or elaboration you need.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G. Dean Blake" <Dean@.nospam.com> wrote in message
news:etioX$ebEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I'm a little new to SQL Server but I recall using Materialized Views in
> Oracle. Does SQL Server have anything like it?
> Thanks,
> G
>
Materialized Views?
I'm a little new to SQL Server but I recall using Materialized Views in
Oracle. Does SQL Server have anything like it?
Thanks,
GSQL Server 2000 introduced Indexed Views, which are basically the same
thing.
You can read about them in Books Online, and then come back and ask for any
clarification or elaboration you need.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G. Dean Blake" <Dean@.nospam.com> wrote in message
news:etioX$ebEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I'm a little new to SQL Server but I recall using Materialized Views in
> Oracle. Does SQL Server have anything like it?
> Thanks,
> G
>
Oracle. Does SQL Server have anything like it?
Thanks,
GSQL Server 2000 introduced Indexed Views, which are basically the same
thing.
You can read about them in Books Online, and then come back and ask for any
clarification or elaboration you need.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G. Dean Blake" <Dean@.nospam.com> wrote in message
news:etioX$ebEHA.1764@.TK2MSFTNGP10.phx.gbl...
> I'm a little new to SQL Server but I recall using Materialized Views in
> Oracle. Does SQL Server have anything like it?
> Thanks,
> G
>
Materialized views
Hi,
Can one table have two materialized views?
1. Count(*)
2. Freq of one column (select column_name, count(*) from table_name
group by column_name;)
And approximately how many rows table can have materialized views?
Thanks.Yes, you can have more than one materialized view on a table. You can even have the different materialized views have the same query, just using different names, and it will work as well.
As to the number of rows possible using materialized view, I don't think there is a limit just because it is a materialized view. The only limits posed (I believe, correct me if I'm wrong) are that of space... run out of space to store the table, run out of rows you can add. Remember, a materialized view has similar properties to a regular table.
Hope this helps.
JoeB|||Thanks for the reply.
Can one table have two materialized views?
1. Count(*)
2. Freq of one column (select column_name, count(*) from table_name
group by column_name;)
And approximately how many rows table can have materialized views?
Thanks.Yes, you can have more than one materialized view on a table. You can even have the different materialized views have the same query, just using different names, and it will work as well.
As to the number of rows possible using materialized view, I don't think there is a limit just because it is a materialized view. The only limits posed (I believe, correct me if I'm wrong) are that of space... run out of space to store the table, run out of rows you can add. Remember, a materialized view has similar properties to a regular table.
Hope this helps.
JoeB|||Thanks for the reply.
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
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
--
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
--
Saturday, February 25, 2012
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
Monday, February 20, 2012
master with two detail views
Hello community,
I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):
In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table)
SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] =@.id)"
For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).
The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this:
SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @.person_id)"
...
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="person_id" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
But now the problem: how should I declare a parameter @.task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks:
SelectCommand="SELECT [task_id], [task_name],[task_date], [task_description] FROM [TabTasks] WHERE ([task_id] =@.task_id)"
@.task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.
Thank you in advance for your help
Hello,
I found the solution on my own.
Within the properties of the gridview with the master records the property "datakeyname" must contain both keys: person_id;task_id
And in the second details datasource the task_id can be referenced as the second (index) within the selecteddatakey-collection in a select/control paramter like this:
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="task_id" PropertyName="SelectedDataKey(1)" />
</SelectParameters>
Perhaps this may help someone else here.
regars
dieter
Subscribe to:
Posts (Atom)