T-SQL: Allow low privileged user to Truncate table

The problem with TRUNCATE is that it is a DDL statement rather than the regulation DELETE statement which is DML. As such, its not captured by using the db_datewriter database role and so on the face of it, you're left with two options:

1) Add the user to db_ddladmin role (or db_owner)
2) Grant ALTER table permissions to the table(s) in question

I'm not really a fan of either of these approaches as the first gives far too much control to the user and I try to avoid giving object level permissions if possible.

An alternative is to make use of the EXECUTE AS clause and wrap your TRUNCATE statement within a stored procedure and grant permissions on that instead.

-- create a test database 
CREATE DATABASE TruncateTest
GO
USE TruncateTest
GO
-- create a low privilege user to test with
CREATE USER NoRights WITHOUT LOGIN
GO
-- create a table to test against
CREATE TABLE dbo.TestData (Id INT IDENTITY(1,1))
GO
-- add some dummy data
INSERT INTO dbo.TestData DEFAULT VALUES
GO 20
-- check the data is ok
SELECT COUNT(*) FROM dbo.TestData
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- try to issue the truncate command
TRUNCATE TABLE dbo.TestData
GO
Msg 1088, LEVEL 16, State 7, Line 1
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO
-- create a wrapper procedure to truncate the table
CREATE PROCEDURE dbo.TruncateTestData
AS
TRUNCATE TABLE dbo.TestData
GO
-- grant execute to the test user
GRANT EXECUTE ON dbo.TruncateTestData TO NoRights
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO
Msg 1088, Level 16, State 7, Procedure TruncateTestData, Line 4
Cannot find the object "TestData" because it does not exist or you do not have permissions.
-- switch back to my superuser
REVERT
GO
-- modify the procedure to execute in the context of the owner (dbo)
ALTER PROCEDURE dbo.TruncateTestData
WITH EXECUTE AS OWNER
AS
TRUNCATE TABLE dbo.TestData
GO
-- impersonate the test user
EXECUTE AS USER = 'NoRights'
GO
-- execute the procedure
EXEC dbo.TruncateTestData
GO
-- switch back to the superuser
REVERT
GO
-- check the data
SELECT COUNT(*) FROM dbo.TestData
GO
-- tidy up
DROP DATABASE TruncateTest
GO

T-SQL: Change Schema of User Defined Type

I've been working with one of our developers to try and rationalise some database objects into some sensible schemas using the ALTER SCHEMA syntax:

http://msdn.microsoft.com/en-us/library/ms173423.aspx

Just out of interest, is it me or is this syntax for changing an objects schema a bit strange? Are you altering the Schema or the Object? Anyhow, I digress...

In our example, we have several User Defined Types which require transfer. Here is some sample code illustrating the problem:

CREATE SCHEMA NewSchema
GO
CREATE SCHEMA OldSchema
GO
CREATE TYPE OldSchema.MyUDT FROM VARCHAR(20);
GO
ALTER SCHEMA NewSchema TRANSFER OldSchema.MyUDT
GO


This gives the rather unhelpful error:

Cannot find the object 'MyUDT', because it does not exist or you do not have permission.

I checked the documentation and there isn't anything that suggests types should be treated any differently so I was stumped. Fortunately, I managed to unearth this post which provides a workaround to the problem.

ALTER SCHEMA NewSchema TRANSFER type::OldSchema.MyUDT
GO


I'm not sure why this is not documented in Books Online (or at least nowhere I can see). One to keep an eye out for.

T-SQL: Get Last Day of Month

One of my early blog posts highlighted how to get the first day of the current month. Well, another common task is get to the last day of a month and i'm (reasonably) pleased to see that SQL Server 2012 has an inbuilt function to achieve this.

In versions previous to SQL2012, this problem can be solved by running the following:

DECLARE @InputDate DATE = GETDATE() 

SELECT DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,@InputDate)+1,0))


However, we can now just use the inbuilt function EOMONTH which should make coding easier to read.

DECLARE @InputDate DATE = GETDATE()

SELECT EOMONTH(@InputDate)


-- 2012-02-29 



You can also specify an offset to the function which will allow you to look at months x number of months from your inputdate which I can certainly see being useful.

DECLARE @InputDate DATE = GETDATE()

SELECT EOMONTH(@InputDate, 4)


-- 2012-06-30

Note how the return type is a DATE and not a DATETIME.  Interestingly, Books Online suggests that the return type is "start_date or datetime2(7)"  although my tests suggest it always returns DATE.

T-SQL: INSERT with XML shredding is very slow

Back in 2010, I came across a performance bug with SQL2008 SP1. When shredding XML using the nodes query, the performance is fine with a SELECT but when you want to INSERT the data to a table/temptable/tablevariable the performance becomes very poor.

The query I was having problems with was something like this (Thanks for the sample data wBob!):

DECLARE @xml XML;
-- Spin up some test data 
WITH cte AS 
( 
SELECT 65 x 
UNION ALL 
SELECT x + 1 
FROM cte 
WHERE x < 90 ) 

SELECT @xml = (
    SELECT CHAR( a.x ) + ' & co.' AS "name"
    a.x AS "value"
    'FIELD' AS "paramdata/field"
    FROM cte a 
      CROSS JOIN cte b 
      CROSS JOIN cte c
    FOR XML PATH('root'), TYPE
    ) 

DECLARE @ConfigData TABLE (ID INT, Name NVARCHAR(255), Value NVARCHAR(255), ParamData XML)

INSERT INTO @ConfigData (ID, Name , Value, ParamData) 
SELECT 1,
    tbl.cols.value('name[1]', 'varchar(1000)'),
    tbl.cols.value('value[1]', 'varchar(1000)'),
    tbl.cols.query('./paramdata[1]')
FROM @XML.nodes('//root') AS tbl(cols) 
GO

I raised this with MS via Connect (#562092) and went on to implement my own workaround using the following CLR shredding function:

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "tvf_clr_FillParameterData",
        TableDefinition = "Name nvarchar(255), Value nvarchar(255), ParamData nvarchar(255)")]
    public static IEnumerable tvf_clr_ParameterDataShredder(SqlXml parameterData)
    {
        XmlDocument document = new XmlDocument();
        document.LoadXml(parameterData.Value);
        return document.SelectNodes("//parameter");
    }

    public static void tvf_clr_FillParameterData(object row, out string outName, out string outValue, out string outParamData)
    {
        XmlNode document = (XmlNode)row;

        outName = document.SelectSingleNode("name").InnerXml;
        outValue = document.SelectSingleNode("value").InnerXml;

        outParamData = null;
        if (document.SelectSingleNode("paramdata") != null)
            outParamData = document.SelectSingleNode("paramdata").OuterXml;
    }
};
 
This served me well and solved the performance issue and all was fine. Until now.

The thing is, this method doesn't handle decoding of escape characters in XML, so any &s that exist in the XML string being shredded will be returned as &:amp. Not ideal. My first thought was to just implement a decoding function within the CLR routine but the main candidates use System.Web which is not available within SQL Server. I eventually stumbled upon this post from Jonathan Keyahias which provided a sql safe Encoding implementation and I was going to use this as a basis for writing my own decoding function. While waiting the 20mins for Visual Studio 2008 to open, it occured to me to revisit the Connect case and fortunately, there were a number of workarounds there, and a combination of the solutions the most effective.

INSERT INTO @ConfigData (ID, Name , Value, ParamData)
SELECT     1,
    tbl.cols.value('(name/text())[1]', 'nvarchar(255)'),
    tbl.cols.value('(value/text())[1]', 'nvarchar(255)'),
    tbl.cols.query('./paramdata[1]') 
FROM @xml.nodes('//root') AS tbl(cols) 
OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

The above simple modification to my SQL INSERT statement and performance was acceptable and more importantly, the decoding of the escaped characters was fine. Saved me a lot of work.

T-SQL: SQL2012 Code Snippets

I've never really bought into the idea of object templates in SQL Server Management Studio and in my experience, not many other database professionals have either!

