Indexed Views - EXACTLY what is stored...
-
Hi -
I'd like to use some indexed views but am wondering exactly what gets stored. Is it just the columns in the view which are indexed or is it all columns in the view ? Stored meaning that all articles I've read state that the data for the view is physically stored in duplicate in the DB and thus adds some overhead.
Example:
Create View Test as
Select T1.UniqueIDIntColumn1, T2.UniqueIDIntcolumn2, T1.VarcharColumn1, T1.VarcharColumn2, ...T2.Varcharcolumn255 from Table1 T1, Table2 T2...
Note that there are two key columns and then 255 other varchar columns in this view. I want to create an index on only the 2 ID columns of the view.
Now for sure the int ID columns will be stored in duplicate, or as part of the view and add extra insert/delete overhead
- but what about the other 255 varchar columns ? Are they also maintained in duplicate 
?Thanks - B
-
SQL Server stores only the columns that are in the view. This of course means that data will be duplicated, but only for the columns that are part of the underlying tables and the view. So in your case the extra varchar columns will also be duplicated.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Ouch that suks... Can anyone else validate this ?
Was hoping to hear that only the columns involved in the actual index would be used but - when was the last time you got what you hoped for anyway.
- B
-
What would be the point of that???
You would now have to do 2 joins instead of one. There would be only loss in that scenario.
What problem are you trying to solve by using the indexed view? Maybe we can offer an alternative solution to your problem!
-
The point of what ?
I don't need an alternative, just want to know how Indexed views behave. It it helps you, then change my sql example to this:
Select (1 ID column), (255 other varchar columns) from (10 different joined) tables
Now there are more than 2 joins - there are 10. And I can index the ID column and join to it effeciently. But, it does not help me much if the 255 varchar columns are also stored in duplicate.
Can anyone else please validate the first reply's answer?
Thanks - B
-
Let me put it in my words, but I'm still not sure I completly understand your question :
All columns listed in the select part of the view will be physically stored in the indexed view, in a ready state, or as if this was a whole new table altogether.
The view will require a unique index to be created, which then becomes the primary key of the table >> so the view will be invalid in duplicates are found here (will throw a unique constraint error IIRC).
That new unique index behaves exactly like any other indexes and is also stored on the disk. But only columns specified in that index will be on the disk, if a column in that index is already part of a composite PK on its base table, only the specified column will be part of the index, not the rest of the composite key.
Does that answer your concerns?
-
In case someone does not post a validation to what I said (it is not particularly overdocumented)
:You can validate it yourself by:
Create a few tables, add some dummy data, prefereably for every varchar column give some unique value, like aaaaaa, bbbbbb, etc.
Create an indexed view with a select statement.
then, check the first field of the sysindexes table (it is easier in this case just to use the legacy system view, I would not like to go into dataspaces, ...).
This will give you the pageid of the first datapage for the materialized system view. in hex, 4 bytes for teh page id, 2 bytes for the file id.
Then:
DBCC TRACEON(3604)
DBCC PAGE (-databaseid-, -fileid-, -pageid-, 1)
DBCC TRACEOFF(3604)
You will see the raw data. If you used varchars, and column values like aaa, bbbbb, ccccc ... you will easily interpret the results
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Then there's always option b : simply store that unique id and drop the 255 varchar columns from the view. That way only one column will be duplicated... pretty much the same as if you were simply creating an index.
-
Well Doc - I trust you I guess...
Just doesn't seem to really provide any help, effective help anyway. Guess I won't be using Indexed views after all especially when you factor in all the limitations around them(must use schemabinding, no outer joins allowed, etc..)
MS is just tryin to keep up w/ Oracle. I bet Oracle "materialized views" do things in a more effecient fashion.
Thanks -B
-
Hard to comment on that since we still don't know what problem you are trying to solve. Since you seem to be somewhat new to SQL server, you might ask the experts around here about what problem you are trying to solve so we can point you out in the best possible direction.
-
Indexed views are actually quite handy, and can increase query performance. SQL Server's query optimizer does use them automatically, even if the view is not referenced. You are right however about the limitations, and that there are cases where it is not a good idea to use them. It has been some time since I looked at how Oracle handles materialized views. I'd be happy to hear about it though (pm or in the forum).
Hope you will find a solution,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Thanks again Doc...
In the end, I have a simple 3 level hierarchy, each level having attributes. I will joining to this "hierarchy thing" quite often and it seemed that an indexed view would be perfect : except for the duplication of the DATA. I can live with duplication of the int index columns, but every column in the view?
Example,
A business with attributes. Each business has one or more Business Units with attributes. Each business unit has a business interface and attributes. I will join to the "Business" concept quite often - in particular from the lowest level a.k.a. the business interface. Having to reproduce the Business -> BizUnit -> BizInterface joins each time is annoying.
If I could create an indexed view of "the business" without saving every ~%#$ column in the view - I would do it. It would be a nice conceptual abstraction.
I guess I was hoping this would behave in the same way that a non-clustered index does in that it maintains only the "index" portion and leaves the raw data alone in another table - the index just points to the data.
Take care - B
-
Billy -
You're comment "I bet Oracle "materialized views" do things in a more effecient fashion." cracked me up - the answer is nope, they operate in pretty much the same fashion as indexed views - both materialize (make real) the underlying data. Which is great when the view is a subset of the larger tables but a waste of time and space in a situation like yours - you're better off figuring out the most efficient way possible to get at just the data you need to solve the problem. Which just begs the question of why you need to return 255 varchar columns from your query... isn't there a better way to accomplish this? Based on your comment about your data having a key pair or object/attribute relationship maybe an multi-resultset/recordset or XML result would be a better alternative?
Joe
-
Wow Joe - yes...
Why am I returning 255 columns, my bad. Thanks for your brilliant insight and I just may use a multi-resultset XML Object/attribute pair to solve my problem.
-
I am trying to figure out your concern over the duplicated data. Yes, the data is dupicated from the base tables to the indexed view, but this is controlled by the system. All updates occur to the base tables, not the indexed view, and those changes are then visible in the indexed view.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply