Imagine this scenario: you're working on a piece of C# or VB.NET and you eventually find yourself in a data-access layer bit of code that refers to a stored procedure, such as:
What's your inclination at this point to examine its contents? Open up SQL Management Studio, connect to the right database (a number of clicks and keystrokes), scroll around to find it (or filter for it, which is a certain amount of clicking and typing as well)? Or you could use Server Explorer within Visual Studio - but are still left to hunt-and-peck for your objects.
With SQL-Hero's Visual Studio integration, just highlight the object name, and hit Alt-Q followed by Alt-L (or use the Edit -> SQL-Hero Find Object menu option). You're prompted for the database to look in:
Select the desired database, click OK, and voila: you've got the procedure open, ready to go:
A variation of this situation can happen if you're working on a SQL object, like a complex reporting stored procedure. If you highlight a table name, just press F2 and suddenly the table is selected in the object tree on the left - ready for you drag column names from, into your SQL, for example. There's an option to locate objects based on what's in your clipboard, as well - very handy!
What if you can't remember all of these nifty shortcut keys? Since tool buttons lay out broad functionality, you can explore the tool-tips to get the applicable short-cut:
There are numerous capabilities built into the SQL-Hero editor to make locating and working with objects easy. For example, the Locator tool lets you use wildcards to quickly pick one or more objects. In fact, the editor tree allows you to multi-select nodes, set properties on them, script the associated objects, drag them to an editor window to get their names in a convenient list, and so on. (Note that this kind of multi-selecting is
not available in SSMS 2008.)
There are some unique properties for different object types that SQL-Hero manages, above and beyond what you'll see in many other products.
Let's suppose in the same procedure you have a SELECT statement that includes some references to local variables or procedure parameters. If you wanted to run this with some test data to diagnose an issue, you might be tempted to copy the text, paste it into another window and manually change the variables into literal values. An easier way is to just highlight the SELECT statement and press F4. You're immediately prompted for values for each of the variables. When you click OK on that dialog, you can have the resulting text placed into a new editor window to continue your testing.
Furthermore, these parameters you've entered here are "remembered" across invocations, including when you use the window to enter parameters to run stored procedures.
Now let's suppose you have a code table embedded in some of your SQL statements. What if you want to quickly look at its contents? Open an editor, type a select statement and execute it, only to have to switch back to your prior code afterward? Scroll around looking for the table to get its data? No: highlight the table name and hit F11. A grid pops up, with the table contents and clicking outside of it or hitting Escape closes it instantly. You don't even need to highlight the real table name: it'll look for a "best match" so you could for example highlight "PatientID", hit F11, and the "Patient" table would get shown, if it's the best match to "PatientID". It's a small thing, but it saves time.
In the spirit of saving time, rather than having to highlight the SQL text you want to execute, then hit F5 to run it - with the SQL-Hero editor, just put your cursor anywhere in the statement you want to execute and hit Ctrl-E... or simply click on it with your middle mouse button! Another convenience can be found when you're investigating your result data. Say you have a result grid that contains a foreign-key to a code table you want to look up a value for. Pick the cell with the value to join with, hit Ctrl-J (or use the context menu option for "Join To"), and give the table the column is linked to. (A table name is inferred based on the column name, but you can override this.) The SELECT statement for this is scripted into the editor and executed for you, all in one step.
Even something as simple as having a list of the most recently objects you've worked on, where picking one automatically brings you to it (and its code, if applicable)... it's not a big thing, but it's a task we tend to repeat and repeat as developers. (And this list is preserved across sessions, too, along with almost all other settings and state.) Or how about using a single-click or short-cut key to navigate back to the previous object you had selected? Or how about having a list automatically maintained of your most recently executed queries? These are part of
all editions of SQL-Hero.
Another variation of a recent used list is the tracking list. To illustrate this, consider a SQL script where you ALTER a table, create a procedure and an index. After you've executed this script, the tracking list would show:
Taking the above example one step further, let's say you open the procedure that's been created and change its name. If you were to execute it now, you're asked if you're creating a new object or renaming the old one - something SSMS isn't "aware" enough to do:
Another scenario has us making a change in a view in one database, and we need to make sure our changes are also realized in another region - say a staging database. We can accomplish this with three mouse clicks. Click on "Execute In", pick the database or databases to execute the SQL in
(same dialog that we've seen before), and click OK. Done! Similarly, say you open a procedure to alter and then execute it in a different database (but forget to change the ALTER to a CREATE) - SQL-Hero is conscious of this and handles the change for you automatically:
From this example you can tell how important connection management / aliasing is: you're able to publish connections globally among your team, but apply different credentials, if desired. Connection aliases are used throughout SQL-Hero and can be grouped conveniently:
SQL-Hero administrator privileges can be set on the user management screen, with thought given on how to secure certain settings among your team. You can achieve a great deal of security, even with SQL Authentication, whereby developers don't need to be given connection details. This centralized connection data is encrypted as well.
Let's consider another scenario: you're working on some T-SQL in a view and make a change. You have good intentions but are quickly distracted onto your next task and forget about adding a comment. Six months later a new developer is tasked with maintaining your code and is unsure what you were trying to fix before. Maybe you're not there anymore (if you're lucky?) - but maybe you are and are forced to try and recall the arcane details of your change.
With SQL-Hero comment prompting, when you hit "Execute" to commit your view change in the first place, you might have seen a dialog such as this:
If you cancelled out of this, your change would not have been saved. Note that you
had to provide a comment to click OK. In doing this, your view would have been updated to look something like this:
No more guesswork. (Not to mention the fact that comments are also stored in a queryable form as well.) You can also customize this commenting feature based on your own standards.
As you can tell, many of the product advantages are about streamlining your development process. Another example is the use of dynamic snippets. For example, type in "inssel" and hit the Tab key twice. You're suddenly prompted for a table name. Once you pick that, a SQL INSERT statement is crafted for you, sourced from a SELECT statement. This is an example of a template that's been run - many pre-defined ones exist, but you can also create your own. The powerful template language has over 100 keywords and is used in numerous places by the application. It's fully recursive, as well, opening the door to some interesting capabilities many other code generators can't match.
A template with a unique twist is the "DELCARE temp table from a SELECT" template. Shown below, we have a SELECT statement and actually create a DECLARE TABLE statement based on the columns that come back from it (the input to the template is SQL itself!):
Templates allow you to adapt SQL-Hero to your organization's standards. As one example, when you elect to create a new stored procedure, a base template defines the initial content including things like the header comment block, standard parameters, etc. If you wish to change these, you can modify the base template and have it instantly available to
all developers if you're using the SQL-Hero application server. You can also change defaults for user-defined properties, substantially adapting code generation to suit your needs if they deviate from the out-of-the-box settings.
In addition to making your development process easier, SQL-Hero includes your very own built-in DBA to provide performance tuning advice. Below is an example of an execution plan where color coding helps very quickly identify problem areas - especially useful with large plans.
Productivity enhancements are often about little things. An example is in providing a quick way to navigate from the list of dependencies for an object, to the dependent object itself (just double-click it):
Similarly, you can quickly locate the object referenced by, for example, a foreign key:
The "locate declaration" command can be used to jump from a variable name or table/view alias to where the object is declared in your SQL code:
There are a lot of commands that if you know the key combination, you'll be amazed at how handy they are. Another example is the "Copy Column Name" command. In the example below, we've typed part of a line that we wish to complete with the text that is already reflected earlier on the line - namely a column name from a table. If at this spot we hit "Alt-Period", the text completes with @FrequencyType - we saved 11 keystrokes!
If you need some quick help on SQL syntax, the SQL-Hero editor also integrates with Books Online directly. Here we've highlighted "ALTER TABLE", clicked on "Object / Syntax Help" and BOL is launched in a browser, on the correct topic:
Consider yet another scenario you might have faced. Say someone e-mailed you a list of invoice numbers. Now you need to perform some research or update them - basically you want to construct some SQL that includes them in an "IN" clause. You could paste them into an editor, quote them up, add commas, etc. manually. Or, you could use the "Parsed Paste" feature in SQL-Hero:
From the above dialog, clicking OK puts the previewed text into your current editor window, and from there you're a lot closer to constructing your desired SQL.
Speaking of "IN" clauses, another neat way to construct one is from a result set. Templates can be run against many different kinds of entities - not just database objects. Getting the context menu on the result set here, we can see some template choices, including "IN Clause from Result Rows." If we pick some rows and run the template, an "IN" clause is constructed for us, put on the clipboard, and we get the following when we elect to paste it into an editor:
One of the great benefits of SQL-Hero is the tight integration of its various pieces. Being able to navigate to an object that could be listed in history or tracing or a schema compare is one example. It also integrates well with things like MS Office / Outlook. You can export most grids to HTML and paste them into e-mails or save them to Excel with high fidelity.
Most grids in SQL-Hero can be sorted, grouped and filtered against, as well. In fact, most grids preserve their layout and defaults can be set that preserve across sessions.
You can even search through your result set grids for text: one of dozens of use cases that address real scenarios data architects sometimes face when trying to investigate issues.
And yes, SQL-Hero has SQL statement completion built in (and it works with 3-part names!), along with auto-capitalization of keywords. You won't have to go out and buy SQL 2008 to get this! It works with SQL Server 2000, 2005 and 2008. (Or maybe you're looking to get this kind of functionality for MySQL or Oracle? It's coming.) To illustrate how SQL-Hero tends to go a few steps further than most, take a look at this example of auto-completion:
Notice that comments, set on tables, views, columns, etc. are shown in tool-tip form, while using auto-completion! This is a great way to establish a data dictionary that can be leveraged by your entire team. And it goes one step further: these comments are transformed into XML comments on the code generation side, when you build your business objects! How easy is it to set these types of user-defined properties? It's an option available for most object types, with the list of properties changing based on object type, and properties are strongly-typed, thus allowing for data entry like this:
User-defined properties are flexible enough to be flagged as being database-neutral or database-specific, and can be linked to look-up lists (another form of typing to ensure integrity of your meta-data). UDP's can also be expressed using XML and included in schema objects' comments - and these can be picked up even if you do not use the SQL-Hero Editor. This ability to aggregate schema changes into a central repository, track user-defined properties enterprise-wide, schedule tests that run in the background, and deliver notifications is a function of a Windows service that can be installed as part of the SQL-Hero Advanced and Enterprise Editions. The enterprise-level central repository continues to take on new capabilities as SQL-Hero
evolves.
An important feature is the ability to apply filters against the list of visible objects. The tie in with user-defined properties is seen here where you can even filter based on user-defined property values:
There are also "quick" ways provided to filter, give we tend to do that a lot... shown here, you can type a name filter right in a toolbar text box, hit Enter, and have a filter applied instantly: all one click away. The same is true for searching the
content of objects.
This filtering capability is used in a number of convenient places within the application, not just the SQL editor. You can even apply object coloring based on different criteria, making it easy to quickly
see the state of your database:
A quick search feature allows you to filter by name with minimal clicking, plus you can do advanced searching of the content of objects using terms like "AND", "OR", "NOT" and "NEAR". You can even do regular expression content searches.
Back to coloring: you can also associate colors with database connections and then use this fact to color your SQL editor tabs, removing the need to show the database name in them, saving screen real-estate (you can also relabel your tabs manually, shelve them in one click, and more). You can also associate a hot-key to a database connection, allowing you to flip to it quickly.
Another feature of the editor is the ability to use coloring to illustrate your parenthesis (and BEGIN/END) pairings - especially useful when you use a lot of nested SQL functions. So many of these features are what we as developers would have expected to have all along!
This is actually a summary of
some of the editor's features. Others include: automatic backup of your work in case of a crash or power loss, a "favorites" list, "pinning" of editors to prevent them from being auto closed, different options to navigate and clean up your editor tab workspace, and the list goes on.
A natural extension of the SQL Editor is the ability to have integrated SQL source control. This feature differs a bit from the implementation of some other SQL source control tools -
the focus is on simplicity and transparency, while still offering the flexibility of fully functional sandbox databases you can work with in both connected and disconnected situations. (In addition, the concept of "shelving" is indirectly supported through the "Quick Save" and "pinning" features.) A special filter can be used to show items that are checked out to you, which can be very useful when combined with automatic checkout to keep track of objects that you've recently worked on.
The source control engine is configurable on a number of levels. It also works non-obtrusively when disconnected from your central source-of-truth database, supporting the design goal of letting you work with fully functional sandbox databases which might be on your laptop.
It's also easy to see what you have currently checked out, in summary form:
Another scenario: someone has accidentally dropped a stored procedure after having made a number of recent changes to it. As such, your most recent backup with it is old. You might bite the bullet and do the restore and lose the most recent work, or you could simply do a history search for it - for example:
Here we've captured every schema change - whether you use the SQL-Hero editor or not - and can report on these changes using a number of useful search criteria. Yes, you could do similar things with DDL triggers (for free) or other third party tools. SQL-Hero, however, puts it in a convenient repository that can span any number of databases in your organization. And you can with a couple of mouse-clicks compare any version of the procedure and see it with easy-to-understand color-coding:
(This kind of side-by-side comparison is available in numerous places throughout the product.) Getting a complete history for any object is as close as a right-click away. Note that these are all of the
actual object versions as they existed (and potentially ran) in the database, not a file-system source control view of the database. This is incredibly useful information from a number of perspectives: from development through to audit.
This history search screen has powerful criteria including full-text searching, and the results can also be manipulated in very handy ways:
You can "label" existing objects manually, something that can be useful ahead of applying major changes:
You can easily configure which databases will receive automatic tracking (even outside of the SQL-Hero editor) and optionally specify conditions that prevent schema update (note: SQL 2008 includes that ability - you can get the same effect using SQL-Hero with SQL 2005 - even the SQL Express versions).
Notice that with SQL 2008, SQL-Hero will allow DML tracking even if not using the SQL-Hero editor. It's important to note that many SQL source control mechanisms that rely on scripts can be circumvented - and even if we assume a pure process, developers still often need to work against live databases to debug and troubleshoot. SQL-Hero closes an audit gap that exists whether we want to believe it exists or not. On the topic of audit, SQL-Hero's application framework elements deal with data auditing in an easy, declarative way. The framework also provides built-in ways to transform audit history data into different forms that can be easily consumed by your applications. Templates and user-defined properties play well together to give many choices:
The history tracking mechanism adds very little overhead to your development process. One particular customer has nearly 4,000,000 change records and history queries are snappy and checking for region differences is very fast. What exactly are "region differences"? This is a feature whereby you can get a report that shows if someone has updated a region such as say production and
not updated the staging region that should be the source
for production. How can this happen? We've seen it happen when someone (usually in haste) applies a hot-fix and forgets to "back apply" it to the regions that precede it. This speaks to SQL-Hero's ability to enforce compliance and how the complete schema audit history can be leveraged in many different ways. Note that the region difference tool is not simply a schema comparison: you're doing a very stylized kind of comparison that's using change history as its basis. Also, region differences can be assigned to developers for resolution, where the change history tells us something about object ownership. Here's a view of the region differences report:
These region differences can also be tracked by the notification engine, letting you do things like send automated e-mails to team members to advise about such things as changes in production objects, region difference violations, SQL errors or performance threshold failures in production, and more:
Here's another scenario: you've got a report stored procedure in development that should take about a minute to run. Someone has changed an index without realizing the impact on this report. A typical occurrence would be this index change makes its way to production and you suddenly have an issue when it takes hours to run.
If you set up a unit test on this procedure in your staging region and schedule unit tests to run say nightly, you could have arrived in the morning to a screen in Visual Studio (or the SQL-Hero standalone executable) showing something like this:
Suddenly you're aware of a problem and can track down the issue before it hits production. You can even perform tests looking for expected results, or by simply "exercising" the object by running it, problems can often be uncovered proactively. Note that in cases, calling sp_refreshsqlmodule will
not reveal all types of problems (such as dropped tables) - actually invoking the object is the surest way to go and SQL-Hero makes this easy. Test meta-data is stored in the same central repository used for code generation and history tracking, among other things. One can even automatically construct tests based on prior executions (as captured in SQL traces):
SQL-Hero makes it exceedingly easy to set "expected results" against object output, as illustrated here:
Once you've set up test parameters on a stored procedure, these are also exercised when you update the procedure in the SQL-Hero editor, so you're made
immediately
aware if a problem is introduced. (Notice in this example, this is an error that's only returned by the actual execution of the procedure.)
A related option, "Copy, Test, Rollback" allows you to see if there would be any errors introduced by copying one or more objects to a database of your choice. In the example here, we're told that a table is missing in the selected target database:
Since a barrier to testing can be the perceived
time it takes to establish test cases, SQL-Hero offers some automation in this regard. You can establish a global "test requirement policy" that includes the ability to randomly generate tests:
With this testing capability, how do you know how well your SQL code is covered? Reports are available to help highlight code coverage:
SQL-Hero pays attention to detail. All of this unit testing functionality is driven off of a SQL-Hero template in the back-end, and that template can be used in many different ways. When it gets used to optionally test objects you've scripted from one database to another, you're basically running these tests in a SQL editor window. This could result in having a large number of result sets appear - possibly so many that you run into memory issues. SQL-Hero addresses this by letting you specify the <EDITOR_SUPPRESS_RESULT_SET/> tag in a SQL comment, which prevents result sets for the current command within the batch from being rendered. This is used by the unit testing template - but can also be used anywhere else you might find it helpful.
So where can you go to view unit and performance test results? The notification results grid is a convenient one-stop location to view this, object changes and code generation invocations.
Going above and beyond existing tools like SSMS, SQL-Hero extends the ability to test into numerous areas of the tool. For example, when you "check syntax" now, your unit tests will be run at the same time:
Another example of streamlining your workflow is the one-click tracing feature. Imagine being able to fire up a SQL trace that easily - right from within Visual Studio. In its simplest form, you can do this with SQL-Hero. With the resulting trace data, you can perform many useful operations such as scripting statements directly into an editor window, select stored procedures listed in the trace and do in-grid filtering. (This doesn't even get into the advanced analysis and reporting features that are available that work with trace results.)
Going above and beyond other tools, the tracing tool offers you options such as being able to load (and analyze) a subset of your trace data and merge multiple results:
In addition, you can store trace data in the central repository and then later retrieve it using advanced search criteria. Plus some reports are available that work with stored trace data to analyze trends and identify problem areas.
Trace results can be transferred into the repository on a schedule, which when combined with the notification engine, can alert you to problems such as long-running procedures or procedures that take longer than you'd like.
Complementing tracing is the monitoring tool. Being right within Visual Studio offers convenience for SQL developers who also do .NET development.
Alerts and notifications make events of interest immediately apparent and can trigger actions such as taking a monitoring snapshot when blocking is detected.
The cross-functional nature of SQL-Hero permeates the product. For example, tracing and unit testing are linked indirectly, allowing you to construct more realistic unit tests based on previously collected trace data, which in turn can be analyzed by other tools within the product as well!
Many of the above features may seem like little things - and the little things
do add up... but SQL-Hero is also about some powerful fundamental features such as application generation and scaffolding. Its MSBuild integration supports some complex - and unique - code generation scenarios that are easily supported. For example here we see four different templates that can generate useful SQL (and execute it), and code files can be created (integrated with optional source control) from a wide variety of meta-data. You can even run SQL-based unit tests as part of a "code gen" since it's all template-driven. Take a look at our
future plans to get a sense of the scope of where we're going with this. SQL-Hero is the foundation for tested production systems now and in the future.
Each code gen step is highly configurable and based on plug-in design patterns:
Furthermore, SQL-Hero is built on top of an application framework that's available for others to use, royalty-free. The CodeX Framework allows SQL-Hero to live in it's multi-tiered form, communicating with back-end WCF services using HTTP or TCP. SQL-Hero eats its own dog food in a big way.
Many application frameworks assume a one-to-one mapping between tables and business objects, but the CodeX framework is more flexible than this. You can use stored procedures, views, and virtually any other source that can be described using XSD (e.g. web services). At the same time, it strives to perform well, while still offering maximum flexibility via layering and extensibility points. Imagine being able to automatically create .NET enumerations that are in-sync with values in tables (and have your business objects use these enumerations for property accessors), create strongly-typed procedure wrappers that can populate business objects (or be called as static methods), create SQL objects that support auditing and concurrency (declaratively), and have all your business objects built off a combination of database schema
and rich meta-data, in a single step. One of the basic principles is to encourage compile time errors in the face of breaking schema changes, rather than having run-time issues appear, leading to much more robust applications than might otherwise be possible. These facts are being used quite successfully in production systems - without a lot of the learning overhead associated with some frameworks. SQL-Hero's
design goals lay out a path for evolution that will be
complementary to many existing technologies. Here we see an example of a non-trivial procedure that accepts inputs and returns a result set where the .NET code has been generated with little effort to support this in a strongly-typed manner.
Next we see some settings that can be used to control things like concurrency checking, done per entity. One particular concurrency mode allows you to
merge changes from different users to a single record, in a non-destructive manner. This is mentioned because that level of functionality is rarely seen in other products, yet this is only
one small example of the kinds of things that can be done using SQL-Hero.
Here we see how the Visual Studio build integration ties back into something the framework offers: the ability to customize not just .NET components, but generated SQL objects as well. Unlike many tools that build applications, you aren't forced into one specific way of doing things.
These are very basic examples: the kinds of things you can do with CodeX might just surprise you. More white-papers and videos will explain this in more detail. Through plenty of experience, we understand there can be a fine line between over-architecting solutions and we see that in some products today already. SQL-Hero is built on many simple principles that work and most developers grasp well, as proven in real-world situations. You can also use LINQ to Objects with CodeX very effectively. CodeX also includes code that supports not just a specific application tier, like many application frameworks: it aims to support
all tiers using proven foundations that work with technologies such as WPF, the WPF Ribbon and Grid controls, WCF, and so on. The ultimate aim is to deliver as close to one-click application generation as possible, codifying numerous best practices, and offering plenty of flexibility.
Related to code generation, SQL-Hero promotes team collaboration through its notifications engine. For example, when a code generation is invoked or completes, messages can convey status to the entire team:
The notification engine has tremendous flexibility as evidenced by the configurations detail screen for a single notification instance:
Notifications can among other things tell you when team members have modified SQL objects, either in near real-time, or on a schedule. This leads to the option of doing SQL code reviews in a fairly non-obtrusive manner: something that's proven useful in real-world implementations.
Notifications can also be configured to have different delivery mechanisms, including:
This summary has focused a lot on the SQL editor, but other powerful tools exist in the tool-chest. For example the schema and data compare tools are integrated right within Visual Studio (Developer and Advanced Editions) and have some specialized capabilities such as creating replication-friendly scripts, cascading deletions, etc.
Another example of functionality getting re-used in many different ways is how the data compare feature is also found in the context menu for result sets from queries you might run, letting you compare sets of data in an easy-to-understand visual manner:
You're also able to schedule schema compares to run behind the scenes and deliver the resulting report to your team as either a PDF attachment, Excel attachment, PDF file link, or Excel file link. Delivery options exist to even customize each report instance, by developer. For example, if Fred modified procedure A but has never touched procedure B, he can be told about schema differences related to A but B's differences are hidden from him. Also, these types of compares contain additional information that SQL-Hero knows about - whereas many other tools do not. For example, who modified the object last in a given database (both user ID and machine name), captured comments related to the last change, etc.
New capabilities added to SQL-Hero allow you to track object lists that are intended to be included in builds more easily than ever before. You can create builds that accumulate objects assigned to them, promote them, run compares based on them, track a complete history of changes related to them, etc. There's no less than seven different ways in which you can add objects to a build. This feature is by no means essential: if you use other build processes, you can opt to stick with basic compares and audit history to support compliance checks - something that will never go away due to the fact that there are always ways around most source control tools and well-intended processes. The next two screen shots illustrate some of the simple but powerful build tools present in SQL-Hero:
In so many ways, SQL-Hero is about adapting to the way
you want to work - your choices can be made the default in response to dozens of questions:
There are a host of settings that allow you to, for example, have the oldest editor windows be closed automatically (to reduce clutter), dynamically resize panels on the editor window based on the current context, list connections to pre-load object lists for (in the background), and so on. Another nice feature is that your position in the object tree is preserved: across refreshes, across database switches, and even across entire sessions! These are examples of simple yet powerful features you don't find in many other competitive products. Also, you can define multiple
profiles that encapsulate all settings, allowing you to work with multiple SQL-Hero application servers if you need that ability.
Believe it or not, this is by no means a comprehensive list of features! In fact, how can you know all of what it does? We aim to address that by providing more scenario-based storyboards in the form of documents and videos. Keep checking back.
In the end, we recommend you simply try it out: download an evaluation version for 60 days. Or you can install the version 0.9 product for free - with no expiry, no limitations, and no nagging. If you aren't satisfied, let us know why. Or if something is holding you back, let us know why too. We understand your challenges because we've lived them.