Just recently however, I've been getting a increasingly frustrated at the non-standard development pattern of database objects and (just as importantly) the lack of documentation for procedures and functions. I've written in the past about using Extended Properties to help version your database and this could easily be extended to document objects too. However, in a previous role I picked up quite a nice habit of adding documentation headers to each procedure and function which gives some basic information to anyone looking at the procedure to give them a headstart in debugging/understanding. A heading may look like this:


/*
created by: Richard Brown
date created: 02/02/2012
description: this procedure just returns information about customers
debug:
   declare @custid = 1
   exec dbo.getcustomers @custid

*/

Encouraging developers to do this in practice is a real problem and this is where code snippets can come in. There are numerous default snippets available in SQL2012 management studio and you can access them by hitting CTRL-K, CTRL-X and the tabbing to the appropriate snippet:

Here you can see there are already a few default stored procedure snippets including a "create a simple stored procedure" snippet. You can create/add new snippets as you see fit but for my purposes, I want to just modify the existing snippets so they include my header as default.

Take a note of the location of the snippets by navigating to Tools/Code Snippets Manager:

You can then find the .snippet file and make the appropriate modifications. Its just an xml file and for my needs I just needed to make the following change:

<Code Language="SQL">
            <![CDATA[
/* 
created by: 
date created:
description:
debug:

*/
CREATE PROCEDURE $SchemaName$.$storedprocname$ 
    @$Param1$ $datatype1$ = $DefValue1$,
    @$Param2$ $datatype2$ 
AS
    SELECT @$Param1$,@$Param2$ 
RETURN 0 $end$]]>
        </Code> 

When I hit the snippet function, I now get the updated template for my stored procedure. Hoepfully, this will encourage the devs to follow the standards.

Admin: Bulkadmin vs ADMINISTER BULK OPERATIONS

I had an application thrust upon me recently which required the user executing it to have permissions to use the BULK INSERT command in T-SQL. I was aware of the server role bulkadmin which would have been a nice sledgehammer approach to crack this nut, but I was hoping for something a little more refined. Unfortunately, the only thing I came up with was the ADMINISTER BULK OPERATIONS command which would grant this privilege without having the server role membership but I was curious as to what the difference between the 2 was.

As far as I can tell, there is no difference between the 2 operations and both seem to achieve the same thing. From this I assume that bulkadmin is just a wrapper around the ADMINISTER feature although I'd be interested in knowing (as I couldn't find out) if there are other permissions the server role gives you.

Lets look at the example (running on SQL2008 SP2)

-- create some test logins
CREATE LOGIN [DOMAIN\user1] FROM WINDOWS
GO
CREATE LOGIN [DOMAIN\user2] FROM WINDOWS
GO
-- lets impersonate a user
EXECUTE AS LOGIN = 'BARRHIBB\user1'
GO
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO 
-- does the user have bulk op permissions? 
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS');
GO

-- lets go back to being a superuser 
REVERT
GO
USE MASTER
GO
-- how about if we just grant administer bulk operations to the user? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO
-- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER')
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO



-- can the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO

Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.

So lets see what happens with the server role:

REVERT
-- remove the users permissions 
REVOKE ADMINISTER BULK OPERATIONS TO [DOMAIN\user1]
GO 
-- now add the user to the bulk admin role and check permissions again
EXEC MASTER..sp_addsrvrolemember @loginame = N'DOMAIN\user1', @rolename = N'bulkadmin'
GO
 -- lets impersonate that user
EXECUTE AS LOGIN = 'DOMAIN\user1' 
GO 
-- and check the server permissions 
SELECT * FROM sys.server_permissions
GO
SELECT * FROM fn_my_permissions(NULL,'SERVER') 
GO
-- does the user have bulk op permissions?
SELECT has_perms_by_name(NULL, NULL, 'ADMINISTER BULK OPERATIONS'); 
GO

NB: The main difference here is that the permission has not been explicitly granted but it is an effective permission.

-- perhaps with the server role the user grant other users to be bulky people? 
GRANT ADMINISTER BULK OPERATIONS TO [DOMAIN\user2]
GO

