New SQL Server Features Database Designers Favorite — Redmondmag.com

Questions and answers

Q&A with Karen Lopez: New SQL Server Features Database Designers Favorite

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 at November’s Live!360 conference in Orlando, Florida titled “A Database Designer’s Favorite New SQL Server Features” that goes way 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 might 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 to come in her Live!360 session, Lopez recently shared with us her perspective 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 was the addition of security and privacy features because I’m a big fan of data protection. But for most developers and DBAs, I guess it’s the performance improvements around Query Store and Autotuning. These features help the database engine support the work of responding to workload changes and making small changes to respond so that DBAs don’t have to spend so much time working. on the setting.

Is there a feature you would like Microsoft to add, but hasn’t yet materialized?
Many people are asking for a full JSON data type, similar to XML added in the past. SQL Server supports JSON, but not in a persistent data format. It provides functionality to export relational data in JSON format and to parse it into columns, but there is no native JSON support. I kinda agree with Microsoft on this, that storing JSON in the database is not good practice.

“Anything that has been deprecated should not be used in future development. This includes data types, functions, etc. There are also features almost universally known as ‘never use’.”

Karen Lopez, Senior Project Manager

For me, I want to see persistent data masking in SQL Server. Right now it supports dynamic data masking, which applies a mask the moment data is returned by a query, and that’s a good start. But some companies need to keep only masked data for certain use cases.

Right now this all has to be implemented by developers, who don’t always know what the best mask is for a data set, so they do the best they can. Then we end up with the same data masked in different ways, which could cause data leakage by design.

What is the functionality that exists, but that you can do without? Is there one that DBAs should avoid?
Anything that has been deprecated should not be used for future development. This includes data types, functions, etc. There are also features almost universally known as “never use” – auto-grow, hierarchical data types, etc. would be an unfortunate technical debt.

What’s a little-known or underrated SQL Server feature you wish more people knew about?
My favorite right now is databases and general ledger tables. It is newly available and allows us to leverage blockchain-like functionality directly in 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 provide this additional protection instead of having to try to integrate it with another set of data tools and locations. Like I said, I love the security and privacy features and making data more tamper-proof is a great idea.

About the Author

Gladys Rama (@GladysRama3) is the publisher of Redmondmag.com, RCPmag.com and AWSInsider.net, and the editorial director of Converge360.

Maria H. Underwood