@SQLMason

Subscribe to this blog

Follow by Email

Search This Blog

Posts

Parallax effect

In an effort to not have single switch case statements, some DBD's have leveraged a way to use ISNULL NULLIF. Consider this:

DECLARE @first CHAR(1)='A' , @second CHAR(1)='C'; SELECTCASEWHEN @first ='B'THEN @second ELSE @first END; SELECTISNULL(NULLIF(@first ,'B'), @second);
Here we want to show the variable @first unless it equals 'B'. In that case, we want to see @second. For something simple, this is fine. It may be harder to read for many, but it works.

This is where we went off the reservation and caused a concern. Consider this:

When creating temporary tables within stored procedures with indexes, there is an opportunity for performance gains. SQL Server will cache the DDL of temporary tables as long as there are no DDL changes after the creation. As confusing as that may sound, it will make more sense by looking at the code. The following are two procedures, one to create a non-cached table and the other cached. CREATEPROCEDURE dbo.NoncachedTempTable AS /**************************************************************************************** Object Description: Create a noncached temp table and enter 10000 random numbers then drop Revision History: Date Name Label/PTS Description ----------- --------------- ---------- ---------------------------------------- 05/21/2017 Dan Andrews PTS: 00000 Initial Release ****************************************************************************************/ BEGIN CREATETABLE #TempTable(ID INTIDENTITY , randomVal INT); CREATE…