Msg 4613, Level 16, State 1, Line 2
Grantor does not have GRANT permission.

So it seems that the bulkadmin server role doesn't offer any "admin" from a security point of view which begs the question, just why would you add a user to this role rather than just explicity grant them the permission through the GRANT statement (or vice versa)?

What is the RIGHT technology?

One of things I strongly believe in is using the right technology for solving the right problem. You can often find (understandably) that IT professionals look for solutions using technologies they understand best which can lead to inappropriate decsions being taken. "When all you have is a hammer....".

However, just how do you define the right technology?

An analogy. Whats the best way of buying music? Is it online or in HMV (other retail outlets are available)? Now, for price and convenience then online is pretty tough to beat. But what if you don't have a computer or don't know how to use one? There may be a premium associated with buying in the high street but its still the best understood method there is - everyone knows how to buy from a shop.

I've been involved with a particular application written entirely on SQL Server technologies and its an impressive implementation. Its fast and reliable and for someone familiar with the technology entirely supportable. However, its not me that has to use it. The "customer" has limited technology skills and yet is tasked with driving the application with recurring difficulty and as a consequence, there is a feeling that the application is not robust. The fact is, that the application is entirely robust, but is not built for a technology-light person to use.

Previous to the SQL implementation, there was an Excel equivalent which was less robust and performant. However, the users fully understood how to work it and were much more comfortable with it even at the cost of longer runtime. Asked to use its Excel predecessor, I would have probably claimed it not robust as a result of not being comfortable using that technology. Same issue, different technology.

We need to make the right technology choices and this doesn't just mean what is the best technology for the job. First and foremost, applications need to be fit for purpose and usable by the customer even if thats at the expense of the most elegant technology solution. If customers have no experience or understanding of a particular technology, then we shouldn't be building solutions that require that skillset. Simple.

Admin: Start and Stop Local Instance of SQL Server

As a database professional, i'm accustomed to having a local sql server instance installed. However, it seems that SQL Server (typically Express) is finding its way onto more and more desktops, often unused and unknown to the user!! With the web browser FireFox taking up almost half your memory, you don't want any other applications taking your valuable resources.

I recommend changing the service settings of your SQL services to manual and only turning them on when you are actually going to use them. If you're using a shared database server, you may find that even as a SQL developer you may not use your local instance as often as you might have thought.

I have created 2 simple batch files on my desktop which I can use for stopping and starting my local SQL services meaning its a mere double click away to get your instance available.

StartSQLServices.bat

NET START mssqlserver
NET START sqlserveragent
NET START ReportServer
NET START msdtsserver100

StopSQLServices.bat

NET STOP mssqlserver
NET STOP sqlserveragent
NET STOP ReportServer
NET STOP msdtsserver100

* your Service names may vary depending on instance names and SQL version.

Admin: Windows Group Login Ambiguity

Its a typical scenario to grant login access to SQL Server via Windows groups as this eases the administrative burden of setting up multiple logins all with the same access. However, its not uncommon for a user to belong to multiple Windows groups and its possible that these groups could each have a SQL login. So whats the problem? Well, consider this example:

User - DOMAIN\PeterPan
Groups - DOMAIN\ReadOnlyGroup, DOMAIN\WriteOnlyGroup

We have 2 windows groups both of which have access to SQL Server, albeit for different purposes. Peter is a member of both groups as he needs access to do both actions. My question is, when Peter logs in, under which Windows Group has he been granted authentication?

Why does it matter, I here you ask?

From a pure permissions point of view, there is no issue as the usual hierarchy applies. Permissions are built on top of each other so SQL Server will combine the permissions from both groups (DENY overriding GRANT etc) and that gives us the behaviour we want. You can validate this by simply adding a differing set of permissions to the different groups and then adding or removing the member from it:

EXEC sp_addrolemember N'db_datareader', N'DOMAIN\ReadOnlyGroup'

EXEC sp_addrolemember N'db_datawriter', N'DOMAIN\WriteOnlyGroup'

You can view the permission set in the following way:

