July 7, 2011 at 8:02 am
I have a question about some strange behavior I've observed. I'll simplify things a little for this question, but the schema is actually quite a bit more complicated.
We have a third-party accounting database with a "client" table and a "bills" table that has a one-to-many relationship between clients and bills, and we're building custom applications which use the data in that database. The client table has a client code field, which is a char(10) and is not the primary key. When I run a straight join query for a single client, none of the rows' 'client code' values are ANSI padded; in other words, none of them end up being "03637 ". When I dump the results into a table, i.e. through a query like "SELECT * INTO #TEMP FROM CLIENTS C INNER JOIN BILLS B ON C.ID=B.CID WHERE C.CLIENT_CODE='03637';", I notice that most of the values in the CLIENT_CODE column are ANSI padded, but a handful are not. This is wreaking havoc on some of our custom business applications because we are using .NET code and LINQ queries to group the results, which ends up creating 2 groups for a single client: those with "03637" and those with "03637 ", unless I litter my code with "trims".
I have heard that I can turn ANSI padding off in the connection, but for one, that approach seams rather fragile, and also, I think I read somewhere that that "feature" of SQL Server connections was soon to be deprecated in later versions of SQL server (e.g. you would get an error if you tried to do that).
Any insight would be greatly appreciated.
July 7, 2011 at 11:07 am
rapperson (7/7/2011)
I have a question about some strange behavior I've observed. I'll simplify things a little for this question, but the schema is actually quite a bit more complicated.We have a third-party accounting database with a "client" table and a "bills" table that has a one-to-many relationship between clients and bills, and we're building custom applications which use the data in that database. The client table has a client code field, which is a char(10) and is not the primary key. When I run a straight join query for a single client, none of the rows' 'client code' values are ANSI padded; in other words, none of them end up being "03637 ". When I dump the results into a table, i.e. through a query like "SELECT * INTO #TEMP FROM CLIENTS C INNER JOIN BILLS B ON C.ID=B.CID WHERE C.CLIENT_CODE='03637';", I notice that most of the values in the CLIENT_CODE column are ANSI padded, but a handful are not. This is wreaking havoc on some of our custom business applications because we are using .NET code and LINQ queries to group the results, which ends up creating 2 groups for a single client: those with "03637" and those with "03637 ", unless I litter my code with "trims".
I have heard that I can turn ANSI padding off in the connection, but for one, that approach seams rather fragile, and also, I think I read somewhere that that "feature" of SQL Server connections was soon to be deprecated in later versions of SQL server (e.g. you would get an error if you tried to do that).
Any insight would be greatly appreciated.
I would not mess with turning the ANSI_PADDING server option OFF for any connections.
Did you check to make sure the columns were created with the same ANSI_PADDING setting?
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.client')
AND type IN (N'U') )
DROP TABLE dbo.client ;
GO
-- toggle this to change option during table creation
SET ANSI_PADDING OFF ;
GO
CREATE TABLE dbo.client
(
code_char CHAR(10),
code_varchar VARCHAR(10)
) ;
GO
SELECT OBJECT_NAME(object_id) AS table_name,
name AS column_name,
is_ansi_padded
FROM sys.columns
WHERE OBJECT_NAME(object_id) IN ('client') ;
GO
-- toggle this to change option during query
SET ANSI_PADDING ON ;
GO
DECLARE @code_char CHAR(10),
@code_varchar VARCHAR(10) ;
SELECT @code_char = '12345 ',
@code_varchar = '12345 ' ;
INSERT INTO dbo.client
(code_char, code_varchar)
VALUES (@code_char, @code_varchar) ;
SELECT '"' + code_char + '"' AS code_char,
LEN(code_char) AS len_code_char,
DATALENGTH(code_char) AS datalength_code_char,
'"' + code_varchar + '"' AS [@code_varchar],
LEN(code_varchar) AS [len_@code_varchar],
DATALENGTH(code_varchar) AS [datalength_@code_varchar]
FROM dbo.client
GO
EDIT: update code snippet
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 7, 2011 at 11:14 am
Results from snippet for all combinations:
Table Query code_char len_code_char datalength_code_char @code_varchar len_@code_varchar datalength_@code_varchar
------- ------- ------------ ------------- -------------------- ------------- ----------------- ------------------------
ON ON "12345 " 5 10 "12345 " 5 6
ON OFF "12345 " 5 10 "12345 " 5 6
OFF ON "12345" 5 5 "12345" 5 5
OFF OFF "12345" 5 5 "12345" 5 5
Note: tested on SQL 2005 Standard Edition
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 7, 2011 at 12:00 pm
Thanks for your reply. Please note, though, that I'm only talking about one column. That column comes from the "one" side of the "one to many" join, and since I'm filtering down to just one client, we're only talking about one row on the "one" side. The result, however, is a table that has different padding in that column on different rows (some rows are padded, some are not).
If I turn ansi padding "OFF" for the query, obviously none of the results have padding, but I know (and you pointed out) that that's not a good option.
I just noticed, too, that the problem happens only when I select *. If I select any named list of columns, or if I select * and one or more other columns, it doesn't happen.
I'm guessing at this point that this is just a strange bug in SQL Server 2005.
July 7, 2011 at 12:07 pm
I just went for a generic POC...the concept should still apply. I am super-curious now about your comment regarding select * vs. a named column list. Please provide DDL, DML to create some sample data and a sample query that demos the problem. Can you also provide your build # from @@VERSION?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply