April 23, 2014 at 12:13 am
Hi,
I am facing a question which I cannot really have an answer without having a second opinion.
My application have a master table, which contains field BusinessCode Char(5). There are lot of other tables already have reference to this table. This is existing situation.
Now, having CHAR(5) data type for BusinessCode is really a mistake, we want it to be changed to Varchar(5). It is not really mandatory that BusinessCode should have 5 characters. But can't do it now as it is outside project scope.
There is a new functionality needs to be added and for that I am creating few tables which reference to BusinessCode. I need to fix the mistake and have Varchar(5) for new tables on BusinessCode field, but cannot add foreign key reference because of different data type. This new table is having a trigger in order to log the changes to another table.
Now, for validating (validate, rather than having foreign key reference - am satisfied with that) the BusinessCode, I can have a constraint which call a function or can have this validation inside the trigger. I chose trigger.
Which is better and why?
Note: There could be bulk insert/update happens to this new table. It is having an Identity column as Primary Key.
April 23, 2014 at 2:16 am
This may help in making a decision.
http://technet.microsoft.com/en-us/library/ms189261(v=sql.105).aspx
IMO - for your scenario - I would choose a foreign key constraint 🙂
gsc_dba
April 23, 2014 at 2:21 am
To be honest, leave it at Char(5). It's not a huge columns, there isn't a massive waste of space if the data is smaller. Make the column char(5) in the new tables too and use a foreign key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2014 at 2:37 am
I prefer to change it to varchar(5), so that i can avoid the padding space and usage of trim everywhere. Yes, it is not a big deal, but if nothing costs too much then i would like to make it right.
The link provided above makes me to conclude that there is nothing wrong in having my validation inside trigger.
April 23, 2014 at 2:44 am
But you don't need to right trim everywhere, maybe on display but SQL ignores trailing spaces when doing comparisons.
Yes, you can do validation in a trigger, but you're losing out on some of the advantages of foreign key constraints and, unless the business code is usually 2 or 1 character, you're using the same amount or more space for the varchar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2014 at 3:02 am
But what are those advantages of foreign key constraint, I still be able to maintain data integrity through trigger. There are cases that business codes only contains two characters.
April 23, 2014 at 3:37 am
But triggers aren't read by the query optimizer, foreign key constraints are. You can actually get better execution plans because of enforced referential integrity.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 23, 2014 at 3:43 am
iam777here4u (4/23/2014)
But what are those advantages of foreign key constraint, I still be able to maintain data integrity through trigger. There are cases that business codes only contains two characters.
Here are some pros and cons:
This link favours foreign key constraints - as I suggested, suits your scenario better.
gsc_dba
April 23, 2014 at 3:49 am
iam777here4u (4/23/2014)
There are cases that business codes only contains two characters.
Ok, but unless the *majority* of the values are two or one character, you won't be saving any space by changing to varchar, you may end up using more space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2014 at 3:53 am
The last link gave me a better idea. Extended transaction is what caught my mind. I will think about it again.
April 24, 2014 at 8:08 am
Another disadvantage of using triggers in place of constraints is that a newly created trigger won't validate existing rows, only newly inserted or updated rows. Also there are more scenarios where data modications can slip throught he cracks with a trigger. Referential constraints are more reliable.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply