Monday, August 27, 2007 11:08 AM tonyrogerson

Read Only File Group - Tables are not really Read Only in the strict sense.

Are read only file groups really read only? What behaviour do we expect on tables that reside on the file group? Following on from my article on creating read only tables where it has become apparent (thanks MarkC) that putting the table on a read only file group doesn’t really make it read only in the strict sense – you can still drop columns! This article describes the behaviour and a method for getting round the problem.

First, create the file group:

ALTER DATABASE [csvtosp] ADD FILEGROUP [READONLYTABLES]

GO

ALTER DATABASE [csvtosp] ADD FILE ( NAME = N'mydb_readonly_tables', FILENAME = N'G:\SQL2005DATA\mydb_readonly_tables.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [READONLYTABLES]

GO

Next the table and populate it with some data:

DROP TABLE mytable

 

CREATE TABLE mytable (

    id  int not null identity( 0, 2 ) primary key clustered,

    somedata    char(4000) not null,

    somedata2   char(4000) not null,

 

) ON READONLYTABLES

go

 

SET NOCOUNT ON

 

DECLARE @i int

SET @i = 1

 

WHILE @i <= 50

BEGIN

    INSERT mytable ( somedata, somedata2 ) VALUES( '1234', '9876' )

 

    SET @i = @i + 1

 

END

 

Now mark the file group as read only:

ALTER DATABASE [csvtosp] MODIFY FILEGROUP [READONLYTABLES] READONLY

Let’s try some stuff - first can we add a column?

ALTER TABLE mytable ADD anewcol1 int NULL

Command(s) completed successfully.

Yes, interesting; can we add a NOT NULL column?

ALTER TABLE mytable ADD anewcol2 int NOT NULL DEFAULT( 1234 )

No, the plot thickens:

Msg 652, Level 16, State 1, Line 1

The index "PK__mytable__21B6055D" for table "dbo.mytable" (RowsetId 72057594039697408) resides on a read-only filegroup, which cannot be modified.

The statement has been terminated.

What is happening here is that the adding of a NULL column does not need to touch the actual table data – it’s a Meta only operation so only data on the PRIMARY file group which is read / write gets modified (the system tables).

Adding a NOT NULL column requires us to use a DEFAULT constraint which requires the actual table data to change, for every row the new column value is added. So, that needs to touch data in the read only file group as well as the schema.

Can we drop a column?

ALTER TABLE mytable DROP COLUMN somedata

Command(s) completed successfully.

Now that is really interesting and not good, you can drop a column from the table that resides on the read only file group. The above is again just a Meta data change, the only data modified is on the PRIMARY file group which remember is read / write. Read my other article on the behaviour where you drop a column but the data remains – how do you reclaim the space?

The work round is simple and introduces some new functionality that was introduced in SQL Server 2005, DDL (Data Definition Language) Triggers that can be server or database scoped.

CREATE TRIGGER make_table_read_only

    ON DATABASE

    FOR ALTER_TABLE

AS

BEGIN

    DECLARE @event_data XML

    SET @event_data = EVENTDATA()

 

    IF @event_data.value( '(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)' ) = 'mytable'

    AND @event_data.value( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' ) LIKE '% DROP %'

    AND @event_data.value( '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' ) LIKE '% COLUMN %'

 

    BEGIN

        RAISERROR( 'You are not allowed to use ALTER TABLE DROP COLUMN on table mytable', 16, 1 )

 

        ROLLBACK

 

    END

 

END

Looking at the above there are 3 important items to remember:

CREATE TRIGGER make_table_read_only

    ON DATABASE

    FOR ALTER_TABLE

The ON clause gives us the event scope; some events are Server scoped for example CREATE DATABASE, ALTER DATABASE and CREATE LOGIN, some are Database scoped for example CREATE TABLE, ALTER TABLE and CREATE PROCEDURE. For a complete list of these groupings see the entry in SQL Server 2005 Books Online.

CREATE TRIGGER make_table_read_only

    ON DATABASE

    FOR ALTER_TABLE

The FOR clause gives us the events we want this trigger to fire for. See the entry in SQL Server 2005 Books Online for a complete list of available events and what their scope is (Server or Database).

The final piece in this jigsaw is the EVENTDATA()  function. This gets back an XML structure containing all the information pertaining to why and on what the trigger fired, here is an example:

<EVENT_INSTANCE>

  <EventType>ALTER_TABLE</EventType>

  <PostTime>2007-08-27T10:59:28.760</PostTime>

  <SPID>63</SPID>

  <ServerName>TORVERM1</ServerName>

  <LoginName>TORVER\tonyrogerson</LoginName>

  <UserName>dbo</UserName>

  <DatabaseName>csvtosp</DatabaseName>

  <SchemaName>dbo</SchemaName>

  <ObjectName>mytable</ObjectName>

  <ObjectType>TABLE</ObjectType>

  <TSQLCommand>

    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />

    <CommandText>ALTER TABLE mytable DROP COLUMN somedata2

</CommandText>

  </TSQLCommand>

</EVENT_INSTANCE>

Note the CommandText is the actual statement within the batch that caused the trigger to fire, so, if you hid the ALTER TABLE in a stored procedure and executed the stored procedure you would see ALTER TABLE and not the exec on the stored procedure. For more information on the EVENTDATA() function see the entry in the SQL Server 2005 Books Online.

Comments

# Interesting Finds: August 27, 2007

Monday, August 27, 2007 3:02 PM by Jason Haley