Logo 
Search:

SQL Server Articles

Submit Article
Home » Articles » SQL Server » Meta Data FunctionsRSS Feeds

SQL_VARIANT_PROPERTY Function

Posted By: Sarita Patel     Category: SQL Server     Views: 4965

This article explains about SQL_VARIANT_PROPERTY function in sql server with examples.

SQL_VARIANT_PROPERTY function is used to get information about base data type and sql_variant value.



Syntax of SQL_VARIANT_PROPERTY Function :

SQL_VARIANT_PROPERTY( expression, property )

expression is a valid sql expression of type sql_variant.

property is a sql_variant property name. It is of type varchar(128). It can be of below values.

Value

Description

Base type

BaseType

The SQL Server data type, such as:

char
int

money
nchar
ntext
numeric
nvarchar
real
smalldatetime
smallint
smallmoney
text
timestamp
tinyint
uniqueidentifier
varbinary
varchar

sysname

Invalid input = NULL

Precision

The number of digits of the numeric base data type:

datetime = 23
smalldatetime = 16
float = 53
real = 24
decimal (p,s) and numeric (p,s) = p
money = 19
smallmoney = 10
int = 10
smallint = 5
tinyint = 3
bit = 1
all other types = 0

int

Invalid input = NULL

Scale

The number of digits to the right of the decimal point of the numeric base data type:

decimal (p,s) and numeric (p,s) = s
money and smallmoney = 4
datetime = 3
all other types = 0

int

Invalid input = NULL

TotalBytes

The number of bytes required to hold both the meta data and data of the value. This information would be useful in checking the maximum side of data in asql_variant column. If the value is greater than 900, index creation will fail.

int

Invalid input = NULL

Collation

Represents the collation of the particular sql_variantvalue.

sysname

Invalid input = NULL

MaxLength

The maximum data type length, in bytes. For example,MaxLength of nvarchar(50) is 100, MaxLength ofint is 4.

int

Invalid input = NULL


Return type of SQL_VARIANT_PROPERTY function is a sql_variant.




Examples of SQL_VARIANT_PROPERTY Function :

Example 1 : Use of SQL_VARIANT_PROPERTY function in select clause

SELECT  SQL_VARIANT_PROPERTY (OrderID, 'BaseType')
FROM     Orders
WHERE  OrderID = 10248

Output
float

Above example returns data type of OrderID column of Orders table.
  
Share: 

 

Didn't find what you were looking for? Find more on SQL_VARIANT_PROPERTY Function Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of SQL_VARIANT_PROPERTY Function is from United States.
 
View All Articles

Related Articles and Code:


 
Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

 
No Comment Found, Be the First to post comment!