How numbers are stored in SQL

Question

Post reply

How numbers are stored in SQL

naresh1253

Right there with Babe

Points: 727
More actions
September 8, 2009 at 11:37 pm

#138688

Hey I am new to this forum, hope i am posting my query at the right place.
I am using uint datatype in c# however to save storage i am converting them into int and storing them in SQL as both uint and int are of 4 byte excepts that int can store -ve values. so any positive uint value which is over the range of int is converted as -ve value and is stored, while retrieving it i convert it into +ve uint value. Now my questing is, why the following two commands give different outputs, please help me understand.
1. select convert(bigint,convert(binary(8),3000000001))
2. DECLARE @i BIGINT = 3000000001
select convert(bigint,convert(binary(8),@i))
Thanks in advance.
Naresh

Viewing 6 posts - 1 through 5 (of 5 total)

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

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 1

select convert(bigint,convert(binary(8),3000000001))

DECLARE @i BIGINT = 3000000001

select convert(bigint,convert(binary(8),@i))

in this case sql server has some problems with guessing datatypes - if you will use smaller number i.e. 1234567890 both queries return same values.

Paul White SSC Guru Points: 150467 More actions · Answer 2

Naresh,

I am confused: you talk of storing 4 byte unsigned integers and show an example using 8-byte big integers.

Are you planning to store the unsigmed integers in a SQL Server INT, BINARY(4), BIGINT, or BINARY(8)?

Are you certain that the space savings are worth it?

Have you considered using the vardecimal compression in 2005? (deprecated and 'replaced' in 2008 by ROW/PAGE level compression)

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

naresh1253 Right there with Babe Points: 727 More actions · Answer 3

hmm...actually while playing with the data i accidentally found the scenario(the example which i have given), so my actual question is why the below two cmds are giving different outputs.

1. select convert(bigint,convert(binary(8),3000000001))

2. DECLARE @i BIGINT = 3000000001

select convert(bigint,convert(binary(8),@i))

Thought i found that casting the number as bigint in the first cmd gives the correct output

select convert(bigint,convert(binary(8),cast(3000000001 as bigint)))

so is the value taken as a string if we don't cast it?

-Naresh

Paul White SSC Guru Points: 150467 More actions · Answer 4

So when you write 'select convert(binary(8), 3000000001)' what data type are you expecting the constant to be?

As someone who uses C#, you should know better than to reply on implicit conversions of constant values.

If you really want to know the type SQL Server gives it implicitly, try:

use tempdb

select a = 3000000001

into #a

exec sp_help #a

drop table #a

The data type for column 'a' is numeric(10,0)...and SQL Server doesn't have a 'string' datatype.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

naresh1253 Right there with Babe Points: 727 More actions · Answer 5

thanks for the reply Paul.

and by 'string' datatype(in c#) i meant 'varchar or nvarchar' in SQL.