SQL Server and varchar 1

[awhitney77]

I have this table (that I did not design) that has 81 fields, each with a data type of varchar (255) (40,000 records). This table contains date and numeric fields as well as characters. Now, I have a couple of questions:

1) Could there be/have been a reason for creating a table in such a matter?

2) Will the current structure impact the performance of the VB6 frontend?

Thanks for reading and possibly responding. All opinions welcomed.

 

[tlbroadbent]

There is no rational reason to create a table with all varchar(255) columns. Chances are the original table was created from an import of some sort and the default column types and sizes were retained. Someone just didn't want to do the proper analysis.

With varchar columns, you are proably not wasting much space but there are a number of inefficiencies introduced in this design. In order to deal properly with the data, SQL will need to convert it - implicitly or explicitly. Conversions take time. Searches will likely be slower.

I recommend a thorough analysis and conversion of the column type to appropriate data types. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.