Tony Rogerson's ramblings on SQL Server

What a fantastic response we've had to session voting and registration opening, we've had over 2,000 votes as of now, with 10 votes per person that's over 200 people now registered!

We only have room for 350 so we are half way there and registration has only been open for a couple of days.

If you really want to come - get in there NOW and register and vote, avoid disappointment.

Saturday Oct 6th sees the first SQL Community conference (SQLBits - http://www.sqlbits.com/), 20 sessions in 4 tracks (Business Intelligence, DBA, Development and Katmai) on a Saturday in Reading at a cost of £0 - that's free of charge! 20 sessions from some of the top SQL folks in the UK.

You need to register on the http://www.sqlbits.com/ site and then select the 10 sessions (http://www.sqlbits.com/information/PublicSessions.aspx) you would most like to see. We will use this voting to help decide which sessions are to be run. We have over 36 sessions crossing the full SQL Server spectrum and only 20 slots.

Once you have selected and saved your selection you will be given the conference registration URL.

To register for sqlbits: http://www.sqlbits.com/information/PublicSessions.aspx

Tony.

We have now opened the full registration for www.SQLBits.com

You will need to register on the site and then select the 10 sessions (http://www.sqlbits.com/information/PublicSessions.aspx)  you would most like to see. We will use this voting to help decide which sessions are to be run. We have 36 sessions and only 20 slots. Even I can do those maths.

Once you have selected and saved your selection you will be given the registration URL.

Please don't just click any 10 because then you won't get the sessions you want to see.

We only have 300 places so make sure you register quickly.

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.

When using ALTER TABLE <x> DROP COLUMN <z> SQL Server does not actually remove the data, it simply changes the Meta data in the system tables so that the column no longer exists. Let us take a look at this behaviour and how to reclaim your space. This entry came about because MarkC found you could add Nullable columns to a table that resides on a read only file group.

First create the table and sample data:

CREATE TABLE mytable (

    id  int not null identity primary key clustered,

    somedata    char(8000) not null

)

go

SET NOCOUNT ON

DECLARE @i int

SET @i = 1

WHILE @i <= 50000

BEGIN

    INSERT mytable ( somedata ) VALUES( '1234' )

    SET @i = @i + 1

END

Capture the current space used and page density stats:

DBCC SHOWCONTIG( mytable )

TABLE level scan performed.

- Pages Scanned................................: 50000

- Extents Scanned..............................: 6275

- Extent Switches..............................: 6274

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.60% [6250:6275]

- Logical Scan Fragmentation ..................: 0.37%

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 83.0

- Avg. Page Density (full).....................: 98.97%

sp_spaceused mytable, true

name     rows   reserved    data        index_size   unused

-------- ------ ----------- ----------- ------------ --------

mytable  50000  401544 KB   400000 KB   1496 KB      48 KB

You can see that the table is 401Mbytes, the average page density is 98.97% full, the average free bytes per page is just 83.

Now drop the column:

ALTER TABLE mytable DROP COLUMN somedata

DBCC SHOWCONTIG( mytable )

TABLE level scan performed.

- Pages Scanned................................: 50000

- Extents Scanned..............................: 6275

- Extent Switches..............................: 6274

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.60% [6250:6275]

- Logical Scan Fragmentation ..................: 0.37%

- Extent Scan Fragmentation ...................: 0.00%

- Avg. Bytes Free per Page.....................: 83.0

- Avg. Page Density (full).....................: 98.97%

sp_spaceused mytable, true

name     rows   reserved    data        index_size   unused

-------- ------ ----------- ----------- ------------ --------

mytable  50000  401544 KB   400000 KB   1496 KB      48 KB

As you can see there is no change in the physical space used statistics, even the bytes free per page and page density are the same which is worrying.

First thought I suppose would be to defrag the index:

DBCC INDEXDEFRAG( 0, mytable, 1 )

Pages Scanned        Pages Moved          Pages Removed

-------------------- -------------------- --------------------

50000                0                    0

No change; that did not reclaim any space which is understandable because the INDEXDEFRAG works at getting pages in the correct order and doesn’t touch rows.

The way I’d recommend is to use DBREINDEX.

DBCC DBREINDEX( mytable )

DBCC SHOWCONTIG( mytable )

TABLE level scan performed.

- Pages Scanned................................: 81

- Extents Scanned..............................: 11

- Extent Switches..............................: 10

- Avg. Pages per Extent........................: 7.4

- Scan Density [Best Count:Actual Count].......: 100.00% [11:11]

- Logical Scan Fragmentation ..................: 1.23%

- Extent Scan Fragmentation ...................: 9.09%

- Avg. Bytes Free per Page.....................: 71.3

- Avg. Page Density (full).....................: 99.12%

At last, we have normality – the column really has gone.

This relies on you using a clustered index, if your table has no clustered index then it’s called a heap and the space will not be reclaimed. If you have a table heap and drop a column on that table then you will need to copy the data out, truncate the table and copy it back in again, that may be drop and recreate the table, SELECT INTO etc..

If I had the time I’d take a look and see what effect this had on page splits, I’m almost wondering that the ‘free space’ that isn’t really ‘free space’ will not be used.

To summarise, always use a clustered index in your design, if you are going to drop a column from the table always use DBCC DBREINDEX afterwards to ‘really’ apply the drop and just to Meta data.

If you have a requirement to make a table truly read only (even to sysadmins, well until they disable what you've added :)) usually people would just write a trigger for INSERT, UPDATE and DELETE and do a ROLLBACK but you could also use a CHECK constraint or can you? What is the best way to make a table read only?

First create the table and put some data in it:

CREATE TABLE mytable (

    somedata    varchar(10) not null

)

INSERT mytable ( somedata ) VALUES( '1234' )

Now add a check constraint to make it read only:

ALTER TABLE mytable WITH NOCHECK ADD CONSTRAINT chk_read_only CHECK( 1 = 0 )

We simply make a constraint expression that will always evaluate to false.

Now, when we try and update the table we get a constraint violation:

Msg 547, Level 16, State 0, Line 1

The UPDATE statement conflicted with the CHECK constraint "chk_read_only". The conflict occurred in database "master", table "dbo.mytable".

The statement has been terminated.

If we try inserting we get a constraint violation:

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_read_only". The conflict occurred in database "master", table "dbo.mytable".

The statement has been terminated.

What about a delete?

(3 row(s) affected)

Ah, oh dear – the check constraint didn’t work, why? Check constraints only validate data coming in via Update or Insert activity (Bulk Insert is another example); but, why validate data that’s getting deleted? You don’t, we aren’t interested in that data anymore.

Let’s look at the trigger:

CREATE TRIGGER mytable_read_only ON mytable

    INSTEAD OF INSERT,

               UPDATE,

               DELETE

AS

BEGIN

    RAISERROR( 'This table is read only.', 16, 1 )

    ROLLBACK TRAN

END

This effectively makes the table read only; but there is a problem here, namely performance. Let’s make our table a bit bigger:

DROP TABLE mytable

CREATE TABLE mytable (

    somedata    char(8000) not null

)

SET NOCOUNT ON

DECLARE @i int

SET @i = 1

WHILE @i <= 50000

BEGIN

    INSERT mytable ( somedata ) VALUES( '1234' )

    SET @i = @i + 1

END

Add the trigger back.

CREATE TRIGGER mytable_read_only ON mytable

    INSTEAD OF INSERT,

               UPDATE,

               DELETE

AS

BEGIN

    RAISERROR( 'This table is read only.', 16, 1 )

    ROLLBACK TRAN

END

Try an update; it takes an age!

Change the trigger so it’s only on DELETE’s.

ALTER TRIGGER mytable_read_only ON mytable

    INSTEAD OF DELETE

AS

BEGIN

    RAISERROR( 'This table is read only.', 16, 1 )

    ROLLBACK TRAN

END

Re try the UPDATE; it’s substantially quicker, this is because on the first row that breaks the constraint the UPDATE is terminated.

UPDATE mytable SET somedata = 'asasdasd'

That’s it; a method for making your table read only in SQL Server. You could just have well used the SELECT permission or DENY permission; however that doesn’t cover sysadmins or db_owners...

DENY UPDATE ON mytable TO [torver\tonyrogerson]

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

If you really need a table to be truly read only you could also either a) put it in its own database or b) put it on a file group and mark that read only, here’s how:

USE [master]

GO

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

USE csvtosp

GO

DROP TABLE mytable

CREATE TABLE mytable (

    somedata    char(8000) not null