Edit: At the suggestion of a much knowledgable commenter who shall remain named Aaron, I changed from using schema_name() function to using sys.tables. When writing code that is expected to have reuse, it can be safer to use the tables rather than functions because the tables will work in the context of the database that is in the from ...

Scalar user-defined functions are bad for performance. I already showed that for T-SQL scalar user-defined functions without and with data access, and for most CLR scalar user-defined functions without data access, and in this blog post I will show that CLR scalar user-defined functions with data access fit into that picture.   First ...

I recently inherited a data warehousing SQL Server solution from my good buddy James Rowland-Jones (you may know him from his more auspicious role on the SQLPass board) and it contained a very cool technique of using composable DML (a feature that arrived in SQL Server 2008) to maintain history of updates to a particular table. I knew about ...

I showed why T-SQL scalar user-defined functions are bad for performance in two previous posts. In this post, I will show that CLR scalar user-defined functions are bad as well (though not always quite as bad as T-SQL scalar user-defined functions). I will admit that I had not really planned to cover CLR in this series. But shortly after ...

Edit: As I reread this, I felt I should clarify.. As usual refers mostly to the ''Scary'' part. I have a lot of stage fright that I have to work through. And it is always exciting to be picked.   I have been selected this year at the PASS Summit 2012 to do two sessions, and they are both going to be interesting. Pre-Con: Relational ...

On my current project we are using SQL Server Data Tools (SSDT) to manage our database schema and one of the tasks we need to do often is insert data into that schema once deployed; the typical method employed to do this is to leverage Post-Deployment scripts and that is exactly what we are doing. Our requirement is a little different though, our ...

In a previous blog post, I demonstrated just how much you can hurt your performance by encapsulating expressions and computations in a user-defined function (UDF). I focused on scalar functions that didn’t include any data access. In this post, I will complete the discussion on scalar UDFs by covering the effect of data access in a scalar UDF. ...

So you thought that encapsulating code in user-defined functions for easy reuse is a good idea? Think again! SQL Server supports three types of user-defined functions. Only one of them qualifies as good. The other two – well, the title says it all, doesn’t it? The bad: scalar functions A scalar user-defined function (UDF) is very much like a ...

I recently overheard a remark by Greg Low in which he said something akin to ''the most interesting parts of a new SQL Server release are the myriad of small things that are in there that make a developer's life easier'' (I'm paraphrasing because I can't remember the actual quote but it was something like that). The new DATEFROMPARTS ...

The optimizer is the part of SQL Server that takes your query and reorders and rearranges your query to find the optimal execution plan. In theory.   In practice, that doesn’t always work out well. Often, the optimizer manages to come up with brilliant ways to execute a complex query very efficiently – but sometimes, it misses an ...