Move all SQL Server indexed views to a new filegroup

By: Aaron Bertrand | Updated: 2013-02-06 | Comments (4) | Related: > Indexing

Problem

As databases grow, it often becomes necessary to add new I/O paths to accommodate the growing space. Even without growth that requires this scale, it can still be useful to utilize multiple I/O devices to spread out the load. One way that you can make optimal use of new drives is to add a filegroup to the database and move certain objects (say, all of your indexed views) to the new filegroup. (In fact, you could make a case that you should move all user objects from the PRIMARY filegroup, but I will save that for another post.)

Solution

Moving a single indexed view to a new filegroup is quite simple. Well, really, you move the clustered index, which - for all intents and purposes - is the data that the view materializes. So we just need to take the definition of the view, and add the WITH DROP_EXISTING and ON [FileGroup_Name] options.

Quick example:

CREATE UNIQUE CLUSTERED INDEX UQ_ViewName_ColumnName 
  ON dbo.View_Name(Column_Name);
GO

Becomes:

CREATE UNIQUE CLUSTERED INDEX UQ_ViewName_ColumnName
  ON dbo.View_Name(Column_Name)
  WITH (DROP_EXISTING = ON)
  ON [FileGroup_Name];
GO

You can also optionally add ONLINE = ON if you are running Developer or Enterprise Edition. But what if you have a large number of views? What if your indexed views also have multiple non-clustered indexes? What if you want to schedule this work for a maintenance window?

One way to do this would be very tedious:

In Management Studio's Object Explorer, open up your database's Views node
For each view, expand Indexes and check if the view has an index
Right-click each index, choose Script View As > CREATE To > some location (as long as Tools > Options > SQL Server Object Explorer > Script Indexes is set to true, not the default - otherwise, repeat for each index)
Remove the CREATE VIEW and other code not relating specifically to the index(es)
Add the DROP_EXISTING and ON [FileGroup_Name] options
Execute the script or save it somewhere for later execution
Goto 1

Another way would be similarly tedious. With Views highlighted in Object Explorer, open Object Explorer Details (F7). Unlike Object Explorer, Object Explorer Details allows you to select multiple objects, so that you can perform the same operation against many items at the same time. From here you can select all of the views (or a subset, if you know which views have indexes or not - unfortunately this view does not offer "Has Index" or "Number of Indexes" as an exposed property). Then you can right-click and perform steps 3-6 above just once, instead of once per object.

A Better Way

Management Studio doesn't invent all of this data that it outputs through the scripting options; it gets the information from the catalog views. Well, so can we, and we can generate the precise scripts we'll need to recreate these indexes dynamically . Let's say we have the following objects, and we've added a new filegroup:

USE master;
GO
CREATE DATABASE ivtest;
GO
USE ivtest;
GO
CREATE TABLE dbo.Sample1(a INT);
GO
CREATE VIEW dbo.Sample1_View
WITH SCHEMABINDING
AS
  SELECT a, c = COUNT_BIG(*)
    FROM dbo.Sample1
 GROUP BY a;
GO
CREATE UNIQUE CLUSTERED INDEX PK_Sample1 ON dbo.Sample1_View(a);
GO
CREATE TABLE dbo.Sample2(a INT, b INT, c INT);
GO
CREATE VIEW dbo.Sample2_View
WITH SCHEMABINDING
AS
  SELECT a, b, c, d = COUNT_BIG(*)
    FROM dbo.Sample2
 GROUP BY a, b, c;
GO
CREATE UNIQUE CLUSTERED INDEX PK_Sample2 ON dbo.Sample2_View(a);
GO
CREATE INDEX IX_Sample2_d__a   ON dbo.Sample2_View(d,a)
CREATE INDEX IX_Sample2_b_a__c ON dbo.Sample2_View(b,a) INCLUDE(c);
CREATE INDEX IX_Sample2_d__c_a ON dbo.Sample2_View(d)   INCLUDE(c,a);
GO
ALTER DATABASE ivtest ADD FILEGROUP Indexed_Views;
GO
ALTER DATABASE ivtest ADD FILE (name=N'Indexed_Views', filename=N'C:\temp\ivtest_iv.ndf') 
  TO FILEGROUP [Indexed_Views];
