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

) ON READONLYTABLES

go

INSERT mytable ( somedata ) VALUES( '1234' )

Go

Now you need to make the File Group read only...

ALTER DATABASE [csvtosp] MODIFY FILEGROUP [READONLYTABLES] READONLY

Msg 5070, Level 16, State 2, Line 1

Database state cannot be changed while other users are using the database 'csvtosp'

The problem is that you need to kick people out of the database while you do it, unfortunately WITH ROLLBACK IMMEDIATE doesn’t work for this option so it’s back to KILL.

When you try and INSERT or UPDATE you get a lovely message now:

Msg 652, Level 16, State 1, Line 1

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

My preference would be making the file group read only because another benefit of that is that SQL Server doesn’t have to issue locks. If you decided on the separate database route you simply create a view in the read/write database back to the read/only one – SELECT <cols> FROM readonlydb.schema.table.

One last thing, if you truly want the data so it cannot be modified then these methods are not the approach to take because the sysadmins can tamper; you need to keep the logs or use a linked server out to another locked down SQL Server that has different admin permissions.

I'm researching some comparisons between Oracle and SQL Server to counter a crib sheet an Oracle DBA has knocked up (more on that), for instance what is the equiv of a tablespace or system trigger in SQL Server.

Looking at SQL Server books online and the MSDN online version of bol -> http://msdn2.microsoft.com/en-us/library/ms130214.aspx we have a nice tree structure to our left that greatly assists navigation and finding and reading stuff, it's fairly structured. 

Oracle, what a pain; the equiv of the tree is here: http://www.oracle.com/pls/db111/show_mindex, click on T say and it gives you another page (http://www.oracle.com/pls/db111/master_index?letter=t); very intuitive (not).

I often winge about bol but looking at the competitor equivalent I sigh with relief.

Do you know what % of your SQL Server memory cache is being used by the procedure cache rather than data, you know - forcing you to get more of your data from disk rather than in cache. A client of mine on SQL 2000 recently had a proc cache of 800MBytes, this was standard edition and SQL was taking in total 1.7GBytes of memory - the procedure cache was nearly 50% of the memory allocated to SQL!

If like me you want a configurable option in sp_configure to control this then vote for it here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293188&wa=wsignin1.0

Tony

Yet another take on the CSV route for passing an array to a stored procedure, this one is clever though – it’s got full data validation because I load the values into a table, but with no loops, no self-joins; just dynamic SQL and the REPLACE function. The concept is very simple, I’ve bloated the procedure out to give nice data error handling; so if one of your CSV values fails the validation (CHECK constraint for instance) then I tell you what the value position is.

I’ve also coded in (it’s so simple) so check flags that you specify if you want to allow only unique values, or if you only want to allow not null values.

The procedure is below; I’ve not tested it for scalability but because of the data validation it won’t be as good as straight dynamic SQL, also, it would probably be better with a table variable but that would make the coding just a little bit more tricky J.

Example 1 – Only allow unique values

exec csv_eg @csv = ',11,12,13,14,,,,,,,,,,,,,,12,',

            @is_allow_nulls = 'Y',

            @is_unique_values = 'Y',

            @is_debug = 'N'

Msg 2601, Level 14, State 1, Line 12

Cannot insert duplicate key row in object 'dbo.#csv_split' with unique index 'ncui'.

The statement has been terminated.

Msg 50000, Level 16, State 1, Line 44

Data Error on value position 6, processing terminated.

Note – it tells you which position causes the error (value position 6) because that is the second NULL to be inserted.

Example 2 – Don’t allow NULL’s/Empty values

exec csv_eg @csv = ',11,12,13,14,,,,,,,,,,,,,,12,',

            @is_allow_nulls = 'N',

            @is_unique_values = 'N',

            @is_debug = 'N'

Msg 515, Level 16, State 2, Line 10

Cannot insert the value NULL into column 'csv_value', table 'tempdb.dbo.#csv_split__________________________________________________________________________________________________________000000000027'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Msg 50000, Level 16, State 1, Line 44

Data Error on value position 5, processing terminated.

As you can see, we get the error, and it the position on the csv that caused it.

Example 3 – CHECK constraint firing...

