Simple, but effective code example

The other day I answered this question at SQLServerCentral, "Need a date function to return every Wednesday of the year", and got a couple of kudos, one from Jonathan Kehayias and one from Chris Morris (frequent contributor to the SSC forums), so I thought I'd post the code here and then explain it a bit. So here's the code:

SELECT TOP 366
   IDENTITY(INT, 1, 1) AS n
INTO
   #nums
FROM
   sys.all_objects
   
   SELECT 
       DATEADD(DAY, n, '1/1/2008')
   FROM
       #nums
   WHERE
       DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) = 
              CASE @@DateFirst
                   WHEN 7 THEN 4
                   WHEN 6 THEN 5
                   WHEN 5 THEN 6
                   WHEN 4 THEN 7
                   WHEN 3 THEN 1
                   WHEN 2 THEN 2
                   ELSE 3
              END      
DROP TABLE #nums

The first section just creates a temporary numbers table and you can do that several ways, I just opted for a simple one. If you don't have a numbers table already, you should as there are many good uses for it.

Then I just run a simple select against the temporary numbers table adding each number to the first day of the year. The WHERE clause then just compares the value returned from the DATEPART function (weekday) to the value for Wednesday based on the @@Datefirst value. According to Books on line @@Datefirst:

SET DATEFIRST indicates the specified first day of each week. The U.S. English default is 7, Sunday.

Language settings affect date information.

This can also be affected by using SET DATEFIRST if you want it to be different from the default for the language. This case statement is important if you expect the code to work on any database in any SQL Server install.

Ideally to solve a problem like this you would have a dates table as Jonathan recommends in the thread.

For more information on a numbers table check out these articles:

http://www.sqlservercentral.com/articles/TSQL/62867/
http://www.sqlservercentral.com/articles/Advanced+Querying/2547/

Improving the blog

Well, this blog has been up for about 9 months now and I have over 50 posts. I don't know how many "regular" readers I may have, but if you are reading my blog, I'm looking for comments and criticisms (constructive) about it. I believe that I can do a better job with the blog, and I think the best way is to get others to critique what you are doing. So how's my grammar? What can I do to make the posts easier to read? Are the posts too long or too short? Here's the big one, do I stay on topic?

I've read a few How to blog blogs and tried to pick out the and apply the good stuff.

I really am interested in hearing what you have to say. I know Andy Warren has asked a similar thing in his blog, of course I'm not offering anything in return. You can post a comment on the blog or if you don't want to criticize me publicly you can email me at corbett.jack@gmail.com.

Thanks in advance, and don't worry you won't hurt my feelings.

SQL Quiz #2 - Challenges

Chris Shaw posted a new SQL Quiz and I have been tagged by Kendal Van Dyke, so I need to take up the challenge. The question is: “What are the largest challenges that you have faced in your career and how did you overcome those?”

Starting out

I'd say my first challenge was entering the Information Technology field in the first place. I should explain that I had little interest in computers in high school and college. I was more interested in sports than technology and my original goal was to become a basketball coach so I went to college to study Physical Education. Well, along the way I learned full-time coaching gigs are hard to find and I don't really like working with children so teaching was out. A good friend of mine is a developer (a true geek genius) and he started mentoring me. When he was the Software Development manager at the local pulp and paper mill he had two openings that he was struggling to fill and he managed to convince me to apply, as he was convinced I could do the job. Well, I finally gave in and was hired (his boss had to approve). Well, I found out I loved doing the work, but it has always been a struggle as I don't necessarily understand all theory and it was then as I had to learn VB, SQL Server, accounting, and pulp and paper making all at once! I managed to do it and have been doing it for almost ten years.

The big project

The second challenge would be the first big project that I was the IT lead for. We were purchasing a soup to nuts Manufacturing Execution system that required me to write the data transfer process, interface with the vendor's development team for custom modifications, prove bugs were bugs, and basically question everything. The biggest issue that I had to solve was a deadlock issue in the database from the RF units on our forklifts in the warehouse. Basically the way it was coded, once you had 3 or more units working at once the units totally locked up because of deadlocks in the database. I don't recall the details in the code, I do know it had much looping (RBAR). I used Profiler (this was SQL 7) to find the offending SQL code. Then I had to go into the vendor's proprietary scripting language and change the code that was causing the problem. Miraculously, the units stopped locking up. Much to the vendor's consternation I proved the bug, but to my knowledge they never fixed the code, and shall remain nameless.

