CODEWITHSUNDEEP
sql-serverms-access
mwolfe02
mwolfe02

Reputation: 24227

MS Access/SQL Server Len() Brain Teaser

I was looking for bad data recently in a table linked to a SQL Server backend. I ran the following query:

SELECT Len(MyField) AS FieldLength, "#" & MyField & "#" AS FieldContents
FROM MyTable
WHERE Len(MyField) <> 10

I got the following results:

FieldLength    FieldContents
-----------------------------
10             #M1023-324 #
10             #X3253-215 #

So after telling the query specifically not to return fields of length 10, it returned several records with fields of length 10. All of the fields had trailing whitespace.

I did figure out what was happening and it's the sort of thing that could easily trip up an unsuspecting dev/DBA (like myself). So who knows what's going on here?

Upvotes: 3

Views: 703

Answers (3)

mwolfe02
mwolfe02

Reputation: 24227

@Andriy M wins the prize. As he correctly guessed, SQL Server processes the WHERE clause and MS Access processes the SELECT clause.

Normally this goes unnoticed, but the Transact-SQL LEN() trims trailing whitespace before counting characters, while the VBA Len() function does not. Read below for the full explanation.

I ran SQL Server Profiler to see what MS Access was actually sending behind the scenes and here's what I got:

SELECT "dbo"."MyTable"."MyTableID" 
FROM "dbo"."MyTable" 
WHERE NOT(({fn length("MyField" )}= 10 ) )

The above query returned the primary keys of all of the records that match the criteria Len(MyField) <> 10. The {fn length()} is an ODBC Canonical Function.

MS SQL Server implements the {fn length()} canonical function with its own Len() function. As @Sir Crispalot noted in his answer, the Transact-SQL Len function trims trailing whitespace. So in MS SQL Server, Len('M1023-324 ') = 9. Of course, in VBA, Len("M1023-324 ") = 10.

If you look at the above query closely you will see that it does not actually return the fields I requested in my original SELECT statement. Instead it returns just enough information (ie, the primary key fields) for MS Access to request the full records as it needs them.

It does this for performance reasons. If my query had returned 30,000 rows and I had three dozen fields in each row, it would not make sense to bring all of that information over from SQL Server if I was only displaying 10 rows at a time in my datasheet. So what Access does is it gets all of those primary keys, and then requests individual records from SQL Server a few at a time. Furthermore, it actually creates temporary stored procedures in SQL Server that it executes to return those individual rows. I'm veering off-topic now, but if you have a chance to use SQL Server Profiler it's worth doing some experimenting to see what Access actually does behind the scenes.

Anyway, within the SELECT clause, MS Access only requests the contents of the fields themselves from SQL Server. That is, it requests MyField as opposed to '#' & MyField & '#'. It also means that it requests MyField as opposed to Len(MyField).

The end result is that SQL Server does the calculations for the filtering operation (the WHERE clause) and MS Access does the calculations for the display operation (the SELECT clause). And because the Len() function behaves slightly differently in each environment, we end up with what some would consider a very confusing result: 

10 <> 10

But it makes perfect sense to me.

Upvotes: 2

iDevlop
iDevlop

Reputation: 25262

After seeing your hint: isn't that because the field in Sql Server is a char instead of a varchar ?

Upvotes: 0

Sir Crispalot
Sir Crispalot

Reputation: 4854

I reproduced the weird behaviour of the where clause so had a look at the documentation.

Taken from MSDN - "Returns the number of characters of the specified string expression, excluding trailing blanks."

Therefore according to SQL, "M1023-324 " is of length 9.

Still doesn't explain why they both have 10 in the first column. I can't reproduce that bit!

Upvotes: 0

Related Questions