T-SQL usage: prefer Table Type over CTE (Common Table Expression)

I came to the CTE world late (the same time I learned MERGE), but I've been using it here and there.  I love Table Type; the introduction of that changed the way we write our .NET app and BizTalk app, and in some way level the playing field of all these technologies against SSIS.

But through my many recent round of building database, data warehouse, data marts, I find my self migrating away from using table type and views, partly because it just seemed like such a hassle to maintain a separate object for each table in my visual studio project.  Also, I was able to write a recursive CTE recently that otherwise would have taken me 20x the lines of code to do.  So I was fairly happy.

Fast forward to now, I got a big surprise to learn that in MS SQL server, the common table expression is not deterministic, which means the MS SQL server is free to re-evaluate the expression each time the CTE is referenced.  Not only does this mean that it's not true when I thought my CTE would improve performance by running the query once, it also means that my SQL may be working with inconsistent data in the same transaction.

So, back to my original way to writing and maintaining table types.  No more CTE unless I absolutely need the write recursive SQL.

Print | posted on Thursday, April 19, 2012 11:34 AM


No comments posted yet.

Your comment:


Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski