Should I use SQL_Variant data type?

by

10 reasons to explicitly convert SQL Server data types

As a general rule, you should avoid using SQL Server’s sql_variant
data type. Besides being a memory hog, sql_variant is limited:

  • Variants can’t be part of a primary or foreign key. (this doesn’t hold as of SQL Server 2005. See update below)
  • Variants can’t be part of a computed column.
  • Variants won’t work with LIKE in a WHERE clause.
  • OLE DB and ODBC providers automatically convert variants to nvarchar(4000) — ouch!

To avoid problems, always explicitly convert sql_variant data types as
you use them. Use any method you please, just don’t try to work with
an unconverted sql_variant data type.

I haven’t used sql_variant before but with these restrictions and performance implications in mind, I would first look at alternatives.

Following would be my most to least prefered solution

  • Simply create three different columns. 3 Different data types (should) mean 3 different ways of interpreting it both at the client side and server side.
  • If that is not an option, use a VARCHAR column so you can at least use LIKE statements.
  • Use the sql_variant data type.

Edit Cudo’s to ta.speot.is

Variants can be part of a primary of foreign key

A unique, primary, or foreign key may include columns of type
sql_variant, but the total length of the data values that make up the
key of a specific row should not be more than the maximum length of an
index. This is 900 bytes

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. Select top 10 records for each category
  3. How important is the order of columns in indexes?
  4. How to create multiple one to one’s
  5. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  6. sql query to return differences between two tables
  7. SQL Server – transactions roll back on error?
  8. Difference between a User and a Login in SQL Server
  9. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  10. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  11. Insert Picture into SQL Server 2005 Image Field using only SQL
  12. Execute Stored Procedure from a Function
  13. How do I group on continuous ranges
  14. Should I design a table with a primary key of varchar or int?
  15. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  16. Restrict an SQL Server connection to a specific IP address
  17. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  18. SQL Server PIVOT perhaps?
  19. Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
  20. How to escape square brackets inside square brackets for field name
  21. How do you copy a record in a SQL table but swap out the unique id of the new row?
  22. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  23. How do I find the data directory for a SQL Server instance?
  24. Add a row number to result set of a SQL query
  25. Compare dates in T-SQL, ignoring the time part
  26. Is there a severe performance hit for using Foreign Keys in SQL Server?
  27. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Backup SQL Schema Only?
  30. Hierarchical Queries in SQL Server 2005

Leave a Comment