Category — SQL Server

The other day I was looking up some information about SQL Server Profiler and I came across this book “Mastering SQL Server Profiler” by Brad McGehee.  It is in PDF format, so I downloaded it and opened it up to take a look. 306 pages.  Oh my!  On just Profiler alone!  Then I grabbed another book, “SQL Server Execution Plans” by Grant Fritchey.  This one was 181 pages! And my Reporting Services book tops those two at 866 pages.

Ten years ago, when I started my first job in the database world, working with SQL Server 6.5, I could not have imagined the volume of information dedicated to SQL Server.  Back then, a whole book on the product was only 400-500 pages.  The product has grown so much that no one person could know it all.  Even the people considered the most knowledgeable about SQL Server typically specialize in just a part of it.  That’s probably why I have over 25 different SQL Server blogs in my RSS reader.  No one blog covers it all.

I have to remind myself to focus my self study efforts.  I have to partition out all the things I would like to know about SQL Server into two buckets:  stuff I need in my head and stuff I should just know to how to look up.   It’s less stressful that way.

I have spent the last few weeks thinking about what the new year and the new decade will bring.  I am happy to leave 2009, which was a strange year, at least I felt strange about it.  Not much was accomplished in my civilian career because I spent much of the year deployed to Kosovo with the National Guard.  It was an interesting experience, but it put me behind in building new skills for my civilian career.  So….2010 is the year to jump start my career.  Fortunately, I do have a good job, one that will provide excellent experience for me in the coming year.

So other then doing my day to day work at my job, what I am going to do to jump start and beef up my SQL Server skill set? I will focus on the following 3 items:

1. Increase my blogging.  My goal this year is to post at least once per week, 52 times this next year.  Writing about a subject is a good learning experience, at least for me.
2. Take and pass the two exams for SQL Server 2008 development,  70-433 & 70-451.
3. Attend at least 4 in-person events relating to SQL Server.  This could be a code camp, PASS, local user group, or some classroom training.  It just has to be in person and related to SQL Server in some way.

So, here’s to a great new year!

Now that I am back home from my deployment to Kosovo, I’ve gone back to work.  My first day back was Monday, December 7th.  It was nice to come back, but I was a little nervous that first day thinking about what changed while I was gone.  Now that I’ve been back for 4 days now, I feel a bit silly that I was nervous last Monday…there are many good changes.  Most of our servers are now running SQL Server 2005 and we have a major upgrade project that is being developed on SQL Server 2008.  It’s nice to be moving off SQL 2000.

It feels good to be back.  2010 will be a great year!

Just the other day I received a call from a developer with a database problem. He was receiving timeouts when he called a particular stored procedure from his application. So I took a look at the proc, reviewed the code, and tried to run it. It just ran and ran and ran, no error messages, nothing. Then I tried to recompile the proc, same thing, just hung. So I stopped it, looked at the amount of data it needed to run, not much data involved, so I cut the proc into pieces and ran it a chuck at a time. Finally, I got to the problem part of the code, the part that just hung. In reading the code, I noticed that it was referencing a linked server, hmmm, where does this point to? So, I go into the Client Network Utility and here we are, an old server that no longer exists! Change the server name and port number and yes! The proc complies and runs! Weird, wonder why it just didn’t just return an error message. Oh well, all in a day’s fun!

Notice the lack of table aliases for the select list:

Select  FirstName,
            LastName,
            c.ID,
            Address1,
            Address2,
            State,
            Zip,
            Phone1,
            Phone2,
            Email,
            RegionName,
            EmployeeName
FROM Customers c
JOIN Location l
          ON c.ID = o.ID
JOIN Contact c
          ONc.ID = o.id
WHERE c.ID = @ID

Don’t do this!

If this was a database you just inherited from the last guy (or gal), it might not be obvious which column goes to which table.  So you have to take the time to figure that out before you can make the necessary changes.  And that is just wasted time. 

If you are writing a multi-table query, please use table aliases on every column in the select.  The next person to work on the code will thank you.

