MSSQL Types

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
-System Data Type: There are around 33 system data types available in SQL Server 2008 R2. Please refer above to see the list.
-User-Defined Data Types: User-defined types are useful for defining a consistent data type that is named after a known business or application-centric attribute, such as PIN, EmailAddress. Once a user-defined type is created in the database, it can be used within columns, parameters, and variable definitions, providing a consistent underlying data type. You can Create and Drop UDDT but cannot ALTER. You can associate the UDDT with Rules or Default or Both.
-User-Defined Table Types: Represents the definition of a table structure. UDTT cannot be used as a column in a table or a field in a structured user-defined type. Default values are not allowed. It can’t be altered. You have to drop and recreate it. Non clustered indexes are not allowed.
-User-Defined Types: Create DB objects that are programmed against an assembly created in the .NET Framework CLR. Database objects can take advantage of the rich programming model (C# or VB.NET) provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types. A user-defined type (UDT) may be simple or structured and of any degree of complexity. It can encapsulate complex,
user-defined behaviors. A user-defined type can be used to define the type of a column in a table, or a variable or routine parameter in the Transact-SQL language. An instance of a user-defined type can be a column in a table, a variable in a batch, function or stored procedure.
Large UDT: User-defined types (UDTs) introduced in SQL Server 2005 allow a developer to extend the server’s scalar type system by storing common language runtime (CLR) objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, unlike the traditional alias data types, which consist of a single SQL Server system data type. Previously, UDTs were restricted to a maximum size of 8 kilobytes. In SQL Server 2008, this restriction has been removed for UDTs. Now it allows us to store, compress, and decompress binary data (up to 2GB in size) inside SQL Server 2008 and called as Large UDT.
-XML Schema Collections: XML is getting more widely used both inside and outside SQL Server. When information is exchanged in XML format, needs to be an agreement between the sender and receiver about the structure and content of the XML document. Using an XSD (XML Schema Definition) Schema, you can validate the structure and content of an XML document. SQL Server introduced XML Schema Collections to validate the xml structure. An XML data type variable or column that is bound to an XML Schema collection is called TYPED XML. When a TYPED XML column or variable is modified, SQL Server will perform the validations defined in the SCHEMA.

Leave a Reply

Your email address will not be published. Required fields are marked *