Lots of NULL values can cause trouble

There isn’t a limit to the number of NULLs allowed in data sets but having more NULLs than you think you need may be a sign that it’s time to review. Some applications require a high amount of NULLs. It is considered to be acceptable and is a built-in expectation. For example, I was working on a large ETL process and the staging server allowed NULLs as it fused data from different sources. This worked fine. But there were clean up processes which deal with the NULLs . There were post Import scripts which dealt with NULLs in preparation for... Read more →

Query to find non – sargable sql statements – SQL Antipattern #009

When a predicate is SARGABLE we mean the sql statement is able to exploit the indexes supporting the sql statement. If the sql statemet is non – SARGABLE – we mean the statement is not capable of exploiting the indexes If a statement is non-SARGABLE it can return the correct data set – as intended by the developer – but the response rate can be slower than expected. As the statement cannot exploit the index – it’s forced into a full scan. For the smaller data sets this may not appear to be a problem, but as the data sets... Read more →

DRY or WET – SQL Antipattern #005

I was discussing with some software developers the DRY software design principle. It is an acronym for "Don’t repeat yourself". For some developers , it’ s considered one of the basic principles of software programming. But like many principles, DRY can be misunderstood and applied in a way leading to confusion. DRY or WET may be an SQL Antipattern . Read more on how to spot a SQL Antipattern The DRY principle is stated as “Every piece of knowledge must have a single, unambiguous, authoritative representation within a system.”The principle was formulated by Andy Hunt and Dave Thomas in The... Read more →

Multi-statement table valued function and inefficient Excecution Plan

Working with Multi-statement table valued function can cause the SQL Optimizer to not estimate accurately the amount of rows. The SQL Optimizer cannot \ doesn’t know the amount of rows , building the execution plan on guesswork . This can result in an inefficient execution plan causing the Estimated number of rows = 1 Similar problems are experienced when working with Table Variables or Recursive CTEs. Read up on SQL Table variable and temporary table This basic example illustrates the problem. Below the code , I’ve included the execution plan from Test 1 (using the select statement calling function) and... Read more →

Select all SQL Server Functions

Question: How can I list all all SQL Functions in a SQL Server database? Answer: Use the sys.objects system SQL View and the type column to choose just the Functions. Try not to use sys.sysobjects as it’s a deprecated view , to be decommissioned at some point in the future Script to list all SQL Server functions --Method 1 --FN = SQL scalar function --FS = Assembly (CLR) scalar-function --FT = Assembly (CLR) table-valued function --AF = Aggregate function (CLR) --TF = SQL table-valued-function select [name],[TYPE] from sys.objects where type IN ('FN','FS','FT','AF','TF') order by [name] --Method 2 SELECT ROUTINE_NAME,* FROM... Read more →