Mike Gunderloy is editing a series of articles that promise to help us select tools and components. The idea is that he will produce guides in various categories that compare all of the available products, making it easier for us to pick the one that meets our needs.

I'm a big fan of Mike's work - I subscribe to his weblog (it was one of the first I read religiously, before I even used an aggregator), and I've read his Coder to Developer book (excellent - basically a Microsoft-focused followup to the equally excellent Pragmatic Programmer).

So, I'm a bit behind the times, but I just saw it last night, so here is my obligatory review. It was OK.

The dialog sucked, although I thought the acting wasn't too bad (even Natalie Portman's work). The story was pretty good - I mean, there were holes, but given enough willful suspension of disbelief, the story worked. Overall, it was a very good college sophomore attempt at writing a Shakesperian Tragedy, and given that Lucas is a moron (evidenced by Ep. I and II), I think its a stunning success in that regard.

But, my biggest gripe - holy f'ing sh*t - the effects. It was too damn much. I almost threw up trying to figure out what was going on during the opening sequence. At when Grevious brings out 4 lightsabers, I just knew I wasn't going to be able to follow it. But the Anakin/Kenobi fight, and the Palpatine/Yoda fight were well done. And Samuel L. got to go out in style, and not like a punk.

But, as someone else has pointed out (ok, many someone elses) - why did it take 2 decades to build the first Death Star, and then 2-3 years to build the second? Either the second was started partway through, or they had union labor on the first one, and then brought in scabs to do the second. :)

Here's some code to convert a comma seperated list of id's into a temporary table variable, suitable for use in IN statements in your WHERE clauses. It assumes that your list of id's is comma seperated, and includes a trailing comma (ex. 1,2,3, ):

DECLARE @curriculum_ids TABLE(cid int)
DECLARE @curInd  int,
 @nextInd int,
 @value  int

SELECT @curInd = 0,
 @nextInd = CHARINDEX(',', @curriculumIds, @curInd)

WHILE (@nextInd > 0)
BEGIN
 SELECT @value = CONVERT(int, SUBSTRING(@curriculumIds, @curInd, @nextInd-@curInd))

 INSERT INTO @curriculum_ids (cid) VALUES (@value)

 SELECT @curInd = @nextInd + 1

 SELECT @nextInd = CHARINDEX(',', @curriculumIds, @curInd)
END

From my old weblog.

I was reading an interview on the CodeGeneration site with Dave Thomas, and one of the ideas he mentions really struck me.  I've synthesized his idea a little (but I have to give him all of the credit) - it occurred to me that instead of building Enums in our code files, and maintaining them by hand, why not use code generation to extract them from the database at build time?  Most of the enums I've used tie directly to code tables in the database. 

Its pretty trivial to write some code to do it - sure, it would cause some issues with Intellisense when you need to use a “new” item that hasn't yet been built.  Obviously, you'd probably need to have an automated build process to keep everything straight, but you do that already, right?  Overall, it seems like it would make it easier to maintain by avoiding repetition (and help you keep Dave's DRY rule).

Watch this space - I have actually coded this. When I get a chance, I'm going to post an article and the code.

From my old weblog:

OK, it gets mentioned a lot, but since I just spent a few hours going through and fixing stored procedures to not use it, I figured someone else might benefit from a reminder:

Don't use @@IDENTITY.  Use SCOPE_IDENTITY() instead.  Unless you're still stick with a SQL 7.0 box...

It was a very easy habit to get in to.  You spend years using a keyword, and then, all of the sudden, something better comes along.  And you try, but you still ocasionally slip into the bad habit (or work with people who haven't been reformed).

Why is SCOPE_IDENTITY() better?  Because it returns the IDENTITY column from the last INSERT in your query batch (or stored procedure), regardless of any activity that occurs due to triggers.  Of course, you should avoid triggers, too, but that's just my opinion.

For anyone doing any code generation, here is a snippet of SQL I use to get the parameters of a stored procedure.  Works wonders for writing that DAL code, when you want to call stored procedures that do a little more than plain old one-table CRUD.

SELECT    PARAMETER_NAME,

          DATA_TYPE,

          CHARACTER_MAXIMUM_LENGTH,

          NUMERIC_PRECISION,

          NUMERIC_SCALE,

          PARAMETER_MODE,

          IS_RESULT

FROM      INFORMATION_SCHEMA.PARAMETERS

WHERE     SPECIFIC_SCHEMA = 'dbo'

AND       SPECIFIC_NAME = 'ProcedureName'

ORDER BY

     ORDINAL_POSITION

As a sometime database administrator, I can say that I have abused more than my fair share of temporary tables. Temporary tables are a great tool for gathering together a set of data to use for additional processing, especially when it’s easiest to build up information from several sources via multiple SELECT statements. On the other hand, because they are created in the server's temporary database, they can cause contention for disk resources, the bane of every large database I’ve ever worked with.

The worst way to create a temporary table is to use the SELECT .. INTO statement:
SELECT Firstname,
  Lastname
INTO  #tmp
FROM Customer
WHERE State = 'PA'

This is bad, because the act of creating a temporary table locks the tempdb while it is being created. If you explicitly create the table (using CREATE TABLE), the creation process finishes as quickly as possible. If you use the SELECT .. INTO statement, the creation isn't complete until the SELECT populates all of the data in the new table. This keeps the lock on the tempdb during the entire process, preventing other processes from accessing the tempdb. In a typical environment, this will quickly drag performance down to unacceptable levels.

So, it is better to create the table before you try to populate it. Of course, most programmers don't really like having to go and create the table creation script - its tedious busywork. SQL Server 2000 Query Analyzer provides us with an easy way to generate scripts from existing objects by using the Object Browser. If you don't see the Object Brower, you can toggle it on using the toolbar button, or selecting the toggle from the Tools, Object Browser menu.

Navigate to the server, database, object type, and object you are interested in generating the script for. Right-click on the item, and select one of the options, such as Script to Clipboard. Now paste it where ever you are writing your code, and clean it up to get rid of the columns you aren't using, as well as any constraints that don't apply to your temporary table. Add any additional columns by hand or script out another table and paste them in, cleaning up the code to reflect the columns you ultimately want. This saves a ton of typing, and reduces your mistakes. Now there is no reason why you should use SELECT .. INTO to create a temporary table:

CREATE TABLE #tmp (
 Firstname VARCHAR(20),
 Lastname VARCHAR(50)
)

INSERT INTO #tmp
SELECT Firstname,
  Lastname
FROM Customer
WHERE State = 'PA'

So, we've eliminated some of the contention, but our data is still being written to the disk (unless you have your tempdb in memory, which is not a standard or necessarily advisable practice). We could help performance even more if we could make the stored procedure more efficient by reducing recompilations, and help eliminate even more of our resource contention in our tempdb. In SQL Server 2000, there is an option available to achieve this goal - the table datatype:

DECLARE @tmp TABLE(
 Firstname VARCHAR(20),
 Lastname VARCHAR(50)
)

INSERT INTO @tmp
SELECT Firstname,
  Lastname
FROM Customer
WHERE State = 'PA'

It is still best to avoid any sort of temporary tables, but if they are the best tool for the job, make sure you keep in mind these suggestions. Never create a temporary table by using SELECT.. INTO. Try to use table variables instead of temporary tables - they can make you stored procedures more efficient.

Every programmer knows there are two kinds of CEO – technical and non-technical.  They have to be managed very differently.  In either case, if the CEO isn’t managed properly, he or she can get in the way of doing really cool stuff and, without really cool stuff, work takes way too much time out of the day.

[Fractals of Change]

Excellent advice about manipulating your boss – we’ve heard it before, but it bears repeating – you make them think it was their idea.  I think Tom is being a little bit restrictive here, though.  In my experience, most CEO/President/Chairman types fit into another classification – CEO’s who THINK they are technical.  We’ve all seen it – CEO-types in a meeting, trying to lay out the architecture, and mandating a vision that can’t work, or isn’t the best use of the technology.  I think this is a huge contributor to many failures.

Of course, in all fairness, how about all of the programmers/techies/geeks who think they know something about business? No matter what you’re role, it is important to remember that there is SOME TOPIC that you are not an expert in – try to learn from those around you.