Design Data Integrity in Visual Basic .NET

Draw Quick Response Code in Visual Basic .NET Design Data Integrity

8

Table 8-1

Description Variable-length binary data from 0 through 2^31 1 (2,147,483,647) bytes. A data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. A data type that stores values of various data types supported by SQL Server 2005, except text, ntext, image, timestamp, and sql_variant. A special data type that can be used to store a result set for processing at a later time. The table data type is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function. Generally used as a mechanism for version stamping table rows. It is incremented for each insert or update operation performed on a table that contains a timestamp column within a database. 16-byte GUID. Stores XML data. It is new to SQL Server 2005. Content can be restricted to a well-formed XML fragment or document. It is also possible to specify the XML schema collection.

You should use types such as smallint and tinyint as a way of optimizing data storage within extents. These types reserve less data space and increase database efficiency. You can read more about extents in 4, Disaster Recovery.

Alias types are based on system data types. You use alias types when several tables within a database must store the same type of data in a column and you need to ensure that these columns have an identical data type, length, and nullability. For example, you might be working with scientific data and want to ensure that all tables

within the database store measurements using the same level of precision. The simplest way of accomplishing this is to create an appropriate alias type and apply it to all columns that must store the scientific measurements within the database. If you create an alias type in the model database, SQL Server copies it to all new userdefined databases you create. If you create the alias type within a user-defined database, that type exists only within that database. When you create an alias type, you must specify the following parameters:

To create a type like the one mentioned in the preceding example called Sci_Measure, you would issue the following Transact-SQL statement:

CREATE TYPE Sci_Measure FROM decimal (6,3) NOT NULL ;

To learn more about creating alias types, you should consult the following MSDN article: msdn2.microsoft.com/en-us/library/ms175007(SQL.90,d=ide).aspx.

1. What are alias types useful for 2. Which Transact-SQL data type would you configure a column to use if you needed to store only values 0, 1, or NULL 3. Which Transact-SQL data type is functionally equivalent to the decimal data type

1. You use alias types for ensuring that a set of columns are of the same length, type, and nullability. 2. You would use the bin data type to accomplish this task. 3. The numeric data type is functionally equivalent to the decimal data type.

8

Orin Thomas We ve all heard the story about the guy who figured out how to siphon off all the rounding errors in a bank s daily financial transactions into his own account. This is a great example of why it is important to be as precise as possible when setting up data types. As that guy found, a rounding error of even the smallest fraction of a cent over the course of millions of transactions turns out to be quite a pile of money. In scientific calculations, you can be only as precise as your most imprecise measurement allows. It can also go the other way. If you think that your data is more accurate than it actually is, you might detect things when datamining that are not actually there. In many real-world applications, you need to record data as accurately as possible.