Working with Data Types

Data comes in many types and sizes, such as a string that has a defined length, a number that has specific accuracy, or a user-defined data type that is another object that has its own set of rules. The DataType object classifies the type of data so that it can be handled correctly by Microsoft SQL Server. The DataType object is associated with objects that accept data. The following SQL Server Management Objects (SMO) objects accept data that must be defined by a DataType object property:

The DataType property for objects that accept data can be set in several ways.

  • Use the default constructor and specify DataType object properties explicitly
  • Use an overloaded constructor and specify the DataType properties as parameters.
  • Specify the DataType inline in the object constructor.
  • Use one of the static members of the DataType class, for example Int. This will in fact return an instance of a DataType object.

The DataType object has several properties that define the type of data. For example, the SqlDataType property specifies the SQL Server data type. The constant values that represent SQL Server data types are listed in the SqlDataType enumeration. This refers to data types such as varchar, nchar, currency, integer, float, and datetime.

When the data type is established, specific properties must be set for the data. For example, if it is an nchar type, the length of the string data must be set in the Length property. The same applies for numeric values, where you would have to specify precision and scale.

UserDefinedDataType and UserDefinedType data types refer to objects that contain the definition of the type of data defined by the user. The UserDefinedDataType is based on SQL Server data types from the SqlDataType enumeration. The UserDefinedType is based on Microsoft .NET data types. Typically, these would represent data of a specific type that is frequently reused by the database because of business rules defined by the organization. For example, a data type that stores an amount of money and a currency denominator would be helpful in a company that deals in multiple currencies.

The SqlDataType enumeration contains a list of all the SQL Server-supported data types.