There is much that has been said about “eating your own dog food“. Indeed, there is no better way to ensure that what you are building is of real use. It’s not always possible mind you, as is the case in our company, where we provide data and analysis software for the energy and engineering segments, and we aren’t in either of those businesses. As a result, our products keep our customers very close in design and implementation for our best success.
My head did a bit of a turn sideways to contemplate a very odd thing the other day. I’ve been working in C# on .NET 2.0 creating a relational database walker to do a transform/load into a custom schema and management system we need to work with. As I’m creating this system, I need to discover the primary keys in the Microsoft SQL Server 2005 tables I’m crawling. Oddly, the metadata doesn’t seem to contain any such information. There are some constraints, procedures, and the table and columns of course, but the primary key is absent. As the SQL Server Management Studio shows the primary key, I’m pretty sure I’ve done something wrong. So into Google and MSDN we go.
The part that made my brain do a serious double-take, and say “I emmust/em have read that wrong, was looking at the ADO.NET 2 documentation, specifically this page on MSDN. It states quite clearly that the SQL Server provider doesn’t do primary keys. But emOracle’s/em provider does. Apparently SQL Management Studio has a connection with the greater cosmos that allows it to magically divine primary keys from the fabric of space-time. After some more searching, I’ve found this gem from the Program Manager of SQL 2005/Whidbey. I appreciate it “bothering him”, but the management studio obviously wasn’t eating the dog food of the Whidbey release on the schema collections, and is instead using some alternate mysticism to achieve the desired results.
By the looks of the XML file Carl provides, it appears the magic is within the mystical (and decidedly specific) System Tables. This is obviously up there with a hack, as it’s a graft into the .NET config files of the workstation you install and run on, but given the relative simplicity of the configuration change, I’m rather baffled as to how this was missed in the product release. Again, from a dog food perspective this wouldn’t have been missed without disabling the entire SQL Server Management Studio from being useful at all if the only public remote interface was the one that the ADO.NET system provides. It would have improved the .NET system and the SQL Server Client metadata at a minimum.
So the short of it is, thanks to blogs and other “out-of-channel” communication, there is a very awkward work-around to what seems to be a very fundamental oversight. But again, if you want a better product, dig in an use what you build as much as possible. Adhere to your own published interfaces, and if you have an API, make sure your other products are using it, and not some other obscure method for integration.