October 9, 2003 at 2:41 am
I'm working on a database (SQL Server 7) that stores values for historical insight.
The problem is that there are two types that can be stored, these are int and smallint.
Is there any way to define a column for storing both these two types, or use some kind of rowtype command.
I could just make the column type int, but a lot of values are stored in smallint,
so then i would waste 2 KB with every insertion of a smallint value. I could also make 2 different tables with 1 of them int and the other one smallint, but because the table with the value in it is linked with a lot of other tables (device, properties, ..) i would have to make a lot of useless changes to the other tables, whitch also waste space.
Is there any way to solve this problem?
Thanks in advance
October 9, 2003 at 3:28 am
quote:
I'm working on a database (SQL Server 7) that stores values for historical insight.The problem is that there are two types that can be stored, these are int and smallint.
Is there any way to define a column for storing both these two types, or use some kind of rowtype command.
No, a column can not have hybrid data types.
quote:
I could just make the column type int, but a lot of values are stored in smallint, so then i would waste 2 KB with every insertion of a smallint value. I could also make 2 different tables with 1 of them int and the other one smallint, but because the table with the value in it is linked with a lot of other tables (device, properties, ..) i would have to make a lot of useless changes to the other tables, whitch also waste space.
Is this really a problem???
I mean are you talking about a multi million rows table
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 9, 2003 at 3:40 am
Well, the values are logged by a scada system, whitch gets the data from an industrial process. This way an enormous amount of data is being logged. So yes, millions of rows are going to be inserted.
Off course if there is no other option, then i will make the value column type an integer.
October 9, 2003 at 3:50 am
There is a data type introduced in SQL2K called sql_variant, but it would yield you nothing, because AFAIK those variants allocates the space for the biggest possible data type. Well, at least that's what variant in a programming language do, but I think SQL Server handles things similar. In THAT case you would really waste space.
After all, disk space shouldn't be a problem, and integers are fast to sort and search.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 9, 2003 at 4:11 am
Indeed, sql_variant is no option because of the waste of disk space and because it's not supported in SQL Server 7.
I'm just going to stick with the int type then.
Thanks for your help Frank
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy