SQL Server Performance Forum – Threads Archive

char to varchar

Hi,
Please clarify my below problem,
In my application our previous programmer created the database and created more than 100 tables. In all the tables he used char datatype instead or varchar. Our application is developed in Power Builder. After migrating the whole application to latest version of power builder. While retrievig string values from database, it having values with space for eaxmple
select name into :ls_name from user the values in ls_name is like
ls_name = "ABC "
it means in the table it is declared as name char(50). So in whole application i have to use trim function.
Instead of that if I changed table structure ie all char datatype field to varchar datatype, is it cause any problem?????????? We are using this application for 5 years. Database size is too large.
Thansk
Thanesh Peer Thanesh
Unique System
Sharjah
UAE

It is always good to use variable length datatype than fixed length.
You can change the datatype and i guess there won’t be any issue with it. But try it on test box and then on prod.

From the database point of view, it shouldn’t cause any problems. You will also need to UPDATE your existing data data to get rid of the trailing spaces. Consider this
SET NOCOUNT ON
CREATE TABLE t1 (c1 CHAR(10))
INSERT INTO t1 VALUES (‘FRANK’)
SELECT DATALENGTH(c1)
FROM t1
ALTER TABLE t1 ALTER COLUMN c1 VARCHAR(10)
SELECT DATALENGTH(c1)
FROM t1
UPDATE t1 SET c1 = LTRIM(RTRIM(c1))
SELECT DATALENGTH(c1)
FROM t1 DROP TABLE t1
SET NOCOUNT OFF ———–
10
———–
10
———–
5 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

As you say database is big in size then make sure you’ve tested this approach with same volume on a test platform in order to avoid any last minute glitches. So the transaction log will come into sight during this operation, so take care of their sizes during this operation. Also ensure the application will not have any issues with the change in the data type, may check with vendor or developers. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

or When querying use ltrim(rtrim(col))=’ABC’ Madhivanan Failing to plan is Planning to fail

You wouldn’t remove trailing spaces from the tables? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>