Implement Data Types

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: use appropriate data; understand the uses and limitations of each data type; impact of GUID (newid, newsequentialid) on database performance, when to use which data type for columns

What I see:

·         appropriate data types

·         NEWID() vs. NEWSEQUENTIALID()

Appropriate Data Types

                This is one of those can’t-really-explain-it-in-a-blog-post topics, but I think what Microsoft is getting at here is that you should know the limitations (for data storage as well as storage consumption) of the many data types that SQL Server uses.  For instance, know the difference (precision, storage) between datetime and datetime2, and when to use one or the other.  See BOL for a refresher on the data types.

NEWID() vs. NEWSEQUENTIALID()

                I wrote a blog post on this exact topic.  For extensive information, review that.  But in summary, NEWID() does not ensure a sequentially generated GUID, and that is a bad candidate for a clustered index key.  This could lead to high amounts of page splits and index fragmentation.  NEWSEQUENTIALID() solves that problem by ensuring sequential identifiers when called.

References

·         BOL reference on Data Types

·         SQL Salt blog post containing information on NEWID() vs. NEWSEQUENTIALID()

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.