Author: LizetP

Following the thread from previous post (and mostly to have this as a personal reminder) I came across this article
Identity and Primary Keys @ sqlteam.com
The idea behind this article is to encourage Database developers to use Natural Keys as the Primary Keys on their entities design. I have seen so many designs like that and have been so much against them. I wanted to double check and posted a thread on sql-server-l at groups.ittoolbox.com.

Some of the brief comments here:
Natural key is not good as a primary key at all. It is usually much longer
than int identity(1,1) 4 bytes.

Natural key is very cumbersome to use as a foreign key.

I am amazed that this became a debate here.

Ahem, it might be a newbie question alright!

I also got another comment on how a uniqueidentifier field cannot guarantee uniqueness:

I have seen plenty of people create databases where the only unique ident=
ifier was a surrogate key, such as a sql server id column. These people o=
ften show up here asking how to delete duplicates. (haven’t you seen a to=
n of them here Kalman?) Duplicates? How can there be any duplicates if yo=
u have a sql server ID column as a primary key? LOL

This dba proposed to use Natural Keys as PK and got this reply back:

Take my advice all: int identity(1,1) is the best primary key. You can put
unique contraint or unique index on the natural key column.

Back in the glory days of sybase it was a pain to construct your own
identity(1,1) through the use of the max function.

But the best of all is this comment that was inserted into the original article. Original comment here (page two of the comments, nickname quazibubble)I know the comment is a little bit rude, but I liked how clear that explanation is:

A solid, mature OLTP database architecture has entities with a single identity or GUID PK defined, usually as a clustered index, that by definition is totally meaningless except for building relational structures behind the scenes. That is it’s ONLY purpose. Think of it as an undercover system column that has nothing to do with the entity it belongs to, you just happen to be able to see it as a developer. You’ll use design systems in the future that don’t even give YOU, the developer, access to this column or it’s values, but it will be there (they’ve already existed as CASE tools and their ancestors, as well as their simpler, more useful prodigy). Your job should be designing ENTITIES AND RELATIONSHIP, not PKs and FKs. If you find yourself pondering what to use for a PK (beyond the question of “should it be an identity or GUID?”), you are doing something terribly wrong and will be costing a whole team a great deal of money and headache, even long after you a fired.

Furthermore, if a PK is not meaningless–e.g. it is any attribute remotely helping to add human meaning to the entity (SSN, network login, etc.), there WILL be cascading updates throughout the entire system, many people are going to be very pissed, and you will look like a jacka**. Users make typos, people change their names. Any data that was entered by or derived from a human at any point is subject to change. To be perfectly compliant with the forms of normalization, the value in a PK should not even be used as an account number. But giving a system-defined value that later becomes meaningful to a human is not nearly as harmful as using an existing value of meaning for a PK.

End of reminder

A few posts ago I mentioned why Primary Keys in a table shouldn’t have business meaning. I didn’t quote the article I usually re-read for reference. I ran into this article while looking for another subject in www.agiledata.org today.
The section of this article I like the most is:

There are two strategies for assigning keys to tables. The first is to simply use a natural key, one or more existing data attributes that are unique to the business concept. For the Customer table there was two candidate keys, in this case CustomerNumber and SocialSecurityNumber. The second strategy is to introduce a new column to be used as a key. This new column is called a surrogate key, a key that has no business meaning, an example of which is the AddressID column of the Address table… Addresses don’t have an “easy” natural key because you would need to use all of the columns of the Address table to form a key for itself, therefore introducing a surrogate key is a much better option in this case.

The primary advantage of natural keys is that they exist already, you don’t need to introduce a new “unnatural” value to your data schema. However, the primary disadvantage of natural keys is that because they have business meaning it is possible that they may need to change if your business requirement change. For example, if your users decide to make CustomerNumber alphanumeric instead of numeric then in addition to updating the schema for the Customer table (which is unavoidable) you would have to change every single table where CustomerNumber is used as a foreign key.