I think almost everyone I follow I know who blogs has been tagged for this, but I have not seen R. Barry Young's name go past so I'll tag him.

Kerberos Authentication and SQL Server

Great article by K. Brian Kelley on SQLServerCentral today on Configuring Kerberos Authentication. If, like me, this was something you do not really understand this is a very good explanation that is easier to understand than most other resources I have found.

As always, it is a good idea to check out the discussion as well, as questions you may have are probably in there and answered.

Measuring Developer Performance

I just read this post on Joel Spolsky's blog (Joel on Software) where he says, about using timesheets to measure performance:

We have a theory, here, that this is a bad idea. Using timesheets as a performance metric can lead to only one thing: bad data in timesheets.

You really need to read the whole post to understand where he is coming from, but I agree with his point. As soon as someone knows how you are measuring their performance, many people will find a way to "game" the system. What Joel doesn't do, likely because this was not the real point of his post, is mention how he measures developer performance. So the question is:

How do you measure performance for developers?

I wish I had a good answer for this. I have always thought performance should be measured by what you accomplish, not how long it takes to accomplish it. If the task is to create three reports for the accounting department by the end of the week and one developer gets it done by Tuesday and surfs the net for the rest of the week and another developer gets it done on Friday, which one performed better? Both met the expectation, working under the assumption that the reports work the same, so how do you evaluate performance? Granted the one who finished sooner could be assigned more work so is more productive, but both met the requirement.

I had a short contract position writing SSRS reports for a company. The first day I showed up, they had a certain number of reports spec'd out for to work on and expected it to take me several days to get them done. Well, by the middle of the second day I had completed them all and the Business Analyst I was working with didn't have anything else ready for me and I was expected to be there from 8 to 4:30. I couldn't leave, but I wasn't being productive, yet my employer was happy with my perfomancem and my contract, which was originally for four weeks, was extended and I ended up there for six months.

The point of the story is that I had days where I was paid for 8 hours and worked 3-4 because I was waiting on someone else. So was my performance good, they thought so. Also my timesheet showed 8 hours worked, which really isn't accurate.

This also relates to the editorial at SQLServerCentral. Be sure to read the discussion as well.

RedGate SQL Refactor

I was fortunate enough to receive a copy of RedGate's SQL Toolbelt, which includes SQL Prompt, SQL Compare, SQL Data Compare, SQL Refactor, SQL Packager, SQL Data Generator, SQL Doc, SQL Backup, SQL Response, SQL Multi Script, and the SQL Comparison SDK. I have used SQL Prompt (intellisense for SQL), SQL Compare, and SQL Data Compare in the past and have always liked RedGate's products.

Today I had the opportunity to use SQL Refactor to rework several tables in a a database. My co-worker had asked that a table, in a database in development, could be renamed. As you probably know, there is no thing as a simple table rename, especially when it is one of the core tables in the design. In the past I would have to go through every relationship, trigger, view and stored procedure and make the changes, but today, with SQL Refactor, I was able to do it much faster using the Smart Rename function. SQL Refactor plugs into SSMS, so if you right-click on an object you get a Smart Rename option. Once selected you are prompted to provide the new name, and Refactor finds references to the table in foreign keys, triggers, views, and stored procedures, and generates a script for all necessary changes. It does the same for column renames as well. Even with this tool I spent my entire afternoon making changes. Mainly because this one table had references to several other tables, thus every place there was table_id, I needed to make the change to that table as well as Refactor does not rename columns in other tables. Still I would have been looking at probably a full day plus of work without the tool.

The only change I would, and will, suggest to RedGate is to allow you to rename multiple columns in a table at one time so one script is generated instead of having to rename each column separately for the refactoring to be done.

The next tool I will be trying is SQL Response. I'll probably let you know how it goes.

Script published on SQLServerCentral

I'm a day late for this, but the script, Get SQL Server 2005: Details about running or defined traces, I submitted to SQLServerCentral was published in the newsletter yesterday (12/1/2008). I put his script together after answering a couple of questions on the forums about Profiler and Server-side tracing. I've decided that any common answers I provide I am going to try to create either a script or article to try to "head off" the next post. If that doesn't work then at least I'll have a link to give them.