Can T-SQL store ulong's?

Question

I want to store a C#.NET ulong into a T-SQL database. I don't see any provisions for doing this, as the SQL bigint has the same Min/Max values as a normal long.

Is there any way I can do this? Or is catching an OverflowException my only hope?

Ludwigshafen

See this answer which tells you to use DECIMAL(20,0) CHECK (columnName >= 0 AND columnName <= 18446744073709551615). – Shults

Answer 1

This should answer your question:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/ff08c190-a981-4896-9542-3f64b95a84a2/

You would use BigInt, you just have to be careful in how you convert the signed type back into an unsigned type in C#

// This has not been tested
unchecked
{
    myUlong = myDataReader.GetInt64(...);
}

...

The other possibility is to use VarBinary with a length of 8, then convert the bytes to a ulong in C#

Forsaken

@Robert Harvey: I posted a short example – Forsaken

Do you mean treating long.MinValue as 0 and long.MaxValue as ulong.MaxValue? – Ludwigshafen

Is the behavior for the signed bit defined properly here? If you don't want to lose precision, you have to use the sign bit as the additional high-order bit in the ulong. Will this do that? – Motoneuron

@Robert Harvey: thats why it's unchecked. High order bit doesnt matter to ulong, it just wants the 8 full bytes. – Forsaken

bigint cannot store ulong as that is basically long. ulong has higher range. varbinary is the right answer. – Crosshatch

@Mrchief, it doesnt have anything to do with the range, it's the number of bits. Notice the code above converts from unsigned to signed. – Forsaken

Well, try inserting 9223372036854775808 (long.Max + 1) in a bigint. You'll be greeted with a "Arithmetic overflow error". That's what I mean by range. That number is valid ulong but can't be stored in bigint. Makes sense? – Crosshatch

@Mrchief, which is why you convert it to signed first. 9223372036854775808 converted to signed is -9223372036854775808, which inserts into a bigint field just fine. You just have to remember to convert it again on the way back out of the database. Ulong.MaxValue (18446744073709551615), converted to signed, is -1, which also inserts into BigInt just fine ;) – Forsaken

I used UInt64 as a variable's type and the type of the corresponding column in DB is BIGINT. It is showing the following error: The parameter data type of UInt64 is invalid. Can this be sorted out or I need to change UInt64 to Int64? – Ben

@barnes: did you remember the "unchecked" code block? – Forsaken

Answer 2

I know it's not the same, but would this do:

select convert(decimal(38, 0), 12345678901234567890123456789012345678)

The maximum for an unsigned long seems to be 18,446,744,073,709,551,615 which is significantly less than this decimal can store. There might be issues converting, but I'm sure that a couple of wrapper functions in your application would sort it pretty quickly.

Scan

Good approach for accommodating the range of a ulong, however be aware that sql reports that decimal(38,0) takes up 17 bytes. – Photoperiod

Are you saying it's oversize for the amount of data we want to store? – Scan

@Johnathan: I think so. decimal(38,0) is more than double the size of bigint (2.125 times, actually) . If space was an issue, I'd be a little concerned. – Ludwigshafen

@Ludwigshafen The maximum value of an unsigned long is 18,446,744,073,709,551,615, meaning you only need DECIMAL(20, 0). This takes 13 instead of 17 bytes. The advantage of DECIMAL over BIGINT is that it will preserve the semantic meaning of the numbers you are storing. Any other application, ETL, report, or human that looks at the numbers will understand them. From a data architect's perspective, that justifies the storage penalty. The BIGINT, on the other hand, will present negative numbers (i.e. lies) to the reader. – Arnica

Answer 3

For me Neil N's solution didn't work. Visual Studio kept complaining that I can't assign a long value to a ulong variable, no matter the unchecked.

What did work in the end was simply

ulong fileSize = (ulong)(long)reader["fileSize"];

(withoutunchecked).

In the SQLite DB the value is stored as long, so very high values become negative and sorting them on SQL-side fails. But after getting the ulong values Linq can do its magic

items = items.OrderByDescending(x => x.FileSize).ToList();

Homophonous