can varchar length in convert or cast decrease performace of sqlserver 2005

Question

Dear all,
i am creating hyperlink from stored procedure
ex.
table name is emp and col1 is bigint.

select '<span><a target="_blank" href="index.htm'+CAST(col1 as varchar(20))+'">Index</a></span>'

from emp

i can use varchar(30) in place of varchar(20) in above case.
my question is,can the increase of length of varchar 20 to 30 can decrease the performace of sqlserver 2005 in any situation.

(please consider that i have more than 200 stored procedure where i am creating many links as shown above)


yours faithfully

Answer 1

Casting a string of up to 20 chars as varchar(30) or varchar(100) instead of varchar(20) is not likely to make any difference. On the other hand, if the string is actually longer, than there is performance implication, but may not be significant.

Optimization article:

http://www.sqlusa.com/articles/query-optimization/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 2

thank u ,

one more concern.can the increase of varchar(20) to varchar(30) increase the  memory usage?

Answer 3

If the data stays the same, there is no difference between varchar(20) and varchar(30).

If the data content increases 10 bytes on the other hand, the table row length will increase, fewer rows will fit into 8K pages, more disk io will be required.

Nonetheless, practical performance degradation may not be significant at all.

If data content increases from 20 bytes to 800 bytes, that is significant.

For top performance design the table thin with fixed size columns:

http://www.sqlusa.com/bestpractices2005/performancebytabledesign/

---

Kalman Toth SQL SERVER & BI TRAINING