SQL Server Design: No Code Necessary
Data pro Thomas LaRock dives into the intricacies of SQL Server design, emphasizing the power of smart datatype decisions.
In the growing landscape of database management, SQL Server stands as a pillar for enterprises, bustling with robust features and functionalities. For database designers, understanding SQL Server's rich variety of data types isn't just an exercise in technicality -- it's about laying the groundwork for efficient, reliable and high-performance databases.
Nobody knows this better than Thomas LaRock, principal data evangelist at Selector Software and expert on everything data and the database. In his own words, he's here to "help clients and customers find ways to enjoy longer weekends."
Ahead of his November Live!360 conference session, titled "Database Design: SQL Server Datatypes," he shared some of his experiences helping enterprise IT reclaim some of their free time back, and lets us know what he thinks of the state of SQL Server today.
And don't forget to save your space for Larock's session in Orlando to discover the keys to optimized database performance through intelligent design strategies, while gaining insights on selecting the most efficient data types, evaluating their performance, and rectifying poor data type choices.
Redmond: Your session description says attendees will learn “how to fix bad [design] decisions” (among other things). Have you ever seen such a bad design decision that it would classify as a disaster story? Can you share it? (Without naming names, of course!)
LaRock: Yes, I have many such memories. I once had the opportunity to work with a database roughly 1TB in size. This database had only one table, with a few hundred columns. A little investigation revealed the datatypes were ripe for optimization. I was able to save 600GB of storage just by changing the datatypes.
Why does bad database design happen in the first place? In your opinion, what’s the biggest culprit?
In modern environments, databases are constructed on the fly, with code-first generation. The end result is we have databases built from a handful of spreadsheets, with little regard for luxuries such as design reviews.
Are there any red flags that pros need to watch out for when they’re choosing their datatypes?
Date times are my first item to review, as the level of precision required does not always match what is chosen. While a difference of four bytes doesn't seem like an issue to start, if you have a billion rows it will add up quickly. After date times, any character columns defined as (max) are worthy of review. Often it comes down to matching system datatypes with business requirements.
In your opinion, what’s been the biggest feature update/change/new capability in SQL Server that’s been the most useful for enabling good design practices?
The work done with data classification and Azure Purview is a step in the right direction. Anything which enables the database designer to make informed decisions.
What’s the biggest takeaway you hope your attendees will get from your session?
That it is possible to identify opportunities to improve performance without touching code.