Of course, you may not have a choice, if some esteemed predecessor has implemented database structures with it. In that case, you should definitely read up on it, but I just wanted to throw out one particularly useful function that I encountered yesterday while tearing my hair out trying to solve a problem with our resident SQL_VARIANT infection.
CREATE TABLE dbo.tbFoo (
ID INT IDENTITY(1,1),
That function would be SQL_VARIANT_PROPERTY(). The primary use of this function is to get the BaseType of the SQL_VARIANT column or variable, as in:
SELECT SQL_VARIANT_PROPERTY(bar, 'BaseType') FROM dbo.tbFoo
This will let you know whether your variable or column is actually being used as an int, varchar, nvarchar, etc. There are other properties that can be checked as well using this function; essentially anything that sp_help would usually give you.
Knowing the base type can be enormously helpful. For example, when you encounter code that joins the table to itself to compare SQL_VARIANT values directly...
SELECT * FROM dbo.tbFoo f1
INNER JOIN dbo.tbFoo f2
ON f1.bar = f2.bar
AND f1.ID < f2.ID
Now say you have dozens of processes inserting into this table, and they're not particularly standardized. Say one of them inserts non-unicode strings (VARCHAR) while another inserts unicode strings (NVARCHAR).
INSERT INTO dbo.tbFoo SELECT CAST('A1B2C3' as varchar)
INSERT INTO dbo.tbFoo SELECT CAST('A1B2C3' as nvarchar)
If you had CAST them in your query, they would be comparable, but without the cast, they may or may not be, and you might not get the row back that you expected.
The best fix is to avoid writing this code, and be sure to CAST your SQL_VARIANTs into the type you're expecting to use, but if you need to restore functionality to this type of code, you can update your SQL_VARIANT column base type.
UPDATE dbo.tbFoo SET bar = CAST(bar as NVARCHAR) WHERE SQL_VARIANT_PROPERTY(bar, 'BaseType') = 'varchar'
All this may or may not help you with your SQL_VARIANT problems, as there's plenty of unexplored territory even within the issues I've touched upon here. But it would have helped me yesterday. Good luck.