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

 

[SQLDenis]

>>Clustered indexes, by definition, always have all the data with them

That is also the reason when you create a table on drive d the clustered index on drive E all the data will be on drive E


I use this 'trick' all the time to create some space on a drive, drop clustered index and recreate on another drive

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

 

[mercurymik]

First of all, indexed views are great if you only need straight forward select statements from base tables. If you want to combine data using derived tables, they have some major limitations. The real question is why do you not want your users in the base tables??? If you set your security up properly, they should only have read access anyhow.

If they create long running queries that lock tables and transactions fail, there are a couple other really good ways to go. A snapshot... basically back-up and restore the production database to a different non-production database once a day. Let them use the snapshot.

If they need up-to-the-minute data, then replicate the database. We have several of them and the longest we are behind production is about 10 seconds. With replication, long running queries can cause problems. Plus you want to make sure the security is set for users to only read the tables.

Both of these options give your users all the data, and they are not touching your "production" database.

As for creating something like an Oracle Materialized View. That is just a fancy way of saying we created a table. The MV actually holds the data just like a table. In SQL Server, you can simply create another table from the base table, or get creative and use the programming of a stored procedure to fill a table. It can be dropped and recreated through the SP every day using a DTS job.


If all else fails, download the tables to MS Access for the users.... Ok that was a joke for all you SQL Jocks..

 

[SQLDenis]

>>If all else fails, download the tables to MS Access for the users.... Ok that was a joke for all you SQL Jocks..


my heart stopped for an instance until I passed the 4 dots

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

 

[ESquared]

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]

 

[ptheriault]

E2,
Oralce
In a MV the data is stored on disk. you will always get the same data every time unless you have refreshed it. The query engine will always select the data from the MV. It is a static table.

SQL
Even if you index a view (and yes, I know that stores the data on disk) there is no guarantee that the query optimizer is going to use the index and may revert back to selecting data from the base tables. So a view in sql server is just a compiled query.

Also there are no limitations that I know of to the type of query you can have that makes up your MV.

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

 

[ESquared]

I don't mean to be annoying about this... none of what you've said means that SQL Server actually doesn't have materialized views. It's a view, and the data is materialized (stored on disk). "Compiled query" is an acceptable synonym for both Oracle's and SQL Server's implementations of the concept.

Oracle's views have to be refreshed on some basis. That may be good in some cases (you can duplicate this functionality if you want with scheduled updates of changed rows to a separate table, aka, a data warehouse). But it may be bad, too. Just because materialized views are different in Oracle and SQL Server doesn't mean one of them doesn't have materialized views.

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

According to the definition of a materialized view SQL Server does not have materialized views.

(

http://en.wikipedia.org/wiki/Materialized_view)

A materialized view takes a different approach in which the query result is cached as a concrete table that may be updated from the original base tables from time to time. This enables much more efficient access, at the cost of some data being potentially out-of-date.

While we can create a substitute, we do not have materialized views in the SQL Server world.

I'm not sure how SQL Server can have an implementation of the concept as we don't have a true implementation.

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]

What Denny said!

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

 

[ESquared]

I guess it all comes down to who gets to define it. Since both are views and both are materialized, I suspect that as time passes more and more people will call Sql Server Indexed Views, Materialized Views.

Doing some web searches, I find some sites calling SQL Server's indexed views materialized views, and others being more careful to distinguish them. The page Materialized Views between Oracle and Sql Server is a good example where the author knows the difference between them but calls them both materialized views.

I agree that for many applications the SQL Sever materialized view is inferior to the Oracle one (data warehousing uses particularly).

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

 

[sweetleaf]

It seems that SQL MV's are more geared to adress performance, whereas Oracle MV's can be used for that (MV's as summary tables) as well as for end user data access. A lot of good discussion has been generated - thanks all for the input.

 

[ptheriault]

In my mind an Oracle MV is more like this..

"Select * into NEW_Table FROM Table"

I've made a static table and the data in it won't change until I update it. I can do anything I want to that new table. The query plan will always select data from it and the base table it was built from it not affected by it.

However, with and Indexed view. The data will be updated based on what happens to the base table. and because of the index DML performed on the base table will impact the view.

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

 

[SQLDenis]

>>I agree that for many applications the SQL Sever materialized view is inferior to the Oracle one (data warehousing uses particularly).


We usually build cubes in Analysis Services to this kind of stuff. THe OLTP is optimized (normalized) for insert/updates/deletes

The OLAP cubes are optimized for SELECTs and are preaggregated

How would I even create an materialized view? I would need at least 500GB of disk space just to create one from some of the data that I have

I do however have partitioned views which have made the selects 100 times faster

It all depends on knowledge and implementation, calling a SQL Sever materialized view inferior to the Oracle one is just one viewpoint

Maybe you didn't create your SQL server view correctly etc etc etc


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

 

[ESquared]

I agree Denis that the SQL Server indexed view can be superior to the Oracle materialized view in some situations. I've used it to speed up searching in a database, where updates were rare and searching speed was important. It worked handily, doubling or tripling the speed of searches.

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