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.

Difference between dataTypes in SQL 2005?

What is the difference between binary and image dataType.

When and how should I use them?Hello,

Here is difference between binary and image data type.
BINARY: Fixed-length binary data with a maximum length of 8,000 bytes
IMAGE: Variable-length binary data with a maximum length of 2^31 - 1 bytes

You can use them accordingly...

Diff Datatypes used doubt int and bigint ........SQL SERVER 2005......Any useful links ?

Hello Frdz,

I have doubt regarding the datatypes fields used in SQL SERVER 2005.

The value for bigint Int64 is 18

The value of int Int32 is 9/10

Now,if in int i write : 1234567890 (accepted)

This gives error : 9874565656 (not accepted........why is it so ? )

Why is it so ??

I want to know the perfect size of all the datatypes used in SQLSERVER 2005.

There are also smallint,tinyint....

What's the main difference with all of them ??

Can anyone provide me the nice links which can explain me what m i asking in this post...

Please help me...I want to know all the datatypes used differences...

you cannot store 9874565656 in an int as it exceeds the max value for an int which is: 2,147,483,647

http://msdn2.microsoft.com/en-us/library/ms187745(SQL.90).aspx

|||

thanxs...i think it's helpful link..

I want to also know that in SQL SERVER 2005 can we assign or fix manually the values upto the limit...like,

Id1 int - 4

Id2 bigint - 10

like in varchar we can do

Name Varchar(20) if we take varchar(50) as datatype...

Hope u understand what i ask...this questions are not solved in my mind...

Please help me...

Thanxs again....

|||

varchar is a variable length datatype and you are allowed to set its length

you cannot do that with the integer datatypes.

|||

If you really intend to limit the value <= 4 digits ( = 9999) you can create a constraing on the column to make sure the value <= 9999.

|||

You can also create your own datatype that is based on an integer value type that can store all your possible values and has a constraint.

This may help:

http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/07/426930.aspx

More complex datatype needs can be done via a CLR UDT, but I'm not sure how well they perform. Like:

http://www.devx.com/dotnet/Article/22644

|||

thanxs all of...

I think it's better to make a constraint......

Nice answers to clear my doubt...