Q&A
Q&A with Karen Lopez: A Database Designer's Favorite New SQL Server Features
Some SQL Server features are good, some are great -- and some are great only in the right situations and in the right hands.
Senior project manager and longtime data evangelist Karen Lopez knows the difference. Lopez will lead a session in November's Live!360 conference in Orlando, Fla. called "A Database Designer's Favorite New SQL Server Features" that goes far beyond just listing the latest additions to Microsoft's database platform. It will cover the pros and cons of each new feature, how new features can affect your existing applications, and how (or if) you need to change the way you use your current SQL Server tools to take advantage of all these new features.
In a preview of what's coming in her Live!360 session, Lopez recently shared with us her take on the current state of SQL Server.
Redmond: What has been the most impactful addition to SQL Server for database designers and developers in recent years?
Lopez: For me, it has been the addition of security and privacy features because I'm a huge fan of data protection. But for most developers and DBAs, I'm guessing it's the performance improvements around Query Store and Automatic Tuning. These features help the database engine take on the work of responding to changes in workloads and make slight changes to respond so that DBAs don't have to spend as much time working on tuning.
Is there a feature that you wish Microsoft would add, but it just hasn't materialized yet?
Many people are asking for full JSON datatype, similar to the XML one added in the past. SQL Server does support JSON, but not at a persisted data format. It provides features to export relational data in a JSON format, and to parse it into columns, but there is no native JSON support. I'm kind of with Microsoft on this, that storing JSON in the database isn't a best practice.
"Anything that has been deprecated should not be used on future developments. That includes datatypes, functions, etc. There are also features that are nearly universally known as 'never use.'"
Karen Lopez, Senior Project Manager
For me, I want to see persisted data masking in SQL Server. Right now it supports Dynamic Data Masking, which applies a mask just as the data is returned by a query, and that's a great start. But there are business needs to persist only the masked data for some use cases.
Right now, this all has to be implemented by developers, who don't always know what the best mask for a set of data is, so they do the best they can. Then we end up with the same data masked different ways, which could cause a data leak by design.
What's one feature that does exist, but you can do without? Is there one that DBAs should steer clear of?
Anything that has been deprecated should not be used on future developments. That includes datatypes, functions, etc. There are also features that are nearly universally known as "never use" -- auto grow, hierarchical datatypes, etc. While it may be forever until these features are actually not supported, adding them to new databases would be unfortunate technical debt.
What's a little-known or underrated SQL Server feature that you wish more people knew about?
My favourite right now is Ledger Databases and Tables. It's newly available and allows us to leverage blockchain-like features right inside the Azure SQL database to protect data against tampering.
In-database ledger functions mean that we can use the same tools, methods and processes to deliver this added protection instead of having to try to integrate it with yet another set of data tools and locations. Like I said, I love security and privacy features and making data more tamper-proof is a great idea.