SQL Server materialized views - question 1

[sweetleaf]

Does SQL Server have an equivalent to Oracle materialized views? How are mat views different than logical views. My chellenge is i don't want to grant power users direct access to tables for their reporting needs. But IT needs to give them some way to analyze their data in an ad hoc way...

Any help would be greatly appreciated.

Thanks

 

[nice95gle]

Do a little reading on Indexed views for SQL Server. There are some things to take into consideration when using this kind of view. I haven't played with it much in 2005 but for SS 2000 you need Enter, DEV and I think Eval editions for the optimizer to take advantage of the index. Also if you don't have a good amount of data in your table SS will not use the index. I can't talk that much about it, so do a little research.

Good Luck


Well Done is better than well said
- Ben Franklin

 

[ptheriault]

SQL Server doesn't have materialized views. Are your users looking for real time data? If the data doesn't need to be real time and you are on SQL 2005 you could create a snap shot of you OLTP database. There are a lot of benefits to this approach.
1. It's read only
2. reduces the load on the OLTP db
3. you don't need to create and maintain views

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ESquared]

SQL Server doesn't have materialized views.

SQL Server does have materialized views. Just create a clustered index on a view.

Of course, it's more complicated than that, as there are many strict requirements in order for a view to qualify for being materialized. But it's possible and I've used them.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[ptheriault]

Umm, not in the sense of what Oracle defines a materialized view.

Just create a clustered index on a view

you can do that unless you are using schema binding. Which depending on the complexity of your view may not be possible.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ESquared]

>> there are many strict requirements in order for a view to qualify for being materialized

... such as schemabinding, to name one ...

Paul, would you share in what sense does Oracle define a materialized view that the SQL Server indexed view doesn't qualify? I'd like to learn this and I don't know it.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[cmmrfrds]

I too would like to understand the differences between Oracle's materialized view (MV) and SQL Server's indexed view. I am familiar with Oracle's MV, but not the indexed view in SQL Server. My readings on the indexed view seem to indicate that the clustered index is physical storage but the view is still dynamic, where the MV takes physical storage and can be indexed and refreshed on a schedule or dynamically. See this thread for a brief description of the MV. thread759-1407273
Can you explain how the index view differs in physical storage and maintenance. Thank you.

 

[ESquared]

Materialized views in SQL Server need no refresh schedule as they are always updated at the same time as the table(s) they are based on. Just like in Oracle, materialized views in SQL Server consume space as if they were tables. They have a clustered index--they have to take space and the clustered part proves that every column in the view is in the index.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[mrdenny]

Indexed views require some storage. Not as much as a full copy of the data, as only the indexed columns are stored within the indexes.

Indexed views would be the closest thing that SQL Server gives to an MV.

If all you are looking for is a read only copy of the data, Take a look at snapshots in SQL Server 2005. It's a snapshot of the entire database but it will allow you to let the users read the data, without having to go to the actual tables.

With snapshots keep in mind that when you go and read the snapshoted database you can be hitting the same physical pages as the source database. The way that snapshots work is that a very small snapshot database is created. When a data page is updated in the origional database the origional data page is copied to the snapshot database. As time goes on the snapshot database will continue to grow depending on the data change rate of the production database.

So while the users won't be running queries and taking locks on the database, they will be adding load to the disk subsystem while running there queries.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

A materialized view is a lot like a snap shot. The data is precompiled and stored on disk. The data only changes when you refresh it. we use them on our dev oracle server, they refresh nightly and point back to our production server. A SQL Server view contains no saved data, it's only a saved query. Unless as E2 pointed out you create a PK on it. The problem with that is that in order to have a PK you must use schema binding. And in order to use schema binding the view must be a fairly simple one. For example, you can't have the following in a view.

* TOP
* DISTINCT
* text, ntext, or image columns
* MIN, MAX, COUNT(*), COUNT(<expression>), or AVG
* SUM on a nullable expression
* a derived table
* the ROWSET function
* UNION
* subqueries, OUTER joins, or self-joins
* full-text predicates (CONTAIN or FREETEXT)
* COMPUTE or COMPUTE BY