GO

To find all of the views with at least one index, and each index that hasn't already been moved to the Indexed_Views filegroup:

SELECT [view] = v.name, [index] = i.name
      FROM sys.views AS v
      INNER JOIN sys.indexes AS i
      ON v.[object_id] = i.[object_id]
      INNER JOIN sys.filegroups AS f
      ON f.data_space_id = i.data_space_id
      WHERE f.name <> N'Indexed_Views'
      ORDER BY v.name, i.index_id;

This returns:

view          index
------------  -----------------
Sample1_View  PK_Sample1
Sample2_View  PK_Sample2
Sample2_View  IX_Sample2_d__a
Sample2_View  IX_Sample2_b_a__c
Sample2_View  IX_Sample2_d__c_a

We also need to track down the columns in each index and, for non-clustered indexes, whether the column is a key or include column. The order of the key columns is also important. So we can add in a few columns to get the additional information:

SELECT 
  [view] = v.name, 
  [index] = i.name, 
  [column] = c.name, 
  [include] = ic.is_included_column
FROM sys.views AS v
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
INNER JOIN sys.columns AS c
ON c.[object_id] = v.[object_id]
INNER JOIN sys.index_columns AS ic 
ON c.[object_id] = ic.[object_id]
AND c.column_id = ic.column_id
AND ic.index_id = i.index_id
INNER JOIN sys.filegroups AS f
ON f.data_space_id = i.data_space_id
WHERE f.name <> N'Indexed_Views'
ORDER BY v.name, i.index_id, ic.index_column_id;

Results:

view          index              column  include
------------  -----------------  ------  -------
Sample1_View  PK_Sample1         a       0
Sample2_View  PK_Sample2         a       0
Sample2_View  IX_Sample2_d__a    d       0
Sample2_View  IX_Sample2_d__a    a       0
Sample2_View  IX_Sample2_b_a__c  b       0
Sample2_View  IX_Sample2_b_a__c  a       0
Sample2_View  IX_Sample2_b_a__c  c       1
Sample2_View  IX_Sample2_d__c_a  d       0
Sample2_View  IX_Sample2_d__c_a  c       1
Sample2_View  IX_Sample2_d__c_a  a       1

