Hi Richard.

The answer to your question depends on so much. First of all, are retaining the VFP front-and-middle level code as-is? This is usually the quickest migration path... and often the worst decision, as well. If you rely on record-level operations rather than SQL Selects, you might find yourself in a position of needing to grab all the records from SQL Server into a VFP view cursor, applying indices as required, and then executing the EXACT same actions that you would execute against the VFP tables. In other words... it will be slower.

If you are able to change the existing code to offload processing to the SQL box (and if you currently rely on Select statements a good deal this will prove easier), then you could see a gain... if you take advantage of one of the most important advantages of a true database model, and use a really souped-up server for the database.

But again, all the RAM in the world on the database server isn't going to help you if you are replacing this:

lnCounter = lnCounter + 1
USE MyTable INDEX LastName
SET KEY TO "Miller"
SCAN FOR FirstName = "VIN"
  lnCounter = lnCounter + 1
ENDSCAN

... with this ...

lnCounter = lnCounter + 1
USE MyRemoteViewThatGrabsEveryRecord
INDEX on LastName tagLastName
SET KEY TO "Miller"
SCAN FOR FirstName = "VIN"
  lnCounter = lnCounter + 1
ENDSCAN

I've upsized a few apps, and I have come to the conclusion that as painful as it sounds, keeping the existing code that was intended for single-tier database access, and just "switching out the back end," is rarely the right decision.

As for database normalization issues and so on... performance doesn't have a direct and absolute correlation to level of normalization, IMO. In some cases, removing denormalized columns may actually slow performance because an extra join is required. There are many good reasons to take this opportunity to normalize your tables, but performance would not be one that would jump out at me. It might be that your table design is denormalized in a way that does, in fact, hinder performance... but not necessarily.

Hope this helped. Keep us posted.

>My company is trying to upsize it's database backend from Visual Foxpro to SQL server.
>
>The Foxpro backend has duplicated data, tables with 130 plus columns with 40+ index's, oh yeah the company has never produce any form of worth while data dictionary so we don't even know what certain columns represent however we do know that many of them are duplicate data columns. Normalization of data does not exist here, and these are just a few small examples of what our current production backend for our live CRM database in Foxpro.
>
>The people in my I.S. department are under the impression that SQL server 2k will perform well if we just up size the back end with out changing the table structures. I am concerned about the ramifications of this decision.
>
>My opinion is that SQL is a better back end than Foxpro any day of the week but I also am of the opinion that SQL Server is more rigid and needs to be used in it's MS suggested and documented formats. While it is also my opinion that because foxpro evolved from flat file type database systems it may lend itself in a more forgiven manner to not so normalized data structures better than SQL server.
>
>I guess my question really is; with the limited description I have provided you with of our current conversion and upsizing strategy; what would your prediction be when it comes down to the feasbility and/or over all performance of the end result of upsizing this table structure to SQL server 2k for a production Call center and CRM database being used by just short of a 1000 users at a time???