Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Question

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time.

Increasing just the size of a column in a table is a rather simple task.

But when your shop adheres to the rule that the length of stored procedures parameters has to match the length of the corresponding table columns, an avalanche of changes through your stored procedures goes down hill.

Locking at Oracle shows, that procedures don't need to know about the length of varchar2 parameters.

What are the pros and cons, when you just declare each varchar parameter as varchar(max) or nvarchar(max)?

Obviously you can forget the maintenance nightmare of having to adjust your procedures each time you increase your column sizes.

Answer to SQLRockStar: I want to keep a rather conservative table design, where fields, with a known limited length are modeled according to the current restrictions without reserve for possible future extensions.

Many of my tables contain an id int, a code varchar(30) and a description column varchar(255).

The code field has to reflect local naming conventions of different customers.

Some costumers need this field increased to 50.

When I do not code the length into the procedure parameters, I need only adjust the table column of this single costumer.

When I insist to code the length into the parameters, then I have either to support different versions of procedures for different customers, or I have to change and roll out the change to all customers.

Example

-- SQL Server
Create Table dbo.SQLServerTable (
    id int identity(1,1) primary key not null,
    code varchar(30) not null,
    description varchar(255) not null
);
go
Create Procedure WrapInsertSQLServer1 (
    @code varchar(30),
    @description varchar(255)
) as
INSERT INTO SQLServerTable values (@code, @description);
go      

Create Procedure WrapInsertSQLServer2 (
    @code varchar(100),
    @description varchar(1000)
) as
INSERT INTO SQLServerTable values (@code, @description);
go      

Note: When you increase the length in SQLServerTable by some character, you have to adjust WrapInsertSQLServer1, but WrapInsertSQLServer2 doesn't need to be changed.

-- Oracle
Create Table OracleTable (
    id NUMBER(10) primary key not null,
    code varchar2(30) not null,
    description varchar2(255) not null
)
/
CREATE SEQUENCE S_OT START WITH 1
/

Create OR Replace Procedure WrapInsertOracle (
    p_code varchar2,
    p_description varchar2
) as
begin
INSERT INTO OracleTable values (S_OT.NEXTVAL, p_code, p_description);
end;
/   

Here the parameters of a stored procedure never have a length.

If you say WrapInsertSQLServer2 isn't OK => you are saying Oracle is inherently wrong

Answer 1

Since your talking about the proc params:

CREATE PROCEDURE myProc
  @var1 varchar(30),
  @var2 varchar(max)
AS
...

If the parameters are defined as the correct length, SQL will throw an error when calling the procedure. If you always define as (max) then you'll have to manually handle passed parameters that are too long for the destination table column before any processing in the proc happens.

So in a nutshell, more work on your end since you'll have to add error handling to the proc you didn't need before.

Answer 2

1. Performance: Max types are LOB types = slower. Demonstratably so.

2. Indexing: key length is 900 bytes = never able to have index or unique constraints

3. Truncation: insert a Max value into, say, a 20 length field = error

4. Datatype precedence: compare a 20 length field to Max variable = implicit conversion = no use of indexes etc

Arguably, you should change lengths very infrequently. It's a design error...?

Edit:

• Points 3 and 4 apply to your specific complaint that "In Oracle I can do this..."
• Points 1 and 2 apply if you want to match Max types in the columns too

Answer 3

The biggest con's are those where those fields are misused for other purposes than where they were meant for. Normally dimensions are here for a reason. The question looks a bit like what often happened in the old COBOL days where developers asked: can't you just give 1 record type with 1 field of type character with a large size. Doing (the old cobol filler field way) so will make the meaning of a relational database less clear. An other one is: what about programmatic errors that are adding length to strings, without noticing. This will end up lots of extra space. Storage is still at a cost. If developers make no errors and the usage remains as is defined, I see no harm in setting length of [n]varchar2 fields a bit higher than currently needed. Especially if it is foreseeable that the size of the contents will grow, defining that upfront will save a lot of time.

Answer 4

I think you should use field types and use these field types when creating tables and SP-s.

CREATE TYPE [dbo].mycodefield FROM [varchar](10) NULL
GO
create table mytable (code mycodefield, numb int)
go
create procedure mySP(@code mycodefield, @num int) as
begin
 insert into mytable(code, numb)
 values(@code, @num)
end
go
exec mysp 'abc',11