SQL Server Compact - Compact & Capable

How to: Authoring a 64-bit ClickOnce Bootstrapper package in Visual Studio 2008 SP1

Location of SQL Compact Bootstrapper

        <BootstrapperDir>\Packages\SQL Server Compact Edition\<LocaleCultureDir>\

Where,

·         <BootstrapperDir>  can be found by reading [HKLM\Software\Microsoft\GenericBootstrapper\3.5]path value otherwise its default value is %ProgramFiles%\Microsoft SDKs\Windows\v6.0A\Bootstrapper\ .

·         <LocaleCultureDir> is culture specific dir, ex: EN, DE, zh-CHS, zh-CHT, …

Follow the below steps, If you have an ‘Any CPU’ ClickOnce application using SQL Compact 3.5 SP1, for which you want to install SQL Compact x64 MSI on 64-bit machine.

Note: It is required to install both x86 and x64 MSIs on 64-bit OS, since x86 MSI is pre-requirement for x64 MSI.

Steps:

1)      Download the SQL Compact 3.5 SP1 x64 MSI and place it under <BootstrapperDir>\Packages\SQL Server Compact Edition\<LocaleCultureDir>\

2)      ClickOnce setup.exe is always runs as WoW process on x64 OS, that’s why reading a registry key is not useful for x64 MSI. We can achieve the install check using  <MsiProductCheck> element.

Edit the <BootstrapperDir>\Packages\SQL Server Compact Edition\<LocaleCultureDir>\Package.xml file with following details

·         As part  of <InstallChecks> element add <MsiProductCheck> as below

<InstallChecks>

    ---

  <MsiProductCheck Property="SQLCompactRunTimex64Installed" Product="[ProductCode]" />

    ---

</InstallChecks>

[ProductCode] is the Product Code/GUID of the x64 MSI. Depending on locale, get it from below table for 3.5 SP1 x64 release.

You can use Orca.exe to get the ProductCode property of a MSI.

3)      Add <PackageFile> element for x64 MSI. Replace [ThreeLetterLocaleID] with three letter locale id mentioned in above table.

<PackageFiles>

    ---

<PackageFile Name="SSCERuntime-[ThreeLetterLocaleID]-x64.msi"    PublicKey="3082010a0282010100a2db0a8dcfc2c1499bcdaa3a34ad23596bdb6cbe2122b794c8eaaebfc6d526c232118bbcda5d2cfb36561e152bae8f0ddd14a36e284c7f163f41ac8d40b146880dd98194ad9706d05744765ceaf1fc0ee27f74a333cb74e5efe361a17e03b745ffd53e12d5b0ca5e0dd07bf2b7130dfc606a2885758cb7adbc85e817b490bef516b6625ded11df3aee215b8baf8073c345e3958977609be7ad77c1378d33142f13db62c9ae1aa94f9867add420393071e08d6746e2c61cf40d5074412fe805246a216b49b092c4b239c742a56d5c184aab8fd78e833e780a47d8a4b28423c3e2f27b66b14a74bd26414b9c6114604e30c882f3d00b707cee554d77d2085576810203010001" />

</PackageFiles>

4)      Add <Command> elements for x64 MSI.

     </Commands>

            <!--  Install case for the x64 redist   -->

       <Command PackageFile="SSCERuntime-[ThreeLetterLocaleID]-x64.msi" Arguments="">

<InstallConditions>

            <!--  ByPass if we have installed the x64 redist   -->

  <BypassIf Property="SQLCompactRunTimex64Installed" Compare="ValueGreaterThan" Value="0" />

            <!--  Install only on AMD64 Processor   -->

  <BypassIf Property="ProcessorArchitecture" Compare="ValueNotEqualTo" Value="AMD64" />

            <FailIf Property="AdminUser" Compare="ValueEqualTo" Value="false" String="AdminRequired" />

  </InstallConditions>

   <ExitCodes>

  <ExitCode Value="0" Result="Success" />

  <ExitCode Value="1641" Result="SuccessReboot" />

  <ExitCode Value="3010" Result="SuccessReboot" />

  <ExitCode Value="4123" Result="SuccessReboot" />

  <DefaultExitCode Result="Fail" String="Anunexpected" FormatMessageFromSystem="true" />

