The data type of SQL server is a very important thing to building the table objects. So we must have clear point of view before using different data types.
In my professional life I have seen may DBA uses the wrong type of data type in table objects and results, performance slow down, Time out …expire etc, as they don't have enough idea of data type related to SQL server.
In these articles, I try to define some new data types that came with SQL 2008 and not used in previous version of MS SQL server.
DATE TIME
It's much easier with SQL 2008; MS provide us better control related to Date and time data types. In SQL 2008 introduced: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
[A]
DATE
The DATE data type stores only date in YYYY-MM-DD format.
It stores range of 0001-01-01 through 9999-12-32. It takes 3 bytes to store.
[B]
TIME
It stores only time in the format hh:mm:ss.nnnnnnn, and range between 00:00:00.0000000 through 23:59:59:9999999 and is accurate to 100 nanoseconds. It takes 3 to 5 bytes to store.
[C]
DATETIME2
It is as similar to the SQL 2005 DATETIME but have greater range value. It runs from 6 to 8 bytes to store.
[D]
DATETIMEOFFSET
It is similar to DATETIME2, but includes additional information to track the time zone. The format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-01-01 00:00:00.0000000 through 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC), and an accuracy of 100 nanoseconds. Storage depends on the precision and scale selected, and runs from 8 to 10 bytes.
SPECIAL
Two new spatial data types have been added- GEOMETRY and GEOGRAPHY-which you can use to natively store and manipulate location-based information, such as Global Positioning System (GPS) data.
[A]
GEOMETRY
The GEOMETRY data type is used to store planar (flat-earth) data. It is generally used to store XY coordinates that represent points, lines, and polygons in a two-dimensional space. For example storing XY coordinates in the GEOMETRY data type can be used to map the exterior of a building.
[B]
GEOGRAPHY
The GEOGRAPHY data type is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and polygons on the earth's surface. For example, GPS data that represents the lay of the land is one example of data that can be stored in the GEOGRAPHY data type.
HIERARCHYID
The HIERARCHYID data type is used to enable database applications to model hierarchical tree structures, such as the organization chart of a business.
The HIERARCHYID data type makes it easier to express these types of relationships without requiring multiple parent/child tables and complex joins.
· Organizational structures
· A set of tasks that make up a larger projects (like a GANTT chart)
· File systems (folders and their sub-folders)
· A classification of language terms
· A bill of materials to assemble or build a product
· A graphical representation of links between web pages
FILESTREAM
FILESTREAM is not a data type as such, but is a variation of the VARBINARY(MAX) data type that allows unstructured data to be stored in the file system instead of inside the SQL Server database.
Posted By: MR. JOYDEEP DAS
No comments:
Post a Comment