System Views in SQL Server Compact Edition: Provider Types

The last view we’ll discuss is the INFORMATION_SCHEMA.PROVIDER_TYPES. Strictly speaking, this does not provide a view into your particular database, but into SQL Server Compact Edition. As such, it probably won’t be very useful in day to day queries into the database.

What it may be useful for is in automating database code generation. This table lists all of the valid database types that SQL Server CE supports. Presumably if Microsoft chooses to add new data types to SSCE they will show up here.

Let’s take a look at the query that’ll return some useful info, and what each field means.

select type_name, data_type, column_size,

       literal_prefix, literal_suffix,

       is_nullable, case_sensitive,

       unsigned_attribute, fixed_prec_scale,

       minimum_scale, maximum_scale, is_fixedlength

 from information_schema.provider_types

 

type_name contains the list of valid datatypes, such as int, real, nvarchar, etc.

data_type contains a numeric representation of the type of data stored in the field. Note this is not unique, for example nchar and nvarchar, and ntext all have the same data_type of 130.

Column_size indicates the max size for a data_type.

Literal_prefix / literal_suffix indicates what you should use to wrap a literal value. This is probably one of the more useful items in this table. Let’s say you have a static text value you want to insert into a SQL statement, perhaps a system name such as the word arcanecode.com. Looking up the literal_prefix for a ntext item I see that it is N’ (the letter N followed by a single quote). The literal_suffix is just a single quote mark, so to include in a SQL statement I’d need to put N’arcanecode.com’ .

Is_nullable is obvious, a 1 indicates the field is allowed to hold null values, a 0 means it can’t. As of this writing, all the field types in SSCE are nullable.

Case_sensitive is similar, 1 shows the field is case sensitive, 0 not. Again, as of this writing none of the fields in SSCE are case sensitive.

Unsigned_attribute is null for non-numeric type, but for numbers a 1indicates the data_type is unsigned only (i.e. no negative values allowed). A 0 indicates negative values are valid.

Fixed_prec_scale is another Boolean field. A 1 indicates the precision (the number of positions after the decimal point) is fixed. A 0 indicates you can change the precision.

Minimum_scale, maximum_scale is tied to the fixed_prec_scale. For items like int’s where the precision is always the same, this is null. However on numerics the user (i.e. you) can set the scale, and these fields indicate the valid range.

Is_fixedlength is a Boolean that flags whether the data_types length is fixed. For numerics this is going to be 1 (true), and for most text types this is 0 (false).

If nothing else this table makes a great quick reference. Since SSCE only supports a subset of the SQL Server data types, it’s handy to have a place to see which types are supported.

In your application, you could use this to build code generators or build SQL statements to create new databases. For example you could use it to look up the prefix / suffix types for a data_type.

And there you go, this completes a week long look at the various views built into SQL Server 2005 Compact Edition, and how you can use them within your applications.

About these ads

2 Responses to “System Views in SQL Server Compact Edition: Provider Types”

  1. Margo Says:

    This walkthrough will illustrate the best way to
    use a SLAX compilation to a USB stick through windows.
    It boasts a memory card slot to use if the style you need is obtainable on a memory.
    Featured US military-level encryption technology-256-bit
    AES, Wondershare USB Drive Encryption guarantees the highest
    a higher level file to safeguard your organization and personal data.

  2. http://sizegeneticsdiscounts.net/ Says:

    By applying the extender, it initiates cells within the penis to multiply and encourages tissue growth
    in addition to brings about a vast improvement from the blood circulation
    to the penis. The essense would be to stick on the program, with as
    few interruptions as you can, until you happen to be satisfied with the
    outcomes. The dryness and wrinkling may also be in connection with irritation a result of rough use or overindulgence.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: