More on the new data types in SQL Server 2008
Posted by decipherinfosys on November 2, 2008
We have covered the new data types in SQL Server 2008 in different posts at our blog. In this one, we will summarize all of them here and also cover the ones that we did not cover before. These are the new data types in SQL Server 2008:
1) Date and Time:
c) DATETIME2, and
We had covered these data types in our blog post here.
2) Filestream: We had covered filestream in this blog post.
3) HierarchyID: HierarchyID was covered in this blog post.
4) Spatial: Under the spatial data type category, two new data types have been added in SQL Server 2008: GEOMETRY and GEOGRAPHY. These can be used to store and manipulate location based information like the GPS (Global Positioning System) data. Using these data types in addition to the other business information can help in creating some pretty neat RIA (Rich Internet Applications) – one can for example use this spatial data (longitude and latitude) with the actual address and create an application to show it on the map though the same can also be done by using the Google Maps API by using just the address. Both these data types are implemented as .Net CLR data types which essentially implies that they can support the various properties and methods like calculating distance between two Geometry co-ordinates or computing the difference between two Geographical locations based on the latitude and longitude information.
The Geometry data type is used to store flat data i.e. X and Y co-ordinates which essentially means that we are tracking in a two dimensional system. So, this is used for the flat earth data. So, if you are building a software for supply chain execution systems, you can use this data type to denote the location of the different items within a warehouse and present it in a graphical format.
The Geography data type is used to store the ellipsoidal data and we use the latitude and longitude to denote the location on the earth’s round surface. So, any GPS related data will be stored using this data type. If one needs to provide application support to show flight patterns or look at areas for a city/country or a sub-division – anything GPS related will be supported by this data type. Virtual Earth application uses this data type.
Unlike conventional indexes, indexes created on spatial data types are spatial indexes – the difference being that these indexes consist of a grid level hierarchy and each level in the index further divides up the grid sector from the level above. In terms of visualizing the data stored, one has the spatial results tab in SSMS (SQL Server Management Studio) in which one can look at the data in a map viewer.
Here are some URLs to further help you with your research for the spatial data types:
- Overview of the spatial data support in SQL Server 2008: MSFT site.
- MSFT whitepaper on delivering location intelligence using spatial data types: MSFT site.
- MSFT partners specializing in the spatial data applications: MSFT site.
- MSDN blog post by John Paul Cook – a good getting started intro: Blog post.
- And the MSDN virtual lab for the Geography data type: MSDN Virtual Lab.