The other day I was staring a SQL Server Management Studio and saw the impossible. There, in front of me, was a table build diagram showing that a table had two primary keys.
I had right-clicked on the table and then clicked 'design'. I stared at it in disbelief for several seconds until I realized that the view was trying to show me that there was just one primary key consisting of two columns, but the 'simple' design view couldn't do it. I don't usually go anywhere near these GUI fripperies inherited from MS Access, so I doubt if any of you would have been fooled as easily. However, this thought remained with me: are we inclined to want to make things harder for developers with this sort of cosseting? Do developers really need protection from a simple, rational
CREATE TABLE statement? No. When they need to construct a class in C#, they don't heave out a prosthetic GUI to do it. They just cut code. What's so difficult about DDL code?
Microsoft, unlike Oracle or Sybase, has had a strange cultural aversion to SQL. Although ODBC is SQL-based, the Office tools have always been quick to protect us from going near it. MSQuery in Excel and the Access GUI provide elaborate tools to shield us from SQL. SSIS too makes simple SQL elaborate and complicated, though, I admit, splendidly diagrammatic. When we ask SMO/SSMS to generate
CREATE TABLE statements, we get a scary mumbo-jumbo full of angle brackets and elaborate syntax that is unnecessary because it repeats the defaults. We get weird computer-generated names for constraints for which we don't want to provide explicit names. We get an awkward and unnatural syntax for foreign key constraints, separating them from the column to which they refer. And so on. This is a language dialect created by machines for machines.
Microsoft have somehow made the whole business of creating and altering tables, and their relationships, more difficult than it would be if one stuck to the standard SQL Syntax, but with logically-consistent additions for SQL Server specific extensions. What must change? We need a way to reverse-engineer rational human-oriented
CREATE TABLE statements, and we need proper syntax diagrams on MSDN, rather than the rash of brackets we see there now, enough to put off the most resolute of code-cutters. Might railroad style diagrams help?