This seems to be all of the information we need to re-create these indexes, assuming that the rest of the default options were in use at the time (and the defaults haven't been changed). You may want to add columns to this output in order to use in the dynamic output so that those settings are maintained. Now, in order to generate a script that you don't have to run manually, I use a dynamic SQL technique that I employ often. It just uses a variable to append each line of code I want to execute, drawn on the results of the join above. Since an index can have multiple key or include columns, I use a concatenation trick with FOR XML PATH in a subquery in order to build the comma-separated list of columns.

The code looks like this:

SET NOCOUNT ON;
DECLARE @opt NVARCHAR(13),  @sql NVARCHAR(MAX), @fg  NVARCHAR(128);
SELECT 
  @fg  = N'Indexed_Views', -- you'll want to change this to your FG name
  @sql = N'', -- important to initialize this!
  @opt = CASE WHEN CONVERT(NCHAR(3), SERVERPROPERTY('Edition')) 
    IN (N'Ent', N'Dev') THEN N', ONLINE = ON' ELSE N'' END;
SELECT @sql = @sql + N'
  CREATE ' + CASE WHEN i.index_id = 1 
    THEN 'UNIQUE CLUSTERED' 
    ELSE '' END
  + ' INDEX ' + QUOTENAME(i.name) 
  + ' ON ' + QUOTENAME(SCHEMA_NAME(v.[schema_id]))
  + '.' + QUOTENAME(v.name) 
  /* 
      comma-separated list of key columns, ordered 
  */
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.index_columns AS ic 
    ON  c.[object_id] = ic.[object_id]
    AND c.column_id = ic.column_id
    WHERE c.[object_id] = v.[object_id]
    AND ic.index_id = i.index_id
 AND ic.is_included_column = 0
 ORDER BY ic.index_column_id
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
  + ') '
  
  /* 
      comma-separated list of include columns, if necessary
      (order is not important)
  */
  
  + COALESCE(' INCLUDE (' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.index_columns AS ic 
    ON c.[object_id] = ic.[object_id]
    AND c.column_id = ic.column_id
    WHERE c.[object_id] = v.[object_id]
    AND ic.index_id = i.index_id
 AND ic.is_included_column = 1
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
  + ')', '') + ' 
  WITH (DROP_EXISTING = ON' + @opt + ')
  ON ' + QUOTENAME(@fg) + ';'
FROM sys.views AS v
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
INNER JOIN sys.filegroups AS f
ON f.data_space_id = i.data_space_id
WHERE f.name <> 'Indexed_Views'
ORDER BY v.name, i.index_id;
SELECT @sql;
-- EXEC sp_executesql @sql;

Now, you can inspect the output using Results to Text in Management Studio, however keep in mind that PRINT is limited to 8K, so if this ends up being a large script, the output will be truncated (so it may not look correct or complete). Unless you need to go to great lengths to validate statements beyond the first 8K, you'll just have to trust that it's all there.

My output is not all that pretty, but it does what I asked:

  CREATE UNIQUE CLUSTERED INDEX [PK_Sample1] ON [dbo].[Sample1_View]([a])  
  WITH (DROP_EXISTING = ON, ONLINE = ON)
  ON [Indexed_Views];
  CREATE UNIQUE CLUSTERED INDEX [PK_Sample2] ON [dbo].[Sample2_View]([a])  
  WITH (DROP_EXISTING = ON, ONLINE = ON)
  ON [Indexed_Views];
  CREATE  INDEX [IX_Sample2_d__a] ON [dbo].[Sample2_View]([d],[a])  
  WITH (DROP_EXISTING = ON, ONLINE = ON)
  ON [Indexed_Views];
  CREATE  INDEX [IX_Sample2_b_a__c] ON [dbo].[Sample2_View]([b],[a])  INCLUDE ([c]) 
  WITH (DROP_EXISTING = ON, ONLINE = ON)
  ON [Indexed_Views];
  CREATE  INDEX [IX_Sample2_d__c_a] ON [dbo].[Sample2_View]([d])  INCLUDE ([c],[a]) 
  WITH (DROP_EXISTING = ON, ONLINE = ON)
  ON [Indexed_Views];

You'll notice that it created the clustered index for each view first, then the non-clustered indexes in order, and everything is escaped with square brackets to protect you from most poorly-chosen identifiers. You can make certain changes here, for example you can keep certain indexed views where they are (for now, or permanently). You could also adjust the query so that the clustered indexes go on one filegroup and the non-clustered indexes go on another (which is a technique I see used from time to time). So next, you just have to uncomment the final line (`EXEC sp_executesql @sql;`), run it again, and it will move all of the indexed views. (But be sure to test this in a non-production environment first, and the final deployment should be during relative downtime or during a maintenance window - especially if you are not able to use ONLINE = ON.)

If you want to move them back, you just have to change a couple of lines in the last section:

  WITH (DROP_EXISTING = ON' + @opt + ')
  ON [PRIMARY] + ';' -- was ON ' + QUOTENAME(@fg) + ';'
FROM sys.views AS v
INNER JOIN sys.indexes AS i
ON v.[object_id] = i.[object_id]
INNER JOIN sys.filegroups AS f
ON f.data_space_id = i.data_space_id
WHERE f.name = 'Indexed_Views' -- was <> 'Indexed Views'
ORDER BY v.name, i.index_id;

Conclusion

And there you have it, a single dynamic script that will move all of the indexed views to a specific filegroup. And you can adapt this technique to move indexes on base tables (however you will have to add conditional handling for filtered indexes, which aren't valid on indexed views, and perhaps other differences).

Next Steps

Review the following tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2013-02-06