Tag Archive for UNION ALL

SQL Bacon Bits No. 2 – Dropping Temporary SQL Objects

Bacon file8121243652304This is my second SQL Tidbit and I have already decided to change the name to SQL Bacon Bits. Why? Because it’s my blog and I can. These quick posts are supposed to be simple and yummy, just like bacon.

Today’s featured product is ER/Studio Data Architect.

The Need

Whenever a change script is created from an ER/Studio Data Architect model, the original objects are kept with a date time stamp as part of their name. These original copies are then left for the developer to determine if they are still needed.

When I first came across these objects in my database, I was flummoxed. “Why would they leave such a mess?”, I asked myself. But they didn’t leave a mess. They were actually being considerate. Sometimes the SQL Objects have issues because of the changes being brought into the database. Since the original objects are kept as back up copies, you can go look at them before deleting them.

So how do you get rid of these objects once you have confirmed they are no longer needed?

Well, you create a script. This script is based on a version that my co-worker Chris Henry created. I’ve added to it and generalized it.

Let’s break it down

I found that ER/Studio Data Architect uses UTC as the time for the timestamp on the temporary objects. (That drove me nuts until I figured it out!). By using the GetUTCDate() function you’ll be able to programmatically create the date to filter the SQL objects that you want to get rid of.

DECLARE @Date AS varchar(10)

SELECT
    @date = '%' + CONVERT(varchar(2), MONTH(GETUTCDATE())) 
				+ ( CASE 
						WHEN DAY(GETUTCDATE()) < 10 THEN '0'
                        ELSE ''
                        END ) 
				+ CONVERT(varchar(2), DAY(GETUTCDATE())) 
				+ CONVERT(varchar(4), YEAR(GETUTCDATE())) + '%'

 

I then use UNION ALL to join the different types of objects with the proper DROP syntax.

SELECT
    'ALTER TABLE [' + s.name + '].[' + p.name + '] DROP CONSTRAINT  [' + o.name + ']' AS ItemToDrop
	,o.type

FROM
    sys.objects AS o
	JOIN sys.objects AS p ON o.parent_object_id = p.object_id
	JOIN sys.schemas AS s ON p.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type = 'F'

UNION ALL

SELECT
    'Drop Trigger [' + s.name + '].[' + o.name + ']' AS ItemToDrop
	,o.type
FROM
    sys.objects AS o
	JOIN sys.objects AS p ON o.parent_object_id = p.object_id
	JOIN sys.schemas AS s ON p.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type = 'TR'

UNION ALL

SELECT
    'drop table [' + s.name + '].[' + o.name + ']' AS ItemToDrop
	,o.type
FROM
    sys.objects AS o
	JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type  ='U'

UNION ALL

SELECT
    'drop proc [' + s.name + '].[' + o.name + ']' AS ItemToDrop
	,o.type
FROM
    sys.objects AS o
	JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE
    o.name LIKE @date
    AND o.type  ='P'
ORDER BY
	o.type

 

After it executes, I copy the statements out of the results pane and execute them.

Bonus Tip

Since this is a script you’ll use again and again…and again. You’ll want to turn it into a template. Two of the many ways to do that are as follows.

  • SSMS comes with a Template Browser. You can save your script in the Template Browser for future use. Each you need it, simply double click on the script in the Template Browser and copy will be created for you. By adding the following code at the top of the script, you can use Ctrl+M to pick which database you want to use.
USE <DatabaseName, string,>
GO

 

  • If you are a SQL Prompt addict like me, then  you can add your script as a snippet. Instead of adding the code I just showed you, add the following code and a default database name will appear as a default when you use Ctrl+M.
USE <DatabaseName, string,$DBNAME$>
GO

You can download full script from here.

SQL Bacon Bits

SQL Tidbits: No.1– Outputting from ER/Studio Data Architect Directly to SQL Server Management Studio

 

Using Set Theory Instead of ISNULL To Filter Data

I have written dozens upon dozens of reports for my users. I love the challenge of finding the data and wrangling it into a report in a way that doesn’t take a hundred years to run when they decide to return a year’s worth of data. One of the common requests that I get is to provide a parameter that will allow them to choose a single client or all clients. At first blush this is a very simple request and can be accomplished by using the ISNULL function.

ISNULL - ISNULL Code

Unfortunately there are performance implications using the ISNULL function. The ISNULL function is non-sargable. This means that when the function is used as part of a predicate, it can’t utilize the necessary index in an optimal way to filter the data. In this example, the Execution Plan shows that a full index scan was used to return one row. This equated to 80 Logical Reads for my dataset. This is the same number of Logical Reads whether one row was returned or all rows were returned. Eighty Logical Reads may not be that big a deal, but what about on a table that has hundreds of thousands of rows?

ISNULL - ISNULL Execution Plan
Execution Plan

ISNULL - ISNULL Reads 1
Statistics on the Table I/O Tab in SQL Sentry Plan Explorer Pro

 

An Alternate Universe

There is an alternate way of accomplishing this same request and it uses Set Theory to solve the problem. I can accomplish the same request by separating out the ISNULL logic into two different SQL statements and using UNION ALL to join the two result sets into one result set.  Even though I’m using two different SQL statements, only one will return data during the execution of the logic.

ISNULL - Union All Code

The first SELECT statement returns rows when the @ClientID variable has a value other than NULL. The second SELECT statement returns rows when @ClientID is NULL. By using UNION ALL instead of UNION we forgo the task of checking if there are duplicate rows in the second SELECT statement.

The Execution Plan now contains the ability to handle both cases, and the Logical Reads changes based on the value of @ClientID. There are still 80 Logical Reads when @ClientID is NULL because the whole table is being returned, but the Logical Reads are reduced to two when @ClientID is set to a single client number.

 

ISNULL - Union All Execution Plan 1
Execution Plan when @ClientID =7890

ISNULL - Union All Reads 1

 

Statistics when @ClientID = 7890
ISNULL - Union All Execution Plan 2
Execution plan when @ClientID IS NULL

ISNULL - Union All Reads 2
Statistics when @ClientID IS NULL

 

In this example I was able to use UNION ALL to filter on one or all clients, but it isn’t always the best solution. If the table being filtered fits on one page, then a table scan will occur regardless of how the predicate is setup. In that case, the ISNULL statement is easier to maintain so it would be the better solution.

Conclusion

It is always best to take a look at the Execution Plan of a SQL statement to see if there are any Index Scans on filtered data. If there are, you should take a look to see if there is another, set based approach to solve the problem.

%d bloggers like this: