In previous posts I discussed the
finding and
fixing of leading and trailing spaces in variable character columns (varchar and nvarchar) is SQL Server 2008. I also discussed
here the affect that the ANSI_PADDING setting has on variable character trailing spaces. In this post I will discuss specific methods to stop leading and trailing spaces from getting into the variable character columns of your database.
Introduction
There are two main categories of "Stop Leading and Trailing Spaces" methods. The first, I call "Real Time" methods. The second, I call "After The Fact" methods. I will discuss each in that order.
Please note that there are likely additional methods that are not included in this posting for stopping leading and trailing spaces in variable character columns. If other methods come to light I will update this post. If anyone has any ideas or suggestions please share them in the comments below.
Real Time Methods
There are several possible methods that make up this category. These methods work in real time to either fix or block leading and trailing spaces from getting into the variable character columns in your database. Following is a listing of some possible methods.
- ANSI_PADDING OFF
- CHECK CONSTRAINTS
- INSERT/UPDATE Triggers
- INSERT/UPDATE Stored Procedures
- Application Developer Training
After The Fact Methods
I came up with a couple of related methods for this "After The Fact" idea. These methods don't fix or stop leading and trailing spaces from getting into variable character columns in your database, but rather are about finding and fixing the problem at some later date/time after the INSERT/UPDATE has occurred. There are several possible variations on these two ideas.
- Periodic Variance/Audit Report and Cleanup
- Audit and/or Notification Triggers
Let's get started.
Real Time Methods
ANSI_PADDING OFF
As described
here, when creating a varchar column either through a CREATE TABLE or ALTER TABLE ADD column command use the ANSI_PADDING setting of OFF. When you do this the database engine will automatically trim trailing spaces from any values inserted into the varchar column. The default for ANSI_PADDING is ON so you must explicitly set it to OFF when creating a varchar column. Please remember that the leading spaces are never automatically trimmed. Also, please remember that trailing spaces are never automatically trimmed from nvarchar columns.
CHECK CONSTRAINTS
Following is some sample code that shows a CHECK CONSTRAINT that blocks INSERTs and UPDATEs on a variable character column if the value being written contains leading and/or trailing spaces. Recommend not backfitting this into a production database if the applications writing data to the database aren't also updated to either account for this error or trim variable character data before it is inserted or updated in the database. This method is closely connected to the "Application Developer Training" method below. This is one of those methods that can break a bunch of stuff if not done correctly. The earlier in the SDLC (Software Development Lifecycle) that you implement this idea/method for stopping leading/trailing spaces in variable character columns the better!
CREATE TABLE MyTable1 (MyCol nvarchar(20));
GO
ALTER TABLE dbo.MyTable1 ADD CONSTRAINT
CK_MyTable1_MyCol_StopLeadingTrailingSpaces CHECK
('X' + ISNULL(MyCol, '') + 'X' = 'X' + LTRIM(RTRIM(ISNULL(MyCol, ''))) + 'X')
GO
INSERT dbo.MyTable1(MyCol) VALUES (NULL); -- should work
INSERT dbo.MyTable1(MyCol) VALUES ('Test String'); -- should work
INSERT dbo.MyTable1(MyCol) VALUES (' Test String'); -- throws error
INSERT dbo.MyTable1(MyCol) VALUES ('Test String '); -- throws error
INSERT dbo.MyTable1(MyCol) VALUES (' Test String '); -- throws error
INSERT/UPDATE Triggers
If you have a table with variable character columns that you want to stop leading/trailing spaces from showing up in, you can add INSERT/UPDATE Triggers to the table and LTRIM & RTRIM variable character column values before they are inserted into your table. Following is some sample code that shows this. Many people don't like Triggers because they are "hidden" or non-obvious code blocks and can sometimes cause problems. I concede that some of the gripes about Triggers are legitimate but I believe judicious use of Triggers is sometimes the best choice and in some cases the only real choice (for example with tracking data changes). I use them on a limited basis and make sure they are well documented.
CREATE TABLE MyTable2 (id int IDENTITY(1, 1) PRIMARY KEY,
MyCol nvarchar(20));
GO
CREATE TRIGGER MyTable2Trim ON dbo.MyTable2
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE MyTable2
SET MyTable2.MyCol = LTRIM(RTRIM(MyTable2.MyCol))
WHERE MyTable2.id IN (SELECT inserted.id FROM inserted);
SET NOCOUNT OFF;
END
GO
INSERT dbo.MyTable2(MyCol) VALUES (NULL);
INSERT dbo.MyTable2(MyCol) VALUES ('Test String');
INSERT dbo.MyTable1(MyCol) VALUES (' Test String');
INSERT dbo.MyTable1(MyCol) VALUES ('Test String ');
INSERT dbo.MyTable1(MyCol) VALUES (' Test String ');
GO
-- Should see the expected results with the inserted strings
-- automatically trimmed by the INSERT/UPDATE Trigger.
SELECT id, 'X' + MyCol + 'X' MyColTrimmed
FROM dbo.MyTable2;
INSERT/UPDATE Stored Procedures
DENY all users direct INSERT/UPDATE access to the table with variable character columns. Require CRUD operations to go through stored procedures. Within the INSERT/UPDATE stored procedures LTRIM and RTRIM any variable character column values to ensure that no leading and/or trailing spaces get inserted into the database table. This method is fairly straightforward to implement so there is in no code listing.
Application Developer Training
Communicate with the people that build applications and/or processes that write to the variable character columns. Convince them of the value and need to trim before writing any data. If early in the SDLC let them know that you are adding CHECK CONSTRAINTs that will block writes. Good luck with this one!
After The Fact Methods
Periodic Variance/Audit Report and Cleanup
This method is the
finding and
fixing processes described earlier.
Audit and/or Notification Triggers
Similar to the previous Trigger method but rather than automatically fix leading and trailing spaces, you capture the INSERT/UPDATE event information that leading/trailing spaces were written to the database. Information such as the date/time of the data write, the login, the IP/Name of the box, and possibly the application that made the change could be captured. This information could either be stored in a separate audit table and reviewed later or emails could be sent to the DBA notifying them of leading/trailing spaces showing up in the database. The spaces could then be eliminated as required. Also, the applications, processes, developers, etc. responsible for the spaces could be upgraded/corrected as appropriate. Implementation of this solution is left to the reader or possibly a future post.
Conclusion
If anyone has any additional methods to stop leading and trailing spaces from showing up in variable character columns please share them in the comments below. Any feedback that you may have will be appreciated. Thanks for reading!