correctly size fields in database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

correctly size fields in database

Author

Topic

dkeener635
Starting Member

6 Posts

Posted - 2007-06-07 : 22:36:08

I have a table in a SQL 2000 database with 41 fields. I have established all of the fields as varchar with various lengths (most are 255). My question is since I already have the data imported into the table, is their an efficient way to size the fields more accurately? I'm thinking there is probably some way to write a query that would check the datalength of each field and give me the max characters in each field, but that seems like a lot of records to loop through to go through each field and see the greatest number of characters in the column. There may even be something built into SQL that will let me efficiently size the fields. Any help would be greatly appreciated.

Thanks,
David

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-07 : 23:56:26

Since the column datatype is varchar, you no need to worry. The storage space of varchar will be length of the data entered in it not the declared length.

--------------------------------------------------
S.Ahamed

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-06-08 : 02:16:34

Hi David,
as Pbguy said there is no need worry with the size of varchar fields.
Still if u want to get the size of each field, u can use
SELECT MAX(DATALENGTH(col1)), MAX(DATALENGTH(col2)),...
FROM tbl

this ll get u the max size of data in each field.

Kristen
Test

22859 Posts

Posted - 2007-06-08 : 03:26:06

I would reduce them now you have your data in. otherwise folk will start assuming that the data is never going to get longer, but of course it can.

For example, you have 4 address lines in a table. Longest data is 40 characters, but is defined as 255. You make a label printing program ... and then someone ACTUALLY enters a 255 character address line ...

Better to right-size the columns.

Its one of the first things I check when I am asked to look at a new database - what's the defined size compared to the Max Length of the data.

I suggest you use LEN rather than DATALENGTH in PeterNeo example

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources