SqlCredit – Developing a Complete SQL Server OLTP Database Project Part V

I am writing this month from Redmond, where I am attending the SQL Server Katmai “System Design Review”. Considering that we’re fairly close to seeing the first publicly-available CTP for Katmai, I have decided to end my struggle with supporting both SQL Server 2000 and SQL Server 2005 in SqlCredit. As of this month, this project supports only SQL Server 2005. Those of you that have already made that plunge with your products are probably thinking “finally”. Those of you that are still required to support SQL Server 2000 in your jobs are probably wishing it were that easy for you to drop support.

As I built out the last pieces of the original table set (Card, Vendor, and Purchase), I found a number of pieces that needed to be fixed in the design. At this early stage in development, allow yourself to rework the design, as you need to, and don’t allow your application developers to lock you into the original just because you gave them the code. They should be dropping and recreating their development database as you publish changes. At some point, we will consider this database “published”. From that point on, changes will have to support upgrades. Until then, nothing is “written in stone”.

New Objects: Card, Vendor, Purchase

I added these new tables and their associated stored procedures. There is also a new script (BuildSqlCredit.bat) that is a simple script to build the SqlCredit database. It currently includes a call to drop the existing database.

These tables and procedures are straightforward. The new schema looks like this.

Not all of the unit tests are included yet. I will add those soon.

Running Changes

PrimaryCardholderID

Based on a previous discussion, I added PrimaryCardholderID to the Account table. The Account record needs to be associated with one cardholder record that is the “primary” cardholder.

This required some creative coding; it borders on “hack”, but it works for now. To insert a new Account takes these steps:

• BEGIN TRAN
• Create the Account record with a PrimaryCardholderID of 0 (zero) as a placeholder.
• Create the Cardholder record for the “primary” cardholder.
• UPDATE the Account record with the ID of the just-created Cardholder record.
• COMMIT TRAN

I also had to leave out the foreign key on Account.PrimaryCardholderID. That would have created even more problems. Yuck.

I am not happy with this solution; it will definitely have to be refactored in the future. This will work for now, but I want to see a much more elegant solution. As I have stated before, this series is a process. You don’t see just the final, pristine code.

Small Schema Changes

1. Changed Status column name to StatusCd.

This column name is used in every table and represents a simple name/value mapping (e.g. Account.StatusCd 1 means ‘ACTIVE’). The views do the translation to strings. Because this status value includes this mapping, I have chosen to use a suffix of “Cd” for “code” to differentiate it from other columns. In the views, the string status is returned in the StatusString column. It is consistent throughout the database.

2. Removed the index on Account.StatusCd

All of the indexes are based on experience and assumptions at this point. None of the other tables have indexes on StatusCd, so I dropped the index here. We can add indexes later if we find they are needed.

3. Added “OBJECT::” to DENY statements.

If you’re like me, every time I look something up in SQL 2005 Books Online, I find something new. I was reading through the entry for DENY and saw that they used this “OBJECT::” prefix. It specifies “the class of the securable on which the permission is being denied. The scope qualifier ‘::’ is required.”

4. Added DENY statements to views.

I had DENY statements for the tables but no DENY statements for the views on those tables.

5. Added FILLFACTOR to nonclustered indexes

Each of the clustered primary keys uses an IDENTITY column, so I can leave those fillfactors at the default (100%). Each of the non-clustered indexes will be populated fairly randomly, so I set those to 80%.

6. Renamed filegroups and files for consistency.

I did not like it that I had filegroup names and file names that included “Data”. In general, the naming was inconsistent.

Example Filegroup Changes

Filegroup Name
Old	SqlCredit_FGData1
New	SqlCredit_FG1
Logical File Name
Old	SqlCredit_Primary_Data1
New	SqlCredit_Primary_File1
Old	SqlCredit_FGData1_File1
New	SqlCredit_FG1_File1
Old	SqlCredit_Log1
New	SqlCredit_Log_File1
OS File Name
Old	SqlCredit_Primary_Data1.mdf
New	SqlCredit_Primary_File1.mdf
Old	SqlCredit_FGData1_File1.mdf
New	SqlCredit_FG1_File1.mdf
Old	SqlCredit_Log1.ldf
New	SqlCredit_Log_File1.ldf

I also did not like it that the four non-primary filegroups were each created with two files and the primary filegroup had just one. I added a second filegroup for primary. Now each filegroup and the log have two associated files.

7. Redistributed tables and indexes on filegroups.

The table and index distribution over the four non-primary filegroups was not consistent, so I redistributed everything. This exercise is as much art as science. The basic guidelines are that you don’t want a table and an index on the same filegroup so that the system can do simultaneous writes when a record is added (or updated or deleted). If there are two tables that are often queried together (JOIN), separate those tables onto different filegroups, again so that the system can do simultaneous reads.

Objects and Filegroups:

Object	Table	Index	Filegroup
Account	X		SqlCredit_FG1
Cardholder	X		SqlCredit_FG2
Cardholder.AccountID		X	SqlCredit_FG3
Cardholder.LastName		X	SqlCredit_FG4
Card	X		SqlCredit_FG3
Card.CardholderID		X	SqlCredit_FG1
Card.CardNumber		X	SqlCredit_FG2
Vendor	X		SqlCredit_FG4
Vendor.BusinessName		X	SqlCredit_FG2
Purchase	X		SqlCredit_FG1
Purchase.CardID		X	SqlCredit_FG4
Purchase.VendorID		X	SqlCredit_FG3

Filegroups and Objects:

Filegroup	Table	Index
SqlCredit_FG1	Account	Card.CardholderID
	Purchase
SqlCredit_FG2	Cardholder	Card.CardNumber
		Vendor.BusinessName
SqlCredit_FG3	Card	Cardholder.AccountID
		Purchase.VendorID
SqlCredit_FG4	Vendor	Cardholder.LastName
		Purchase.CardID

If you look at the distribution of your tables and indexes, they should make sense. For instance, I originally had three indexes on FG3 and one on FG4, so I moved Purchase.CardID to FG4. Some of this may need to be reworked after doing performance testing, but this gives you a good start.

Some of these changes seem fairly minor. What is the point? Consistency. I should have caught some of these as I was writing the initial code. Part of the value I receive from writing this series is ending up with code that I can use as an example in future projects.

Create Boring Schema Code

One thing I notice as I add new tables and procedures to the project: They all look the same. If you looked at Cardholder before and look at Card in the new code, they look painfully similar.

I think of that as a feature, not a lack of creativity. Implement your designs based on best-practices and standards, not on a whim. If someone reads your table-creation schema code, they should be bored. They should not be scratching their head wondering why you chose to name things differently from one table to the next or included DateCreated in one table and not another.