Multi-column keys add overhead, and make queries more cumbersome. Much of my early work with serious (i.e. not dBase or Access) databases was using PeopleSoft and Oracle. Every parent-child table relationship was made up of compound keys (OrderItem would have for it's key something like OrderId and SequenceId).
The problem then is that anything referencing OrderItem has a JOIN clause that looks like this:
SELECT ...
FROM dbo.xyz x
INNER JOIN dbo.OrderItem oi
ON x.OrderId = oi.OrderId
AND x.OrderItemSequenceId = oi.SequenceId
To find compound keys, you can use this SQL:
SELECT kcu.TABLE_CATALOG,
kcu.TABLE_SCHEMA,
kcu.TABLE_NAME,
COUNT(kcu.COLUMN_NAME) AS ColumnCount
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG
AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
AND tc.TABLE_NAME = kcu.TABLE_NAME
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
GROUP BY
kcu.TABLE_CATALOG,
kcu.TABLE_SCHEMA,
kcu.TABLE_NAME
HAVING COUNT(kcu.COLUMN_NAME) > 1
There are very few exceptions to this rule - in fact, none that I can think of. EVERY table in a relational database should have a primary key. To verify that every table does, you can use this SQL to discover any tables that don't:
SELECT t.TABLE_CATALOG,
t.TABLE_SCHEMA,
t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_CATALOG + '.' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME NOT IN (
SELECT tc.TABLE_CATALOG + '.' + tc.TABLE_SCHEMA + '.' + tc.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY')
I end up doing a lot of work with SQL Server (if you are a "long time fan", you may even recall I started this blog incarnation with the title "I'm not a DBA, but I play one on TV"). I have seen a lot of very badly written SQL in my day. This article goes a ways towards fixing that (I don't agree with some of it, but that's not important - any coding standard will not get 100% agreement - the important thing is 100% compliance).
I'm not going to go into the article - you should read it yourself and use some or all of the recommendations, as you see fit. As I said, the important part is a coding standard, so that anyone who knows your standard can easily look at your code and figure out what it's supposed to do.
I have toyed off and on with my own SQL coding standards and design best practices. I have some notes in my files, which I'm going to use as a basis for this series of entries. Keep in mind that these suggestions are based on my knowledge of SQL Server 2000 - they may not apply to SQL 2005 (and as I work more with 2005, I'll post updates). I like to think of myself as being a knowledgeable SQL Server programmer (I am an MCDBA, for whatever that's worth :) ), but I'm no Kim Tripp, so please feel free to post your thoughts in the comments - I'd love to have the feedback on my thought process.
Enjoy!
I don't have any real attachment to the people I am currently working with.
You can argue that it's because I'm a consultant, just a hired gun. I shouldn't be attached to any people in this company. I've been here a year, longer than a fair number of them. But I'm just that guy that knows a fair amount about .Net, and makes a good devil's advocate from time to time.
But the thing is, none of them have any attachment to each other, either.
It has bugged me for a long time. Back around the mid-point of my stay here, they even tried to hire me a couple times. I couldn't bring myself to seriously consider it. The biggest reason was the lack of a "team". I've often wondered how it could be like this. I'm not sure how it got here, but one of the things I've realized is this - I've never gone out to lunch with anybody else who works here. I've never seen any of them go out for lunch together, either. For the most part, I could not tell you much about any of my current co-workers. I just don't really know anything about them.
It's not me. I'm a little shy, but everywhere else, given enough time, I have always managed to come out of my shell. But recently I realized, it is probably because nobody bothers to find anything out about anybody else. Nobody makes any effort. Now, I'll be the first to admit - it is a very hard thing to do, especially when you are the new guy on the block. And, really, instead of griping about it, I should be trying to fix it. But I can't bring myself to do so - I've mentioned it to the powers that be, and they assured me that when they move (they have outgrown this space, and are moving near the end of the summer), things will be different. I'm just a consultant, and repeatedly reminded that I am an outsider. That is fine - I had may chance to become an "insider".
I should have realized the problem back when I started. On my first day, no one made any mention of lunch, or even where there were good places to go for lunch. No one tries to gather up the troops for a beer after work on a Friday. A vendor even sent in lunch one day - we all gathered lunch and went back to our desks.
It really is the simplest form of team-building, and it really does go further than any other for the same level of cost and time. You need to have a place to go, to get to know about the lives of your co-workers. To find out what makes them tick. To learn what you have in common. Otherwise, there won't be anyone to watch your back when crunch time comes.
I've avoided doing the link fest thing, mostly because Mike and Jason do an AWESOME job of it, so I don't have to, but the last few days on the Windows Off-Topic list have produced a lot of interesting things, so I'm posting them here, mostly so I can find them to follow up when I have time (I think there is a free 5 minutes sometime in late April 2020... :))
Open Workflow Engine - It's Java and open-source, and it has a C# interface library. [Via Oswald Campesato]
PostSharp - A free and open-source post-compiler for the .NET Framework. [Via Slavo Furman]
Ping Pong Development - I have to admit doing this on occasion. :) [Via Mark Brooks]
DasKeyboard - matches perfectly with DasBlog! This is some seriously hardcore hardware. [Via Shawn Van Ness]
These aren't from the OT list:
Stop Fakes - a site by the US Patent office pertaining to IP theft, especially in terms of small business. Don't know how useful it all is, but I'll probably check it out some more anyway.
Structure and Interpretation of Computer Programs - an online version of a book, mostly about Scheme, which is another of those languages I keep meaning to look into more (after Ruby, Python, and LISP). [Via Xevious on the PragProg list]
I have used PRINT statements in my SQL while I'm trying to develop something a little complicated, but I always take them out when I'm done. Actually, I've turned over a new leaf in the last year or so and have been trying to keep business logic out of the database altogether, but that's a different post.
I was integration testing some code I had just finished up in our web project, and I couldn't even get to the spot, as some other code was blowing up first (I guess nobody had bothered to test it, but again, another day's post). I looked at the SQL error message (well, the first one - there were a bunch, but the rest didn't seem to make much sense). Then I looked at the sproc and the C# code, and I couldn't make any sense of the spaghetti (it was bad code - another contractor (those damn consultants! :) )).
So, I decided to look into the other SQLErrors a bit more. Turns out, they were actually the results of PRINT statements - you can tell because their Class property was 0. The SQLError.Class property holds the Error Severity Code - 0 means there was no error, which is why I ignored them at first. So, after looking at them, I discovered the Message on the last one was completely blank, because the value being appended in the PRINT statement was NULL, I discovered. So, the problem was a data setup issue - a field that shouldn't have had nulls did. I fixed the field and updated the table definition to make that field no longer nullable, and the problem has gone away.
So, it turns out that if for some crazy reason you want to put business logic in your stored procedures, you can actually make use of PRINT statements for debugging.
Seems like maybe they may be more relevant to the real world - at least by the structure of the actual titles. We'll see if the content is more real-world oriented! Details here.
Wexford, PA is #28 on Money's Best Places to Live list!
Of course, how useful is a list that has a place in Jersey as the best place? :)
So, my dear old Dell Axim is obsolete (ok, maybe it was obsolete when I bought it :)). It's an X5 I purchased back in February of 2003, and it came with PocketPC 2002 as the OS. Dell briefly offered an upgrade to Windows Mobile 2003, but at the time, it just wasn't worth $30 to me - my X5 worked fine, and did what I needed it to do. I could write code for it using the lastest tools (VS 2003), and I was happy.
So, I finally got around to installing the beta of VS2005 at home, and I finally figured out what I was going to monkey with to see how the new features stacked up - time to do a little work on mobile development. Except, I just discovered - CF 2.0 doesn't run on PPC 2002.
And Dell, in their infinite wisdom, doesn't sell the upgrade CD anymore. Or offer any way to get it, like perhaps a download. And I just don't use my Axim enough to justify replacing it with another $400 PDA (at least, not until I'm ready for a new cell phone, and Verizon offers some decent products - not that I don't enjoy my Motorola v710, but...).
So, does anybody know of a place where I can buy the upgrade? I tried eBay, but that just didn't work.
Microsoft is providing free e-learning classes for Visual Studio 2005 and SQL Server 2005. Once the actual software is released, these title will go back to being "paid", so if you have time, now would be the time to check them out.
|