</ExitCodes>

       </Command>

            <!--  Reinstall/Repair case for the x64 redist   -->

       <Command PackageFile="SSCERuntime-[ThreeLetterLocaleID]-x64.msi" Arguments="REINSTALL=ALL">

<InstallConditions>

            <!--  Check if we haven’t  installed the x64 redist, no need to repair it   -->

  <InstallIf Property="SQLCompactRunTimex64Installed" Compare="ValueGreaterThan" Value="0" />

            <!--  Install only on AMD64 Processor   -->

  <BypassIf Property="ProcessorArchitecture" Compare="ValueNotEqualTo" Value="AMD64" />

            <!--  This is the actual condition for reinstalling the x64 MSI  -->

  <BypassIf Property="ENU_INST_GAC" Compare="ValueExists" />

  <FailIf Property="AdminUser" Compare="ValueEqualTo" Value="false" String="AdminRequired" />

</InstallConditions>

<ExitCodes>

  <ExitCode Value="0" Result="Success" />

  <ExitCode Value="1641" Result="SuccessReboot" />

  <ExitCode Value="3010" Result="SuccessReboot" />

  <ExitCode Value="4123" Result="SuccessReboot" />

  <DefaultExitCode Result="Fail" String="Anunexpected" FormatMessageFromSystem="true" />

</ExitCodes>

       </Command>

      </Commands>

ClickOnce Reference: http://msdn.microsoft.com/en-us/library/ms229223(VS.80).aspx

Thanks

Manikyam Bavandla

Sync Services optimizations

    There are some optimizations that can be done for an "Sync Services" sync scenario. These are, (might not be an exhaustive list)

1. Small sync chunks (using smaller SyncGroups which are only absolutely necessary)
2. Index creation on tracking and filtering columns
3. Transfer encryption of data payload on web
4. Batching the changes                 
5. Filtering the data at various stages to reduce network load

    Sync (synonym for "Sync Services sync", in this post) interfaces with the providers using DataSet objects. That is, sync uses DataSet to store and retrieve changes happening at client and server. DataSets are known to be not very memory efficient, and the amount of memory consumed by these, is a bit of a concern when one is using these in device clients in Sync Services. 
 
    Sync happens in chunks, using the SyncGroups that one supplies. That is, all data in a SyncGroup is logically one sync, and corresponds to one DataSet. Since sync DataSets are one per SyncGroup, and these are needed only till the corresponding SyncGroup commits, we can optimize the memory taken by DataSets, using smaller SyncGroups. Divide sync tables into various SyncGroups, so that, each group has it’s own (smaller) DataSet, and it is disposed (by garbage collector) once the SyncGroup changes are committed. However, to ensure logical consistency, you should always keep the tables which are related (by a foreign key constraint, for example), in the same SyncGroup.
 
Example:
Tables Order and OrderDetails are related.
Tables Customers and ShippingDetails are related. But, these are unrelated to the first set.
 
SyncGroup customerSyncGroup = new SyncGroup("Customers");
 
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
customerSyncTable.SyncGroup = customerSyncGroup;
 
SyncTable orderShipSyncTable = new SyncTable("ShippingDetails");
orderShipSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderShipSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderShipSyncTable.SyncGroup = customerSyncGroup;
 
SyncGroup orderSyncGroup = new SyncGroup("Orders");
 
SyncTable orderSyncTable = new SyncTable("Order");
orderSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderSyncTable.SyncGroup = orderSyncGroup;
 
SyncTable orderDetailSyncTable = new SyncTable("OrderDetails");
orderDetailSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
orderDetailSyncTable.SyncDirection = SyncDirection.DownloadOnly;
orderDetailSyncTable.SyncGroup = orderSyncGroup;
 
 
    Rather than having a single SyncGroup with all 4 tables, one can create two sync groups. One for the first set, and the second for the other set. 
 
    Also, to increase the performance of enumerating process at server, one can create indexes on the tracking and filtering columns. As an example, let us say one has two tracking timestamp columns __CreateTime and __UpdateTime. Since, our server side changes enumerating queries always have filtering based on these columns, one should create indexes on these, so that, enumerating server changes becomes faster, and, sync performance increases. 
 
    In case of device syncs, transferring the data on the network/internet is also going to cost more time/resources. Since, transfer of data from a web service/server machine to a device is time/bandwidth consuming, one should look at compressing data sent/received. This is mostly the case, when data being synched is large (Something like, initial synching SQL Compact with SQL Server). One can use System.IO.Compression or your own implementation of compression/decompression algorithm. (More details at http://msdn.microsoft.com/en-us/library/system.io.compression.aspx). Another blog post that refers to this issue and proposes solutions is at, (http://blogs.msdn.com/mahjayar/archive/2008/10/01/dbsyncprovider-improving-memory-performance-in-wcf-based-synchronization.aspx)
 
    Sync Services Server side supports batching. Please refer to http://msdn.microsoft.com/en-us/library/bb902828.aspx for more details.  
 
    Sometimes, we may have server tables holding and synching data, that is alien to clients. For example, server table has a large GPS map of some place (in custom format) per row, that is associated with the data in other columns in the row. The map is stored as an image at client side. But, since, the data is of proprietary format, client can’t interpret the data, and it is not used anyway at client side. And also, since, it is large data, lot of network bandwidth is consumed and response time is compromised for transferring this across. It is better, if one can filter these columns out at server side, rather than sending to client. 
 
    Another scenario is as follows: The client devices of a Sync application are used by Salesmen of a company, and the server side is the company office, where data pertaining to each salesman is stored in a SQL Server database. When the client devices sync, they are mostly concerned about the data relevant to them alone, and not to other sales persons. Here also, one needs to filter rows from the table. 
 
    To facilitate filtering of rows/columns at client/server sides, one can use a number of strategies. For filtering data sent from client (or at the client side), one can use the callback architecture of "Sync Services". For server side, since you have more control there, one can choose to strip down the DataSet, when one is sending it over the wire/use callbacks to strip off DataSet data/client can supply queries to server side adapters, which filter data.

Explaining each of these options:

    Callback architecture of sync: Sync provides various callbacks during the process of Synching. One can hook in the callbacks for accessing/modifying the dataset generated at every stage of sync. For example, one can add/drop some columns to the dataset, after changee enumeration is done at client side. After changing the dataset, effectively, the changes being applied to client database are changed. The same provision is present at server side also.

    This callback architecture can be used to modify the DataSet applied/sent to server, on the fly. This is one type of filtering, possible at both client and server.

    Server side gives more options when sending data over the wire. First of all, server chnages are enumerated within the "Sync Services" application itself. That is because, server change enumeration is done by queries supplied by the Sync Services client. So, which columns to select from server, can be controlled from the client side, when specifying queries like, SelectIncrementalInserts etc... This is another way of programmatically filtering server data.

    One more option to filter data in a N-tier architecture is that, filtering the wire transferred content. In an N-tier architecture, client and server are in two different, but connected machines. Typically, client machine has a proxy server provider, which delegates calls to enumeraion/application of changes etc... to a web service. Web service talks to a backend data store, and services the data and operation requests. In this setting, interesting possibilities like, use of compression over the wire, and filtering over the wire etc... come up. When server side data store gives data, it can do some filtering. Also, the client side proxy can be used to filter irrelevant data. This is another type of filtering.

Example for callback architecture:
For client side DataSet filtering (client side), use SelectingChanges before the enumeration and ChangesSelected after the enumeration
For server’s data set filtering (client side), ApplyingChanges before the application of changes and ChangesApplied after the application
For filtering server side changes (at server side):SelectingChanges before the enumeration and ChangesSelected after the enumeration
For client’s data filtering (at server side):ApplyingChanges before the application of changes and ChangesApplied after the application
 
In every one of these methods, eventArgs.GroupMetadata and eventArgs.Context.DataSet are the ones need to be changed.

 Thanks

Udaya Bhanu,

SDE II, SQL Compact

Released - SQL Server Compact v3.5 SP1, SQL Server 2008 RTM, Visual Studio 2008 SP1, .NET Framework v3.5 SP1

Yes, all of them are released on August 11, 2008. 

SQL Server Compact v3.5 SP1:

What is new in this Service Pack? - Click here and here

Where to download?

SQL Server Compact v3.5 SP1 for Desktops (32-bit and 64-bit)

SQL Server Compact v3.5 SP1 for Devices (all platforms & processors)

Server Tools (32-bit and 64-bit) (See Also: this)

Documentation, Books Online

Visual Studio 2008 SP1

What is new in this Service Pack? - Click here

Where to download? - Click here

.NET Framework v3.5 SP1 - Download it here

SQL Server 2008 RTM - Click here

The good thing is with VS 2008 SP1, you will get "SQL Server Compact SP1 for Desktop (32-bit)", and of course updated "SQL Server Compact SP1 Design Tools".

We received a huge feedback from you on VS 2005 SP1 that we should limit the Service Pack Size.  With that feedback, we felt it is a reasonable to ask customers to download "SQL Server Compact v3. SP1 for Devices" explicitly than including it with Visual Studio 2008 SP1 there by bloating VS 2008 SP1 size.  We also made a decision to not include "SQL Server Compact v3.5 SP1 for Desktops (64-bit)" so as to make sure we do not trouble all VS 2008 SP1 customers.

What about Platform Builder Updates?

Platform Builder 5.0 and 6.0 Updates for SQL Server Compact v3.5 SP1 are also released to Platform Builder team and they should be available in the next Platform Builder 5.0 and 6.0 Update.  I will update this blog post with the download URL soon after they are available. 

Windows CE 5.0 Platform Builder Monthly Update (July 2008) - Download it here

Windows Embedded CE 6.0 Platform Builder Monthly Update (July 2008) - Download it here

Thanks,

Laxmi Narsimha Rao Oruganti

Insight into SQL Server Compact database corruptions

Database corruptions are often the most painful bugs to repro and debug. A good percentage of the SQL Server Compact bugs reported are in this category. However it so turns out that in majority of the cases Compact is not the culprit. Surprised!! Let us see why.

How SQL Server Compact detects a file corruption?

Compact database comprises of a series of pages, each 4K in size. When a page is written to disk the following sequence of steps is executed.

1.       Compute the checksum on the page data and store it as part of the page.

2.       Encrypt the page.

3.       Write to disk at a given file location or offset.

4.       Update system pages/run time cache with the page offset.

When a page is read from disk the following sequence of steps is executed.

1.       Read from the disk.

2.       Decrypt the page.

3.       Re-compute the checksum and compare it with the expected value. (If it fails page is not fully baked. Meaning file write failed)

4.       Check if this is the same page that we are expecting as per the system pages/ run time cache. (If this fails we lost a complete page write)

If (3) or (4) of the above read steps fail, the db is marked as corrupt and any further db activity is suspended till it is repaired successfully.

Notes:

·         Verify reports step (3) errors as “Bad Checksum - idPage: %d, tyPage: %d, iPage: %d”.

·         Verify reports step (4) errors as “Page with valid ordinal not found - idPage: %d, iPage: %d”

What could have triggered the corruption?

1.       A simple device on/off or suspend/resume can result in half-baked file. (No reason to panic as we handle this as described in the next section.)

2.       Compact relies on underlying FAT/TFAT file system to manage the database file. Any bugs in these components can lead to Compact file corruption. Compact stresses file system in many different ways as it tries to keep the file size as small as possible. As it employs shadow paging the writes and reads are quite random. (Note: 6 months back a bug has been identified in FAT/TFAT system that results in a Compact file losing some of the clusters during shrink operation.)

3.       If SD card is being used for db file storage, it’s being equally stressed as the above.

4.       More importantly SD cards bring in additional complexities during Suspend/Resume cycles. Here is a good blog link: http://blogs.msdn.com/windowsmobile/archive/2007/01/12/everything-you-want-to-know-about-sd.aspx

Am I suggesting SQL Server Compact bugs can’t result in any kind of corruption?

Of course Compact code bugs can result in corruption. A lot of them have been found internally before v3.0 release and also reported by customers thereafter. They are more logical in nature like index constraint is compromised or a table page says there are 1000 rows when there are only 55.  We did a very good job in fixing these and they are hard to find now. However Compact bugs don’t result in physical corruptions like “checksum errors” or “page missing” errors.

What does Compact do to prevent corruptions?

We believe most of the corruptions should be due to device suspend/resume or on/off cycles.  For ex: Compact is trying to write to a file and the user simply boots his device. Now file is in half-baked state. Or let’s say process gets killed before it can complete the flush cycle. How do we avoid this?

Well, we use shadow paging to overcome this situation. We can loosely categorize the db pages into user data pages, system pages and header page. As the name suggests user data pages contain all the user data. Header pages and system pages help us locate a specific user page on the disk. When it’s time to flush the changes to disk, we write shadows of the user data pages first. Then the shadows of the updated system pages are written. Finally the header page is written. The old pages are retained till the header page is updated. This ensures that we have a transactional write to the disk. Either the change is completely present or not.

Conclusion: Why do we still see corruptions?

Clearly the above is not enough. Some of the interesting cases are

·  What if any of the underlying systems ‘SD card software’ or FAT/TFAT has a bug?

·  Suspend/resume cycle can result in file buffer loss if SD card is used. So the changes supposed to be written to disk could be lost.

The intent is not to blame other components. However in the last 3 years, at least 90% of the corruption bugs reported are due to bad checksum. It means disk write is bad for some reason (which is not owned by Compact).  The fault is somewhere in the bottom stack and it has to be fixed. Our options are very limited without a repro but we are trying hard to improve our debugging infrastructure. We are hoping that with this knowledge customers will be able to understand the corruptions better and come up with good practices that would vastly reduce these corruptions. 

Katmai new datatypes support

In Katmai, new datatypes are introduced. Some of these (including some older types in Yukon) are supported for merge replication synching with SQL Compact in SQL Compact 3.5 SP1. These types are mapped to downlevel compatible types for SSC. These types and their mappings are, as follows:

SQL type                              Compact type                  Format                               

Date                                     nvarchar(10)                   ‘yyyy-mm-dd’

Time                                     nvarchar(16)                   ‘hh:mi:ss.nnnnnnn”

Datetime2                            nvarchar(27)                   ‘yyyy-mm-dd hh:mi:ss.nnnnnnn’

DatetimeOffset                    nvarchar(34)                  ‘yyyy-mm-dd hh:mi:ss.nnnnnnn [+|-]hh:mi’

Geometry                             image

Geography                           image

The format of strings used when inserting/updating the data on SQL Compact, should be same as that given above, or as the type demands, in case of Spatial types.

Spatial types are interesting, because, one can map them to image columns on the client without data loss. The data can even be manipulated on the client side, if the required spatial type libraries are present. Other new types in Katmai, like, HierarchyID etc... are not supported for merge with SQL Compact.

Thanks

Udaya

Query performance

Understanding the reason for slowness of a SQL query and then tuning to boost it is a slightly complicated process, but it can be extremely rewarding in some cases. In this post, I am going to list down some ways in which you can tune the query performance for SQL Server Compact, along with pointers to relevant online resources. If you are a seasoned database developer, you probably know most of the tricks already.

Before I jump into it, an overview of the basics first.

Basics:

•  Displaying actual and estimated Execution plans for SQL Server Compact: You should be familiar with Execution Plans (also called as query plans and showplans).
• For non-parameterized queries: There are several easy ways to do this
• When database is on a docked device or desktop: Generate a query plan using SQL Server Management Studio (SSMS) that gives a nice graphical view of the query plans. (You can connect to the database on device as long as it is docked. No need to copy the file to desktop.)
• When database is on a device: Generate a query plan using Query Analyzer. This generates a .sqlplan file that can be viewed graphically in SQL Server Management Studio as before (preferable) or opened as a plain XML file.
• For Parameterized (as well as non-parameterized) queries:
• Use the TSQL Statements SET SHOWPLAN_XML  (for estimated execution plan) and  SET STATISTICS_XML (for actual execution plan), followed by SELECT @@SHOWPLAN to generate the query plans.  Store the results as an XML file. (Tip: To view the query plans graphically, just rename the file with a .sqlplan extension and it is ready to be opened in SSMS like an ordinary non-parameterized query plan.)
• Indexes and statistics on indexes:
• One of the important tasks of the query optimizer is to select the right index to execute the query. An optimizer can make better decisions if it has histogram data (i.e. statistics) about the distribution of values for an index.
• CREATING/UPDATING/DELETING statistics on an index: SQL Server Compact 3.5 creates statistics on all indexes by default and updates them as required. (You can surely tweak these settings, but it is advisable not to. See CREATE STATISTICS /UPDATE STATISTICS/DROP STATISTICS).
  
• To view the statistics distribution, the built in stored procedure sp_show_statistics is pretty handy. 
• Statistics were not created by default on primary key indexes till version 3.1, so you might want to check this and add them yourself :- ).
• ... A lot of other things too J, but this will do for the time being.

