1

I was looking at system tables and saw sql_variant datatype for some of the columns. I have never seen any column with sql_variant datatype in production databases. Is there any reason for this.

Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286
  • Perhaps due to the overhead of having to explicitly cast to a different datatype in order to compare to other columns/values? – Bridge Nov 27 '12 at 14:42
  • 2
    [Bad Habits to Kick: Using sql_variant at all](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type): *Several external tools don't support it (e.g. entity framework), some providers like ODBC automatically convert the values to NVARCHAR(4000), and you can't use the column in the definition of a computed column. It also can't be part of a primary or foreign key, nor can you use wildcard operators such as LIKE. And even if you only store numbers, you cannot use operators like SUM(), MAX(), addition or subtraction.* – Remus Rusanu Nov 27 '12 at 14:44
  • 3
    From a relational purists' perspective, each column should contain data of the same type. `sql_variant` works away from this. Not that I'm claiming the all or even most SQL developers *are* relational purists... – Damien_The_Unbeliever Nov 27 '12 at 14:49
  • Then why microsoft introduced sql_variant data type. Is it just to make database open for changes? – Shantanu Gupta Nov 27 '12 at 14:52
  • @Damien_The_Unbeliever Well they should be :) – podiluska Nov 27 '12 at 14:52
  • See also this question: http://stackoverflow.com/questions/9770315/using-anydata-sql-variant-in-fact-table/9770625#9770625 – Pondlife Nov 27 '12 at 15:07

0 Answers0