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
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s