Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DwainCSQL

Dwain Camps is a seasoned project manager specializing in business solutions delivered through software development and implementation. Because performance often takes the forefront when it comes to customer satisfaction in software applications, his technical focus is in high-performance T-SQL.

High Performance T-SQL using Code Patterns

What exactly does this mean?  We’ve heard of “code patterns” in other programming languages, but what does it mean to use T-SQL code patterns to develop high performance T-SQL?  Having now been called out by a couple of folks in the SQLverse that I respect, most recently the editor of… Read more

5 comments, 4,603 reads

Posted in DwainCSQL on 27 May 2015

The Best Tool for Comparing Whether Two T-SQL Queries Produce Identical Results

There is one software tool that should be in every software developer’s tool chest.  That is knowledge of the platform on which you are developing.  With all the hype that surrounds every new software tool that comes out, it is important to recognize the tools you already have at hand,… Read more

12 comments, 6,511 reads

Posted in DwainCSQL on 21 May 2015

Excel in T-SQL Part 2 – The Normal Distribution (NORM.DIST) Density Functions

Today’s blog will be the second in a multi-part series on replicating Excel functions in T-SQL, continuing with Excel’s NORM.DIST built-in function, thus enshrining my geekdom in the SQLverse forever.

Today’s solutions will once again focus on creating T-SQL in-line, Table Valued Functions (iTVFs), introduced in SQL Server 2005, that… Read more

1 comments, 439 reads

Posted in DwainCSQL on 14 May 2015

Excel in T-SQL Part 1 – HARMEAN, GEOMEAN and FREQUENCY

Today’s blog will be the first in a multi-part series on replicating Excel functions in T-SQL, starting with HARMEAN, GEOMEAN and FREQUENCY.

We’ll focus our solutions on creating T-SQL in-line, Table Valued Functions (iTVFs), introduced in SQL Server 2005, that apply the solution technique to a specifically designed… Read more

1 comments, 230 reads

Posted in DwainCSQL on 29 April 2015

Ruminations on Writing Great T-SQL

Today we’re going to try to extend some advice that I once heard from SQL MVP Jeff Moden, which I wrote about in my previous blog entitled “Make it Work, Make it Fast, Make it Pretty.”  That advice was just as pertinent then as it is now,… Read more

4 comments, 8,076 reads

Posted in DwainCSQL on 23 April 2015

Stupid T-SQL Tricks – Part 3: A Zodiacal SQL

Today we’ll learn how you can use T-SQL to calculate your zodiac sign from your birthdate.

“Heeeeeere’s Johnny!” – Ed McMahon introducing Johnny Carson for the Tonight Show

If you’re old enough to remember the Tonight Show hosted by Johnny Carson, how can you not remember the iconic Stupid Pet… Read more

1 comments, 214 reads

Posted in DwainCSQL on 16 April 2015

An Even Faster Method of Calculating the Median on a Partitioned Heap

Back in 2013 I wrote an article kindly accepted for publication on the Simple Talk web site called Calculating the Median Value within a Partitioned Set Using T-SQL.  In that article, I was delighted to propose a previously unpublished solution for calculating the median over a partitioned set that… Read more

2 comments, 167 reads

Posted in DwainCSQL on 8 April 2015

An Easter SQL

Since Easter is nearly upon us, I got to thinking once again about how challenging it is to calculate the day that Easter Sunday falls on.  Since Calendar tables and their step-children Holiday tables are so interesting I thought I might write a sequel to my earlier blog link in… Read more

2 comments, 169 reads

Posted in DwainCSQL on 31 March 2015

Stupid T-SQL Tricks – Part 2: Retrieving Columns Set to the Current DEFAULT CONSTRAINT Value

“Heeeeeere’s Johnny!” – Ed McMahon introducing Johnny Carson for the Tonight Show

If you’re old enough to remember the Tonight Show hosted by Johnny Carson, how can you not remember the iconic Stupid Pet Tricks skits, and the later tributes to those done by David Letterman?

My Stupid T-SQL Tricks… Read more

0 comments, 203 reads

Posted in DwainCSQL on 19 March 2015

Lessons Learned from a Poor-performing VIEW

It is a “common knowledge” among SQL practitioners that VIEWs don’t perform well. Recently during an application’s development I put this to the test. I needed to construct a VIEW for a report the system was producing. It was highly complex. Let’s take a look at the general structure of… Read more

9 comments, 8,181 reads

Posted in DwainCSQL on 12 March 2015

Stupid T-SQL Tricks – Part 1: Logarithms

Not just for math geeks, logarithms and their inverse functions (the exponentials) can have useful and often unexpected capabilities in T-SQL.

    “Heeeeeere’s Johnny!” – Ed McMahon introducing Johnny Carson for the Tonight Show

If you’re old enough to remember the Tonight Show hosted by Johnny Carson, how can you not… Read more

1 comments, 1,381 reads

Posted in DwainCSQL on 1 May 2014

Using the T-SQL MERGE Statement

In SQL Server 2008, Microsoft added a new SQL query type: the MERGE statement. This flexible query provides the ability to perform INSERTs, UPDATEs and even DELETEs all within a single statement. Used in combination with Common Table Expressions (CTEs), this can be a powerful tool to replace multiple… Read more

0 comments, 1,316 reads

Posted in DwainCSQL on 13 April 2014

The One Million Row T-SQL Test Harness

So far in our blogs, we have talked a bit about performance, but today we’re going to show you a way you can confirm without doubt that you’re writing high-performance T-SQL code. This extremely valuable technique is something Developers and Testers alike should be familiar with.

Why one million (1M)… Read more

5 comments, 2,871 reads

Posted in DwainCSQL on 8 April 2014

Manipulating Dates and Times in T-SQL

In SQL 2008, Microsoft introduced some new date and time data types to augment the options available in prior versions. The full list of these data types with detailed explanations can be found in Microsoft Books on Line (BOL), but we’ll list them here with a very brief description.

Read more

0 comments, 3,740 reads

Posted in DwainCSQL on 4 April 2014

Make it Work, Make it Fast, Make it Pretty

When I first heard this, it struck me as being a remarkably concise wisdom applicable to virtually any programming task. The entire quotation is actually:

“Make it work, make it fast, then make it pretty… and it isn’t finished until it is pretty!”

SQL MVP Jeff Moden (RedGate’s 2011… Read more

8 comments, 383 reads

Posted in DwainCSQL on 1 April 2014

Calendar Tables in T-SQL

In an earlier blog, we covered a type of auxiliary table (the Tally Table) that can provide a lot of querying flexibility if you have one in your database or construct one in-line to your query. Today we’re going to talk about another: the Calendar table.

The basic concept… Read more

4 comments, 5,319 reads

Posted in DwainCSQL on 30 March 2014

Tally Tables in T-SQL

The Tally (or Numbers) table is one of the most useful constructs in intermediate-level SQL. Have you ever written a T-SQL WHILE loop? How about a CURSOR? If your answer to either of those questions is yes, you’ve probably been coding some really poor-performing SQL and you may not even… Read more

8 comments, 738 reads

Posted in DwainCSQL on 27 March 2014

Getting an INDEX SEEK to Speed up LIKE “%string%” Searches

In today’s blog I will attempt to challenge the popularly held notion that LIKE “%string%” wildcard searches must be slow (Sargability: Why %string% Is Slow).

A Sample Table Populated with 10 Million Rows of Test Data

In order to do this, we’ll need a large table of test… Read more

2 comments, 534 reads

Posted in DwainCSQL on 26 March 2014

Common Table Expressions in SQL

In SQL Server 2005, Microsoft introduced the Common Table Expression (CTE).  CTEs share similarities with VIEWS and derived tables, but are really not the same as either.  Oracle SQL also supports CTEs and while the syntax is basically the same, some of the properties that we’ll discuss may be slightly… Read more

5 comments, 462 reads

Posted in DwainCSQL on 23 March 2014

The T-SQL ROW_NUMBER() Function

If you learn one new T-SQL (i.e., Microsoft SQL Server) concept today it should be ROW_NUMBER().  Introduced in SQL 2005, this function is one of 4 window ranking functions (the others are RANK(), DENSE_RANK() and NTILE()).  Oracle SQL has a similar capability.

Let’s first create some sample data we can… Read more

1 comments, 492 reads

Posted in DwainCSQL on 22 March 2014