Partager via

My Dream SQL Tool Features

  • Article

It's interesting to jump back and forth between both programming models as lately I've been working on a decent size SQL 2K database (4M+ rows). Personally, I'm a Query Analyzer guy and I do all my SQL work there. I'm used to having IntelliSense in VS, which is annoying not to have in QA, but for some reason I live with 8pt fonts for queries, but I couldn't stand it in VS. Go figure. In any case here are my dream SQL Tool programming features:

DTS support for Importing XML

  • I'm uploading a monster (multi-gigabyte) XML file into SQL 2K and you should see the stupid pet tricks I have to do to load it. I *don't* want to manually define the schema and create a mapping file, and a VBS script to run it, etc I want to use DTS and have it see an XML file the same way it sees any other data source. It should inspect the XML file, give me a list of tables/types and let me map them to existing data.

Data Validation Rules

  • I won't elaborate here, but the data I've been working with is of unknown quality. As such I would love a way to easily validate that data. SQL has some easy validation rules (data type, nullability, joins), but I'd love more, like the ability to say that a date must be within a valid range, or that a number can't be negative, etc. I also want to be able to run these validation tests on data that already exists in a DB, in addition to running it as part of the data import process.

Unit Test Support

  • Given the above point on unknown data quality, I would love a way to stub out unit tests to test for data integrity, as well as to test results of queries/views/stored procs against dummy data. You could even have code coverage tools. 

Refactoring

  • Rename - I would love a Rename refactoring for variables, table/view data types and any other DB objects. The refactoring would automatically check and update any dependencies, comments, etc just like VS 2005.
  • Extract to Stored Procedure/Function - How many times have you seen a 10+ page query that is completely procedural and poorly organized? You'll find 1/2 of the code copy/pasted into about five other stored procs too. Having the ability to refactor and componentize queries (where possible) would rock.
  • Extract to Lookup Table - Take a table Customer with a varchar(50) column named State with values like "Alabama, Arkansas,...". Extract the unique values to a Lookup table with the structure like (State_PK, State_text) and have a wizard walk you through setting up the state lookup table name, PK name and type, and take the extracted values/datatypes (the state names) automatically add it to the lookup table in a column with the same name/data type. The original table would of course remove the State varchar column and instead add a foreign key relationship based on State_PK.
  • Convert Columns to Rows or Convert Rows to Columns- There may be a time when you have data of an unknown structure that you use a Row based table structure. A row based structure doesn't define the specific columns, but instead has only a couple of columns like Key and Value. There are times when you need to convert an already existing DB design between these. Although more rare, it's definitely a pain, and something that you could easily automate.
  • Promote Local to Parameter - This would be nice to have, in particular if it was integrated with the Client Code Dependency analysis feature (below).
  • Client Code Dependency Analysis - Assuming an ASP.NET example, I want to know which pages will break if I change my stored procedures, and if it's possible, automatically fix the changes. Let's say I rename a stored proc, the Web page that calls that stored proc won't work anymore. This would automatically update the reference to the correct name.
  • Normalization Analysis/Automation - Show me where my DB isn't normalized and refactor the database (ex remove calculated columns). It doesn't have to hit some uber normalization like 6NF, but how about to 3NF? The key, the whole key, and nothing but the key, so help me Codd!

FxCop

  • SQL Server already has a Best Practices analyzer, which is pretty good, but I want it integrated into Query Analyzer. SQL Server BPA includes rules for maintenance and design issues, and I believe query "no-no's" like cursors which is also great. <aside>: Cursors are the biggest, easily replaced perf hitters I know of, and I've always wanted to start "nodatabasecursors.com" with examples of cursors and why people think they need them and how they can be replaced with SQL.</aside>

Source Control for Stored Procs, Views, Triggers, Functions

  • Nuff said

 

My dream scenario:  I would be able to import data using XML Bulk Insert through a wizard, run some validation code pre/post load, create a stored proc and refactor it automatically, generate unit test stubs for the stored proc, validate via FX Cop and then check in my code to some source control mechanism.

For the DB programmers out there, what other dev tool IDE features do you miss when writing SQL code? Are there any cool tools/add-ins that have the features I listed above that I might not know about?

Comments

  • Anonymous
    February 10, 2005
    What's wrong about check contraints for your data validation needs? (And/or what kind of additional code would you need to make them more easily accessible?)
  • Anonymous
    February 10, 2005
    CN,

    Check constraints are great and I should've listed them in the SQL features, but I want pre-built rules, data masks, and a simple tool do this, as well as more complex features like regular expressions on a field. I also mentioned how I want data validation rules to be run on data in an already existing table. Check constraints wouldn't let me add unclean data to a table. That's a good thing and a bad thing as you sometimes have to live with unclean data. I want a tool to run validation rules on data and not have it be coupled with the table definition.
  • Anonymous
    February 10, 2005
    I know it may not be completely clean, but it is possible to turn the constraints on/off, but of course the results when turning them on again could be presented in a more ideal way than just an error stating that the constraint wouldn't hold.

    Regarding regexps -- isn't that more of a general deficiency in SQL that you don't have anything more powerful than LIKE?

    BTW, I love your refactoring list, where I think that normalization is the hardest. Or, well, I guess it could maybe work if all tables are populated with significant amounts of real data, reflecting all types of variations that are possible in the real world. Normalization is, after all, dependent on the real world.
  • Anonymous
    February 10, 2005
    You're right about regexp (or string manipulation in general) being a deficiency in SQL. The addition of managed code should help alleviate some scenarios, although I don't know if you could use managed code for a constraint (that would be sweet).

    Thanks,
    -Dan
  • Anonymous
    February 10, 2005
    My GOSH...we NEED that XML feature!
  • Anonymous
    February 10, 2005
    The comment has been removed
  • Anonymous
    February 10, 2005
    Dan,

    When you said you were working on SQL 2K, I half expected at the end you would say "guess what, SQL 2005 has all of this".

    Shame =)
  • Anonymous
    February 10, 2005
    I used to to do allot of work in Oracle, and I couldn't live without PL/SQL Developer. In my mind the best DBA tool out there. Its unfortunate they don't have a version for SQL Server. It has a plugin framework so anyone could create plugins. One plugin was a great time saver, you can create shortcuts. For instance type "s*" and it expands to "SELECT * FROM".
  • Anonymous
    February 11, 2005
    Hey TJ,

    Too bad it doesn't work for SQL Server :(

    VS 2005 has a similar shortcuts featured called IntelliSense Code Snippets (used to be called Expansions).
    http://msdn.microsoft.com/vcsharp/2005/overview/productivity/#expansions

    I'd love this capability in a query tool. Query Analyzer does have sql templates, but I have to go to the object browser and dig them out, causing my hands to leave my keyboard, and I rarely end up using them.

  • Anonymous
    February 11, 2005
    A built-in compare utility would be nice too. :-)
  • Anonymous
    February 24, 2005
    we have tool ApexSQL Edit - i'll have author jump on this thread.

    some features from your list:

    unit test control
    source control integration (VSS now but soon SCC and possibly Vault)

    ApexSQL Edit has a ton of other features - intellisense, report integration etc

    here is product page
    http://www.apexsql.com/sql_tools_edit.htm

    here is author's blog
    http://apexsqledit.blogspot.com/
  • Anonymous
    February 24, 2005
    I am the author of the tool mentioned in previous post (ApexSQL). As mentioned in a prior post, I used Oracle and loved PL/SQL Developer but when using SQL I was stuck with QA.

    That's why I wrote ApexSQL Edit, to provide basically what I needed in my day to day SQL querying needs.

    I have thought about trying to add refactoring to my SQL tool and thought it would be nice to pull stuff out of procs and refactor it into a new stored proc. Edit does have Unit Testing although it is currently in flux and my ultimate goal is to integrate the testing with Team System when available.

    Now if I could get Microsoft to give me the debugging API for SQL :)

    Oh, BTW, I like the row/column item since I have needed that before and just wrote big honking queries using case statements,

    case when column = 'Value1' then 1 else 0 as Value1,
    case when column = 'value2' then 1 else 0 as Value2

    So I may add that to our next release of Edit.

    Anyway, all good items that I wish were available, but at least I can try to add them to our tool :)
  • Anonymous
    February 24, 2005
    Hey Ashton,

    Thanks for the comments, the screenshots of ApexSQL Edit look sweet! I'm going to have to download it and take it for a spin. I'd love to see any new features add it to SQLEdit, ping me if you add new features in future releases.

    Thanks,
    -Dan
  • Anonymous
    June 10, 2005
    Continuing the Sql theme... sure we have all been there before where you're looking at someone else's...
  • Anonymous
    August 07, 2005
    You can get Intellisense-like autocompletion for Management Studio & Query Analyzer (and VS) here: http://www.promptsql.com/

    Basically its a C# program that sits in a separate process using system hooks to see when you press Ctrl-Space in Management Studio and then grabs the SQL fragment you are working on, using ANTLR to parse it, and pops up an auto-complete list, pretty much like Intellisense.
  • Anonymous
    February 13, 2006
    Just downloaded the trial of promptsql... seems like a convenient tool for our large e-mail marketing database...