Wednesday, March 5, 2014

Efficient ways to select rows from sys.partition_range_values or from sql_variant data type column

Often we select records with below statement.
Select * from XYZTable
But it may not work for sys.partition_range_values, when partition scheme is created with different data ranges, like numberic (int, bigint etc...) or datetime in same database. Select query
execution result will end up with error output.
SELECT * FROM sys.partition_range_values

Output will be.
An error occurred while executing batch. Error message is: Input string was not in a correct format.

Why it is like this, error in SQL server Management Studio? Answer is NO, below is the schema structure of the system table of sys.partition_range_values, here sql_variant data type can store value with any data type, hence while retrieving SQL doesn’t know what data-type casting is needed.

So what are the ways to retrieve records?  Here are the ways…..
1.       Write below query to simply convert data in varchar…
SELECT function_id
,boundary_id
,parameter_id,
CAST(Value AS varchar) As Value FROM sys.partition_range_values
But it may not get the data in form in it is required.
2.  Determine the base type of target data which will be stored as sql_variant with the help of SQL_VARIANT_PROPERTY, after that write down select case statement to fetch the data with the specific data type.
       SELECT function_id
,boundary_id
,parameter_id,SQL_VARIANT_PROPERTY(Value,'BaseType') as DataType,
       CASE SQL_VARIANT_PROPERTY(Value,'BaseType')
         WHEN 'datetimeoffset' THEN CAST(Value AS datetimeoffset) END AS Value
FROM sys.partition_range_values
In above sample query datetimeoffset has been demonstrated, it can be replaced with bigint, int, time, nchar etc..


This can apply to any table which is using sql_variant data type, one thing to know here is to use system function SQL_VARIANT_PROPERTY that will help to differentiate the data based on its type, even the return value can be given to front-end[.Net, Java etc..] if need to segregate the  data based on their type.

No comments:

Post a Comment