-2

When defining table in database, we set column types as int / varchar etc etc. Why can't it set to auto?

The database would recognize from the input and set the type itself, much like php handles variables. Also, youtube wouldn't have to crash their stats counter.

Siyual
  • 16,415
  • 8
  • 44
  • 58
cowboysaif
  • 241
  • 2
  • 9
  • `Also, youtube wouldn't have to crash their stats counter.` - What on Earth are you talking about? – Siyual Oct 13 '16 at 14:01
  • 3
    Because SQL is a strongly typed language where the choice of a data type preserves you from putting invalid values into the database. –  Oct 13 '16 at 14:03
  • 1
    http://gizmodo.com/gangnam-style-broke-youtubes-view-counter-1666102786 @Siyual – cowboysaif Oct 13 '16 at 14:04
  • What about the capacity of the types ? Couldn't they be auto ? @a_horse_with_no_name – cowboysaif Oct 13 '16 at 14:07
  • 1
    @cowboysaif So just change the type to a `bigint` instead of an `int`, problem solved. Why create so much potential overhead trying to figure out what the type of a column should be when you can strictly define it? I don't want my database engine guessing what my intended types are, and potentially wasting a large amount of unnecessary space. I would much rather define each column, and if something breaks, fix it. – Siyual Oct 13 '16 at 14:08
  • 3
    Sure they can. And they do (at least some DBMS) –  Oct 13 '16 at 14:08
  • 1
    RDBMS'es suffer from a lot of ideological views. For example, everyone would like to sometimes store arrays in columns but no that's not relational! Not allowed. `object` typed columns would be useful but are not well supported. Seems ideological as well. – usr Oct 13 '16 at 15:57

1 Answers1

0

In some instances, you can (e.g. SQL Server's sql_variant or Oracle's ANYDATA).

Whether that's a good idea is another matter...

First of all, not having explicit schema (in the database), doesn't mean you can avoid implicit schema (implied by what your client application expects). For example, just because you have a string in the database, doesn't mean your application will be able to work with it, if it was originally implemented to expect a number. This is really an argument of dynamic vs. static typing, and static typing is generally acknowledged as superior for non-trivial programs.

Furthermore, these "variant" types often have significant overhead and are problematic from the indexing and sargability perspective. For example, is 2 smaller than 10? Yes if these are numbers, no if they are strings!

So if you know what you expect from the database (which you do in 99% cases), tell that to the database, so it can type-check and optimize it for you!

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167