Decreasing the varchar column sizes in a table

I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much...Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.

Thanks.Before doing anything, identify all NON-character fields, sum their storage up, and subtract the result and 8000 from 10468. What you get is the total number of characters that you would have to shrink your character-based fields by.

Next, do a SELECT [field_name Width]=max(datalength(field_name))... on all character-based fields, sum the result across all those fields, and see if you get 8000 or less after addint the sum of NON-character-based fields storage sizes to it. If the result is higher, - you will have to decide if you want to truncate data in some of your character fields.

If the latter is what you get, - consider normalizing the table. For example, if only some of the records contain values for a specific field, take the record key and that field and create a different table using the key as FK to the original (I hope you have a key).|||Here's a proc that might help you analyze your char columns.

Decimal to ASCII conversion

I need to convert a decimal number to 2 ASCII characters using SQL. For example, 13110 (16 bit word which is 2 bytes) would be a decimal value representing 36 in ASCII.
Any ideas?I don't get the transformation fomula.

You get 36, I suppose the characters '3'+'6'. '3' has the ASCII value 51 (decimal) and '6' has the value 54 (decimal). I do not understand how 51 and 54 can be derivated from 13110. Can you help me?|||I'm sorry, actually it's quite straight forward: 51 is the one word, and 54 is the other word, and together they form your 13110 (decimal) or better to see 3134 (hex).

Anyway, the code is:
SELECT char(floor(13110/256)), char(13110-floor(13110/256)*256)

Cheers!|||That worked! You the Man! Thanks Bunches!

Decimal data type: storage in bytes?

SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
DonDepends on the precision you want. Can go from 5 to 17 bytes. Look up the
topic "decimal and numeric (T-SQL)" in SQL Server 7.0 or 2000 Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:95ff01c4338d$ae53cf70$a501280a@.phx.gbl...
SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don

Decimal data type: storage in bytes?

SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don
Depends on the precision you want. Can go from 5 to 17 bytes. Look up the
topic "decimal and numeric (T-SQL)" in SQL Server 7.0 or 2000 Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:95ff01c4338d$ae53cf70$a501280a@.phx.gbl...
SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don

Decimal data type: storage in bytes?

SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
DonDepends on the precision you want. Can go from 5 to 17 bytes. Look up the
topic "decimal and numeric (T-SQL)" in SQL Server 7.0 or 2000 Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:95ff01c4338d$ae53cf70$a501280a@.phx.gbl...
SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don