getting max value of a TYPE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

getting max value of a TYPE

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-27 : 08:21:35

if i have type INT how can i get using a select its max value?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 08:35:42

[code]select max(your_int_col) from yourtable[/code]

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-27 : 08:39:27

your didnt understand me
i didnt want a column name but rather TYPE INT !! OR TYPE SMALLINT
whats there max value

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-27 : 09:05:12

Select column_name, data_type from information_schema.columns
where table_name='table' and data_type='Bigint'
Union all
Select column_name , data_type from information_schema.columns
where table_name='table' and data_type='int'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-27 : 09:12:38

quote:
Originally posted by pelegk2

your didnt understand me
i didnt want a column name but rather TYPE INT !! OR TYPE SMALLINT
whats there max value

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Sorry about this

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 09:16:24

quote:
Originally posted by pelegk2

your didnt understand me
i didnt want a column name but rather TYPE INT !! OR TYPE SMALLINT
whats there max value

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

did you mean to say what is the max values this datatypes can store into it..

FROM BOL..

quote:

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-04-27 : 17:19:31

execlly chiragkhabaria!!!
but i want todo this using a select syntax

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-27 : 17:50:04

quote:
Originally posted by pelegk2

execlly chiragkhabaria!!!
but i want todo this using a select syntax

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

You want to query SQL Server Books Online?


Select
	*
from
	[SQL Sever Books Online].[Data Types]
where
	[Data Type] = 'int'

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-27 : 17:55:01

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by pelegk2

execlly chiragkhabaria!!!
but i want todo this using a select syntax

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

You want to query SQL Server Books Online?

Select
*
from
[SQL Sever Books Online].[Data Types]
where
[Data Type] = 'int'

LMAO!

Tara Kizer
aka tduggan

sorincom

1 Post

Posted - 2008-08-01 : 07:34:08

I guess this question was rather related to how to dynamically get the current max value for a given datatype (like int), knowing limits might change with server versions (BOL = hardcoded). This could be used for ex. to safely make a comparison like this:

where
col_int <= isnull(@param, max(int))

... which could be rewritten, though, as

where
(@param is null) or (col_int <= @param)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-01 : 10:16:03

INT will not change, like in VB for a new version or so. First INT was 16-bit in VB, then turned 32-bit with version ... 4? along with 32-bit operating system windows 95.

BIGINT was introduced into SQL Server to maintain INT as signed 32-bit integer.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-01 : 10:34:33

INT only changed from VB6 to VB.NET, to put it in line with the entire CLR. Of course, hundreds of other things also changed when they made VB into a "real" language (finally!).

So, in short, the max size of data allowed for data types should be considered a constant.

However, if you want to be able to SELECT this from somewhere, then store the values in a table in your database somewhere. Then if you migrate the DB to a new version of SQL Server years from now and for some reason something does ever change, you simply update your table.

- Jeff
http://weblogs.sqlteam.com/JeffS

weitzhandler
Yak Posting Veteran

64 Posts

Posted - 2009-04-16 : 05:32:09

I would suggest you to declare glabal constant variables and / or have a function to expose them by parameters type-name and bit (for min or max) with a case statement.

Shimmy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources