Download - Great Scott! Dealing with New Datatypes
Karen Lopez @datachick #TEAMDATA
GREAT SCOTT!Working with all those weird datatypes
Yes, Please do Tweet/Share today’s event
@datachick@ERStudio
Karen López
Karen has 20+ years of data and information architecture experience on large, multi-project programs.
She is a frequent speaker on data modeling, data-driven methodologies and pattern data models.
She blogs at community.embarcadero.com, datamodel.comand Dataversity.net
She wants you to love your data.
Logistics…
Use Q&A for formal questions – ask at any
time
Slides will be distributed after event
Continue the conversation on Twitter
There’s more to come…
Plan for Today
Types of Data Types
The Outlier Data Types
Working with Data Types
10 Tips for Working with Data Types
Poll: Who are you?
Poll: Which RDBMSs Do You Support?
Datatypes
Why do we need them?What types of datatypes are there?
Datatypes aren’t
just formats
Datatypes are
also about
data quality
Datatypes are
also about
PERFORMANCE
Note…
Today’s focus is mostly on physical data types…
….but I do have some thoughts on logical data types as well
Types of Datatypes*
predefined data types
constructed types
user-defined types
ISO: 9075
Data types
Understand your data type taxonomies, by RDBMS and version.
IBM DB2
Understand the lingo
Data type names
Data type syntax
Data type gotchas
The Basic Datatypes
Strings
Numeric
Boolean
Dates and Times
Interval
ISO: 9075
Fancy Datatypes
• HIERARCHYID
• Spatial data types (GEOMETRY, GEOGRAPHY, more)
• XML
• JSON < NEW!!!!! FANCY!!!
• PERIODs
• MONEY
• Spatial temporal data types (a special one)
Hierarchies
Meet JSON
Javascript Object Notation
JSON support arrays and variable data items
Much like XML in theory (shredding, overuse, popular )
https://docs.mongodb.org/getting-started/shell/import-data/
Periods
Data type for storing an opening and closing date (or time) period
Has special operators and functions to help work with the data
Importantly, an “until closed” end period
https://developer.teradata.com/database/articles/exploring-teradata-13s-period-data-type
Spatial data types
Special functions
Meet needs for shapes and lines
Geospatial Data in SQL Server. (2015, May 14). Wikibooks, The
Free Textbook Project. Retrieved 18:21, November 10, 2015
from https://en.wikibooks.org/w/index.php?title=Geospatial_Data_i
n_SQL_Server&oldid=2958376.
Money (and other specialized data types)
Special use cases
Special limiations
Limited cross-platform support
Often limitations are not well known
http://www.postgresql.org/docs/9.4/static/datatype-money.html
http://www.postgresql.org/docs/9.4/static/datatype-net-types.html
Dates and Times
Various “precisions”
Some, not all, RDBMSs support WITH TIMEZONE
Conversion compatibilities
ISO 9075
Host Implementation Gotchas
TIMESTAMP
Dates and Times WITH TIMEZONE
TINYINT
BIT
And more….
Other Gotchas
Conversion Compatibilities
Performance of conversions
Foreign key consistency
NULLability
https://msdn.microsoft.com/en-us/library/ms191530.aspx
User Defined Data Types
http://www.datamodel.com/index.php/20
14/04/01/sql-server-2014-new-datatype/
Iterative Data Modeling
How do we set data types?
Data architects must work with DBAs, Devs, end users to finalize datatypes
Deciding on Datatypes
Data profiling
Awareness of options
Confirmation of cost, benefit, risk
Urban myths
Testing and benchmarking
What about logical data types?
They are just as important
Many of our thoughts today apply
We should stive to keep them simple
Logical data types for meaning
Yet….
Logical/Physical data type mapping
Other Modeling Gotchas
10 Tips for using the right datatypes
1.Learn all the datatypes available to you, by RDMS and version
2.Read the pros and cons of each
3.Create user defined datatypes that provide benefits to more than just the data modeler
4.Understand datatype compatibilities
5.Know the business case for longer datatypes
10 Tips for Data Modelers
6. Be prepared to defend the use of the correct datatype
7. Size matters: know why
8. Stop using deprecated data types. Really. Just. Stop.
9. Monitor RDBMs upcoming features
10.Request enhancements if your tools don’t support an RDBMS datatype.
RDBMS resources
SQL Server Books On Line (MSDN)
IBM DB2 (z/OS and LUW) Knowledge Center
Teradata Online Library
Oracle Database Online Library
…plus all the others…
Community
community.embarcadero.com
Thank you, you were great.
Let’s do this again some time!
Karen Lopez @datachick