SELECT *, DB_NAME(), SUSER_SNAME() FROM fn_my_permissions(NULL, 'DATABASE')

However, this works because there is a defined hierarchy in place which allows SQL Server to make the correct choice based upon permission settings.

But, what about something where no such hierarchy exists like a default language. If the 2 groups have different default languages then how do we determine which default language the login connection should take? This is particularly important when dealing with dates. US_English and British_English interpret dates in MDY and DMY respectively so the date 30/11/2011 will throw an error in US_English but work correctly in British_English. Worse still, you may not get an error at all and the date is valid but not what the user wants e.g 01/02/2011. Of course, best practice would see us using ISO formatted dates YYYYMMDD but I dare say not every application adheres to this.

I understand that this is a difficult (if not impossible) issue to rectify. I mean, there is no logical hierarchy or set of rules that can be put in place when this situation occurs. However, I'd like to understand how SQL Server does determine which language it uses in this scenario as I couldn't find a method of doing so. Is it the windows group which was last granted access to SQL? Does anyone know?

T-SQL: Audit Data Changes

I was recently asked about how to implement auditing of data changes for a database. I know SQL Server contains the Change Data Capture feature but (as is often the case with things I want!), its an Enterprise only feature meaning its out of the reach for the application in question.

As it happened, it turned out that there was already an application in-house (that I hadn't developed) which implemented its own auditing of changes based around a simple Audit table and Triggers. I'm still yet to find out who wrote it, but it certainly does the trick. The database it runs on is relatively static so I do have reservations how scalable it is but for some tables you want control over, it may be suitable.

CREATE TABLE [dbo].[Audit](
   [AuditID] [int] IDENTITY(1,1) NOT NULL,
   [Type] [char](1) NULL,
   [TableName] [varchar](128) NULL,
   [PrimaryKeyField] [varchar](1000) NULL,
   [PrimaryKeyValue] [varchar](1000) NULL,
   [FieldName] [varchar](128) NULL,
   [OldValue] [varchar](1000) NULL,
   [NewValue] [varchar](1000) NULL,
   [UpdateDate] [datetime] NULL,
   [UserName] [varchar](128) NULL
) ON [PRIMARY]

GO 
ALTER TABLE [dbo].[Audit]  
ADD  CONSTRAINT [DF_Audit_UpdateDate] DEFAULT (GETDATE()) FOR [UpdateDate]
ALTER TABLE [dbo].[Audit] 
ADD  CONSTRAINT [DF_Audit_UserName] DEFAULT (SUSER_SNAME()) FOR [UserName]
GO
CREATE TRIGGER dbo.MyTableChangeTracking
ON dbo.MyTable 
FOR INSERT, UPDATE, DELETE
AS

DECLARE @bit INT
DECLARE @field INT
DECLARE @maxfield INT
DECLARE @char INT
DECLARE @fieldname VARCHAR(128) 
DECLARE @TableName VARCHAR(128) = 'MyTable';
DECLARE @PKCols VARCHAR(1000)
DECLARE @sql VARCHAR(2000) 
DECLARE @UserName VARCHAR(128) = SUSER_SNAME() 
DECLARE @Type CHAR(1) 
DECLARE @PKFieldSelect VARCHAR(1000)
DECLARE @PKValueSelect VARCHAR(1000)

-- Action 
IF EXISTS (SELECT TOP 1 * FROM inserted)
BEGIN
   IF EXISTS (SELECT TOP 1 * FROM deleted)
       SET @Type = 'U' -- update
   ELSE
       SET @Type = 'I' -- insert 
END
ELSE
   SET @Type = 'D' -- delete

-- get all the changed values (need both tables for update) 
SELECT * INTO #ins FROM inserted 
SELECT * INTO #del FROM deleted 

-- Get primary key columns for full outer join 
SELECT @PKCols = COALESCE(@PKCols + ' AND', ' ON') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       ON c.TABLE_NAME = pk.TABLE_NAME
           AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
WHERE pk.TABLE_NAME = @TableName
   AND CONSTRAINT_TYPE = 'PRIMARY KEY' 

-- Get primary key fields select for insert 
SELECT @PKFieldSelect = COALESCE(@PKFieldSelect + '+', '') + '''' + COLUMN_NAME + '''' 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       ON c.TABLE_NAME = pk.TABLE_NAME
           AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
WHERE pk.TABLE_NAME = @TableName
   AND CONSTRAINT_TYPE = 'PRIMARY KEY' 

SELECT @PKValueSelect = COALESCE(@PKValueSelect + '+', '') + 'convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
       ON c.TABLE_NAME = pk.TABLE_NAME
           AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
WHERE pk.TABLE_NAME = @TableName
   AND CONSTRAINT_TYPE = 'PRIMARY KEY' 

-- raise an error if there is no Primary Key 
IF @PKCols IS NULL 
BEGIN
   RAISERROR('no PK on table %s', 16, -1, @TableName)
   RETURN
END

SELECT @field = 0,
   @maxfield = MAX(ORDINAL_POSITION)  
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName 

-- now loop through the fields to log the values from each field
WHILE @field < @maxfield 
BEGIN
   SELECT @field = MIN(ORDINAL_POSITION)
   FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
  
   SELECT @bit = (@field - 1 )% 8 + 1
   SELECT @bit = POWER(2,@bit - 1)
   SELECT @char = ((@field - 1) / 8) + 1
  
   IF SUBSTRING(COLUMNS_UPDATED(),@char, 1) &amp; @bit > 0 OR @Type IN ('I','D')
   BEGIN
       SELECT @fieldname = COLUMN_NAME
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = @TableName
           AND ORDINAL_POSITION = @field
      
       -- dynamically build and execute the audit insert statement
       SELECT @sql = 'INSERT INTO dbo.Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue)'
       SELECT @sql = @sql + ' SELECT ''' + @Type + ''''
       SELECT @sql = @sql + ',''' + @TableName + ''''
       SELECT @sql = @sql + ',' + @PKFieldSelect
       SELECT @sql = @sql + ',' + @PKValueSelect
       SELECT @sql = @sql + ',''' + @fieldname + ''''
       SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),d.' + @fieldname + ')'
       SELECT @sql = @sql + ',CONVERT(VARCHAR(1000),i.'  + @fieldname + ')'
       SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
       SELECT @sql = @sql + @PKCols
       SELECT @sql = @sql + ' WHERE i.' + @fieldname + ' <> d.' + @fieldname
       SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' IS NOT NULL)'
       SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' IS NULL)'

       EXEC (@sql)
   END
END
GO


Its quite a complicated trigger and took some time to get my head round but it certainly does the trick and you just need to implement this for each table you wish to track changes on. The data is readable too:

But i wasn't comfortable with the overhead of the trigger, the dynamic sql nor the fact that I hadn't written it myself! So I considered a second approach, still using triggers but this time just dumping the changed records to an audit table in XML format. This would have the advantage of a much simpler trigger even at the cost of readability of results and potentially higher storage costs.


CREATE TABLE dbo.tblAudit(
   AuditID INT NOT NULL IDENTITY(1,1),
   TableName VARCHAR(255),
   DataInserted XML,
   DataDeleted XML,
   DateCreated DATETIME,
   CreatedBy VARCHAR(2000)
) 
GO 

CREATE TRIGGER dbo.MyTable_ChangeTracking 
ON dbo.MyTable 
FOR INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON-- set the tablename
DECLARE @TableName VARCHAR(255) = 'dbo.MyTable'-- grab the changed data in XML format
DECLARE @InsertXML XML = (SELECT * FROM inserted FOR XML AUTO);
DECLARE @DeleteXML XML = (SELECT * FROM deleted FOR XML AUTO);-- log it 
INSERT INTO dbo.tblAudit (TableName, DataInserted, DataDeleted, DateCreated, CreatedBy) 
SELECT @TableName, @InsertXML, @DeleteXML, GETDATE(), SUSER_SNAME()
GO

To interogate the results, you'd need to be comfortable working with XML but the simplicity of the trigger is something i really like. Hopefully, you wouldn't need to be looking at this data too often so the ease of logging is the main factor when implementing something like this. You could easily deploy this across multiple tables/servers too with a simple Powershell script.

Now, I wouldn't recommend either of this in a highly transactional database and you would want to consider whether to implement this method if the performance of your DML statements was critical and your storage quite tight. I'm going to experiment a bit with the CDC to see just what benefits it gives over a bespoke implementation (I suspect its much more lightweight) while also hoping that the feature gets downgraded to the lower editions of SQL Server.

Denali: Now gets a proper name!

Since I last blogged about Denali, Microsoft has announced the official name for this version of SQL Server to be....(drum roll). SQL2012. Ok, ok. I'm pretty underwhelmed too but hey ho. Project Crescent now has the rather grand title of Power View and Juneau has the slightly less impressive Data Tools.

More exciting and interesting though are the changes to the Editions and also the licensing costs, in particular the introduction of a BI edition and the move to per core licensing. You can read more about this in this blog from Geoff Hiten.

I also came across this blog from the SQL Express guys which talks of the new LocalDB edition aimed at Developers. A lightweight database server with less management overhead than a full SQL Express edition. Nice.

SSRS: Use Stored Procedures in Datasets

This is my contribution to T-SQL Tuesday #24 hosted by Brad Schulz (blog) on the subject of Prox ‘n’ Funx (Stored Procedures and Functions to you and me :-) ).

I'm a big believer in using Stored Procedures (or at the very least, UDFs) for your Reporting Services datasets. and separating your presentation layer from your data layer and moving the SQL code away from the RDL.

The benefits of this are that you as long as the meta-data of your Stored Procedure stays the same, then you able to modify and enhance your SQL code without having to touch the RDL. You essentially abstract away the source code from the report.

Perhaps you're improving performance by moving to JOINs from cursors, extended the business logic to only return rows that meet new criteria or simply doing a refactoring of SQL code to standardise your table names. All of these don't affect the presentation layer and having them reside as Stored Procedures on the database, gives huge maintenance benefits.

Other advantages include having all your T-SQL held in the one place and knowing that you are aware of the impact of any changes without having to worry about dependancies elsewhere. Also, you will often be re-using code (eg for parameter datasets) and using a single stored procedure helps reduce duplication of effort (and probably performance benefits too).

Of course, there are downsides to this approach. If you need to introduce a new parameter to a report (which is passed to your dataset) then you have to change both the RDL and the stored procedure. I can see this being a slight irritation as you now have 2 deployments whereas holding the SQL code "inline" means a simple upload of the new report.

For me though, the former approach still wins and I advocate using Stored Procedures for Reporting Services datasets. I've been experimenting recently with putting Stored Procedures used for Reports into their own schema (acting as a namespace) although I can't categorically say whether this has been a success or not (Jamie Thomson (Blog | Twitter) has an interesting blog post which touches on Schema usage here).

T-SQL: Why have NULL bit fields?

I was asked the question the other day, why SQL Server allows NULLs for the BIT datatype. What you need to bear in mind is what NULL represents - a value which is unknown - and, so there are 3 states which you need to cater for. True, False or Unknown.

As is my wont, I like to explain things with analogies and this is no different. Consider a table holding job application details for a job board website.

CREATE TABLE dbo.tblJobApplications(
   CandidateID INT,
   JobID INT,
   DateApplied DATE,
   Successful BIT)

-- Application in progress
INSERT INTO dbo.tblJobApplicationsSELECT 1, 1, GETDATE(), NULL

-- Application unsuccessful
INSERT INTO dbo.tblJobApplicationsSELECT 2, 1, GETDATE(), 0

-- Application successful
INSERT INTO dbo.tblJobApplicationsSELECT 3, 1, GETDATE(), 1

The key field to note here is of course the BIT field which indicates the success or failure of the applicaiton. Obviously, when a candidate applies to a job, the success of the application isn't known - the candidate has been accepted, nor rejected. Its only at the end of the lifecycle of the application that this field can take on a meaningful value.

Hopefully, this contrived example helps explain just when you might require a NULL bit field.