If the Customer table instead used a surrogate key then the change would have been localized to just the Customer table itself (CustomerNumber in this case would just be a non-key column of the table). Naturally, if you needed to make a similar change to your surrogate key strategy, perhaps adding a couple of extra digits to your key values because you’ve run out of values, then you would have the exact same problem.

The fundamental problem is that keys are a significant source of coupling within a relational schema, and as a result they are difficult to change. The implication is that you want to avoid keys with business meaning because business meaning changes.

And the article can be found at Data Modeling 101

Enjoy!

Today is my first day at the office after a nice week of vacation. I had such an hectic schedule last month that I spent a lots of time without blogging. The main reason was the time I spent studying for the cert exam 70-305. So yes, I passed the exam.
Advice for those who want to write the exam, try to make as many preparation tests as you can, the exam cram book is a good guide, take the official documentation as a reference, but it shouldn’t be the only reference you use. Most of the questions will have slight tiny details on each possible answer, some of them will offer more than two correct answers but you need to select the most efficient solution, so you basically have to train yourself apart from knowing the subject.
Ok, now I’m back to a hectic schedule again but promise to sleep more than 5 hours a day 😉

Ok, add this to my Christmas’ wish list. SQL Server 2000 doesn’t allow where … in clauses with multiple fields, so the following construction fails:

DELETE FROM temp1
WHERE name, description IN
(SELECT name,description
FROM temp2
WHERE name LIKE ‘%computers%’)

The solution is a not so pretty concatenation, if fields are strings

DELETE FROM temp1
WHERE name + description IN
(SELECT name + description
FROM temp2
WHERE name LIKE ‘%computers%’)

But you are forced to use joins if the field is numerical.

I’ve been receiving a training from INETA as part of SQL 2005 launch this November, I’m planning to ask if they have included any extra features in the new T-SQL, that one would be pretty handy.

I phone my dad yesterday, and tried to explain him how was my day while working as Tech Support on Saturdays. He was concerned for all those hours of work, as he knows I work Mon-Fri as well. I said, well, times flies so quickly you have no time to find out what time is it, plus you learn a lot, despite of how hectic things can be, you see lots of real life troubles and you have to find a solution even if you haven’t heard about the problem before.
I tried to explain him, well, people host their websites with us, some of them a pretty complex applications, they use who knows what scripts and we give support for about 4 programming languages, let alone three types of databases. The other day a customer was complaining his Ruby installation wouldn’t work, so boss said, Lizet, write a hello world program in Ruby and show him his installation is working. But boss! Ruby? all I’ve seen is a Ruby site with funny comics, and all I remember is the comics…You remember more than I do, so give it a try…Ok, hello world should always be easy.
I also had to debug some perl scripts for a customer or show him the lines with errors, I had to import several databases, watch for server overload a couple of times. It was fun Dad!
I haven’t mentioned my dad is a MD, and although he wouldn’t work “on call” anymore he used to make a shift a week when he was my age. In the fam we all know more than a story about it. He replied: “It seems to me you’re working on an emergency room”
Hrm, I said, yup, I suppose it feels the same 🙂

“Custom development is that murky world where a customer tells you what to build, and you say, ‘are you sure?’ and they say yes, and you make an absolutely beautiful spec, and say, ‘is this what you want?’ and they say yes, and you make them sign the spec in indelible ink, nay, blood, and they do, and then you build that thing they signed off on, promptly, precisely and exactly, and they see it and they are horrified and shocked, and you spend the rest of the week reading up on whether your E&O insurance is going to cover the legal fees for the lawsuit you’ve gotten yourself into or merely the settlement cost. Or, if you’re really lucky, the customer will smile wanly and put your code in a drawer and never use it again and never call you back.”

Check link here

