Earlier this week I experimented, once again, with using temporary hash tables to speed up intermediate processing. Temp tables can be very useful for creating “optimizer fences” that serve to simplify queries to the point where the optimizer will consistently (more or less) come up with a good plan if not a great one. Temp heaps seem well suited to this purpose, when used simply as low-overhead FIFO structures.
Update (3/28/17): As a simple workaround, I added a string split UDF that does the same thing (only much more slowly). If I need to compile the DB project, I replace the intrinsic function with the UDF in the two places it is called. Otherwise I switch back to the intrinsic. It’s annoying, but what else is a person to do? (I noticed this comment on Connect: “Some fixes have been added to SSDT regarding STRING_SPLIT. If you continue to experience the issue, please try out the next SSDT release.” I will indeed check it out.) [The fix is supposed to be in a “preview” release, which I have not installed. I will test it when the production release comes out.]
I happened to use the new (SQL Server 2016) STRING_SPLIT function in a table-valued function that I wrote the other day. When I went to sync the database to my SSDT database project, however, the project would no longer compile.
119 SQL Code Smells.
Fascinating read: Chris Date and the Relational Model.
Is SQL Server Losing Mindshare.
One of my favorite subjects of late. Just watch out if your variable expression makes calls to a custom assembly. It doesn’t always seem to work! SQL Server Reporting Services Report and Group Variable References.
The trick is to stop and start the RS service at the time of day when it should start up. The recycle time is relative to that, not the time that it last recycled… Prevent SQL Server Reporting Services Slow Startup.
Logging in Maintenance Plans – SQLServerCentral.
Why Local Variables are Bad for Tuning Stored ProceduresBrent Ozar Unlimited®.
TSQL User-Defined Functions: Ten Questions You Were Too Shy To Ask.