SQL Server 2005 - I can’t remember where I read it….

Posts filed under 'SQL Server 2005'

SQL Server 2005 SP3 Released

Service pack 3 for SQL Server 2005 was released last week. In it, they have fixed a curious bug that I reported back in January.

The bug occurs when trying to delete rows from a table that have a NULL value for an image column. This works fine normally, but if there is a foreign key referencing the table (to any of its columns), any rows that have had their image column updated to be NULL fail to be deleted. This SQL demonstrates the problem:

-- create two linked tables
CREATE TABLE [dbo].[TableA]
(
[Identity] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[TableB_Identity] [int] NULL
)

CREATE TABLE [dbo].[TableB]
(
[Identity] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[DATA] image NULL
)

ALTER TABLE [TableA] ADD CONSTRAINT [FK_TableA_TableB]
FOREIGN KEY ([TableB_Identity]) REFERENCES [TableB] ([Identity])

-- insert some data
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)

-- this delete works successfully
DELETE FROM [TableB] WHERE [DATA] IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- insert some data
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)
INSERT INTO [TableB] ([DATA]) VALUES (NULL)

-- update the data to be have a NULL value
UPDATE [TableB] SET [DATA] = NULL WHERE [DATA] IS NULL

-- this delete doesn't work
DELETE FROM [TableB] WHERE [DATA] IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete doesn't work
DELETE FROM [TableB] WHERE ISNULL([DATA], NULL) IS NULL
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete does work successfully
DELETE FROM [TableB] WHERE EXISTS
(
SELECT * FROM [TableB] AS TB
WHERE [DATA] IS NULL
AND TB.[Identity] = [TableB].[Identity]
)
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

Not all of the delete queries work correctly. The output of the script is four result sets with the count of how many rows are in the table at each point. All of them should be 0 (as is the case on SQL Server 2000), but in SQL SERVER 2005 without SP3 they are actually 0, 3, 3 and 0.

The simple delete query:

DELETE FROM [TableB] WHERE [DATA] IS NULL

does not delete any rows after the values for the Data column have been updated to NULL, even though a similar select query:

SELECT * FROM [TableB] WHERE [DATA] IS NULL

will return rows.

Notably, if either the foreign key is removed, or the:

UPDATE [TableB] SET [DATA] = NULL WHERE [DATA] IS NULL

query is not performed, the script behaves as expected. Additionally, using text or ntext instead of image does not work as well, but using the new varchar(max), nvarchar(max) or varbinary(max) data types does work.

Apparrently, the distinction between NULL values stored as a result of an insert or an update has precendece in the WRITETEXT command:

If the table does not have in row text, SQL Server saves space by not initializing text columns when explicit or implicit null values are added in text columns with INSERT, and no text pointer can be obtained for such nulls. To initialize text columns to NULL, use the UPDATE statement. If the table has in row text, you do not have to initialize the text column for nulls and you can always get a text pointer.

This points to the "text in row" option having a bearing on this behaviour. Indeed, altering this option after creating the tables:

sp_tableoption N'TableB', 'text in row', 'ON'

results in the script working as expected. Useful as a potential workaround.

The bug is present in all versions of SQL Server 2005, but not in SQL Server 2000 or 2008.

A full list of what's changed in SP3 can be found here, with a full list of the bugs fixed here.

Add comment Permalink December 23rd, 2008 Adrian Banks 2003 Views

Preventing Accidental Schema Changes To The ‘master’ Database

If you have ever run some SQL within SQL Server Management Studio only to realise that you've run it against the master database by mistake, you'll know that it can sometimes be hard to undo the damage.

A simple way to stop these accidental changes is to create a database trigger that will prevent any schema changes to the master database:

USE master
GO

CREATE TRIGGER StopSchemaChanges ON DATABASE FOR
CREATE_APPLICATION_ROLE, ALTER_APPLICATION_ROLE, DROP_APPLICATION_ROLE,
CREATE_ASSEMBLY, ALTER_ASSEMBLY, DROP_ASSEMBLY,
CREATE_CERTIFICATE, ALTER_CERTIFICATE, DROP_CERTIFICATE,
GRANT_DATABASE, DENY_DATABASE, REVOKE_DATABASE,
CREATE_EVENT_NOTIFICATION, DROP_EVENT_NOTIFICATION,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_INDEX, ALTER_INDEX, DROP_INDEX,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA,
CREATE_STATISTICS, DROP_STATISTICS, UPDATE_STATISTICS,
CREATE_SYNONYM, DROP_SYNONYM,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW
AS
BEGIN
RAISERROR(N'Do you really modify the master database?', 16, 1) WITH NOWAIT
ROLLBACK TRANSACTION
END
GO

Any time you attempt to change the master database, SQL Server will fail with an error. If you do want to make a schema change, simply disable the trigger and then re-enable it once the schema change is complete.

(NB. this only works with SQL Server 2005)

Add comment Permalink June 8th, 2008 Adrian Banks 1946 Views

M	T	W	T	F	S	S
« Jun
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

I can’t remember where I read it….

Posts filed under 'SQL Server 2005'

SQL Server 2005 SP3 Released

Preventing Accidental Schema Changes To The ‘master’ Database

Most Popular Posts

Calendar

Posts by Month

Posts by Category

Feeds

Visitors