SQL Server Stored Proc Argument Type Conversion

Question

Suppose I have a bunch of varchar(6000) fields in a table and want to change those to text fields. What are the ramifications of the stored procedures whose arguments are of type varchar(6000). Does each stored procedure also need those argument data types changed?

Answer 1

Text fields are deprecated in SQL Server 2005 and above. You should use varchar(MAX), if possible. If you expect to have more than 6000 characters passed in the arguments to your stored procedures, you will need to change them as well.

Answer 2

Text fields are rough to work with in SQL Server. You can't actually declare local variables of type text (except as parameters to a stored procedure) and most of the string manipulation functions no longer work on text fields.

Also if you have triggers the text fields will not appear on the INSERTED or DELETED tables.

Basically if the field is just holding data from a program and you aren't manipulating it then no big deal. But if you have stored procedures to manipulate the string then your task will be way more difficult.

As tvanfosson mentioned if you have SQL Server 2005 use VARCHAR(MAX) then you get the length of a text field with the ability to manipulate it like it is a VARCHAR.

Answer 3

The reason for text field usage is that all of the varchar(6000) fields in one row exceed the max row length. Text fields just store a pointer in the row thus not exceeding the SQL Server max row length of 8000 something. ATM the database cannot be normalized. The data is not manipulated by the stored procedures it's just inserted, updated and deleted.

Does VARCHAR(MAX) behave like a text field and only store a pointer to the data in the row?

Answer 4

The other answers are right, but they don't answer your question. Varchar(max) is the way to go. If you made the feilds varchar(max)/text, but kept the stored proc arguments the same, any field that came in through the stored proc would be truncated to 6000 characters. Since you say that it will never exceed that, you will be fine, until, of course, that isn't the case. It doesn't throw an error. It just truncate.

I'm not sure of the exact behavior of varchar(max) verses text, but I'm pretty sure that once you start putting a lot of them in one table, you can get some crazy performance hits. Why so many big fields in one table?