Skip to main content

Posts

Parallax effect

Abusing ISNULL with NULLIF

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' ; SELECT CASE WHEN @first = 'B' THEN @second ELSE @first END ; SELECT ISNULL ( 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: DECLARE   @table TABLE ( Asset_Key        BIGINT              , Asset_Datasource VARCHAR ( 64 )              , Asset_Group      VARCHAR ( 64 )              , Asset_tsID       VARCHAR ( 32 )              , Asset_amNumber   VARCHAR ( 64 )              , Asset_mcID       VARCHAR ( 64 )); SELECT   * FROM   @table T1
Recent posts

Caching Temporary Table DDL

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. CREATE PROCEDURE 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   CREATE TABLE #TempTa