Update: Materialized views in SQL Server are called Indexed Views. Search for “indexed views sql server” for syntax and an introduction. What follows is a look at some of the differences between Oracle’s Materialized Views and SQL Server’s Indexed Views
What’s the difference between Oracle’s “materialized views” and SQL Server’s “indexed views”? They both persist the results of a query, but how are they different? Sometimes it’s difficult to tell.
I’m on the left (or am I?)
One difference is that SQL Server’s indexed views are always kept up to date. In SQL Server, if a view’s base tables are modified, then the view’s indexes are also kept up to date in the same atomic transaction.
Let’s take a look at Oracle now. Oracle provides something similar called a materialized view. If Oracle’s materialized views are created without the REFRESH FAST ON COMMIT option, then the materialized view is not modified when its base tables are. So that’s one major difference. While SQL Server’s indexed views are always kept current, Oracle’s materialized views can be static.
Static Materialized Views In SQL Server?
Yeah, we just call that a table. You can use a SELECT INTO statement and it’s pretty easy. In fact, for fun I wrote a procedure that does the work for you. Given the name of a view it can create or refresh a table:
/* This is a proof-of-concept and is written for illustration purposes, don't use this in production */ create procedure dbo.s_MaterializeView @viewName nvarchar(300), @yolo bit = 0 -- use @yolo = 1 to execute the SQL immediately as declare @persistedViewName nvarchar(300); if not exists (select 1 from sys.views where object_id = object_id(@viewName)) THROW 50000, N'That @viewName does not exist', 1; select @viewName = QUOTENAME(object_schema_name(object_id)) + N'.' + QUOTENAME(object_name(object_id)), @persistedViewName = QUOTENAME(object_schema_name(object_id)) + N'.' + QUOTENAME(N'persisted_' + object_name(object_id)) from sys.views where object_id = object_id(@viewName); set xact_abort on; begin tran declare @sql nvarchar(2000); set @sql = N' IF OBJECT_ID(''' + @persistedViewName + N''') IS NOT NULL DROP TABLE ' + @persistedViewName + N'; SELECT * INTO ' + @persistedViewName + N' FROM ' + @viewName + N';' if (@yolo = 1) exec sp_executesql @sql; else print @sql; commit |
Which can be used to generate sql something like this:
IF OBJECT_ID('[dbo].[persisted_vSomeView]') IS NOT NULL DROP TABLE [dbo].[persisted_vSomeView]; SELECT * INTO [dbo].[persisted_vSomeView] FROM [dbo].[vSomeView]; |
Are Such Static Materialized Views Useful?
Yes:
- They can be used to get around all the constraints placed on regular indexed views. And if you’ve ever implemented indexed views, you understand that that’s a lot of constraints. I think this benefit is what makes this whole blog post worth consideration.
- Because it’s static, you can avoid all the potential performance pitfalls that accompany the maintenance of an indexed view (more on this next week).
- Good or bad, the view doesn’t have to be created with
SCHEMABINDING. - Indexing is strictly do-it-yourself. Chances are you want more than a single heap of data for your materialized view.
… and no:
- Most obviously, the data is static, which is another way of saying stale. But notice how Microsoft promotes indexed views. They say that indexed views are best suited for improving OLAP, data mining and other warehousing workloads. Such workloads can typically tolerate staleness better than OLTP workloads. And so maybe materialized views are a feasible alternative to indexed views.
- You have to manage when these views get refreshed. This means scheduling jobs to do extra maintenance work (yuck). For me that’s a really high cost but it’s less costly if I can incorporate it as part of an ETL process.
- Using Enterprise Edition, SQL Server’s query optimizer can choose to expand indexed views or not. It can’t do that with these materialized views.
I didn’t write the procedure for any important reason, I just wrote it because it was fun. But I have used this materialized view technique in SQL Server at work and I’ve been quite successful with it. It’s not something that should be used often, but it’s always worth considering if you can understand the trade-offs.
Interesting and insightful. Thanks for sharing
Comment by Chris Yates — December 3, 2014 @ 10:00 am
Interesting idea and I appreciate the concept but we also need to understand the fundamental differences between this approach and Oracle’s. When you say “Yeah, we just call that a table” that’s being a bit disingenuous to Oracle. We also have to remember that Oracle can rewrite queries against the base table to use the materialised view to eliminate expensive joins or retrieve aggregated data etc. which SQL Server will not do here.
https://docs.oracle.com/cd/B28359_01/server.111/b28313/
Comment by Phil — December 4, 2014 @ 4:19 am
[…] Materialized Views in SQL Server – Michael J. Swart (Blog|Twitter) […]
Pingback by (SFTW) SQL Server Links 05/12/14 - John Sansom — December 5, 2014 @ 5:25 am
Which Indexed Views – in SQL 2014 Microsoft remove TOP 100 PERCENT functionality from views. Check it out – while view would be created it does not produce a valid sorting order. Sounds like a BUG too me – worked perfectly for years BEFORE!
Comment by Igor Rozenberg — December 6, 2014 @ 4:30 am
I have never had to use indexed views. I may need to in the near future. Is there a significant performance difference between ordinary views and indexed views?
Comment by Michael Lynn — December 6, 2014 @ 8:29 am
@Phil,
I’m not being disingenuous and I suggest that you didn’t read the article or understand me completely. You wrote “We also have to remember that Oracle can rewrite queries against the base table to use the materialised view”
It can, but that’s not the kind of materialized view I was talking about. SQL Server can consider base tables in place of indexed views and indexed views in place of tables like Oracle does with its materialized views. Oracle can do that with its materialized views like you mentioned, but only if that materialized view has query rewrite enabled and the view is not stale.
But my article attempts to compares SQL Server tables (as described) to Oracle’s stale materialized views… That they’re more or less equivalent.
But maybe I’m misunderstanding you. You implied I didn’t describe a fundamental difference between this approach and Oracle, can you give me an example?
Comment by Michael J. Swart — December 7, 2014 @ 8:27 am
@Igor
Using the top 100 percent trick to force a sorting order. I know a lot of people who did that. The thing is that Microsoft always applies the ORDER BY to a query that uses a view, not the view itself. And it’s those queries that need the ORDER BY, not the views. While it used to work for years, it was never a documented feature you can rely on. I predict you’re going to have a hard time convincing Microsoft that this is a bug.
I wrote about this once: Without ORDER BY You Can’t Depend on the Order of Results
Comment by Michael J. Swart — December 7, 2014 @ 8:33 am
@Michael Lynn,
Yes, there’s a significant performance difference between indexed views and regular views.
Check out Improving Performance with SQL Server 2008 Indexed Views, a Microsoft whitepaper.
Comment by Michael J. Swart — December 7, 2014 @ 8:36 am
Ahhh, apologies, just reread the post and you are talking specifically about a static (mentioned) materialised view without query rewrite enabled (not mentioned). Not sure how often that case exists but okay, in that case I take it back. Slurp.
BTW my understanding of the EXPAND/NOEXPAND hint a is quite the opposite of what I was saying. I thought those hints controlled whether or not queries against the view can expand to query the tables. Whereas what I am saying is that queries against the tables could be rewritten to rather query the view but that’s where the requirements come in. For one, query rewrite has to be enabled on the view and the view has to be fresh though there are a number of ways to keep a view fresh beyond what you mentioned which I think also allow rewrite.
https://docs.oracle.com/cd/B19306_01/server.102/b14223/qrbasic.htm
Having worked on both products, now solely on SQL Server, I know how far SQL Server is behind Oracle so I get grumpy when people try to make out how SQL Server has the same functionality/characteristics as other products when really it doesn’t. If we want SQL Server to get better in the required enterprise features, we need to stop pretending it’s better than it is. Seems I let those thoughts prejudice the way I read your post somewhat.
Comment by Phil — December 8, 2014 @ 4:20 am
@Phil
Thanks for the comments Phil. I really appreciate them.
Like you, I’ve worked with Oracle in the past and I’m aware of many features where Microsoft is playing catch-up to Oracle.
I once wrote a the grass is greener on Oracle’s lawn type of post about LAG and LEAD. Sometimes I think I could write a post like that once a month substituting a new different feature each time.
The other lesson I’m taking is to be more careful about tone. My blog readers (both of them) don’t typically call me on stuff like this and so thanks for doing so. Come back any time and don’t pull any punches.
Comment by Michael J. Swart — December 8, 2014 @ 9:23 am
Thanks for an insightful blog posting!
This topic caught my attention as I am currently researching a performance issue where I have a complex view whose performance is terribly slow (1:30 minutes:seconds) when run on an SQL Serve 2012 Express instance or when run on a SQL Server 2014 Express instance when the compatibility level is set to SQL 2012, but is practically instantaneous when run on an instance that is not Express or when the compatibility level is set to 2014 on a 2014 Express instance. While your blog post doesn’t address this question directly, I was wondering if you’ve heard of such a problem before.
Yes I know that I probably shouldn’t be using a single view to do what I’m trying to do, which is to provide my application with the data needed to produce a hierarchical view of the data for presentation to the user, but it is what I’ve got to work with for the time being and can not yet rewrite that functionality of the application at this time.
Thanks,
Jamie Irwin
Comment by Jamie Irwin — December 8, 2014 @ 9:39 am
Okay no problem, anytime.
Say, did you update your first response to me because what I got over email was this:
“@Phil,
You wrote “We also have to remember that Oracle can rewrite queries against the base table to use the materialised view”
I believe I said that when I wrote that SQL Server can’t expand these materialized views.
You implied I didn’t describe a fundamental difference between this approach and Oracle, can you give me an example?”
If so apologies, I didn’t see the amended post so it looks like I was repeating what you said.
Hehehe, you say LAG and LEAD, just don’t get me started on partitioning…
Comment by Phil — December 8, 2014 @ 10:13 am
@Phil,
Yes, I did edit my comment (perks of being the website owner) I know it’s not always kosher to edit a discussion, but it hadn’t been replied to and I wanted to expand on what I wrote.
Comment by Michael J. Swart — December 8, 2014 @ 10:58 am
@Jamie,
There are a lot of reasons for variable performance when using different versions, editions. It can be fun and super-interesting to investigate why. When I have interesting problems like that, my first thought is to ask the Q&A board over at SQLPerformance.com with a specific repro or just the plans.
One plausible theory with your specific case is that you’re enjoying the benefits of 2014’s new Cardinality Estimator.
Comment by Michael J. Swart — December 8, 2014 @ 11:04 am
Right so essentially you have an aggregation or snapshot table, where you have split the ETL between a view and a stored procedure. Given that the data is knowingly stale and can thus be tolerated and you have eliminated any possible query rewrite behaviour, wouldn’t it be better to just include the query in a stored procedure solely for the purpose of updating the table or just include the load in an already existing ETL process (which may mean that the information is not stale as it happens)? I appreciate that you may have to maintain a number of SPs but in this scenario you already have to maintain a number of views but at least in the dedicated SP you get more options to manage performance if required.
Comment by Phil — December 17, 2014 @ 4:05 am
Hey Phil, Welcome back
Oh for sure. Dedicated stored procedures are definitely the way to go here even though I didn’t explicitly say so.
You asked “… wouldn’t it be better …” I’m trying to guess what you think it would be better than. Better than using the procedure I included right?
I did mention that this was “for fun” and that it was written “for illustration purposes” and “don’t use this in production”
This post is not intended to accomplish too much. The procedure I wrote isn’t useful, but static tables populated in an ETL can be. I’ll repeat the last sentence of the post.
Comment by Michael J. Swart — December 17, 2014 @ 8:51 am
@Phil,
Okay, I think I see what you’re advocating. You’re advocating getting rid of the views entirely because they’re only used for the definition which could easily be stored in a stored procedure.
I think back to how I’m using it in real life. I actually use both the up-to-date but slow version of the view
vSomeViewand the stale but fast versionpersisted_vSomeView. Logically it’s a little easier to code for. But I see your point, the table I’m describing is essentially no different than an aggregation or snapshot table.Comment by Michael J. Swart — December 17, 2014 @ 8:59 am
@Phil – view always show FRESH data, while your persisted View would eventually would contain OUT OF DATE data.
Comment by Igor Rozenberg — December 17, 2014 @ 9:59 pm
Any thoughts on updating the materialized view at regular time intervals?
Comment by Ron — November 4, 2015 @ 1:32 am
We have been using this technique also (pseudo materialized view), basically because sql server express does not support materialized (indexed) views (may be sql express supports them nowadays); just wanted to say that we also had very good results (regarding performance).
Comment by ggo — July 16, 2020 @ 12:15 pm