I've done all SQL database development up until now and I'm faced with a problem that I've never run into before. I'm in the process of converting an old VFP database into the DDT to be deployed to a SQL 2008 R2 server. The schema is near perfect now with one minor exception...

One of the tables in the old VFP table has a primary key that is of a data type char(2). This column presents a problem to SQL when importing the old data because SQL believes there are duplicate keys being inserted. Not coming from a VFP background I'm finding out the "aa" and "AA" are looked at as different values and of course SQL is by default case insensitive.

I've never changed the default collation type of a database and, again, coming from a SQL background would primarily stick to int, is identity, auto-increment, as my primary keys. My first thought would be to add that type of column and make it the primary key. I'm not 100% certain how this will affect the VFP project but I feel it would be minor? Maybe not... It would resolve my import issue for sure but I can't say if that's a wise option or not.

The original developer suggested changing the database collation type to "SQL_Latin1_General_CP1_CS_AS" which makes the entire database case sensitive. I've made that change to the DDT database properties and did a fresh deploy creating a new database and all looked good at first. I then found that I did not have the ability to enable/disable triggers anymore which breaks my import script because items are disabled before data is brought in and then enabled afterwards. It also raises questions about what else may have changed from the default collation type behaviour.

I've read a few things about setting collation on a single column and changing the data type to nchar but I'm not sure at this point if that's the way to go either.

I know this is kind of long winded but I wanted to let you know all my thoughts and what I've researched before asking for assistance.

A suggestion here would be greatly appreciated. I've attached an image of a query of the old data.

Thanks,
Matt

Here are a couple more screen shots.

Hi Matt,

If the VFP application/data will not be in use after you complete the SF/SQL application, then I will strongly suggest you re-design that table to use an Int PK and then do a conversion process or stored procedure to update child tables using the pt_cid, of course you will need to add and Int FK to those child tables and change any logic used in the VFP application for your SF application related to those tables.

Edhy Rijo

Thank you for your feedback

I'm more comfortable taking the approach you suggested and intend to do that.

Thanks again,
Matt