If you are trying to improve the performance of your application that uses SQL Server Compact, make sure you have checked the following resources on MSDN already (In this post I will not go into any of them):

• Database Design and Performance: This page talks in some detail about the following:
• Database denormalization
• Variable and fixed sized columns
• Effect of row and key lengths
• Query Performance Tuning: This page explains some of the following:
• Improving indexes by creating selective indexes, choosing the columns in a multi-column index, issues in indexing a small table etc.
• When to create indexes
• Using parameterized queries where possible.
• Rewrite queries where possible
• Using base table cursor (CommandType.TableDirect) mode for simple queries.

(The article on Query Process Overview and Performance Tuning approaches for SQL Server CE 2.0 covers the same points as above, but I found it better than the MSDN links J. It is relevant for SQL Server Compact 3.5 too.)

Now here are some additional ways you can improve performance:

1.       Recompile Stale query plans

<StoryTime>Sometime back I got a complaint about slow performance of our database even though the user was using parameterized queries and had created the correct indexes everywhere. The sequence in which he was performing operations was something like this:

-          Create the full database schema (tables, indexes etc).

-          Prepare SqlCeCommand objects for *all* queries that will ever be used by the application.

-          Populate the database with actual data. Until now all tables were empty.

-          Execute the queries using previously prepared command objects 

