Strate SQL

Ever need to group dates and round them to intervals?  I had this exact need for a report on wait stats I was working on for an upcoming post.  I wanted the flexibility to be able to group a list of dates by either minute, hour, date, etc.  And then also round those dates within those intervals.  For example, round a list of dates to every 15 minutes, every 3 hours, or every 2 days.  At first thought this sounded like something that might be a little tricky to accomplish.  But as I’ll show in the examples below, this is quite easy.

Start With The First of the Month

To resolve this issue, I started by using the logic that I typically when I need to take a date and change it to the first day of a month or quarter.  The logic I use for that utilizes the DATEADD and DATEDIFF functions.  I’ll skip going into how those work since Books Online covers that pretty well. 

To find the start of a month or quarter, these functions can be used together to be compare a date and time against the value 0.  Casting 0 as datetime returns the value 1900-01-01 00:00:00.000.  Using the two functions together, use DATEDIFF to determine how many months or quarters have occurred since 1900-01-01.  And then use DATEADD to add those months or quarters to the date 1900-01-01.  Doing this will provide you with the the first day in either the month or the quarter.

The examples below can be used to demonstrate this:

SELECT DATEADD(M, DATEDIFF(M, 0, '20100310'), 0)
GO

SELECT DATEADD(Q, DATEDIFF(Q, 0, '20100310'), 0)
GO

Returning the following results:

Round to the Desired Interval

Now that’s the easy part which leaves the next part, where dates and times need to be rounded not to the 1st of the month put to periods that are determined at execution time.  As I mentioned above, I need to change the logic above such that I can get dates to round to every 1 week or 2 days or 3 hours.

On the surface, this sounded a lot harder than it turned out to be.  Because if you use the % to find the remainder of division, you can easy round any date or time to any logical grouping of dates or times. 

Suppose you need to round a datetime to the nearest 4 hours.  Find the distance in hours between 1900-01-01 and the datetime in question.  Take that number and find the modulo using your rounding interval.  In this case that value is 4.  Then subtract the remainder from the distance.  And use the new distance value in the DATEADD function from 1900-01-01.  Hopefully, that didn’t lose too many people.

Rather than explain it another way, take a look at the following T-SQL statements where the datetime is rounded down to the nearest 2, 3, and 4 hour intervals.

--Round down to nearest 2 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%2), 0)
GO

--Round down to nearest 3 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%3), 0)
GO