exec csv_eg @csv = ',11,12,13,14,1,,,,,,,,,,,,,12,',

            @is_allow_nulls = 'Y',

            @is_unique_values = 'N',

            @is_debug = 'N'

Msg 547, Level 16, State 0, Line 10

The INSERT statement conflicted with the CHECK constraint "CK__#csv_spli__csv_v__09F4B950". The conflict occurred in database "tempdb", table "dbo.#csv_split__________________________________________________________________________________________________________000000000028", column 'csv_value'.

The statement has been terminated.

Msg 50000, Level 16, State 1, Line 44

Data Error on value position 5, processing terminated.

If I was inclined, I’d make the CHECK constraint definition dynamic as well but I can’t do everything for you ;).

The other good thing about this is that because we have used a # table then SQL Server will have statistics on the number of rows and cardinality on that # table so it will give us a good query; if you’d used the multiple parameter approach as suggested by Joe Celko SQL Server could only sniff the parameter but whether that would give it some good info for your big IN clause I’m not sure. The other good thing about this method is that it’s a table join rather than an IN so it will be more efficient for more values.

CREATE PROC csv_eg

    @csv            varchar(500),

    @is_allow_nulls   char(1) = 'Y',

    @is_unique_values char(1) = 'N',

    @is_debug   char(1) = 'N'

AS

BEGIN

    /***

        Convert a CSV to a TABLE with data validation

        Tony Rogerson, Aug 2007

     ***/

    SET @csv = ltrim(rtrim(@csv))

    --  Initial parse of input to make sure general syntax of the csv is valid

    --  check we have commas correct

    IF LEFT( @csv, 1 ) <> ','       --  If not front comma then add one

        SET @csv = ',' + @csv

    IF RIGHT( @csv, 1 ) = ','       --  If last character is a comma then add a NULL after it

        SET @csv = @csv + 'NULL'

    SET @csv = REPLACE( @csv, ',,', ',NULL,' )  --  Change ,, to NULL

    SET @csv = REPLACE( @csv, ',,', ',NULL,' )  --  Need to do it twice for rest of ,, not caught in first one

    --  // initial parse is over.

    --  Now the donkey work, convert it from a flat CSV structure to a TABLE

    --  Simple string replacing, no need for self joins and loops

    DECLARE @sql varchar(max)

    SET @sql = REPLACE( @csv, ',', CHAR(13) + CHAR(10) + 'insert #csv_split ( csv_value ) values( ' )

    SET @sql = REPLACE( @sql, CHAR(13), ' ); @V' + CHAR(13) )

    SET @sql = RIGHT( @sql, LEN( @sql ) - 7 ) + ' ); @V'

    SET @sql = REPLACE( @sql, '@V', '

if @@error <> 0 goto err;' )

    --  We now have the SQL that will convert CSV to a TABLE, so exec it.

    CREATE TABLE #csv_split (

        val_position    smallint    not null IDENTITY,

        csv_value       int null CHECK( csv_value between 10 and 20 )

    )

    IF @is_allow_nulls = 'N'

        EXEC( 'ALTER TABLE #csv_split ALTER COLUMN csv_value INT NOT NULL' )

    IF @is_unique_values = 'Y'

        EXEC( 'CREATE UNIQUE INDEX ncui ON #csv_split( csv_value )' )

    SET @sql = 'SET NOCOUNT ON; ' + @sql + ';

    goto done;

err:

    DECLARE @msg varchar(120);

    SET @msg = ''Data Error on value position '' + CAST( ( SELECT COUNT(*) FROM #csv_split ) + 1 AS varchar(5) ) + '', processing terminated.'';

    RAISERROR( @msg, 16, 1 );

done:'

    IF @is_debug = 'Y'

        PRINT @sql

    EXEC( @sql )

    IF @@ERROR <> 0

        RETURN

    SELECT *

    FROM #csv_split

END

You'll like this; always worth getting your dev team to watch just how stupid you can look if you code and allow SQL injection in.

Always parameterise or preferably use stored procedures; if using dynamic SQL always try and parameterise and if you can't always make sure you handle apostrophes properly.

Take a look: http://uk.youtube.com/watch?v=MJNJjh4jORY