What's wrong with this?

 A prepared command object (SqlCeCommand) holds a query plan. The query optimizer generated this query plan and had optimized it for the state of database when the plan was generated. In this user's case, the database had just empty tables at that point in time. The optimizer therefore inferred that using a table scan is the best way of executing the query (as opposed to using any index). As a result, all his query plans used just table scans instead of the right indexes even when the table size increased! 

</StoryTime>

Moral of the story: Caching query plans is good, but only as long as the state of database when they are compiled is representative of the average state of database during execution. If your data is changing rapidly, it is better to recompile the queries every once in a while.

So, beware of stale query plans!

Note that query plans can be cached for both parameterized and non-parameterized queries and you can run into this problem in either case. (Another insider hack: SqlCeCommand.Prepare doesn't really prepare the query plan. It just marks the command object for plan (re)compilation and the plan is compiled when SqlCeCommand.ExecuteXXX is called next time.)

2.       Impression of using parameterized queries without really using them

Question: What's wrong (rather sub-optimal) with the following code:

SqlCeCommand cmd = con.CreateCommand();

cmd.CommandText = "SELECT * FROM table1 WHERE C1_DECIMAL = @p1";

cmd.Parameters.Add("@p1", SqlDbType.Decimal);

cmd.Parameters["@p1"].Value = 3.5;

SqlCeDataReader rdr = cmd.ExecuteReader();

while (rdr.Read()) { /* read the data */}

rdr.Close();

cmd.Parameters["@p1"].Value = 335.01;

rdr = cmd.ExecuteReader();

while (rdr.Read()) {  /* read the data */   }

rdr.Close();

Answer: The query plan gets compiled twice, once for every ExecuteReader call!

Reason: First time the plan is compiled, the parameter's precision is 2 and scale is 1 (as inferred from its value ‘3.5'). The query plan therefore uses this precision and scale values. The only other values it can accept are those that fit within this range.

When the query is executed again, the parameter's precision is 5 and scale is 2 (as inferred from its value ‘335.01'). Since it doesn't fit in the parameter as inferred during first plan compilation, the plan is recompiled silently!

Solution: If you can anticipate the range (size, precision or scale) of the parameter values, then specify it explicitly. The plan will be generated based on the specified range then. This holds true for all character, binary and numeric types. (The flip side is that for any parameter value that does not fit into the specified range, an error will be thrown.