For this week’s What If series, we’re exploring what would happen if you had access to SQL Server’s source code – like if you got a job at Microsoft, signed a partner NDA, if the code leaked, or if it went open source.
Today’s question is, “What would you fix or change?”
Erik says: Dynamic SQL
- If you’re using NVARCHAR strings, you need to prefix all of your string concatenations with N, or you could end up silently truncating your final product.
- If you use CASE to branch concatenation paths, it can also silently truncate a string.
- Figuring out how many single quotes you need to properly quote things is awful, sometimes.
- PRINT is limited to 8000 characters, RAISERROR even fewer.
- If you’re using sp_executesql, it’s a real chore to get the variables as the query ran without additional logging.
- Concatenating non-string data requires CAST/CONVERT.
- Sometimes surprise NULL concatenations leave you with EMPTY strings.
There’s a lot that could be done to make dynamic SQL easier (and safer!) to use. This may not be terribly popular, but hey, if I take the popular stuff, you won’t have anything to comment on.
Brent says: I’d Fix the Database Tuning Advisor
The DTA is one of those great ideas that only works if the developers are allowed to continue to put work into improving it. v1.0 simply doesn’t cut it for a product like this. While Microsoft has put more work in, it’s only been to suggest even more kinds of indexes, not make smarter index recommendations.
So it’s time to get out the knife and do the needful. Sorry, Clippy – your parents just never could afford to send you to college, and I can’t let your greasy hands on my database anymore.
Tara says: I’d get rid of two deprecated features
I’m still recommending Database Mirroring to some clients because Basic Availability Groups requires a Failover Cluster. I tell the clients that Database Mirroring is deprecated, is still fully supported and still available in SQL Server 2017, but that I’m recommending Database Mirroring because of its simplicity. If it meets their RPO/RTO goals, why should we complicate things?
Though I know we should be using Extended Events instead of Profiler, Microsoft is going to have to cut the cord. But before they do that, I hope they provide a simpler GUI for Extended Events. Keep the flexibility of the current GUI, but also provide a simple version of it that Accidental DBAs can run. Most companies don’t have the luxury of having a very experienced DBA.
What about you? Leave your answer in the comments.