May 21, 2015 at 4:06 am
Hi,
I'm currently studying towards my MCSA. But this question confused me:
This is essentially the question:
You create a view with joins etc...
You want the view to persist data to disk to improve performance. How?
Possible answers:
A. Add a clustered index to the view
B. Create a columnstore index on all the columns in the view
C. Drop and recreate as a systems view
D. Drop and recreate as a partitioned view
Apparently the correct answer is "C". But it was my belief that you can't create your own system view. Am I wrong???
I would have thought the columnstore index was the best answer as this can increase performance on some queries (not seeking into particular values), but to my knowledge the data is not written to disk.
May 21, 2015 at 4:13 am
You can mark a view as system, but it won't result in the data being persisted to disk. The correct answer is 'A', google 'Indexed Views' for further details.
ColumnStore indexes, which are written to disk, can't be created on views, only table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2015 at 4:19 am
Yes thanks. Found this that explains all:
The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create nonclustered indexes. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.
Out of interest how do you mark a view as a system view?
May 27, 2015 at 9:24 am
dlsilsbury 63952 (5/21/2015)
...Out of interest how do you mark a view as a system view?
No. System views are those distributed with SQL Server. There is rarely a reason to mark a user created object as 'system'.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 28, 2015 at 12:39 am
Eric M Russell (5/27/2015)
No. System views are those distributed with SQL Server. There is rarely a reason to mark a user created object as 'system'.
Thanks, I understand this but I was just curious how?
May 28, 2015 at 7:11 am
dlsilsbury 63952 (5/28/2015)
Eric M Russell (5/27/2015)
No. System views are those distributed with SQL Server. There is rarely a reason to mark a user created object as 'system'.
Thanks, I understand this but I was just curious how?
There is an undocumented stored procedure called 'sp_ms_marksystemobject' which can be used to mark stored procedures as system. This is done when someone writes a general procedure for cross-database operations. Also the procedure is typically deployed in MASTER database. This might also work for views and other objects, but I don't know. I found an article where someone did a write up on how to use sp_ms_marksystemobject.
http://raresql.com/tag/sp_ms_marksystemobject/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply