have you any wool?

I realize my humble little blog doesn’t generate much traffic. It sits in a dark corner of the web, largely undiscovered, and with good reason. I rarely update with anything significant and I do no marketing or advertising to try and drive traffic. Why would I? There isn’t much to read here.

And yet, day after day, week after week, I still get a teeny-tiny bit of traffic from people searching for tips on SQL Server. I’m on the first page of Google results for specific searches regarding rows to comma separated columns and the first result if you phrase it a certain way. However insignificant that may be, it still somehow manages to make me smile from time to time. I only wish I had more SQL tips to post. Not only because I enjoy the drizzle of traffic it brings me, but because I genuinely enjoy working on and figuring complex situations in SQL queries. I’ve recently joined StackOverflow for that very purpose, and of course to assist where I can with jQuery and C#. To be honest, I haven’t really had much to post because I haven’t worked in SQL much since earlier this year. I used SQL every day at my job and Management Studio was almost never minimized. Unfortunately, I left in March for various reasons. Thankfully, I will be going back to a full time developer position in the coming week and I expect to being working with SQL again quite a bit. So fear not, more Tips are on the way.

So to those of you who find your way over the my little slice of the web, searching for something SQL related no doubt, I welcome you and I ask that if you find a useful tip to bookmark me and come back once in a while to see if I’ve posted anything new.

Where I work, there normally isn’t a day that goes by that I’m not doing something in SQL Management Studio. Creating and altering Tables, writing Stored Procedures, modifying functions or just cranking out query after query in order to produce spreadsheets because someone in the office wants to see the data in a way we just don’t have a report for yet. Our development to production cycle is measured in hours, not months. And it’s not that we’re just that good or that fast. It’s just the necessity of how we operate. Heck, we sometimes make alterations directly in production and then script them back into development. Bad, bad, bad I know. But as much as I’d like to take a stand against how we work, there is no ground to stand on. We do it this way, because it’s been done this way for the last 8 years and that’s how things get done.

So imagine my confusion when the first project I’d ever really tackled from start to finish, purely in development, needed to be pushed to production. Poor habits meant I didn’t have any saved SQL scripts that I could just run to make all my schema changes and populate my seed data. I literally had no idea what I was going to do. I was beginning to imagine I’d have to go table by table to see what changes I had made and then duplicate said changes into production. Which would obviously be daunting, boring and prone to error.

SQL Compare to the rescue! I had fiddled with some of Red-Gate’s other products after seeing them mentioned at IndyTechFest and IndyCodeCamp. More specially I had checked out ANTS Profiler as a way to try and speed up a particularly sluggish section of the Web Application we used internally. So when I stumbled onto SQL Compare searching Google for a solution to nightmare, I figured Red-Gate was a good place to start. Of course, not only was it a good place to start, it was a good place to finish because after using this tool, I didn’t even bother to look for anything else. It’s THAT good.

I won’t get into the nitty gritty details of what all it’s capabale of. You can find that here: SQL Compare. Instead I’ll just tell you it doesn’t disappoint. Not only did it allow me to push my schema changes into production with near zero effort, it also made me aware of some discrepancies in other areas of the database that I wasn’t working with. I’ve now used it on several occassion to push up my development changes and I couldn’t be happier.

My work is planning on purchasing the full SQL Toolbelt suite in the very near future. Personally, I’m seriously contemplating purchasing the SQL Compare suite for my own projects. Highly recommended.

This is fairly basic and not terribly useful, but I found it interesting anyway. I wanted to get a  quick count of how many tables and stored procedures I’ve made in my new database project, so I threw together a simple query to do that. This isn’t really rocket-science, but querying sys.objects for anything isn’t marked ‘MS Shipped’.

Select Type_Desc, Count(*)
From Sys.Objects
Where IS_MS_SHIPPED = 0
Group By Type_Desc

One of the side effects was that I saw that I had defined 42 Tables, but only 39 primary key constraints. Time to find those 3 tables and give them some keys.

About 5 months into my current job, I was asked to come up with a report that ranked scores in order of best to worst. My intial intention was to just order the data by the score and print out a count in the Rank Column. (Gimme a break, I was fairly new). This idea was shot when I was told the report had to be sortable by any column.

After spending quite some time on Google, I ended up finding a way to rank in SQL 2000 by joining two identical statements together and doing some trickery on the Join Clause to get what I wanted. Not at all and ideal solution.

In SQL 2005, we have the Rank statement. This statement is very easy to use and comes in handy in many, many reporting scenarions. An example using the AdventureWorksLT database:

Select
	P.[Name] as ProductName,
	cast(sum(LineTotal) as Decimal(10,2)) as TotalSales,
	Rank() Over(Order By sum(LineTotal) Desc) as SalesRank
From SalesLT.SalesOrderHeader SOH
Inner Join SalesLT.SalesOrderDetail SOD on SOD.SalesOrderID = SOH.SalesOrderID
Inner Join SalesLT.Product P on P.ProductID = SOD.ProductID
Group By P.[Name]
Order By P.[Name]

The way this works is that the rank statement takes the Over(Order by XX Clause) and assigns a rank to each row based on where it would fall if you were to order the statement by XX. The great thing is, you don’t have to order the statement by column XX. You can order it by any column you wish and the rank still works.

Writing useful and reusable stored procedures is something I struggled with when first learning SQL. Not knowing anything about Execution Plans, I would often nest several “IF” statements in my procedure to execute different statements based on the parameters I wanted to pass in. This was my solution for dealing with a situation where I wanted to omit some parameters when I called the procedure, but didn’t know how to exclude them from my statements.

As I learned more and more about the language, I realized there were a couple different ways you could handle this situation and have settled with the following. Include all the parameters in your Where Clause, but also check to see if they’re null. Doing so allows you to use all the parameters at once, none of them, one of them, or any number in between.

Here is an example stored procedure built on the AdventureWorksLT database, which can be downloaded here. As always, this may or may not be the best practice solution, it’s just the solution I tend to use most. I’m always open to suggestions and debates on the the things I posted in the TOTD.

Create Procedure SalesLT.pCustomerAddressSearch
@City nvarchar(30) = null,
@StateProvince nvarchar(50) = null,
@CountryRegion nvarchar(50) = null,
@PostalCode nvarchar(15) = null

AS

Select
FirstName, LastName, AddressLine1, AddressLine2, City, StateProvince, CountryRegion, PostalCode
From
SalesLT.Customer C
Inner Join
SalesLT.CustomerAddress CA on CA.CustomerID = C.CustomerID
Inner Join
SalesLT.Address A on A.AddressID = CA.AddressID
Where
(City = @City or @City is null) AND
(StateProvince = @StateProvince or @StateProvince is null) AND
(CountryRegion = @CountryRegion or @CountryRegion is null) AND
(PostalCode = @PostalCode or @PostalCode is null)

Which then allows execution of the stored procedure in multiple ways.

Exec SalesLT.pCustomerAddressSearch @City = 'Ottawa', @PostalCode = 'K4B 1T7';

Exec SalesLT.pCustomerAddressSearch @City = 'Ottawa';

Exec SalesLT.pCustomerAddressSearch;

As I understand it this allows SQL Server to compile and save a proper execution plan which is not regenerated every time you run the procedure as can be the case when you’re placing IF statements in your procedures.

When working with SQL server and normalized data structures, there are often times when you need to flatten out the data for reporting or display purposes. There are probably several ways to do this, both in SQL server and in the presentation layer, but this is one of the methods I’ve been using, when it seems fitting to do so.

Admittedly this is probably a hack since I doubt this is how these commands were intended to be used, so if anyone has some alternative solutions, please let me know.

In addition, this example creates a table variable and manually plugs in the data. Ideally you would be running this on a properly normalized table structure using joins and etc. So please keep that in mind.

Declare @Aliases Table
(
	[FirstName] varchar(50) not null,
	[LastName] varchar(50) not null,
	[Alias] varchar(100) not null
)

