In this article we’re going to go over a uncommon SQL server data types. These are those you won’t use every day, but you’ll want to know if you take the 70-461 exam.
Even if you don’t plan on taking the 70-461, learning these data types is fun, especially the spatial datatype (I had no idea you could “draw” shapes using SQL Server Management Studio).
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
Uncommon SQL Server Data Types
There are over thirty different data types you can choose from when defining columns. Many of these are set up for very specific jobs such as storing images, and others more suitable to general use.
In our article Commonly used SQL Server Datatypes we cover the types you most use in your database. In this article we cover three more. In addition to those commonly used, these are ones you’ll find used within problems on the 70-461 exam.
They are:
- XML
- Uniqueidentifier
- Spatial Data
XML –XML (Extensible Markup Language) Data
Before we begin to talk about how SQL server can use XML data, let’s first understand what XML data really is.
XML stands for Extensible Markup Language. By itself XML does nothing. Its main purpose is to store and transport data.
Here is an example of XML data:
<class> <name>Introduction to SQL</name> <instructor>C. J. Date</instructor> <number>EECS 475</number> <description>Discover and learn relation databases using SQL</description> </class>
XML data is described by elements and attributes. An example of an element is <class>.
I won’t go to much further into XML here, we’ll make the assumption you generally understand what XML is, the difference between attributes and elements, and how a schema defines the structure of an XML document.
If you are new to XML, may I recommend you read A Really, Really, Really Good Introduction to XML.
OK, so the XML data type allows you to store XML data associated with a schema. Knowing the schema, then allows you to parse the XML and extract specific elements such as the Instructor name.
This is how you can define a variable using the XML type:
DECLARE @myVariable xml (xmlSchema);
For example
DECLARE @x xml (Production.ProductDescriptionSchemaCollection);
When XML data has a schema associated with it, it is said to be typed. When you have a schema which can be used to check the structure of XML, it may make sense to store the XML data as an XML type.
XML is very readable and you may have noticed it is just text. Given this, why not just store XML in a VARCHAR field and call it quits?
You could and there are reasons to do so, but before we make a decision to settle with VARCHAR as you self these questions to determine when should you choose type versus untyped XML.
Use untyped XML if:
- Your XML doesn’t have a schema.
- You have a schema, but, you don’t need SQL Server to validate the data as the data is validated by a client program, or the data is just temporarily stored in SQL server to be used and processed elsewhere.
Use typed XML if:
- Your XML has a schema and you want SQL Server to validate the data against it.
- You want to take advantage of storage and query optimizations based on type information.
- You want to take better advantage of type information during compilation of your queries.
If you’re looking for an example of an XML type field in AdventureWorks 2012, check out the Person table.
Here you see the Demographic column is defined as XML type and bound to the Persion.IndividualSurveySchemaCollection.
Here is an example of the Demographics data shown in the first 10 rows
In another article we go into further detail on how you can query for specific values within the elements.
Unique Identifier – A Globally Unique ID (GUID)
The uniqueidentifier type is used when you which to store a GUID (Globally Unique ID). The main purpose to create GUID is create an ID that is unique across many computers within a network.
Unique Identifiers take the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxx where x represents a hexadecimal value (e.g. 0-F).
An example of an GUID is F22620D0-600E-4F0D-86E3-71250D1CE01E.
You can use the NEWID() function to generate GUID’s.
Here is an example:
DECLARE @myGUID uniqueidentifier = NEWID(); SELECT @myGUID
Which when I ran it returned 0AFEBE69-7B1E-43F9-909E-35E7E32535B2. When you run it, it will create a different GUID as you’re running it on a different computer at another time.
Unique Identifiers are important to SQL as they’re used when replicating data.
Spatial Data – Geometric and Geographic Data
There are several spatial data types supported in SQL Server. Rather than get into specific on each one, we’ll provide an overview of what spatial data types are, their purpose, and a very brief example of their use.
As mapping and other means to visualize data become more important, having a means to manipulate spatial data such as geometric or geographic data becomes more and more relevant.
In SQL Server, spatial data covers both geometric data such as points, curves, and polygons, as well as geographic data.
If you’re looking for a really good overview on Spatial data I would recommend looking over the MDSN article Spatial Data Types Overview or Redgate’s Introduction to SQL Server Spatial Data.
Here is an example of a query which creates a square and triangle as geometry types, then though a UNION, selects both objects and returns them as a single result:
DECLARE @sqr geometry, @tri geometry;
SET @sqr = geometry::STGeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))', 4326);
SET @Tri = geometry::STGeomFromText('POLYGON((5 5,10 15,15 5, 5 5))', 4326);
SELECT @sqr
UNION ALL
SELECT @tri
Source: SQL From the Trenches
When you look at the result the data grid is pretty unsurprising; however, you’ll see there is a new Spatial Result tab. Here you’ll see a visualization of the query results!
