Partitions on READONLY Filegroups

Three weeks ago I was presenting a session on SQL Server Partitioning at SQL Konferenz in Darmstadt, Germany. I hadn’t done a session on this topic in quite a while, and it was great to see how many people were interested in knowing more about how SQL Server stores its objects.

One of the delegates came up afterward and asked me a terrific question. “Can you have a partition scheme with some of the filegroups being READONLY?” I was pretty sure the answer was “yes”, but I couldn’t recall that I’d ever verified the behavior. And the question continued: “And could the table be updated if SQL Server was able to determine that only the READWRITE filegroups would be affected?” Again, I thought the answer was “yes”, but it remained to be proven.

So now I am back in my own office, and I decided to test it. I used one of the basic scripts I had used for my demos at the conference, and made just a couple of modifications. I needed to have additional filegroups beyond the primary, so I created two extra ones.

-- Create a partition function, a partition scheme, and a table   
-- I'm using a database called test, but you could use a test database of another name
USE master
GO
/*  
 DROP DATABASE IF EXISTS test;
 GO
 CREATE DATABASE test;
 GO
*/
ALTER DATABASE test ADD FILEGROUP FG1;GOALTER DATABASE test ADD FILEGROUP FG2;GO
-- You may need to change the file path, depending on where you would like--   the database files to be located; -- Note that I am not making the FGs readonly to start with, since I need to load some data into them
ALTER DATABASE test 
    ADD FILE ( NAME = 'data1', 
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\data1.ndf', 
      SIZE = 8192KB , FILEGROWTH = 65536KB ) 
 TO FILEGROUP FG1
GO
ALTER DATABASE test 
    ADD FILE ( NAME = 'data2', 
      FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\data2.ndf',
      SIZE = 8192KB , FILEGROWTH = 65536KB ) 
TO FILEGROUP FG2
GO

So now we can use the database. I first do the usual things of dropping the table, the partition scheme and the partition function if they exist.

USE test; 
GO 
DROP TABLE IF EXISTS Employees; 
GO 
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = 'test_ps') 	
  DROP PARTITION SCHEME test_ps; 
GO 
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = 'test_fn') 	
  DROP PARTITION FUNCTION test_fn;
GO  

Now we create the partition function, the partition scheme and the table, and load some data from another table. I'm using a table from AdventureWorks2016, but you can use any table that has a character column.

 -- Create a partition function defining 5 partitions  
CREATE PARTITION FUNCTION test_fn (int)  	
AS RANGE LEFT FOR VALUES (-1,10, 20, 30); 
GO  
-- The same number of filegroups must be specified, order is important 
CREATE PARTITION SCHEME test_ps   
    AS PARTITION test_fn  TO ([Primary], [Primary], [Primary], [FG1], [FG2]); 
GO  
-- Create a table using the test_ps partition scheme  
CREATE TABLE Employees  
(EmpId int identity(-10,1), EmpName char(500))     
ON test_ps(EmpId);  
GO

-- Populate the table, copying data from a table in AdventureWorks2016 
INSERT INTO Employees   
    SELECT TOP 60 FirstName     
    FROM AdventureWorks2016.Person.Person; 
GO  
-- Look at what partition each row is in
SELECT $PARTITION.test_fn(EmpID) as Partition, *  FROM Employees; 
GO 

-- NOW we can make FG2 readonly 
ALTER DATABASE test MODIFY FILEGROUP FG2 READONLY;  
GO  

-- Without a WHERE clause, you will get an error 
UPDATE employees SET empname = substring (empname, 1, 10) + '2' 

Msg 652, Level 16, State 1, Line 1 The index "" for table "dbo.Employees" (RowsetId 72057594042384384) resides on a read-only filegroup ("FG2"), which cannot be modified. -- I will admit, the error message is very strange, reporting an empty string for the index name.

-- With a WHERE clause that eliminates everything on the readonly paritition,  
--   you will NOT get an error 
UPDATE employees 
SET empname = substring (empname, 1, 10) + '2'    
WHERE empId <= 30;  
GO  
SELECT * FROM employees;  
GO

So there you have it. Very straightforward, but it does illustrate that you can have some of the partitions on READONLY filegroups, and you can update the table, as long as SQL Server knows up front that no rows on the READONLY filegroups will be modified.

Here’s a quiz question. What if the WHERE clause included values that didn’t exist, but that would be in the READONLY filegroup if they did exist?

I hope you find this useful!

~Kalen