Utility scripts for SQL tables

The SQL Server sys.tables & sys.schemas tables are exceptionally handy to find & filter tables in the database. Quickly looping through a list of table commands to execute a delete, truncate, drop, update, disable constraint, enable constraint, or require data script is easy – just use a while loop, a cursor, or print the results to use in a larger script you are writing. Truth is, this little snippet helps you query for the tables, their schema and the boilerplate script syntax. I’ve used scripts just like this for years.

Here it is on GitHub: link.

DevRadio: You’re Coding from the Wrong End! Adventures in Advanced Data Modeling

This is a combined post of part 1, 2, and three especially united for my readers. in this episode I have a lot of fun with Buck Woody who has forgotten more about Data Modeling that most of us. Listen in and take a course with the professor.

Abstract:
Jerry Nixon welcomes Sr. Technology Specialist from the Windows Azure team, Buck Woody to the show as they kick-off their 3 part series on how data applies to developers and how this ties in with Windows Azure’s service offerings. Tune in for part 1 of this 3 part series as they discuss data modeling and how this should be used first in the design process.

SQL Express v LocalDB v SQL Compact Edition

LocalDB is created specifically for developers. It is very easy to install and requires no management, yet it offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express. In effect the developers that target SQL Server no longer have to install and manage a full instance of SQL Server Express on their laptops and other development machines.

Localdb is a minified version of SQL Express that require less number of pre-req to install and offers a fast, zero-configuration installation. – Chirag Shah

SQL’s Table Valued Custom Types

SQL Server allows you to pass complex types to Stored Procedures and User Functions as parameters. In the past, this was overcome by using CSV strings and splitting them. Now, with complex types (think, tables) we can pass whatever we need and return whatever we need back. There’s a limitation that as of 2008 you can only pass READONLY variables, but this is easily overcome by re-inserting into memory tables if the need to edit is there. Here’s the syntax to do it – just paste into SQL Management Studio to see:

Here’s the raw code: http://codepaste.net/tieqkw
 
SET NOCOUNT ON
/*
    by: jerry
    on: 9/2011
    to: demo table types
*/

-- Create the data type
CREATE TYPE MyTableType AS TABLE
(
    Key1 int,
    Key2 int
)
GO

-- Create the proc receiving param
CREATE PROC up_MySlaveProc
    @table MyTableType READONLY
AS
    PRINT 'HELLO FROM SLAVE'
    select * from @table
GO

-- Create the proc sending param
CREATE PROC up_MyMasterProc
    /* no parameters */
AS
    PRINT 'HELLO FROM MASTER'
    declare @table MyTableType
    insert into @table values
         (1, 2)
        ,(2, 2)
        ,(3, 2)
    exec up_MySlaveProc @table = @table
GO

-- Call the sample procs
exec up_MyMasterProc;

tSQL concatenate strings “1, 2, 3” and select results, too

An animal might have more than one color, and you might want to show the colors in a simple string like “Color1, Color2, Color3” using tSQL. This is simple with something like this:

DECLARE @x varchar(500)
SELECT IsNull(@x + ', ', '') + Name
FROM tb_Colors
WHERE AnimalFk = 1
PRINT @x

And although this is useful in many scenarios, if you want to select the user in an output that looks like this:

Animal  Colors
------  -------------------
Animal1 Color1, Color2
Animal2 Color2, Color3
Animal3 Color1, Color3

Before SQL 2005, you were in a pickle since SQL does not allow you to set variables AND select data in the same statement. But, now you can! Using a CTE, you can concatenate and select in a single statement.

Here’s how:

See the code here: http://codepaste.net/m117oh

tSQL Running Total: CTE wins

[ 9/8/10 Updated because of syntax error in sample code ]

How do you calculate a Running Total in SQL Server (using tSQL)?

There are many techniques to creating a running total using SQL. It was not until SQL 2005 that we had the option to use a CTE. A CTE (Common Table Expression) allows you to create a recursive operation. And what is as recursive as a running total function?

In the sample below, I am use three different running total techniques on a table I create for testing (cleverly called x). The first technique is an inner join, the second (and probably most common) is a sub select. The last uses a CTE.

I admit the CTE solution appears more complicated. Especially if you are not familiar with the CTE syntax. But when you see the performance delta you will find it worth your while.

I tested this on multiple machines because I didn’t believe it. But it’s true. And look at statistics, the CTE is so much better it is frightening. Here are the results for a running total against 10,000 rows. You might want to run it yourself to prove the results. Just copy/paste.

Performance Results

Inner Join = 75,390ms
returned 9,999 rows

Sub Select = 47,580ms
returned 9,999 rows

CTE = 793ms (you are reading that correctly!)
returned 9,999 rows

See the code here: http://codepaste.net/bfwqmv 
I should give some credit to this post.

tSQL Regular Expressions

Did you know that you can use Regular Expressions in tSQL? Welp, you certainly can. The key is to use the LINK operator and the [square brackets] to indicate patterns.

Now, don’t get too excited. You can’t use the full suite of syntax (which most people don’t know anyway). You can certainly accomplish a lot.

There’s one thing to remember: this is NOT RegEx. That’s important because RegEx is the fastest string parser in the universe. This only mimics the syntax. Don’t expect its performance. Having said that, I have tested it against LIKE wildcards (%) and it SMOKES them.

But check out what you can do:

Here are the results:

But it doesn’t seem to operate exactly how you would expect:

1. As you can see [A-Z] works correctly, but [a-z] seemed broken.
2. See how [ ] finds a space, but also an empty space (not expected).

(I am using SQL 2008 Developer Edition)

Performance of SQL’s NullIf() Function (versus Case)

Sometimes, courtesy functions surprise me. The core functions are pretty familiar to me. I think anyone do does significant SQL development can say the same thing.

In a previous post (here), I compared the performance between stripping time from DateTime using a variety of approaches. And (here) I compared IsNull() to Coalesce(). Sometimes built-in functions were better, sometimes not.

A colleague (that would be Wes) asked me about the tSQL function NullIf(). Naturally, since I had not heard of it, I assumed me meant IfNull(). But, as it turns out, the library of things I don’t know included (until yesterday) the tSQL function NullIf().

Let’s say you wanted to do this:

The syntax evaluates @x and @y. If they are equal it returns null, otherwise @x. That’s all it does.

Now, let’s see how to accomplish this with NullIf():

The results are exactly the same. The difference is syntax.

And if your maintenance developer does not know NullIf(), then he’ll need to pull up Books Online – and that’s okay.

But, who cares? You might ask. That’s what I thought. Then I wanted to test and see how they compared if I ran them both a few million times. I created this test scenario:

Get the code here: http://www.codepaste.net/o5zpow

Of course, I will be the first to admit that this little test violates every coding convention I have ever promoted! But, hey.

And the results are these:

And, look who’s 40% faster. The built-in function: NullIf()

Our database naming conventions

A messy database is a real nightmare. Modeling a schema is easy; everybody knows that. But creating a model that is both complete and simple – that’s hard. There are libraries full of books written to help you decide your approach. Good luck.

[Simple is the Best Design]

But naming conventions in a database are very important. Poorly named objects overcomplicate queries, burden developers, and make everyone hate you forever.

Here are our conventions for database objects:

.Net development has rightly stopped using Hungarian notation (prefixing objects with type indicators like strName and dteBirth). But for databases, prefixes still serve a vital role. 

Like .Net development, our overarching rule is to avoid abbreviations. Never abbreviate, with three exceptions:

1. 1) if the full word is ridiculously long (like MiscellaneousAttributes could be MiscAttributes), or
2. 2) if the full word is commonly misspelled (like MassachusettsUsers could be MassUsers), or
3. 3) if the abbreviation is more recognizable than the full word (like KentuckyFriedChicken could be KFC).

I understand you may have your preferences. These are ours. With them, I can guess within 99% of exactly what objects are just by seeing their case. Cool.

Note: every object is prefixed except the database itself. We never do anything like dbDatabase because that’s just stupid.

Prefixes (all prefixes are lower case):

Example	Standard	What is it?
tb_Users	tb_ prefix	Table
UserName	Pascal Case	Column
up_User_Insert	up_ prefix	Stored Procedure
v_Users	v_ prefix	View
fn_UpdateUsers	fn_ prefix	User Function
syn_Users	syn_ prefix	SQL Synonym
idx_Users_001	idx_ prefix	Index
@userName	@ + Camel Case	func, proc Parameter
@UserName	@ + Pascal Case	Local variable
LINK2Database	LINK2 prefix	Linked Server

Additional rules:

1. Tables are always plural (tb_Users, never tb_User)
2. Columns are in Pascal Case (UserId, FirstName)
3. Primary Keys are single columns whenever possible
4. Primary Keys are named with the table (tb_Users.UserId)
5. Primary Keys end with Id (UserId, not Key or Pk)
6. Foreign Keys end with Fk (UserFk, never Id, Key or Pk)
7. Stored Procedures never use the sp_ because this is a known SQL Server performance problem
8. Stored Procedures are named up_NOUN_VERB such as up_User_Insert or up_User_Search or up_User_Delete, not up_Delete_User or up_DeleteUser or up_UserDelete.
9. Triggers are named tr_TABLE_ACTION such as tb_Users_UpdateSecurity, not tb_UpdateUserSecurity
10. Index names don’t really matter. But if we want to conform them we use idx_TABLE_TYPE_COLUMNS like idx_Users_Clustered_LastNameFirstName. If the number of columns is too long, then idx_Users_Clustered_001

There are special rules for cross reference tables.

1. Names should include the parent table’s name
2. Names should include the static term “cross”
3. Names should include the child table’s name
4. They always have a primary key called CrossId
5. Many to One = tb_Groups_cross_User (in all reality, this should never happen – use One to Many).
6. One to One = tb_User_cross_Group (singular child)
7. One to Many = tb_User_cross_Groups (plural child)
8. Many to Many = tb_Users_cross_Groups (all plural)

An example schema would be:

dbo.tb_Users
dbo.tb_Users.UserId int primary key
dbo.tb_Users.FirstName varchar(50) not null
dbo.tb_Users.LastName varchar(50) not null

dbo.tb_Groups
dbo.tb_Groups.GroupId int primary key
dbo.tb_Groups.Name varchar(50) not null

dbo.tb_User_cross_Groups
dbo.tb_User_cross_Groups.CrossId int primary key
dbo.tb_User_cross_Groups.UserFk int
dbo.tb_User_cross_Groups.GroupFk int

Optional idea. One thing we have enjoyed is grouping similar objects together with a preceding keyword. Something like tb_Configuration_SecuritySettings and tb_Configuration_General. This helps them to be near each other in the UI – which can help users understand dependencies. The reason this is optional is because of the prevalent use of schemas which accomplish the same thing. So, you can decide how you want to do it. Note that there is no special naming standard for schemas.

I more vigorously defend database naming standards than I do .Net code standards. Both are important. But databases seem to live forever and code comes and goes. We all share the database, while code is often isolated. It’s better to be a jerk about the database, I have found. When you aren’t everyone eventually will suffer – including the customer.

A Better Way to remove Time from a SQL DateTime data type

Every database developer has had to remove the time from a datetime value once. Comparing dates with time included regularly renders false results. The most common approach is something like this:

 

This approach does not actually remove the time from the datetime. It just moves it to midnight. To be fair, this is the best option SQL developers have had before the DATE data type in SQL 2008.

Recently I discovered a second approach to removing time from a datetime value. The technique finds the distance from midnight, the moves the time back to midnight. The syntax looks like this:

What's bad about this technique is that it is hard to remember and type from the top of your head (and get right the first time). What's good about this approach, and surprising to me, is that it is the fastest technique - probably because it does not have to handle a conversion.
I tested this to make sure. Here's the test script:

Get the code here: http://www.codepaste.net/rr8nru

This takes about a minute to execute. It attempts many different types of conversion, including the ones that only SQL 2008 can support (remember that if you run it yourself).

Where I would have expected the classic VARCHAR(101) to perform well, it didn't. Not at all. The DATEADD technique nearly beats the Date data type in SQL 2008. Here are the crazy results:

Conclusion: all the techniques are within milliseconds of each other. It makes sense to code in a away that future developers can understand. If varchar(101) is easiest in your shop, then keep on keeping on. But if you are targeting sheer performance, consider the DATEADD technique.

Creating a simple Partitioned View over Horizontally Partitioned tables in SQL 2008.

This isn’t special to SQL 2008; it works in SQL 2000. Imagine a table with so many rows that query operations are slow.

The easiest solution is Horizontal Partitioning – splitting your table based on the value in one or more columns (like all of this year’s invoices go in Invoices_2009, etc.) And the easiest, and maybe the only, way to create Horizontally Partitioned tables is using a Partitioned View – a single view over the Partitioned tables that unions back to one table again.

You can commit all your SELECT, UPDATE, INSERT, and DELETE operations solely against the Partitioned View – even if your Partitioned tables are on separate files, databases, or even servers.  It’s all so stinking easy. There are a few tricks; but, this sample below should get you moving right along.

Get the code here: http://www.codepaste.net/mqbfvb

SQL Server 2008; fun tSQL syntax to shrink your queries

Here are most of the more basic syntax updates in SQL 2008. Enjoy.

-- old-school variables ;)

declare @x int
declare @y int
declare @z int

set @x = 1
set @y = 2
set @z = 3

-- combined initialization

declare 
     @a int
    ,@b int
    ,@c int

-- compound assigned

declare 
     @d int = 1
    ,@e int = 2
    ,@f int = 3

-- compound operator

set @i += 8
set @i -= 8
set @i /= 8
set @i *= 8
set @i %= 3

-- bitwise operators

set @i &= 1
set @i |= 1
set @i ^= 1

SQL Server 2008 new “grouping” tSQL candy

So many new features in SQL 2008. I presented on the updates at Trifecta a few months ago and never got around to posting some of my samples on my blog. Sorry about that if you were looking. Here’s the bit about grouping syntax. The code should create the sample table and data for you and run right away.

insert into Sales values
     (1999, 'Q1', 'Jerry', 123)
    ,(1999, 'Q2', 'Jerry', 234)
    ,(1999, 'Q3', 'Jerry', 345)
    ,(1999, 'Q4', 'Jerry', 456)

select * from
(
    values (1, 2), (2, 3), (3, 4)
) as DerivedTable(Col1, Col2)

-- ROLLUP = Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.

select
     Year
    ,AVG(Amount) as AverageSales
from 
    Sales
group by
    Year WITH ROLLUP

-- GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.

select
     Year
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    Year WITH ROLLUP

-- GROUPING SET = Specifies multiple groupings of data in one query. 

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter))

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())

-- GROUPING_ID = Is a function that computes the level of grouping.

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING_ID(Year, Quarter) as [x]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())

-- use it in the having

declare @level int = 1

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING_ID(Year, Quarter) as [x]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())
having
    GROUPING_ID(Year, Quarter) = @level
    

-- CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
from 
    Sales
group by
    cube(Year, Quarter, SalesPerson)    
order by
    Year, Quarter, Average