SQL_VARIANT_PROPERTY() to find the info about data type?

BOL says that this function returns the base data type and other information about a sql_variant value. Have you ever looked what is datatype of GETDATE() or perhaps DB_ID() function or what is the data type of SYSTEM_USER??

DECLARE @var sql_variant
SET @var =GETDATE() --SYSTEM_USER
SELECT SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;

I found that very usefull as you can easily retun the basic info about even system objects and how SQL Server interpetes it.

Think before you use ALTER TABLE ....

Let me say you have been told to add a computed column to the very huge table in SQL Server. Well,if you have been working with SQL Server for a while you are probably aware that you will be better of using ALTER TABLE.. command instead of using SSMS because under some circumstances SQL Server will copy the entire table into a temporary one and then rename it , but if the users work on the table they will be locked... So far so good. Now , what if you have been told to add a computed column with PERSISTED clause which means SQL Server will store physically the values of the column..Here stop and think a little bit especialy you deal with huge tables. SQL Server is going to update every row in the table with computed value based on the formula you provide with. How long will it take? I have seen that adding a PERSISTED column by using ALTER TABLE command on the table with 230 million rows took 19 hours on very powerful server, can you afford it?.
So what is the solution? The solution is to add permanent column and then running an UPDATE command , however do not forget to divide the UPDATE into small batches (each one of 10000 rows for example) such as you will control the rows has been updated/affected by the UPDATE and your LOG file will not be blowing up.