Data Type metadata

April 19, 2010 19:24 by Matt Whitfield

So let's take a look at how the typing metadata in SQL Server really works. There are three views involved here, [sys].[types], [sys].[assembly_types] and, on SQL Server 2008, [sys].[table_types].

Each of these views share the columns from [sys].[types], so let's have a look at those first and see what is involved:

nameThis is the name of the type. For system types, you should use the name of the type by itself (for example [varchar]). For user-defined types, you should use the schema qualified name of the type (for example [Sales].[OrderNumber]).
system_type_idThis is the ID of the system type on which the type is based.
user_type_idThis is the unique ID of the type as a whole, so each system and user defined type has it's own ID. MSDN says 'for system data types, user_type_id = system_type_id', but this is incorrect. [sysname], [hierarchyid], [geometry] and [geography] have [user_type_id] values that do not equate to [system_type_id]. The reliable way to determine a user defined type is to look at the [user_defined_type] column.
schema_idThis is the ID of the schema to which the type belongs. For system types, it will always point to the [sys] schema.
principal_idThis is the ID of the database principal that owns the object. If this is NULL, then the database principal that owns the schema also owns the type, as with all schema scoped objects.
max_lengthAs with column and parameter metadata, this is the storage size of the type. It defines the number of bytes that can be stored, and the same caveats apply - i.e. that the number is half the number of characters for unicode text types, that the number is 16 for old-style LOB types and that the number is -1 for (MAX) and [xml] types.
precisionThis is the precision of the type for numeric types. This is the maximum number of digits that the type can store. There are values present for other types too, although these are largely irrelevant.
scaleThis is the scale of the type for numeric types. This is the maximum number of digits to the right of the decimal point. There are values present for other types too, although these are largely irrelevant.
collation_nameIf the type is character based, then this is te name of the collation of the type, otherwise it is NULL.
is_nullableThis is 1 if the type is nullable.
is_user_definedThis is 1 if the type is a user-defined type.
is_assembly_typeThis is 1 if the type is a CLR type, rather than a SQL native data type. Note that msdn says 'based on a SQL Server system data type' - this is misleading, because [hierarchyid], [geometry] and [geography] are system data types, but are CLR based.
default_object_idThis is the ID of the stand-alone (old-style) default bound to the type by using sp_bindefault. Note that this is a deprecated behaviour, and support will be dropped from SQL Server in a future release.
rule_object_idThis is the ID of the stand-alone (old-style) rule bound to the type by using sp_bindrule. Note that this is a deprecated behaviour, and support will be dropped from SQL Server in a future release.
is_table_typeThis is 1 if the type is a table type (SQL Server 2008 only).

So that information tells us quite a lot about the types that are available to us in SQL Server. Both assembly types and table types require a bit more information however... Let's look at the information available for assembly types first:

assembly_idThis is the ID of the assembly that contains the code for the assembly type.
assembly_classThis is the full name of the class that contains the code for the assembly type within the assembly. Note that the class name if fully namespace qualified.
is_binary_orderedThis is 1 if the data that is stored on disk could be sorted by sorting that data, rather than by instantiating the CLR type and calling the comparison operators on that type. This can be quite important for performance, because instantiation of anything but the simplest CLR types can be intensely slow.
is_fixed_lengthThis is 1 is the CLR type always takes up the same amount of space on disk, irrespective of the content. For simple struct types, this will usually be 1, but for more complex types which can contain a variable amount of data, this would usually be 0.
prog_idIf the type is COM-visible, then this is the ProgID (COM class name) of the type as COM would see it. Note that this is not the CLSID (GUID) for the COM visible type.
assembly_qualified_nameThis is the fully qualified, version-number-and-all version of the type name.

Lastly, we come to table types - which are possibly the most complicated in terms of metadata. So we expect to see a lot of extra information in [sys].[table_types], right? No, there's only one extra column:

type_table_object_idThis is the object ID for the table object to which SQL Server attaches column and constraint metadata.

What's going on here?

If we look in [sys].[objects] for the object that relates to the ID given in the [type_table_object_id], and we find that this is an object with type 'TT'. This object is the underpinning of our table type metadata, and we can find out the columns and constraints for the table type using this ID, just as we would do for existing tables. Columns we already know all about from the last post - Constraints are what we look at next time...

Tags: , , ,

metadata

Add comment



(Will show your Gravatar icon)



  Country flag

biuquote
  • Comment
  • Preview
Loading



CAPTCHA image




SQL Everywhere

Tag cloud

Calendar

<<  April 2014  >>
MoTuWeThFrSaSu
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

View posts in large calendar