--Round down to nearest 4 hours.
SELECT DATEADD(HH, DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')-(DATEDIFF(HH, 0, '2010-03-10 03:00:00.000')%4), 0)
GO

These statements produce the following results:

As you’ll hopefully see, this is a very simple technique.  Imagine replacing the hour, day, or month date parts with a variable and then the interval to round the dates with a variable.  Doing that and the expression can be used in a stored procedure or report to group any set of dates along any set of date or time groupings.

Hope you find this as useful as I did.  And in the next couple days, I hope to have something up that uses this technique to make it even easier to use in your own code by providing fully working examples.

Michael Swart (Database Whisperer) posted a great write up and interview with Brad Schulz that is worth checking out.  Michael’s post links to a post from Brad on the use of EXIST and what is required for the SELECT portion of the statement.  Hope you are still with me.

Definitely worth the read and +1  for my blog subscriber list.

As he demonstrates, it doesn’t matter whether you use SELECT *, SELECT 1 or SELECT 1/0.  If you want to know what this means, please read his post.

From SQL Server’s perspective, it doesn’t consider any of this information in preparing the results.  The only thing that matters and this is from a practices perspective is to select a style for writing EXISTS statements and sticking to it.

Coming up this month is a free event to discover more ways to write T-SQL here is an event for you.  The session will be hosted by yours truly and features T-SQL tricks and tips that I’ve picked up and want to spread the word on.

Here is the registration link.

And the details:

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We’ll address the needs and issues today and show you how to make a real difference in the way that you work.

Objectives

While not everyone may not be a great painter, there is an art form to writing great SQL statements. In this session we’ll go over tips and tricks in writing SQL. We’ll also review some popular bad practices and provide demonstrations to better change those velvet Elvis’s to Rembrandts. As every paint brush can’t produce a masterpiece, the same goes for SQL statements. Writing good SQL code requires the right brush. After this session, you’ll have a few more paintbrushes and probably throw out a few that are no longer useful.

1. Review and discussion of common coding methods can impact performance.
2. Discussion of uncommon coding methods that can be used to improve performance.
3. Review and application of existing T-SQL functions.

Audience

Junior to mid-level SQL Server developers

Prerequisites

Understanding of basic T-SQL coding skills.

Course Price

$0.00

Topics

Speaker: Jason Strate of Digineer, Inc. Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft’s published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

In less than a month SSWUG will be hosting the Ultimate Virtual Conference on October 21-24.  This is an excellent opportunity to get the benefits of heading to a conference full of Rock Star speakers while be able to view the content from the comforts of home or your desk.

Reasons to Attend

Some of the details of the event are:

• More than 75 technical sessions
• New! Live sessions track – all live sessions, with speaker Q&A/interviews at the end of the day
• On-Demand access to sessions for 45 days – miss a session? No problem.
• Chat, Twitter Integration, SKYPE integration for Q&A
• Experience-based learning – find out what you need to know from people that are using the technology every day
• These are NOT sales presentations.
• 6-Month SSWUG.ORG Membership (or membership extension), included!
• ALL-ACCESS Pass: SQL Server and Business Intelligence with related SharePoint and .NET technologies – all included, one low price
• Great vendor hall – learn about the best tools, technologies, publications and partners out there for your shop

If that doesn’t jazz you up, then maybe know that one of the best SQL Server humor threads started at the last SSWUG Virtual Conference.

StrateSQL Sessions

I’ll be presenting four sessions at the SSWUG Ultimate Virtual Conference.  My sessions will be:

Solving Business Pains with SQL Server Integration Services

SQL Server Integration Services (SSIS) offers a wide range of features and functionality that can be used to solve business pains within Extract, Transform, and Load (ETL) processes. These pains can be caused by the performance of current ETL process, the maintainability of ETL processes or represent a new pain that SSIS features can resolve. This presentation will provide a demonstration of real world scenarios where features of SSIS were used to solve business pains through modification of existing SSIS packages or through the creation of new SSIS packages. The session will also discuss current best practices in SSIS package design.

Necessarily Evils, Building Optimized CRUD Procedures

Every developer loves them and a lot of DBAs hate them. But there are many and valid reasons for creating generic SELECT, INSERT, UPDATE, and DELETE procedures. In this session, we’ll go through designing CRUD procedures that utilize new and existing SQL features to create CRUD procedures that are optimized for performance.

Improving Daily Imports with Partitioned Tables

At some point, everyone struggles with loading data to OLTP systems. The business needs the data loaded so that users can work through the data. But the users can’t afford the downtime to get the data into the production tables. In this session we’ll review this problem and how to use partitioning to alleviate this issue.

Are You Following Your Own Best Practices?

Everyone has their own best practices that they try to follow. But often times we aren’t sure how well we are following our own or industry best practices. In this session we’ll go into monitoring and managing best practices throughout your environment. The session will look at how to create a solution using policy based management and other tools to report on your compliance to best practices.

One More Reason

For those budget conscious in the current economy, I can even offer a discount code to help nudge those that haven’t quite given in to joining the conference.  With these nine letters you can received a $10 discount off your registration… SPJSUVC09

The Final Pitch

I lied about the one more reason, I’ve got to give one more pitch.

It’s not often that conferences give us the ability to attend all of the sessions that we want when we want to.  With the stellar lineup of presenters and the deep content that is flexible to each persons schedule I highly recommend attending this conference.

The Minnesota SQL Server user group (PASSMN) meeting is tonight at the Microsoft offices.  Come check us out to network and mingle with your peers and also for the opportunity to learn some new things.

Tonight’s topics are:

• Syntactic Sugar Sweetens the SQL Server Pot
• Discover and Manage SQL Server in the Enterprise

If you have questions or need more information on the meeting, feel free to contact me on twitter at @StrateSQL or though email at jstrate@digineer.com.

Well… until next month of course.  If you’re in Minnesota and reading this… and don’t mind sharing some snacks with other like minded SQL professional… then come down and join us for this month’s SQL Server user group meeting.

We’ll be in a different location for this month – across the skyway from the normal meeting spot.  But that puts us right over a coffee shop… not a bad tradeoff for the coffee drinkers in the the group.

Without ado here are this month’s topics…

Syntactic Sugar Sweetens the SQL Server Pot & Discover and Manage SQL Server in the Enterprise

August 18, 2009
3:00 PM – 5:15 PM
2nd Floor Classroom – 8000 Building
8000 Norman Center Drive
Bloomington, MN

Syntactic Sugar Sweetens the SQL Server Pot

Speaker: Mark Knutson, Hennepin Faculty Associates

Among the many new features of SQL Server 2005 were some SQL additions that both improve performance as well as simplify complex queries. I have used these countless times and found them quite handy. These include Common Table Expressions, the analytical ranking functions, and aggregations. I will discuss how these work and provide some SQL examples.

Mark Knutson is the Senior Internet Architect at Hennepin Faculty Associates. In this capacity, he administers SQL Server and Oracle database servers, SharePoint Services, IIS, and Hyper-V virtualization server. He also leads a small development team which creates web applications using .NET and C#, and is working on the Cisco CCENT/CCNA certifications.

Discover and Manage SQL Server in the Enterprise

Speaker: Lara Rubbelke, Microsoft

Do you know where SQL Server is installed in your enterprise? Are you absolutely sure you know? Really? Are you looking for a free tool to help you learn where SQL Server is installed? Once you know, are you looking for a better way to manage all of the instances in your enterprise? Learn how to leverage SQL Server 2008 features and enhancements complemented with free tools to discover and centrally manage all of the SQL Server in your enterprise. This session will dive deep into the Microsoft Assessment and Planning Toolkit and the Central Management Server, with a special focus on configuring, securing and leveraging the CMS for multi-instance management and configuration.

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Technology Specialist for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS (Professional Association for SQL Server) chapter and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Lara authored the Microsoft whitepaper detailing SQL Server 2005 Enterprise Edition features and was a contributing author of "Expert SQL Server 2005 Development". Prior to joining Microsoft, Lara was a Microsoft Most Valuable Professional (MVP).

Coming up in a couple weeks is the next PASSMN meeting.  We’ll be in a different location for this month – across the skyway from the normal meeting spot.  But that puts us right over a coffee shop… not a bad tradeoff for the coffee drinkers in the the group.

Without ado here are this month’s topics…

Syntactic Sugar Sweetens the SQL Server Pot & Discover and Manage SQL Server in the Enterprise

August 18, 2009
3:00 PM – 5:15 PM
2nd Floor Classroom – 8000 Building
8000 Norman Center Drive
Bloomington, MN

Syntactic Sugar Sweetens the SQL Server Pot

Speaker: Mark Knutson, Hennepin Faculty Associates

Among the many new features of SQL Server 2005 were some SQL additions that both improve performance as well as simplify complex queries. I have used these countless times and found them quite handy. These include Common Table Expressions, the analytical ranking functions, and aggregations. I will discuss how these work and provide some SQL examples.

Mark Knutson is the Senior Internet Architect at Hennepin Faculty Associates. In this capacity, he administers SQL Server and Oracle database servers, SharePoint Services, IIS, and Hyper-V virtualization server. He also leads a small development team which creates web applications using .NET and C#, and is working on the Cisco CCENT/CCNA certifications.

Discover and Manage SQL Server in the Enterprise

Speaker: Lara Rubbelke, Microsoft

Do you know where SQL Server is installed in your enterprise? Are you absolutely sure you know? Really? Are you looking for a free tool to help you learn where SQL Server is installed? Once you know, are you looking for a better way to manage all of the instances in your enterprise? Learn how to leverage SQL Server 2008 features and enhancements complemented with free tools to discover and centrally manage all of the SQL Server in your enterprise. This session will dive deep into the Microsoft Assessment and Planning Toolkit and the Central Management Server, with a special focus on configuring, securing and leveraging the CMS for multi-instance management and configuration.

Lara Rubbelke brings her passion for architecting, implementing and improving SQL Server solutions to the community as a Technology Specialist for Microsoft. Her expertise involves both OLTP and OLAP systems, data management, performance tuning, encryption, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS (Professional Association for SQL Server) chapter and regularly delivers technical presentations at local, regional and national conferences, user groups and webcasts. Lara authored the Microsoft whitepaper detailing SQL Server 2005 Enterprise Edition features and was a contributing author of "Expert SQL Server 2005 Development". Prior to joining Microsoft, Lara was a Microsoft Most Valuable Professional (MVP).

About a year ago I was working on a project that required that data be extracted from one database and inserted into an database.  Not the most earth shattering concept.  This is done quite often and doesn’t necessarily require a discussion of foreign keys.

So why talk about them… well… in this case the source and the destination databases were identical. They had the same table and foreign key structure.  But no reason to be sold on this discussion quite yet… because it would be east to just build the ETL process around this.  It would need to be built with the ordering of the tables controlled with precedence constraints or inserts statements in the proper order.

So here’s the rub… this process needed to move data between hundreds of tables.  When hundreds of tables are in the discussion, I lose interest very quickly in trying to determine by hand how to order the tables.  I did in this case and looked for a method to accomplish this through foreign key meta data.

And of course, there wouldn’t be a post if there wasn’t an answer.  Well, maybe I’d post a question, but I not so nanner nanner.  The solution uses a function with a query to get the necessary data.  There might be better ways to do this, but this way worked quite well and had pretty decent performance.  One variation of this used a single CTE that I let run for a couple hours before I decided it was running sub-optimal.

There are two caveats with this solution:

1. Self-referencing foreign keys will cause a recursion error.  When row A needs to be inserted before row B can’t really be helped through metadata.  Since the environment I was working in didn’t have this issue, the solution didn’t address it either.
2. Circular-referencing foreign keys will also cause a recursion error.  There are situations where table A references table B which references table C which then references table A.  I run into these from time to time but not in the environment that this was designed for.

With the caveats out of the way, here is the solution:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ListFKParents]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[ListFKParents]

