DaveWentzel.com            All Things Data

Object Relational Mappers Series - Using Proprietary Features of Your RDBMS Incorrectly

This is my next installment on my series on ORMs.  One of the major selling points of an ORM is to avoid RDBMS vendor lock-in.  In other words, the SQL the ORM is generating should be vendor-neutral and only use ANSI SQL.  I think this is great in theory, but seriously, how many times are you switching RDBMS vendor midway through your development cycle?  In any case, I've yet to see an ORM vendor who is NOT exploiting (sometimes accidentally) vendor-specific syntax.  Here are some examples:  

Incorrect ANSI Settings

Using SQL Server Profiler you will often see calls like SET QUOTED_IDENTIFIERS OFF.  This is NOT an ANSI standard, so why is the vendor doing this.  For instance, IdeaBlade will set this incorrectly if you have AllowColumnSelection set.  In fact, the SQL generated is not even consistent in its quoted identifications:

SELECT Column1, Column2, Column3 FROM "table"

it should really be

SELECT "Column1","Columns2","Column3" FROM "table

It's wrong for two different reasons.  

Incorrect ANSI settings affect lots of things in SQL Server.  More and more new features of SQL Server are REQUIRING those settings to be at their ANSI default.  For instance, filtered indexes require this.

Problems With Temp Tables 

Most ORM tools advise you not to use stored procedures.  I've covered that in an earlier post. Many ORMs will also choke if you use temp tables in your stored procedures.  Why?  The ORMs are reading metadata about your procedures to try to determine what the output result set will look like.  They can then auto-generate the business object tied to that stored procedure.  But if you use temp tables the metadata parser can, and often does, choke.  You may see errors such as "An INSERT EXEC statement cannot be nested."  The canned response by the ORM vendor is usually to convert your temp tables to table variables.  That does overcome the metadata problem, but it may also introduce a number of new problems for you, especially performance.  Temp table are optimized and have statistics associated with them to generate good query plans.  Table variables do not, they always assume a single row.  They also cannot be indexed.  

Using NVARCHAR Incorrectly

I've seen ORMs that operate on a "lowest common denominator" paradigm when declaring and sending parameters to SQL Server.  They will, under the covers, declare what should be a VARCHAR as a NVARCHAR.  Suppose for instance you have a stored procedure called Foo that takes a @BAR VARCHAR(20) parameter.  Although you have declared the parameter as VARCHAR, the ORM is declaring and passing an NVARCHAR.  The result is that an index, if available on the underlying column, cannot be used because we need to now implicitly cast the data type.  The ORM should not be doing this.