CODEWITHSUNDEEP
sqlsql-serversql-server-2008sqldatatypes
sniperd
sniperd

Reputation: 5274

SQL data type - recommendation for 'unknown' number

I'm pulling in some external data into my MSSQL server. Several columns of incoming data are marked as 'number' (it's a json file). It's millions of rows in size and many of the columns appear to be decimal (18,2) like 23.33. But I can't be sure that it will always be like that, in fact a few have been 23.333 or longer numbers like 23.35555555 which will mess up my import.

So my question is given a column is going to have some kind of number imported into it, but I can't be sure really how big or how many decimal places it's going to have... do I have to resort to making my column a varchar or is there a very generic number kind of column I'm not thinking of?

Is there a max size decimal, sort of like using VARCHAR(8000) or VARCHAR(MAX) ?

update

This is the 'data type' of number that I'm pulling in: https://dev.socrata.com/docs/datatypes/number.html#

Looks like it can be pretty much any number, as per their writing: "Numbers are arbitrary precision, arbitrary scale numbers."

Upvotes: 1

Views: 1539

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

The way I handle things like this is to import the raw data into a staging table in a varchar(max) column.

Then I use TRY_PARSE() or TRY_CONVERT() when moving it to the desired datatype in my final destination table.

The point here is that the shape of the incoming data shouldn't determine the datatype you use. The datatype should be determined by the usage of the data once it's in your table. And if the incoming data doesn't fit, there are ways of making it fit.

Upvotes: 2

devtech
devtech

Reputation: 364

What do those numbers represent? If they are just values to show you could just set float as datatype and you're good to go. But if they are coordinates or currencies or anything you need for absolute precise calculations float might sometimes give rounding problems. Then you should set your desired minimal precision with decimal and simply truncate what's eventually over. For instance if most of the numbers have two decimals, you could go with 3 or 4 decimal points to be sure, but over that it will be cut.

Upvotes: 2

Related Questions