This isn't perf-related like most of my earlier posts, but I thought it was useful enough that I should share it.  We recently had a situation where we had to convert a hexadecimal string representation of a binary value to a true binary (e.g. varchar value '0x1234abcdef' --> varbinary 0x1234ABCDEF).  There's a built-in function in SQL (fn_varbintohexstr) to convert from varbinary to a hex-formatted varchar value, but nothing to convert in the opposite direction.  A smart guy on my team (Ahmed Ayad) came up with a clever solution.  There are other solutions floating around out there that do this same conversion, but I don't think I've ever run across one as nice and tidy as this.  It takes advantage of the fact that SQL's parser already knows how to convert a hex string representiation of a binary into a "real" binary. 

IF OBJECT_ID ('usp_hexstrtovarbin', 'P') IS NOT NULL DROP PROC usp_hexstrtovarbin
GO
CREATE PROC usp_hexstrtovarbin @hexstr varchar(3990), @bin varbinary (4000) OUTPUT AS
DECLARE @sql nvarchar(4000)
SET @sql = 'SET @bin=' + @hexstr
EXEC sp_executesql @sql, N'@bin varbinary(4000) OUTPUT', @bin OUTPUT
GO 

DECLARE @hexstr varchar(max), @bin varbinary (4000)
SET @hexstr = '0x1234abcdef'

EXEC usp_hexstrtovarbin @hexstr, @bin OUTPUT

SELECT @bin
GO

Unfortunately, SQL won't allow you to use sp_executesql within a user-defined function, so a disadvantage of this approach is that you can't move this into a scalar function for use in queries like "SELECT dbo.fn_hexstrtovarbin(mycolumn) FROM mytable".