It had happened to me, hrm, lemme think, probably more than three times. One of the projects was a really expensive one and right after the very few deployments it was never used again. For me that was deeply saddening as we had invested so many hours of work in it, the customer did pay, but sometimes you prefer seeing your software being used, even abused, rather than gone to the oblivion.

I’ve always wanted to work in a server farm, so when a friend told me he had an available opening for offering tech support on the weekends at a hosting co, I said YES! can I shell access the servers?
OMG, I never thought it could get that hectic on a Sunday, I spent 8 hours without stop attending requests, some were really minor, like “we need to unlock a domain to make a transfer” some were kinda tricky, like “we want to be able use wildcards on our subdomains”…I hope users keep returning after some of my answers, it’s hard to keep up with the technical work they demand and at the same time make every customer happy.
This company has the best tech support I’ve ever experienced, so I didn’t want to let them down…Go Go the Penguins! 🙂

You’ve probably noticed that English is not my mother tongue, if you hear me talking you will definetely notice an accent and definetely my grammar could use some improvement. My native language is Spanish and I’ll be teaching a beginner’s course for two months at the Regional College. I’m looking foward to meet the students, it’ll be good for me to learn more phrases in English and interact with people other than IT colleagues and family :-p (most of my friends are colleagues as well) also an opportunity to show them a little of my place of origin and the Spanish speaker world. I set up a small site to publish the lessons http://spanish.theniceweb.com. Take a peek if you want!

Now that I finished reading the Web Developer’s certification training book
and was about to do all the labs and get my hands on the Exam Cram book
I read that MS will be changing the certifications next month and I might need to upgrade my skills, right before getting the MCP exam. Ok, Ok, I knew this might happen, I just didn’t know it would be so soon…

Here’s a paragraph taken from the original article:

According to Valvano, Microsoft will follow tradition, releasing exams for SQL Server 2005 and Visual Studio 2005 about 45 days after the release of the products to the general public. Those exams, however, won’t fit into the traditional MCP schema that one might be familiar with. Instead, Microsoft will introduce a new framework for certification that will pair up a credential with a skill-identifying certification based on a tiered approach consisting of the following credentials:

• Tier 1: Microsoft Certified Technology Specialist will require simply passing one to three exams based on a Microsoft technology. As products meet the end of the support lifecycle, its related exam will be retired.
• Tier 2: Microsoft Certified IT Professional or Professional Developer require a Technology Specialist certification, plus one to three more exams, based on the requirement for a particular path. This tier is tied to job role at an organization, such as Database Developer or Business Intelligence. Recertification will be required to maintain status at this level.
• Tier 3: Microsoft Certified Architect is a rigorous, board-level certification that requires recertification. Achieving Technology Specialist or IT Professional or Professional Developer certification not a prerequisite to attaining this level of certification.

Okay, I honestly don’t know if taking the MCP exam at all…

We finally launched a very important web site yesterday.
The web application logs all of it errors in a sql server 2000 database, which is neat as we can review the logs and see what script produced the error, what specific query string was sent to that page, the exception it caused and the end user only sees a nice looking message of “We’re sorry, please try again” kind of thing.
I’ve been trying to isolate a pretty random error, trying to nail down the cause. I reviewed the error log table and need to filter an nvarchar field.

My T-sql sentence looks like this:

SELECT *
FROM dbo.ErrorLog
WHERE
(datestamp = ‘9/19/2005’) AND (error LIKE ‘Object reference %’)

Reviewing the help, I found there are very few wildcards and zero support for regular expressions.

So, here’s my formal complain, how are we going to do decent querying without regular expressions. I know MSSQL 2005 will include the .NET Framework inside and that means it will include the Regex namespace, but, wouldn’t it be more efficient to have reg exps on the T-sql level, instead of having the overload of creating objects just for using regexps?
I’ve been told we should avoid using the Framework on our SQL 2005 sprocs whenever we can accomplish the same task with T-SQL…

Regexps, regexps!