Skip to content

ORM vs TSQL (Benefits / Drawbacks)

I think this is a subjective matter, as there’s strong arguments for both. In my experience, I prefer using ORMs for simple applications in a small team. For enterprise apps with many developers, nothing beats the simplicity and performance of pure TSQL (or SQL for that matter). Yes, there could be a lot of boiler plate code, but that part can be automated.

And of course, doing both is still an option.

ORM

Benefits

  • Potentially less coding for accessing database data and basic CRUD operations.
  • Create a class and it writes out your database schema
  • Caching facilities are available for often-accessed data
  • Entity Framework is available to script out Database tabes to c# objects.
  • ORM syntax may generate database-independent code

Drawbacks

  • Does not use SQL, so new syntax has to be used for CRUD/maintenance operations.
  • Could be harder to debug since the SQL is abstracted. Have to run SQL profiler to view what gets actually generated.
  • May not be able to do everything that TSQL can do.
  • Have to know SQL anyways for things that ORM cannot do.
  • Because of its implicit caching nature, when dealing with huge amounts of data, caching may not be ideal (too much unnecessary caching)

TSQL

Benefits

  • Know exactly what you’re getting, since there’s no middle layer writing out your SQL
  • Potentially faster SQL and no need to go through the translation engine.
  • Every developer knows SQL – it’s most widespread
  • SQL is cross-platform (unless using specific Transact SQL commands)
  • No barriers to optimize TSQL
  • Instantly/efficiently troubleshoot with SQL Sever’s native tools (Management Studio/Query Analyzer)
  • SQL Server objects could be used cross-platform. A TSQL UDF, View, Stored Procedure could be easily used by any application, regardless of the platform: e.g. a PHP/Java/Ruby/CF app can use the same SQL Server Stored Procedure that a C# app is using.
  • Compiled stored procedures (written in SQL) are faster than ORM syntax.

Drawbacks

  • May not implicitly cache data like ORM layer, but can be cached with traditional C# code.
  • More TSQL/C# code may have to be potentially written using traditional TSQL and C#.
  • More advanced TSQL code may be heavily dependent on SQL Server, and may make database migration more challenging.

Dan View All

Blog owner.

%d bloggers like this: