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:

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.

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see "How to: Create a Visual Basic SMO Project in Visual Studio .NET" or "How to: Create a Visual C# SMO Project in Visual Studio .NET" in SQL Server Books Online.

'Declare a DataType object variable and define the data type in the constructor.
Dim dt As DataType
'For the decimal data type the following two arguements specify precision, and scale.
dt = New DataType(SqlDataType.Decimal, 10, 2)