SqlServer: Favor Inline UDFs Over Table Variables

T-SQL's Table Variables surely do have their benefits, but several drawbacks as well. We used to apply them in the past within larger T-SQL batches, in order to save and reuse some smaller query results, and to avoid code duplication:

declare @MyTable table (...)

insert into @MyTable(...)
select ...

select *
from   @MyTable MyTable
       inner join ...
where  ...

But some databases grew in size far beyond original expectations, and those table variables that used to hold 100 rows, now hold 10.000 rows or more, and spill over into TempDB, hence perform badly.

So I replaced them with Inline User Defined Functions (these are table-valued UDFs, which only consist of a single Select statement):

create function dbo.MyUdf(...)
returns table
as
return (
    select ...
)
go

They can then be inlined like this:

select *
from   (dbo.MyUdf(someparam)) MyVirtualTable
       inner join ...
where  ...

This means high performance (due to the UDF query being inlined, query optimizer can do quite some magic) AND avoiding code duplication. Nice!

SqlServer Standard Edition DOES Support Indexed Views

Why on earth do nine out of ten articles on SqlServer Indexed Views state, that they are only available in Enterprise and Developer Edition? This is blatantly wrong, and prevents developers from using a great performance tuning feature.

Standard Edition DOES support Indexed Views (and from what I have heard, but not tried: even so does Express Edition). The only difference is, that one has to provide a "with (noexpand)" query hint in order for the Indexed View to be applied in Standard Edition (I checked execution plans to go sure). Plus Enterprise Edition can choose to use an Indexed View for optimization, even if it does not appear in the original query.

I think this whole confusion might be caused by Microsoft's SqlServer feature matrices, which are pretty unclear on that topic, too.

.NET ValueType Equals()/GetHashCode() Performance

As most people are aware of, .NET Framework's builtin implementations of Equals() and GetHashCode() for ValueTypes are based on Value Equality (as opposed to many ReferenceTypes, where those methods are based on Reference Equality). The problem is, those ValueType.Equals() and GetHashCode() implementations are quite slow for user-defined structs - which is not surprising, as they must apply reflection for obtaining field and property values.

That's why it's better to code your own Equals() and GetHashCode() methods in these cases. The task is pretty much straightforward - just compare those fields and properties, that define equality, in Equals(), and XOR their shifted values (or numeric representations of their values) in GetHashCode():

public override bool Equals(object obj) {
    Nullable<MyType> other = obj as Nullable<MyType>;
    return other != null && 
           this.X == other.Value.X && 
           this.Y == other.Value.Y;
}

public override int GetHashCode() {
    return (this.X * 31) ^ this.Y;
}

I recently was asked to tune some code, and replaced several nested loops with dictionary lookups - only to find out that this was not as fast as I had expected, due to structs being applied as dictionary keys. Provided my own Equals() and GetHashCode() implementations, and voila - ten-fold speedup!