CYCLOMEDIA

If like me you work for an MS shop you might find yourself often switching between MSSQL's Management Console and Visual Studio. You will also notice that they both present different concepts - Database internals and source code files - in a roughly similar manner. In both you have a large source code and visual interaction pane alongside a tree based overview pane. What's interesting is the different concepts both take.

SQL Server Management Console will gladly show you the source code to any of it's objects, but will only ever give you one at once. It also internalizes the physical definitions of these and groups them in the tree view by type with limited "namespacing" available in the form of dbo's and object names containing a lot of underscores.

Visual Studio's Solution view, on the other hand, presents you exclusively with a view of the files in your projects but gives you no clues on what those files contains. With careful management you can name your projects and subfolders to match their namespaces and files to match their classes, but that's not always possible. Once you do get to the individual function, property or variable definition you want to manipulate you find yourself surrounded by a sea of code, namely the rest of the code in the parent file. Code regions can help mitigate this but they are a blunt instrument.

Imagine being able to worry less about managing files and wading through all that code by having the Solution Explorer present each file/classes members as a new level of sub tree, and allowing you to edit just that node, namely the function and nothing around it.

One other consideration is that MS's source control tools are also file centric, meaning you will normally lock an entire file just to edit that one function. If the above level of drill down was also available to that then you would only need to check out and edit the one function, you could even view the history of just the one function or roll it back, reducing the reliance on crude text file diff utils.

Ultimately a tight coupling of Solution, Source Control and SQL integration could result, blurring your database's stored procedures with your basic class functions to the point where the database stops being a mysterious second class part of your projects and a first class right-there-in-the-solution citizen, able to be built and poplated afresh *from scratch* whenever you hit "Rebuild All"...

This last point is actually quite a big deal, I've only worked at one place where they not only had a nightly compile but a nightly database build. Everywhere else slapped together "clean" dbs from backups, copies or by trying to work out what had been changed on the dev server since the last release. Being able to host your app's DB right their in the Solution Explorer and build it clean on demand without having to see the raw scripts will properly bring the Visual to the database

Now that we're in an age of copy-on-write filesystems and persistent storage it'd be great if this way of thinking could seamlessly bubble up into SQL. At the moment us DBAs have to enact our own timestamping policies, usually involving triggers to record a timestamp against a new data update and its old value. What I'd like to see is an intrinsic AT clause that you can just tack on to the end of any SELECT. The example below gets the sales totals to date at 6pm yesterday, using some freaky psudeo date munging:

    SELECT * FROM tblSalesYTD AT (DATEPART(GETDATE()-1) + 0.75)

You could tie it directly into any web page to show how it looked (CSS and HTML design aside!) at a particular time.

    SELECT * FROM vwLatestNews AT @DateParam

Or use it for interesting rolling subqueries

    SELECT 
        (SELECT Price FROM tblStock WHERE ProductID=@ProductID) AS Price_Today,
        (SELECT Price FROM tblStock WHERE ProductID=@ProductID AT GETDATE()-365) AS Price_One_Year_Ago

It wouldnt take much more expanding on the above to be able to instantly generate trend graphs without needing intermediate tracking tables. If your data can change over time (Prices, Stock Levels, Logged in Users) and you have a single field that tracks the current state of this you'd be able to find out what it was at any time.