How smart should datatype detection be?

[vegitron]

(Question for SQLSHR-229)

In the Beta version of SQLShare, we try to guess the type of data a column is in, so that a column of integers can reliably have integer operations performed on it. I'm hoping to expand that to things like dates, and other complex types.

But - it doesn't always work out. Taxonomy IDs for example look like numbers, but aren't.

I have two ideas for addressing this:

1. give up on smart detection, and just make everything nvarchar(max), and let users cast as needed.
2. create 2 columns when it's detected as not-text. 1 being the detected type, and the other being an nvarchar(max) column of the original data.

Do either of those sound good, or is there another approach that should be taken?

[chickadeedee]

An example of the data mentioned below: http://help.ebird.org/customer/portal/articles/1006825-the-ebird-taxonomy]?__hstc=161696355.34d71ca734ac82b10238c55c37b90801.1438636656915.1438636656915.1438636656915.1&__hssc=161696355.2.1438636656916&__hsfp=950436786#_ga=1.222533266.885133027.1438636656http://help.ebird.org/customer/portal/articles/1006825-the-ebird-taxonomy%5d?__hstc=161696355.34d71ca734ac82b10238c55c37b90801.1438636656915.1438636656915.1438636656915.1&__hssc=161696355.2.1438636656916&__hsfp=950436786#_ga=1.222533266.885133027.1438636656

From: Patrick Michaud [mailto:notifications@github.com]
Sent: Monday, August 03, 2015 9:27 AM
To: uwescience/sqlshare sqlshare@noreply.github.com
Subject: [sqlshare] How smart should datatype detection be? (#66)

(Question for SQLSHR-229)

In the Beta version of SQLShare, we try to guess the type of data a column is in, so that a column of integers can reliably have integer operations performed on it. I'm hoping to expand that to things like dates, and other complex types.

But - it doesn't always work out. Taxonomy IDs for example look like numbers, but aren't.

I have two ideas for addressing this:

1. give up on smart detection, and just make everything nvarchar(max), and let users cast as needed.
2. create 2 columns when it's detected as not-text. 1 being the detected type, and the other being an nvarchar(max) column of the original data.

Do either of those sound good, or is there another approach that should be taken?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/66.