Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
just to be clear, are you suggesting that you define all your table columns to be varchar(max), so that when you create stored procedures you can also define the variables as varchar(max)? –  SQLRockstar Mar 16 '11 at 16:20
    
also, i found this at SO: stackoverflow.com/questions/148398/… –  SQLRockstar Mar 16 '11 at 16:22
    
i don't think anyone is saying Oracle is 'wrong', but i do think I understand that it is different. i will see if i can formulate an answer, as i believe i now understand more about your question. –  SQLRockstar Mar 17 '11 at 2:54
add comment

3 Answers 3

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.

share|improve this answer
    
The calling client is prepared check for errors which occur inside the stored procedure. It must support Oracle too which can't check parameter length on call. –  bernd_k Mar 16 '11 at 18:42
add comment
  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
share|improve this answer
    
Do your arguments 1, 4 hold when I choose varchar(8000) as general parameter data type? I don't understand 2 in context of procedure parameters. 3 This error must be thrown and handled by the client –  bernd_k Mar 16 '11 at 20:09
    
@bernd_k: points 2, 3, 4 –  gbn Mar 16 '11 at 20:22
add comment

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.

share|improve this answer
    
You didn't get my point. See my edit above. I'm speaking of the length I have to specify in sql server procedure parameters @code varchar(30). In Oracle I would declare the parameter just as p_code varchar2. –  bernd_k Mar 16 '11 at 17:36
    
Yes, sorry, I was more focusing on the table columns and missed the SQLServer part. Boundaries can help for sanity checking purposes, input validation. I don't know how this works in SQLServer. –  ik_zelf Mar 16 '11 at 20:05
    
I tried to Google for Oracle and Boundary, but found nothing. Please give a link. –  bernd_k Mar 16 '11 at 20:20
    
It is more about sanitizing and untainting, in general ruby.about.com/od/advancedruby/a/tainted_3.htm –  ik_zelf Mar 20 '11 at 10:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.