Adding fields when mandatory is required? 2

[1DMF]

Hi,

I was wondering what you guys do when required to add new fields to a table.

If the requirement is to make them mandatory (not null), what do you do about the exisitng data.

I know in my OU course academically they say create a new table with functional dependency, but, what if you are asked to add two new fields, then a week later another two, then another two.

do you really create three new tables all with just two fields in?

doesn't the DbSchema end up seriously messy with a tonne of tables requiring joins when being queried.

However if you add them to an exisiting table and they are required to be mandatory, what do you do about existing records that wouldn't have this data?

Your guidance is appreciated.
1DMF



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[gmmastros]

doesn't the DbSchema end up seriously messy with a tonne of tables requiring joins when being queried.

Yes.... and no....

Narrow tables perform better than fat ones because there is less to store. Think about it this way, suppose you had data that you rarely used, but needed to keep in the database. If this piece of data is in the main table, it's taking up room. Do this often enough and you end up with a bloated table. If this piece of data is in a separate table, and it's rarely used, the main table will still be narrow and you will only need to join to the other table when you (rarely) need that other piece of data.

Some DBA's do this purposefully. It's called vertical partitioning.

http://msdn.microsoft.com/en-us/library/ms178148.aspx

In most cases, tables are kinda small so performance considerations are not necessary. In this situation, I would add the column to the main table. In particular, I would create the column (allowing NULLS). Then I would set all of the existing rows to a known value that is not NULL (like 0 or "Unknown"). Then I would alter the table to not allow NULLS.

You'll want to carefully test all of your existing functionality to make sure that every insert includes data for the column.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

As always, many thanks george.

I think I need to analyse some tables and look at normalising them (vertical partitioning) and certainly when it comes to adding columns.

Though as you say it depends on the current number of columns and the issue with existing records and nulls.

It was a lot easier before I learned all this stuff and just created additional null columns without a second thought!

Ignorance is bliss.

As you say the issue is where there are insert statements in the application that doesn't provide the additional information where the new column is mandatory and there isn't a default value!

Isn't this fun





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[gmmastros]

Fun? Heck yeah (most of the time).

This is why it is extremely important to have several "environments". Most DBA's will have a production environment, a development environment, and a testing/staging environment. Each "environment" is usually a separate server each with a copy of the production database (restored from original periodically).

It's also helpful to do unit testing on your database code. I know the guys that wrote tSQLt.

http://tsqlt.org/

It may be worth a look.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLBill]

You can also create the new 'NOT NULL' column with a DEFAULT value. That will cause the existing rows to take on the default.

Bad part....if the table is huge adding the default to the existing rows can take a while.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

Thanks Bill,

The other problem is a default value for all existing nulls might not be the right value for all records :-(

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download