Tuesday, March 27, 2012

Difference between max length and length in DataType Schema

Using OleDb I can retrieve the Database Native DataTypes Schema with LoadSchema("DataTypes")

In the CreateParameters column some variable fields such as varchar have CreateParametera of 'max length' while nchar has a CreateParameters of 'length'.

Can someone differentiate between 'length' and 'max length' for me?

TVM

BOL: "nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The SQL-2003 synonyms for nchar are national char and national character.

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying."

so max length for nchar is 4,000.

for varchar(max) max length is 2^31-1 bytes depending on platform.

Thanks.

No comments:

Post a Comment