none
can varchar length in cast function or while declaration in SP, matters/decreases performace of sqlserver 2005

    Question

  • Dear all,

    I have many stored procedure in my database.

    The problem is, if i use varchar(20) in place of varchar(10) to declare variables in stored proc.
    can it decrease performance of my stored procedure or sqlserver 2005
    in any situation.

    and i am also making links in almost all stored procedure as following

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

    1)so please tel me, is there any memory alocation done for stored procs? and is variables declared in a stored proc is considered while allocation, if it is considered then how much one should bother?

    2) is there any diffrence in memory allocation done for

    cast(@colid as varchar(30))  or cast(@a as varchar)

    or cast(@colid as varchar(40)) if it is used in stored proc,

    can reducing varchar(40) to varchar(10) make any diffrence in  server wide memeory allocation or consumption?



    yours faithfully





    • Edited by rajemessage Sunday, November 17, 2013 2:42 PM
    Sunday, November 17, 2013 2:37 PM

Answers

  • >if i use varchar(20) in place of varchar(10) to declare variables in stored proc.
    >can it decrease performance of my stored procedure

     I would be very surprised if changing a variable declaration from varchar(10) to varchar(20) had a noticeable effect on performance.

    >is there any memory alocation done for stored procs?

    Of course there is.

    >can reducing varchar(40) to varchar(10) make any diffrence in  server wide memeory allocation or consumption?

    It is unlikely to make any noticeable difference.

    David 


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, November 17, 2013 3:29 PM

All replies

  • >if i use varchar(20) in place of varchar(10) to declare variables in stored proc.
    >can it decrease performance of my stored procedure

     I would be very surprised if changing a variable declaration from varchar(10) to varchar(20) had a noticeable effect on performance.

    >is there any memory alocation done for stored procs?

    Of course there is.

    >can reducing varchar(40) to varchar(10) make any diffrence in  server wide memeory allocation or consumption?

    It is unlikely to make any noticeable difference.

    David 


    David http://blogs.msdn.com/b/dbrowne/


    Sunday, November 17, 2013 3:29 PM
  • In addition to David's notes: for a sizable table if you change a column from varchar(10) to varchar(50) and actually populate the column with long strings, there will be performance implications.

    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, November 17, 2013 3:51 PM
    Moderator
  • thank u,

    is there any link which can tel me what are the things(critareas) considered for memory allocation to SP.

    yours sincerely

    Tuesday, November 19, 2013 1:30 PM
  • I have seen similar kind of issues explained in one of the web cast by Ramesh Meyyappan (SQLworkshops).

    But unfortunately, I do not have the links handy to the exact scenario. But you can browse through the same.

    http://www.sqlworkshops.com/webcasts


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 19, 2013 1:43 PM