T-SQL Tuesday #32 – A Day in the Life

Thank you to Erin Stellato ( b | t ) for providing this month’s topic, where we were asked to track what we did on Wednesday, July 11th.

Database Developer

Overall I had a pretty normal day in the life of a database developer. My day started around 7:00 am with reviewing tasks and bugs that are assigned to me in TFS (Team Foundation Server), followed by replying to emails. Then getting re-acquainted with where I left myself from the previous night in Management Studio. T-SQL Tuesday

Around 9:00 AM a QA resource presented a bug where a stored procedure was not copying data between tables. What I found was an additional step was added to an insert stored procedure that was attempting to short circuit functionality, when it found a like match. This procedure was working so good, that it only inserted one record… ever… It turned out to be an issue where two columns made up the unique key, and the second column was missing from this check. I added the missing column into the stored procedure to put it back in  business.

Shortly after 10:30 AM, and a quick scrum meeting, I started training another DBA resource on a few stored procedures that I had previously written. This DBA had been tasked with adding  functionality to support new business objectives. Around this same time I worked with an application developer to add functionality in another stored procedure to meet middle tier requirements.

After/during a quick lunch at my desk, I began to review a massive business objective which would eventually output to a Reporting Services Report. This was short lived, when a QA resource discussed an issue with performance of an SSIS Package used to move dimensional data into a Data Mart.

Around 1:00 PM this performance bug was prioritized as critical and I spent the remainder of my day working on a fix. The problem I found in the SSIS package was the shear volume of data coupled with the number calls to an insert stored procedure via an OLE DB Command Transformation. After a couple tweaks to a stored procedure, and a couple new tasks in the SSIS package, performance was significantly improved.

It was now 6:00 PM and time to wrap up my work for today, however the day was not over yet, now it was time to get some dinner and talk SQL Saturday. Rick Krueger ( b | t ) and I will be co-presenting at both SQL Saturday #159 Kansas City and SQL Saturday #161 Iowa City. Rick and I worked on: networking strategies, presentation content, and changing our abstract to get accepted in MN.

After I got home, I decided to review an article that I found on SQL Server Central, which is relevant to our presentations. http://www.mssqltips.com/sqlservertip/2722/sql-server-report-server-2008-r2-execution-log-reports/. Great post from Scott Murray ( b | t )

Kansas City and East Iowa Here We Come…

Rick Krueger ( b | t ) and I are going to Kansas City and East Iowa.
Why you say…
Well let me tell you… We were fortunate enough to have our abstracts selected for both SQL Saturday events, we are also submitting for SQL Saturday #149 in our home state of Minnesota.

At SQL Saturday #159 in Kansas City we were notified that two of our abstracts had been selected. Currently we are scheduled to present Freaky Fast Development Tips in Database Development – Room: Room 4 at 1:00 PM. Then we follow that up with SSRS: Reporting on Reports – Business Intelligence – Room: Room 3 at 2:30.

SQL Saturday #159 Schedule

Then one week later at SQL Saturday #161 in East Iowa we are scheduled to present Freaky Fast Development Tips in Room 3 – Room: 2520B (Conference Room) at 3:30 PM.

SQL Saturday #161 Schedule

If you are going to either event, stop by and say hi, check out our sessions, we love to talk and look forward to meeting you. Hope to see you in Kansas City, East Iowa, and possibly Minnesota

Freaky Fast Database Development Tips

Join Rick and Dave on this most excellent adventure as they teach you how to get more work done in less time. Leave your checkbook at home, because this is all about maximum productivity with minimum budget. Leave with Visual Studio and SQL Server Management Studio shortcut keys, extensions, macros, and add-ins. We guarantee you will see something you haven’t seen before, or your money back. Co-Presenter: Rick Krueger (@DataOgre)

SSRS: Reporting on Reports

Business Intelligence gets a lot of press these days, but do you often wonder to yourself as you are writing a report, ‘Who is going to use this’? Learn about the rich information that SSRS is already capturing for you about your reports: who is running them and how often, how long do they take to execute, how much time is spent in data retrieval vs rendering, which parameters were passed in, and more… Co-Presenter: Rick Krueger (@DataOgre)

Inspired by David Klees ( b | t ) blog post Speaking in Kansas City and Iowa City! whom will also be presenting at both events with Andy Galbraith ( b | t ).

View table data with Query Shortcuts Part – 2

In Part 1 of the Query Shortcuts, I demonstrated a quick SELECT * FROM keyboard shortcut. The problem with this approach is if your table is not in the default schema, you have to select the schema and table name. In this post I am going to demonstrate how you can use a custom stored procedure in the Query Shortcuts to return table data without selecting the schema.

First let’s look at the stored procedure.

Stored Procedure:

The idea behind the stored procedure is to find the schema for the table name that is passed in. If the same name is used in multiple schemas, the stored procedure will output both tables and all their data.

CREATE PROCEDURE [sp_GetTableData] (
   @TableName varchar(200))
AS 
   -- Remove any square brackets, we only want the table name
   SET @TableName = REPLACE(REPLACE(@TableName, '[', ''), ']', '')
 
   DECLARE @SelectFrom nvarchar(max);
   -- If temp table exists remove it
   IF OBJECT_ID('tempdb..#TableNames') IS NOT NULL 
      DROP TABLE #TableNames;
 
   -- Get a list of all tables that match in all schemas
   SELECT   TableName = s.NAME + '.' + o.name
   INTO     #TableNames
   FROM     sys.objects o
   JOIN     sys.schemas s ON o.schema_id = s.schema_id
   WHERE    o.name = @TableName
            AND o.type = 'U'
 
   -- Loop through all tables and output table results
   WHILE ( SELECT COUNT (1) FROM #TableNames ) >= 1 
      BEGIN
         SELECT TOP 1 @SelectFrom = N'SELECT * FROM ' + TableName
         FROM     #TableNames T
 
         EXECUTE sp_executesql @SelectFrom;
 
         DELETE TOP (1) FROM #TableNames
      END
 
   -- clean up
   DROP TABLE #TableNames

To use this query shortcut in all databases in the current server instance you can store this stored procedure in the Master database. Typically I would not recommend using the prefix “sp_”, however in this instance we need the procedure to behave as a system stored procedure. Also by using the prefix “sp_” SQL Server will look in the master database for the procedure before looking in the current database.

Query Shortcut Setup:

In order to setup a query short cut you will need to navigate to the SSMS Options Dialog:
(SQL 2012)

  1. Open SSMS navigate to Tools –> Options…
  2. After the dialog opens navigate to Environment –> Keyboard –> Query Shortcuts. This will open the Options dialog window shown below.
  3. Enter “sp_GetTableData ” in any of the open text fields under Stored Procedure.
  4. Click [OK]

NOTE: The new query shortcut will be available in all SSMS windows opened after the query shortcut is saved.

Query Shortcut for Stored Procedure sp_GetTableData

Now open any query, highlight any table name and press Ctrl+F1. Voila you have the contents of the highlighted table. Below we are looking at a snippet of the [dbo].[uspGetEmployeeManagers] procedure in the adventure works database.

Output After Pressing Ctrl+F1

Notice that this time the schema is not selected as a part of the table name. This gives us the ability to double click to highlight the table, and run our shortcut. This feature is handy when learning/debugging a stored procedure, function, view or any other db object.

A downfall with using the stored procedure version is the stored procedure has to be setup on each instance of SQL Server that you connect to in SSMS. This is because the stored procedure lives in the instance where as the Query Shortcut lives in SSMS. Part 1 does not have this same limitation.

View table data with Query Shortcuts

Query shortcuts are one of the great features in SSMS (SQL Server Management Studio) that is commonly overlooked. One of my favorite query shortcuts for TSQL development is the ability to select all records from a table, by simply by highlighting the table name and pressing Ctrl+F1. In this two part blog post series I will demonstrate how to setup a couple variations of my favorite query shortcut.

Simple Query:

In order to setup a query short cut you will need to navigate to the SSMS Options Dialog:
(SQL 2012)

  1. Open SSMS navigate to Tools –> Options…
  2. After the dialog opens navigate to Environment –> Keyboard –> Query Shortcuts. This will open the Options dialog window shown below.
  3. Enter “SELECT * FROM ” in any of the open text fields under Stored Procedure.
  4. Click [OK]

NOTE: The new query shortcut will be available in all SSMS windows opened after the query shortcut is saved.

Query Shortcut for Simple SELECT * FROM

Now open any query highlight any table name and press Ctrl+F1. Voila you have the contents of the highlighted table. Below we are looking at a snippet of the [dbo].[uspGetEmployeeManagers] procedure in the adventure works database.

Output After Pressing Ctrl+F1

Notice that the schema is selected as a part of the table name. In part 2 of the Query Shortcuts I will demonstrate another approach where the schema does not need to be highlighted. Also we will use a stored procedure rather than a query.

Fun with Checksum…

Typically when we think of the TSQL function CHECKSUM, we are thinking about a hash index. A hash index can be used to improve indexing of columns with long character data types. Rather than trying to add an index to a NVARCHAR(2000) column, you can hash the NVARCHAR(2000) column and index on that hashed value, using the CHECKSUM function. The CHECKSUM function can take multiple columns as parameters, and it builds a hash value for the multiple columns. This gives us even greater flexibility in our hash indexing.

I am going to demonstrate another usage for the TSQL function CHECKSUM, and I will also bring the aggregate version called CHECKSUM_AGG along for the ride.

Alternate use:

What if we used the CHECKSUM_AGG to aggregate the CHECKSUM values over multiple columns. This would give us the ability to handle complex search requirements for sets of data within a given table. Consider the following dataset:

GCID	HoleNum	Par	Sand	Water	Yard
1	1	4	1	0	380
1	2	5	1	1	500
1	3	3	0	1	180
1	4	5	0	0	420
2	1	4	1	0	370
2	2	3	1	1	510
2	3	5	0	1	190
2	4	4	0	0	400
3	1	4	1	1	380
3	2	3	1	0	500
3	3	5	0	0	180
3	4	5	0	0	420

Let’s say we want to know which courses have the following conditions:

  • Hole 1 is Par 4 with sand and water
  • Hole 2 is Par 3 with sand and no water
  • Hole 3 is Par 5 with no sand and no water

We can use both the CHECKSUM AND CHECKSUM_AGG to calculate a hash value from a table variable, and compare the hash value to calculated hash value from our data set.

--Setup Golf Course Holes Table
DECLARE @CGHoles TABLE(CGID int, HoleNum tinyint, Par tinyint, Details nvarchar(20), Yards smallint)
 
--Populate with some test data
INSERT INTO @CGHoles (CGID, HoleNum, Par, Details, Yards)
VALUES (1,1,4,'Sand',380),(1,2,5,'Water',500),(1,3,3,'Sand',180),(1,4,5,'Both',420)
,(2,1,4,'Sand',370),(2,2,3,'Both',510),(2,3,5,'Water',190),(2,4,4,'None',400)
,(3,1,4,'Both',380),(3,2,3,'Sand',500),(3,3,5,'None',180),(3,4,5,'Water',420)
 
--Create Search Criteria Table 
DECLARE @Search TABLE(HoleNum tinyint, Par tinyint, Details nvarchar(20))
 
--Populate Search Criteria
INSERT INTO @Search
         (HoleNum, Par, Details)
VALUES   (1,4,'Both'),(2,3,'Sand'),(3,5,'None')
 
-- Using a Common Table Expression, calculate the aggragated checksum and 
-- compare against the tables aggregated checksum
;WITH  CTE AS (
    SELECT   CKSM = CHECKSUM_AGG(CHECKSUM(HoleNum, Par, Details))
    FROM     @Search S
 )
    SELECT   CGID
    FROM     @CGHoles CH
    CROSS JOIN CTE
    WHERE    HoleNum BETWEEN 1 AND 3
    GROUP BY CGID, CKSM
    HAVING   CKSM = CHECKSUM_AGG(CHECKSUM(HoleNum, Par, Details))

Problem with CHECKSUM:

Notice that in the example code I switched to using words to represent sand, water, both, or none instead of using bit values. I did this because of a range of values limitation in the CHECKSUM function. The CHECKSUM function has a limited range of values of 231 possible values. With this limitation we cannot guarantee values will not be re-used, which is what happened while using the bit values. The issue was both GCID 1 and 3 both were a match where only GCID 3 is the only true match. In a future post I will an alternative way to solve this same problem

With the limitation in range of values, it is advised to use the CHECKSUM functions with caution. However, if your applications can allow for a margin of error in comparisons, or use of an additional check to verify the comparisons, then using the CHECKSUM functions can provide fantastic functionality.