GO

/*================================================================================

Function:    dbo.ListFKParents

Author:        Jason Strate

Date:        July 23, 2009

Synopsis:

    This function returns a comma separated list of parents for a table.

================================================================================

Revision History:

Date:        By            Description

----------------------------------------------------------------------------------

================================================================================*/

CREATE FUNCTION [dbo].[ListFKParents] (@ObjectID int) 

RETURNS table

AS 

RETURN (

WITH cteParentChild (Object_ID, parent_Object_ID, referenced_Object_ID, name) 

AS 

( 

SELECT fk.parent_Object_ID, t1.name, fk.referenced_Object_ID, t2.name 

FROM sys.foreign_keys fk 

    INNER JOIN sys.tables t1 ON fk.parent_Object_ID = t1.Object_ID 

    INNER JOIN sys.tables t2 ON fk.referenced_Object_ID = t2.Object_ID 

WHERE t1.Object_ID = @ObjectID

UNION ALL 

SELECT fk.parent_Object_ID, t1.name, fk.referenced_Object_ID, t2.name 

FROM sys.foreign_keys fk 

    INNER JOIN sys.tables t1 ON fk.parent_Object_ID = t1.Object_ID 

    INNER JOIN sys.tables t2 ON fk.referenced_Object_ID = t2.Object_ID 

    INNER JOIN cteParentChild cte ON cte.referenced_Object_ID = fk.parent_Object_ID 

WHERE fk.referenced_Object_ID <> fk.parent_Object_ID 

) 

SELECT (SELECT ',' + CONVERT(varchar(50), x.referenced_Object_ID) as [text()]

FROM cteParentChild x 

WHERE x.referenced_Object_ID <> @ObjectID

ORDER BY x.referenced_Object_ID 

FOR XML PATH ('')) + ',' AS FKParents

)

