14Jan 2015 by Grant Fritchey 13 Comments

Common Table Expressions Are Not Tables

T-SQL
There's power in naming things. Supposedly some types of magic are even based on knowing the correct names for things. The name for the T-SQL clause Common Table Expression (CTE) is actually pretty accurate. It's an expression that looks like a table and can be used in common across the entire query (at least I think that's what the common part refers to). But note, I didn't say it was a table. It's not. It's an expression. If you look at the T-SQL definition at the link, it refers to a "temporary" result set. Now, to a lot of people, that means table. But it isn't. Let's look at this in more detail. Here's a query that defines a simple CTE and then uses it to query the date in the…
Read More
11Mar 2014 by Grant Fritchey 4 Comments

sp_updatestats Is Not Smart

SQL Server, T-SQL
No, I don't mean the use of sp_updatestats is not smart. It's a fine, quick mechanism for getting statistics updated in your system. But the procedure itself is not smart. I keep seeing stuff like "sp_updatestats knows which statistics need to be updated" and similar statements. Nope. Not true. Wanna know how I know? It's tricky. Ready? I looked at the query. It's there, in full, at the bottom of the article (2014 CTP2 version, just in case yours is slightly different, like, for example, no Hekaton logic). Let's focus on just this bit: if ((@ind_rowmodctr <> 0) or ((@is_ver_current is not null) and (@is_ver_current = 0))) The most interesting part is right at the front, @ind_rowmodctr <> 0. That value is loaded with the cursor and comes from sys.sysindexes and the rowmodctr column…
Read More