Insert @Aliases
Select 'Clark','Kent','Superman'
Union All
Select 'Clark','Kent','Kal-El'
Union All
Select 'Clark','Kent','Gangbuster'
Union All
Select 'Clark','Kent','Supernova'
Union All
Select 'Clark','Kent','Nightwing'
Union All
Select 'Peter','Parker','Spiderman'
Union all
Select 'Peter','Parker','WebSligner'

Select Distinct
	[LastName],
	[FirstName],
	(Stuff((Select ', ' + Alias From @Aliases T2 Where T2.FirstName = T1.FirstName and T2.LastName = T2.LastName FOR XML PATH('')),1,2,'')) as Aliases
From @Aliases T1
Order By [LastName], [FirstName]

The FOR XML PATH(”) command after our select basically does the dirty work of getting the data merged into a single string. The stuff command then takes over and replaces the first comma with a blank. If you’ve never used the stuff command, takes 4 parameters. An input string, a starting index, a length, and a replacement string. It then replaces a subgstring of the input string (using the starting index and length as it’s stop and end points), with the replacement string.

Update: Sorry for the poor formatting, I need to figure out a better way to post code samples using this WordPress Template.

This is another one that I revisist every so often with some of the guys at work. If you have data in two different tables that you need to select back in one query, chances are you’ve come across the Union statement. But did you know that the Union statement, by itself, forces a Select Distinct when returning values? Now in some cases you may want to exclude duplicates, in others you may not. Union All, however, will not force a Select Distinct.

For an added performance boost to your queries and stored procedures, use the Union All statement if you know that you want to return back everything from your merged tables, regardless if there are duplicates or not.

In the example below, notice John Johnson is in both tables @Team1 and @Team2, however his name is only returned once using the Union and twice using the Union All.

Declare @Team1 Table(
	[ID] int identity(1,1) primary key,
	[FirstName] varchar(50),
	[LastName] varchar(50)
)

Declare @Team2 Table(
	[ID] int identity(1,1) primary key,
	[FirstName] varchar(50),
	[LastName] varchar(50)
)

Insert Into @Team1 values ('John','Johnson')
Insert Into @Team1 values ('Jim','Johnson')
Insert Into @Team1 values ('Joe','Smith')
Insert Into @Team1 values ('Jacob','Clark')

Insert Into @Team2 values ('Mark','Davis')
Insert Into @Team2 values ('Albert','Einstien')
Insert Into @Team2 values ('Brian','Thompson')
Insert Into @Team2 values ('John','Johnson')

Select FirstName, LastName
From @Team1
Union
Select FirstName, LastName
From @Team2

Select FirstName, LastName
From @Team1
Union All
Select FirstName, LastName
From @Team2

Many times you need to create and use a table temporarily in SQL query or Stored Procedure and then destroy it when you’re done. A lot of times I see people using Temporary Tables to do this, but I’ve always opted for the table variable approach. For a quick run down on some of the pros and cons of Table Variables, check out this link.

To create a table variable, you do not write a Create Table statement. Instead, you use the Declare keyword to the define the variable the same as any other variable in SQL. However, when you define its type, you set it to table and then specify your colum definitions.

Declare @Test Table(
     [ID] int identity(1,1) primary key,
     [Name] varchar(50)
)

Insert Into @Test values ('John')
Insert Into @Test values ('Jim')
Insert Into @Test values ('Joe')
Insert Into @Test values ('Jacob')

Select *
From @Test

Updating a table with values from a Joined table.

I run into this question from the guys at work quite a bit, but it’s actually a pretty easy concept once you get the hang of it. If you have two tables that you want to sync values on, and you have a primary/foreign key you can join on, you’re all set.

Update Table1
Set Table1.ThisNeedsUpdated = Table2.WithThisNewValue
From Table1
Inner Join Table2 on Table1.ID = Table2.ID

If you get stuck, just remember a few things.

• The table in your “Update” caluse should be the same table in your “From” clause.
• You can join additional tables, nested selects or common table expressions as well and update your main table with values from those as well.
• If you’re working with two tables that have similiar field names, it’s best to fully qualify those fields in your statement to make it more readable.

Please comment if you have any questions or can’t get this working. I’d be glad to help.