Here is another difference between DTS & SSIS that will trip you up if you are not expecting it.  The issue deals with loading flat files with varying numbers of columns.  For example the 1st row has 5 columns, then the 2d row has 3 columns, and then the 3d row has 4 columns.  DTS handled this by always looking for the row delimiter first, whereas SSIS expects to always have the same number of column delimiters in each row.  Here are some great posts that can explain this better than I can:

http://blogs.conchango.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx

http://blogs.conchango.com/jamiethomson/archive/2007/05/15/SSIS_3A00_–Flat-File-Connection-Manager-issues.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

Currently my work life is deep in Reporting Services, doing some enhancements to some existing Accounting reports. They are all in SQL Server 2000 Reporting Services and I would love to upgrade them all to RS 2005. Upgrading the reports would be easy. But I won’t, and here’s why:

1. We have various processes that auto generate these reports in various formats. Converting the reports to RS 2005 would require a re-write of these processes. That would be a lot of time, time better spent on other projects.
2. The isn’t any real benefit to upgrading. They are pretty simple reports.
3. Because of 1 & 2 above, I can’t justify upgrading to my boss. Enough said.

What is wrong with this query?

SELECT c.CustomerName, b.BillingID
FROM BillingInfo bi
JOIN Customer c
ON bi.CustomerID = c.CustomerID
JOIN DailyTransactions dt
ON bi.BillingID = dt.BillingID
WHERE dt.Transdate >= @startdate
AND dt.Transdate < @enddate

Well, the first thing that I see is that we are joining to the DailyTransactions table, but we aren’t returning
any columns in the results set. Why? The reason is because the goal of this query is to return all customers
that have transactions in a given period, so we must check the DailyTransations table for this. But we don’t care
if the customer had 1 transaction or 1000, nor do we need any columns of data from that table, so here is better
(quicker!) way to do this:

SELECT c.CustomerName, b.BillingID
FROM BillingInfo bi
JOIN Customer c
ON bi.CustomerID = c.CustomerID
WHERE EXISTS (SELECT 1 FROM DailyTransactions dt
WHERE bi.BillingID = dt.BillingID
AND Transdate >= @startdate
AND Transdate < @enddate)

A WHERE EXISTS will be much faster than a join because as soon as the first match is found for that BillingID,
SQL Server stops looking for more, unlike a join, which will match up every row.

When I fixed a stored procedure with this exact problem, I cut the run time required by two-thirds. Since this stored
procedure loads a dropdown in Reporting Services, that cut the time required to load the report.

For some 3 years I worked in a shop that primarily did ETL work with DTS. I became quite familiar with all of the little quirks and idiosyncrasies of DTS, and I learned exactly how to get stuff done in DTS. DTS is like an old friend to me! But now I’ve started to work with SSIS – SQL Server Integration Services, the replacement for DTS that’s included with SQL Server 2005. The change to SSIS has been a challenge for me, particularly the separation of control flow from data flow. SSIS is vastly different from DTS, so if you have a need to start working with SSIS, do yourself a favor and budget some time to get up to speed. Believe me, you will need it!

When I first started working with the new PIVOT command in SQL Server 2005, it didn’t make any sense to me.  So I created some very simple examples on my own to help with my understanding.  Here is one.  First, I create the CTE (as I explained in my last post):

WITH Sales as (
SELECT  SalesOrderID, Name, OrderDate
FROM Sales.SalesTerritory st
INNER JOIN Sales.SalesOrderHeader soh
ON st.TerritoryID = soh.TerritoryID
WHERE OrderDate >= ‘7/1/2001′
AND OrderDate < ‘8/1/2001′
)

The CTE contains the sales orders and the territory name for that sale during the month of July 2001.  Next we will create the PIVOT.  The goal of the PIVOT is to count the number of sales by territory.  Not very exciting, but a good simple example:

SELECT Convert(varchar(20), OrderDate, 101) AS ‘Sales Date’,
Northwest,
Central,
Southwest,
Canada,
France,
Germany,
Australia
FROM sales  — the CTE above
PIVOT
(
COUNT (SalesOrderID)
FOR Name in ([Northwest], [Central], [Southwest], [Canada], [France], [Germany], [Australia])) as a
ORDER BY OrderDate

One downside to the PIVOT command is you need to know the column names at design time.  They must be hard coded.  The only way you can get around this is to use dynamic SQL.