How to create custom data types in SQL Server 2008?

Question

I'm trying to create an unsigned short data type in SQL Server, but so far all I've managed is to get it to not allow any negative values, which wastes half of my storage space. There should be some way to create a custom data type where I manage it myself. I'd like to be able to tell the system that the data type has two bytes, and to read the value back in a certain way so that it reads it as a ushort. Any ideas?

Final Solution:

Ok, Nathan's answer below helped me to find a workable solution. So when I store my unsigned short value to the database, I just need to cast it as a signed short. The first half of the values will be correct. If the value is negative, I just need to add 65536 to the number. The final select case looks like:

SELECT case when i >= 0 then i else 65536+i end
  FROM MyDatabase.dbo.testDB

This will properly display my incorrectly cast unsigned short. Thanks for the help.

@Quantum Elf: I'd be interested to know if anyone has tried this and whether they think in retrospect it is worth the pain of complexity e.g. null handling. — onedaywhen
– onedaywhen, Commented Oct 3, 2011 at 10:43

nathan_jr · Accepted Answer · 2011-10-01 21:35:40Z

1

Edit: Upon re-read I see you're looking to emulate an unsigned short (0 to 65,535) in a 2 byte datatype. I'm not seeing a way given the native types in MSSQL, unless you went with the smallint and performed the math yourself.

Is this just an exercise or are you hyper focused on storage space? Space is cheap!

edit: to show example of smallint adjusted:

drop table dbo.yak

create table dbo.yak (i smallint identity(-32768, 1), n varchar(1));
go

insert into dbo.yak(n)
    values('a'),('b'),('c');
go

select i, (i+32768)+1, n 
from dbo.Yak

edited Oct 1, 2011 at 21:35

answered Oct 1, 2011 at 3:44

nathan_jr

9,3023 gold badges43 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TomTom Over a year ago

I hate totel lyou - space is not cheap. Maybe his one byte turns into 100gigabyte (does on some data I have) and then space = IOPS ;) Larger space = more IO needed which is more money.

nathan_jr Over a year ago

Thats why I asked if he was focused on space for a reason or was just pursuing an exercise. If you're dealing with a VLDB then every byte counts ;)

Davido Over a year ago

We are capturing the output from 32 sensors at 1000hz, 8 hours at a time. So each time we capture our data, we would save about 921Mb of storage by using two bytes instead of four (assuming I did my math right...) I'll let you know as soon as I try your suggestion how it works out.

Davido Over a year ago

FYI, your solution lead me to a final solution, which I appended to my original post above.

Collectives™ on Stack Overflow

How to create custom data types in SQL Server 2008?

Final Solution:

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Final Solution:

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related