Also here is a quote right from BOL about indexed view.

The optimizer will evaluate the cost of using that indexed view in processing the query. But just because you have an indexed view doesn't mean the query optimizer will always choose it for the query's execution plan. In fact, even if you reference the indexed view directly in the FROM clause, the optimizer might decide to directly access the base table instead.

So as you can see there is no guarantee that the index on the view will even be used.

Here are a few good links that help explain materialized views.

http://www.databasejournal.com/features/oracle/article.php/10893_2192071_1

http://www.akadia.com/services/ora_materialized_views.html

http://www.adp-gmbh.ch/ora/concepts/materialized_view.html

I also use snapshot for sql 2005 and in this case that would be my choice.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[cmmrfrds]

Okay, I think I understand 1 difference. SQL Server indexed views are always dynamic based on the underlying tables, where as, Oracle MV's can be dynamic based on the underlying tables or scheduled depending on your requirements.

ESquared you imply that all columns in the indexed view take storage while MrDenny states that "Indexed views require some storage". Can either of you elaborate on this. Oracle MV's store all the columns data that are in the MV's Select list, and can be indexed just like a table.
Thank you.

 

[SQLDenis]

Although only the columns that make up the clustered index key are specified in the CREATE UNIQUE CLUSTERED INDEX statement, the complete result set of the view is stored in the database. As in a clustered index on a base table, the B-tree structure of the clustered index contains only the key columns, but the data rows contain all of the columns in the view result set.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[cmmrfrds]

Thank you for the information Tom. It seems like there are a lot of limitations on the SQL for an indexed view. Do you know if Analytical Functions can be used for columns in an indexed view. For instance, Row_number() or Rank().

 

[ptheriault]

I'm not sure about row_number() or rank() but in 2005 you can have scalar aggregates, including SUM and COUNT_BIG without GROUP BY on a view with schema binding.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Here are the 2005 limitations.

View Restrictions

To create an index on a view in SQL Server 2005, the view definition must not contain:


ANY, NOT ANY


OPENROWSET, OPENQUERY, OPENDATASOURCE

arithmetic on imprecise (float, real) values


OPENXML

COMPUTE, COMPUTE BY


ORDER BY

CONVERT producing an imprecise result


OUTER join

COUNT(*)


reference to a base table with a disabled clustered index

GROUP BY ALL


reference to a table or function in a different database

Derived table (subquery in FROM list)


reference to another view

DISTINCT


ROWSET function

EXISTS, NOT EXISTS


self-join

expressions on aggregate results (e.g. SUM(x)+SUM(x))


STDEV, STDEVP, VAR, VARP, AVG

full-text predicates (CONTAINS, FREETEXT, CONTAINSTABLE, FREETEXTTABLE)


Subquery
imprecise constants (e.g. 2.34e5)

SUM on nullable expressions

inline or table-valued functions


table hints (e.g. NOLOCK)

MIN, MAX


text, ntext, image, filestream, or XML columns

non-deterministic expressions


TOP

non-unicode collations


UNION

contradictions SQL Server 2005 can detect that mean the view would be empty (e.g. where 0=1 and ...)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[cmmrfrds]

Tom, from what you show it appears that and indexed view is quite limited compared to an Oracle materialized view. Especially, the lack on a outer join or using a derived table in an indexed view.

 

[ptheriault]

I don't know who Tom is but yes there are limits to creating indexes on view. That is why I prefer to use snap shots.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

Tom = Thanks old mate

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[cmmrfrds]

BTW, thank you for your information sqldenis. I like your interpretation, but I was referring to Paul. Sorry Paul. So, the index view does store the results of the views query in addition to the clustered index.

 

[ESquared]

Clustered indexes, by definition, always have all the data with them. (Technically, the b-tree structure doesn't, but the data rows do.) That's because a clustered index doesn't point anywhere else. So it has to have the data.

A nonclustered index points to the clustered index. And once you're at the clustered index there's nowhere else to go. The data has to be there.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]