SQL Server strange string lengths

I just thought I would document some very unintuitive behavior of SQL Server to watch out for. It's well known that SQL Server is not well-suited to string processing. This is one of the reasons why.

The LEN built-in function returns the length of a given string, however, it has quirks about the way it handles whitespace:

Command	Result
SELECT LEN('abc')	3
SELECT LEN('')	0
SELECT LEN(NULL)	NULL
SELECT LEN(' ') --one space	0
SELECT LEN('       ') --multiple spaces	0
SELECT LEN(' ') --one tab	1
SELECT LEN(' ') --one newline	2
SELECT LEN('ABC ')	3
SELECT LEN(' ABC')	4

I tested these behaviors in SQL Server 2005.

I think the reason for this is because old string types like char are fixed-width, using spaces to fill in unused portions of the string. For example, if I wanted to store 'Hello world' into a char(20), SQL Server would actually store 'Hello world         '. Then, when you want the length of the string, you just want the length of the part before all the trailing spaces. However, I'm not sure why this is still the dominant behavior, since nvarchar and varchar are much more commonly used these days than char, and they don't use all the trailing spaces.

As the the newline, it uses two characters: carriage return (13) and newline (10).

SQL dyanmic queries run in separate connections

In SQL Server 2005, it appears that all dynamic queries run in separate connections from that of the query which generated them. This makes a big difference if you are using connection-specific temp tables (e.g., #MyTempTable).

Here's a few examples in SQL Management Studio to demonstrate what's going on.

Query:

--in a dynamic query, create a local temp table and select from it
PRINT 'Example A:'
EXEC('CREATE TABLE #ConnectionTempTable ( [Foo] INT, [Bar] BIT, [Junk] NVARCHAR(100) ) SELECT * FROM #ConnectionTempTable')
PRINT '------'

--in a dynamic query, create a global temp table; then in the static query, drop it
PRINT 'Example B:'
EXEC('CREATE TABLE ##GlobalTempTable ( [Foo] INT, [Bar] BIT, [Junk] NVARCHAR(100) )')
DROP TABLE ##GlobalTempTable
PRINT '------'

--in a dynamic query, drop the local temp table from Example A (FAILS!)
PRINT 'Example C:'
EXEC('DROP TABLE #ConnectionTempTable')
PRINT '------'

--in a dynamic query, recreate the temp table from Example A (FAILS!)
PRINT 'Example D:'
EXEC('CREATE TABLE #ConnectionTempTable ( [Foo] INT, [Bar] BIT, [Junk] NVARCHAR(100) )')
SELECT * FROM #ConnectionTempTable

Results:

Example A:

(0 row(s) affected)
------
Example B:
------
Example C:
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#ConnectionTempTable', because it does not exist or you do not have permission.
------
Example D:
Msg 208, Level 16, State 0, Line 20
Invalid object name '#ConnectionTempTable'.