Thank you Kevin for hosting this month’s T-SQL Tuesday! It’s an interesting topic to reflect on features we wish we saw in SQL Server. I’m excited to read some of the other ideas and maybe we’ll see one or two get pushed out in the future!
I have three features I’d love to see from SQL Server. Two of these mostly exist already, but they are not easy to utilize. I wish so badly that SQL Server could generate smart Entity Relation Diagrams (ERD) and documentation. Really, I wish it could create the artifacts the way Redgate does. If I had a third feature to wish for while knowing it would be unrealistic, but I’d love if they had a feature to change the culture of a workplace.
Entity Relationship Diagrams
I know SQL Server has the database diagrams feature. (Which was removed and then put back in.) I’ve used it before and it can be handy for what it is, but overall I don’t really like it. I generally use another ERD tool if it’s available. At work I currently usePowerDesigner, but I do really love the Redgate ERD tool. For home, I use DBSchema as it’s pretty cheap for personal use.
DBSchema and PowerDesigner allow for basic modeling and have many more options. You have pre-done templates and icons for conceptual, logical, and physical models. You can export the diagrams in a variety of file types. There are so many different notations and tools you can use within each program to create a handmade ERD that you don’t get in SSMS.
What really makes the other tools so magnificent though… is the ease reverse engineering a database. You can quickly select objects and deselect objects to have a more fine-tuned view of dependencies and interactions in the database. Being able to see an impact analysis of changes from the ERD are critical. How helpful your tools are, depends on how well your database is set up. If you are inconsistent with naming, foreign keys, indexes, etc., any ERD generation tool is going to have problems.
While it also may not be possible currently, I’d love to see these tools be able to generate conceptual ERDs. A lot of the reverse engineering features focus on the physical layer to ERDs. Creating conceptual and logical ERDs may be possible in the future with machine learning and AI.
Database Documentation
If SQL Server could output some detailed documentation on databases, this would be another amazing feature. Here’s a document I produced with Redgate for a personal at-home project with their trial software. To me, I’d consider this a physical data dictionary. It’s great for viewing references, quickly extracting notes from extended properties, or verifying data types or usage in the database.
This is the kind of feature I want in SQL Server. More than just having this ability, it needs to be just as easy to generate this documentation. PowerDesigner has similar capabilities and even more. The issue I have with PowerDesigner in this realm is that they trade ease of use with capability. The abilities of PowerDesigner look endless, but there is a significant learning curve to mastering this. I want to be able to generate my documentation easily. I don’t want to put 80 hours into learning a new platform to add a few rows to a report.
Culture Change
Wouldn’t it be amazing if we could use technology to drive culture change? I don’t really know where I’m going on this request, but I’d love a magic wand from SQL Server to make others see my viewpoint.
A skill I’ve had to learn over time is how to work with people. I don’t just mean getting along with people and being a generally friendly face to work with. I mean I had to learn how to give constructive criticism and how to receive it. I’ve had to learn how to champion my own views and convince others they are the way to go. I’ve also had to learn when to back down and accept that I may be wrong.
It can be difficult to admit you’re wrong, especially when it’s related to your career. This is generally more of a human problem, but technology and policy can affect and guide culture change.
I wish it was possible for SQL Server to quickly lay out the logical facts and present the argument. For example, should we use a trigger, stored procedure, embedded SQL, a view, or dynamically generated SQL? More than what should we do, why should we do it and how can the technology help enforce or push this position? How can it help our colleagues accept the method it chose is the correct way to go? Can it be done with a click of a button?
Normally it’s the data professionals’ job to help guide and fight with their expertise. They should be advocating for the database when necessary to protect the company and their data. With the rise of AI and machine learning though, maybe technology generating culture change in 20 years isn’t a farfetched idea?