SQL Server Performance Forum – Threads Archive
SQL Variant
Hi- We have a situation where we need to store multiple data types in the same column. And I was thinking of sql_variant data type. Was wondering will there any performance issues or disk usage issues that I need to be aware of before using sql_variant. I am concerned about both these issues because the number of records in this table could be close to 50 -60 millions. Thanks for your timeHere is a tip from this site- Avoid using the SQL Server 2000 sql_variant datatype. Besides being being a performance hog, it significantly affects what you can do with the data stored as a sql_variant. For example, sql_variant columns cannot be a part of primary or foreign keys, can be used in indexes and unique keys if they are shorter than 900 bytes, cannot have an identity property, cannot be part of a computed column, must convert the data to another datatype when moving data to objects with other datatypes, are automatically converted to nvarchar(4000) when accessed by client applications using the SQL Server 7.0 OLE DB or ODBC providers, are not supported by the LIKE predicate in the WHERE clause, cannot be concatenated, and don’t work with some functions. [2000] Added 11-8-2000 http://www.sql-server-performance.com/datatypes.asp Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Thanks for your response Gaurav. I will go through the document and see if I can avoid SQL Variant. Thanks for the help
]]>