SQL Server 2005 Workgroup Edition

People may have missed this great offering by Microsoft. If one closely checks out the feature set that comes with this edition, one can say that this edition is even more powerful than SQL Server 2000 Standard Edition and way much cheaper. Here is some quick info:

• 2 CPU Max Supported
• 3GB RAM
• 64bit Support based on WOW
• No Database Size Limit

To my opinion, this is a lot!!! This used to be the numbers for SQL Server 2000 Standard Edition.

Microsoft made the right move in coming up with an edition targeting the small business sector.

Katmai Wish List

As we all know by now, Katmai is the nickname of Yukon’s successor. In my mind, what else could be in that version? But before I jot down my Katmai wish list, I googled and MSN’d the word “katmai” and see if there are some things worth reading at this time.

There was NONE! Hmmm. Redmond must have fixed some plumbing and there are no leaks. Or could it be that the pipes are still dry? Naah. Perhaps Google and MSN just failed to crawl where the leaks are.

But anyways, I have in my mind some things I’d like to see in SQL Server’s post 2005 version that can directly affect things I usually do in SQL Server.

• Transact SQL Editor Intellisense .. sigh ):
• Built-in Change Management and Source Control
• Better presentation of Execution Plan (instead of Graphical and XML)
• Native Array Data Type
• TSQL Support on Regular Expressions (like use of RegEx on WHERE and LIKE)
• Compressed Backups

So far these are the things that I want that can have a big impact on what I do often.

I wonder as to when we start hearing anything about Katmai. Meanwhile, Yukon will keep me busy.

How Often Do We Change Data Types

One colleague of mine raised some intriguing points on why SQL Server’s User Defined Datatypes can’t be edited. You do the editing process by dropping the datatype and recreate the new one. His position was that, he avoided using UDDs simply because of this limited mechanisms. This has led me to think of another question.

The question really is .. how often do we need to change the definition of our data? In its lifetime, I should say it should not be changed at all once our understanding of its nature is final. If the data definition we had changes from time to time, I would say that there is a possible problem in our foresight or understanding of the nature of that data entity. If that is the case, it should not be a good reason for us to define a UDD for that data entity. Worse, If we are not sure of a data entity’s definition, we have to get back to the drawing board and try to understand its nature once again if indeed we are sure of its definition. This problem also reflects the lack of resolve when determining the true nature of certain data entities.

However, I do recognize, that by the time we really need to change the definition of our data entity, I would surmise that the reason for doing so is very significant and justifiable as it will have a great impact in both our database and our application. This issue brings to us the question on how clear are pictures in our minds.

Here is my case with a few examples to show how I deal with the situations when understanding and defining data types.

Scenario #1: How stable can we define a data type?

BirthDate as DATETIME
EmailAddress as NVARCHAR(320)

Scenario #2: Questionable Candidates for UDD Creation

CustomerID as CHAR(5)

Scenario #3: The Lack of Understanding Example

Age as INTEGER – (assuming in Years)

Scenario #4: Things Change Over Time

PhoneNumber as CHAR(7)

The scenarios above are the usual things I encounter when one defines the type of a certain data.

In scenario #1, how often do we need to change datatypes? Should we have these kinds of data and have clear understanding of their very nature, defining UDDs for these would be sound. Thus, EXEC sp_addtype BirthDateType, datetime, ‘NULL’ would almost guarantee us that we will NOT encounter problems in the future or would require us to edit its definition.

Scenario #2 is a classic example of our lack of foresight or anticipation. A good DB designer could easily spot this. In the example, it is just a matter of time before we make it to CHAR(10) or CHAR(15) or better yet CHAR(25). I personally declare IDs as VARCHAR(255) as I don’t have a habit of making ID types as one my system keys. I’d rather have IDs as VARCHAR(255) and indexed them with UNIQUE constraint making them candidates for user consumable keys. As for me, I won’t be inclined to have a UDD for this situation.

Scenario #3 is also a classic on how we fail to understand the very nature of certain data. In most of us, without batting an eyelash, it is almost automatic that we declare Age (in years) as INTEGER, only to find out later on that we need to store it like 10.25 for 10 Years and 3 Months.

Scenario #4 forces us to change. It has been decades that Philippines had 6 or 7 digit local phone numbers. We could have probably saved on a few bytes and declared it to CHAR(7). Now we are seeing 8 digit phone numbers and we need to adapt.

If we had defined UDDs, for all the samples above, scenarios #3 and #4 could probably force us to EDIT our UDDs. Using EXEC sp_addtype PhoneNumberType, CHAR(7), ‘NULL’, we have defined a UDD for #4. To drop and create a new UDD for this to support CHAR(8), this could create a major headache in our app. We will need to revisit our SPs and character validation codes, the formats and pics we defined, the CONSTRAINTS we declared and other stuff on both our DB and the apps and check all support related to PhoneNumberType. How do I avoid this? Should I need support CHAR(8), I would definitely NOT do an “EDIT” by dropping and recreating my UDDs. Instead, I’ll create a new one with

EXEC sp_addtype PhoneNumberType2, CHAR(8), ‘NULL’

and subsequently create new ‘phone’ type columns and define them as PhoneNumberType2. Eventually, I will have to deprecate PhoneNumberType in favor of PhoneNumberType2 but that would be another story.

This way we have zero impact on our apps and databases and have avoided possible headaches.

What Hinders SQL Server BI’s Adoption?

There was a thread in one of the forums I am subscribed to regarding why SQL Server’s Business Intelligence feature’s adoption in our region is slow to gain steam. Where I belong, SMEs are the biggest of corporations already. Those smaller than SMEs are a lot and they make up most of the businesses. Companies with less than 15 employees are a lot.

So what hinders?

BI is expensive to deploy. For the smaller than SMEs companies, BI is a luxury. Only those big ones can afford to deploy it and use it to the hilt. BI is best separated from the OLTP server so this will require one to setup a new box, buy a license and new things to manage and also a new box to take care of. BI is also resource hungry so one needs a lot of diskspaces and memory plus the muscle to crunch and analyze data and turn them into information. One can have OLTP and BI functionality in one box but both affects each other significantly.

With this in mind, what is left are the big players. Only those DB developers from big companies had the chance to get their hands dirty on BI. I myself only had a hand on it on an experimental level instead of in production. I have proposed this stuff to a few clients who can afford but after knowing what entails to come up with a datawarehousing environment, they would gladly say they will consider it in the future … especially if they are able to live with the reports that we did together with the apps …. only to be burried in oblivion.

This situation also prevents us developers from being exposed to more BI boxes in the workplace since not everybody/customer thinks it is necessary. Now, this is a challenge for us to make justifications for them to take on BI and have at least a meager BI setup. It is also a challenge for us to become involved in the “convincing” phase instead of letting those marketing pitches drown their already overwhelmed minds. Instead of us trying to position ourselves to learning and self-benefit, let us feed the minds of those who pay our salaries and buy our servers and license and finance our playground. I say, let us give them the taste test. We reap what we sow later.