GO

IF OBJECT_ID('tempdb..#ParentChild') IS NOT NULL

    DROP TABLE #ParentChild 

CREATE TABLE #ParentChild 

    ( 

    object_id int

    ,name sysname

    ,parents varchar(max) 

    ) 

--List all tables and return a list of all of the parent tables to the table

INSERT INTO #ParentChild 

SELECT object_id, name, x.FKParents

FROM sys.tables t

    CROSS APPLY dbo.ListFKParents(object_id) x

IF OBJECT_ID('tempdb..#TableOrder') IS NOT NULL

    DROP TABLE #TableOrder 

CREATE TABLE #TableOrder 

    ( 

    object_id int

    ,name sysname

    ,rank int 

    ) 

DECLARE @Loop int 

SET @Loop = 0 

--Insert all tables with out parents as 0-level rank

INSERT INTO #TableOrder 

SELECT object_id, name, @Loop 

FROM #ParentChild 

WHERE parents IS NULL 

WHILE 1=1 

BEGIN 

    SET @Loop = @Loop + 1 

--Remove any items that are in #TableOrder and in #ParentChild

    DELETE FROM #ParentChild 

    WHERE object_id IN (SELECT object_id FROM #TableOrder) 

    ;WITH cteParentChild (varchar_object_id, object_id, name, parents) 

    AS 

    ( 

--Select items that match to parent #TableOrder in #ParentChild

    SELECT DISTINCT '%,' + CONVERT(varchar(50),t.object_id) + ',%', t.*

    FROM #ParentChild t 

        INNER JOIN ( 

                SELECT '%,' + CONVERT(varchar(50),object_id) + ',%' as object_id 

                FROM #TableOrder 

                ) x ON parents LIKE x.object_id 

    ) 

--Insert items into that have all parents in #TableOrder

    INSERT INTO #TableOrder 

    SELECT pc1.object_id, pc1.name, @Loop 

    FROM cteParentChild pc1 

        LEFT OUTER JOIN cteParentChild pc2 ON pc1.parents LIKE pc2.varchar_object_id 

    WHERE pc2.varchar_object_id IS NULL 

    IF @@RowCount = 0 

        BREAK 

END 

SELECT * 

FROM #TableOrder 

ORDER BY rank, name

Happy hunting.

I really like a great idea.  They have this way of going from a great idea to success.  When you hear a great idea it is always a good idea to share the idea – and let it spread like a virus.

Today, while I was going through and reading posts from Microsoft’s SQL Server bloggers, I found one of these great ideas.  Buck Woody talked about  combining a little volunteerism with resume building.

In a tight economy non-profits have to make tough decisions between their staffing and infrastructure versus the services that are provided.  By volunteering, we can help with those decisions by filling the void of one where the donations are no longer coming in.  And as is mentioned in Buck’s post, at the same time was can work on building our own resume.  I hope I don’t have to explain the benefit of a good resume.

To help inspire others to volunteer and build their resume, I found a few places in Minnesota where people can do some technical volunteering.

• Hands On Twin Cities
• Idealist
• Volunteer Match
• MN Headhunter

I found a few database related opportunities when I looked through these sites.  And encourage anyone looking to build their resume and do some volunteering to check them out.

If anyone knows any other resources or has a story to share along this topic, feel free to comment or e-mail me about it at jstrate@digineer.com.

One of the things I get to do pretty often is to visit with clients and discuss how they’ve implemented their SQL Server environment.  And if the title of this post is any indication of the contents there are a number of issues that I tend to be able to make recommendations on in all of these environments.

Below are five of the most common tempdb items that I come across:

1. Just a single tempdb data file: As a rule of thumb, Tempdb should have as many data files as there are CPUs/cores for the SQL Server instance.  This prevents helps contention that can occur during peak tempdb activity.  Of course, too many files can be a problem as well so if you are thinking of adding more than 8 data files, look at doing some testing to verify the additional files are not causing performance issues of their own.
2. Multiple tempdb transaction logs: The transaction log, of course, serves a much different purpose than the data files.  It’s there to log all of the transactions from tempdb and performs this function sequentially.  No matter how many files you have only one will be active at any time.  Having extra files won’t necessarily change performance or how tempdb behaves but it’s not going to provide any benefit.
3. Size Doesn’t Matter: The data files for tempdb should all be the same size and large enough to handle the workload for all 24 hours of the day.  If one of the files grows for some reason, the other files should be grown as well to keep all of the files the same size.  This helps SQL Server distribute the work more better evenly across the tempdb data files.  Also, there are a number of operations that utilize tempdb – make sure you have the room.  Skipping DBCC CHECKS because tempdb is too small is a recipe for disaster a la mode.
4. Tempdb and user database data files sharing a disk: Tempdb should be separated and isolated from user databases.  This creates contention between the user databases and tempdb.  Contention with temporary objects when executing a query and reading data from a user data typically has one unfortunate result… poor performance.  And no one likes that…
5. Tempdb on the slowest disk: Ugh!  Just because the databases on the server aren’t creating temporary tables doesn’t mean tempdb isn’t being used.  Queries that use operations such as Sort, Hash Match, and Spool will use tempdb and the faster the disk for tempdb the faster the performance of the query.  Tempdb has a lot of activity from all of the databases on the server and if it can’t push it’s IO quickly nearly everything will suffer.

I’ve kept these items fairly short and without a lot of detail because if you are doing any of these you need to read up on tempdb performance in Books Online and read the whitepaper Working with Tempdb.  Tempdb is a crucial resource in the SQL Server environment and it’s configuration should be a first thought rather than an after thought to prevent from having it bite you in the face.

Taking care of these kinds of easy configuration issues will help make certain that your tempdb is Harder, Better, Faster, Stronger.