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
*/

-- 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

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?

~Kalen

SQL Server Internals

Kalen Delaney teaches the most advanced SQL Server courses in the world. Her 5-day course “SQL Server Internals and Tuning” is designed for advanced SQL Server professionals who want to know how SQL Server really works, in order to take maximum advantage of its potential.