0

A database I'm investigating has defined in it a view admin.View_PartitionRanges, which is heavily based on the view PartitionRanges to be found in this MSDN blogpost from 2009. The view definition there is follows:

CREATE VIEW [dbo].[PartitionRanges] AS
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       1 as [partition_number],
       case when prv.value is null then NULL else '<' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = 1 
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = 1
 where pf.boundary_value_on_right = 1
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       prv.boundary_id + cast(pf.boundary_value_on_right as int) as [partition_number],
       case when pf.boundary_value_on_right = 0 then '<=' else '>=' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_range_values prv on
       prv.function_id = pf.function_id and prv.parameter_id = 1
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id = prv.boundary_id + cast(pf.boundary_value_on_right as int)
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       pf.fanout as [partition_number],
       case when prv.value is null then NULL else '>' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = pf.fanout 
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = pf.fanout - 1
 where pf.boundary_value_on_right = 0
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       NULL, NULL, NULL, NULL,
       fg.name as [filegroup],
       case when dds.destination_id = pf.fanout + 1
            then N'NEXT USED' else N'NOT USED'
       end as [status]
  from sys.partition_functions pf
  join sys.partition_schemes ps on ps.function_id = pf.function_id
  join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id > pf.fanout
  join sys.filegroups fg on fg.data_space_id = dds.data_space_id 

However, when I perform a SELECT * on this view in SSMS, I get the error

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

What's wrong?

1

The problem lies down in one of the sources used by that view, the system view sys.partition_range_values. The Value column of this view is of type SQL_VARIANT, which can lead to problems with indiscriminate SELECT *s, because SQL has to pick a specific type for the resultset, which all the values then need to be coerced to (Thanks to this blog post for this insight).

You can see for yourself if this is what's causing your problem, by doing

SELECT
    function_id
    , boundary_id
    , parameter_id
    , SQL_VARIANT_PROPERTY(Value, 'BaseType') AS DataType
FROM
    sys.partition_range_values
;

If you have multiple different DataTypes, and you can imagine there might be problems casting values between them, then that's the problem.

To fix, you can update your helper view so that instead of

prv.value as [boundary_value]

instead you have

CASE SQL_VARIANT_PROPERTY(prv.Value,'BaseType')
WHEN 'datetimeoffset' THEN CAST(CAST(prv.Value AS datetimeoffset) AS varchar(30)) 
WHEN 'smallint' THEN CAST(CAST(prv.Value AS smallint) AS varchar(30)) 
-- etc as necessary for the types used to partition on in your database
ELSE SQL_VARIANT_PROPERTY(prv.Value,'BaseType')
ELSE 'Value of type ' + CAST(SQL_VARIANT_PROPERTY(prv.Value,'BaseType') AS varchar(15))
END 
    AS boundary_Value

This will give you appropriate textual representations of the partition range values.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.