How to Select max Value for a DataType

Question

Post reply

How to Select max Value for a DataType

raja_saminathan

SSCommitted

Points: 1607
More actions
September 20, 2008 at 1:06 am

#193138

Hi All,
Is there any way to know the Max Capacity for a declared DataType programatically?
can we get the max Value for a SQLDataType using Select statement?
Thanks and Regards,
Rajesh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Not that I know of... closest you might come is...

sp_Help BIGINT

... where BIGINT is the datatype.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

You might want to read the description for SYS.SYSTEM_COLUMNS catalog view in Books On Line. It contains the max_length for columns expressed in bytes. It does not have values for VARCHAR(Max) or for XML columns, so in that regard is not complete. The value for a TEXT type column will be returned as 16, but you can use sp_tableoption 'test in row' to determine that capacity. In this regard it might be a little better than SYS.TYPES catalog view. But it might suffice for what you need. Also check the SYS.TYPES catalog view for the same information.

Sorry but this is the best I can do...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Not 100% sure, but I don't believe that's what the OP is looking for. I believe they want to go someplace through T-SQL to figure out what the max for a given datatype is. For example, they want to go someplace to figure out the INT is capable of a binary 2 billion. Only way I can figure out to do that is to look in Books Online and make a table with the given mins and maxs for each datatype.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 4

Can you get it from systypes?

select * from master.sys.systypes

I'm not quite sure if that's what you're meaning in your question.

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

That will certainly give you the number of bytes the datatype can or does occupy. But it will not give you the min or max values without prior knowledge of the datatype and writing code to produce that. And, in the case of datatypes like DATETIME, there is no way to establish the minimum value of 01/01/1753 based on just the number of bytes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

raja_saminathan SSCommitted Points: 1607 More actions · Answer 6

Thanks EveryBody,

So, I think we have to take the Max.Values manually for the Given DataType and then we can use it for other operations like comparing the value of the column whether it is within the maxValue of the DataType before inserting it into table.

Regards,

Rajesh

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 7

Jeff Moden (9/21/2008)
Only way I can figure out to do that is to look in Books Online and make a table with the given mins and maxs for each datatype.

This is what exactly what I wanted to suggest 🙂

Madhivanan

Failing to plan is Planning to fail