Generating Charts and Drawings in SQL Server Management Studio
You don't have to settle for T-SQL's monochrome text output. These stored procedures let you quickly and easily turn your SELECT queries' output into colorized charts and even computer-generated art.
To turn your own data into a line, column, area, or bar chart using the Chart stored procedure, you need to design a SELECT query that serves as the first parameter in the stored procedure call.
The monochrome text output that T-SQL provides by default isn't very impressive, especially to business analysts and other end users. Although you can use the geometry data type introduced in SQL Server 2008 to add images and colors to T-SQL output, you have to define every image before it can be displayed in SQL Server Management Studio (SSMS), which is a lot of work. We've written several stored procedures that let you easily and quickly turn your SELECT queries' output into colorized line, column, bar, area, and pie charts and even computer-generated art. Before we show you how to use them, though, we'll discuss the geometry data type basics.
Understanding the Geometry Data Type
The geometry data type and the methods that support it let you draw simple shapes (e.g., lines, polygons) using planar coordinates (X,Y). Geometric shapes are described using a subset of the Well-Known Text (WKT) format. This text markup language is regulated by an Open Geospatial Consortium standard that's supported to varying degrees by all major database systems. For example, a line from point (0,0) to point (1,1) is specified as LINESTRING(0 0,1 1). You can visualize this simple shape in SSMS by executing the following SELECT statement using the STGeomFromText method to convert the WKT string into a drawing:
Before you execute this statement in an SSMS query window, make sure that your output mode is set to Results to Grid. After executing it, you'll see three tabs in the result set window: Results,Spatial results, and Messages. Go to the Spatial results tab to see the drawing.
A colored square with the side length of 1 and the left bottom corner at (0,0) can be specified as a polygon with five points, where the first and the last points are the same: POLYGON((0 0,0 1,1 1,1 0,0 0)). Two squares can be described as one geometry object: MULTIPOLYGON(((0 0,0 1,1 1,1 0,0 0)),((2 0,2 1,3 1,3 0,2 0))). To convert these WKT strings to drawings, you can execute the following commands from a query window:
After you know how to draw lines, polygons, and multipolygons, you can combine these primitive shapes into various charts. To begin, create and populate a test table using the CreateTestTable.sql file, which you can download by clicking the hotlink at the top of this page. The test table contains two columns: FY (which specifies the fiscal year) and Sales (which contains sales amounts). You can display data from this table graphically if you concatenate all values from the rows into a WKT string, then use the STGeomFromText method in a SELECT statement to convert the WKT string into a line chart. Listing 1 shows this code, and Figure 1 shows the spatial results from it.
DECLARE @WKT AS VARCHAR(8000);
SET @WKT =
STUFF(
(SELECT ',' + CAST( FY AS CHAR(4) ) + ' ' + CAST( Sales AS VARCHAR(30) )
FROM #Sales
ORDER BY FY
FOR XML PATH('')), 1, 1, '');
SELECT geometry::STGeomFromText( 'LINESTRING(' + @WKT + ')', 0 );
Note that values across the horizontal axis correspond to values in the FY column in the test table and values on the vertical axis correspond to values in the Sales column. The result is a simple line chart representing the data in the table.
To display the same data as a column chart, you have to create a WKT string for a multipolygon, where each data point is described as a rectangle with the height equal to the sales value. The code concatenating the test data and generating the chart is shown in Listing 2, and the resulting image is shown in Figure 2.
DECLARE @WKT AS VARCHAR(8000);
SET @WKT =
STUFF(
(SELECT ',((' +
CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0,' +
CAST( FY - 0.3 AS VARCHAR(30) ) + ' ' +
CAST( Sales AS VARCHAR(30) ) + ',' +
CAST( FY + 0.3 AS VARCHAR(30) ) + ' ' +
CAST( Sales AS VARCHAR(30) ) + ',' +
CAST( FY + 0.3 AS VARCHAR(30) ) + ' 0,' +
CAST( FY - 0.3 AS VARCHAR(30) ) + ' 0))'
FROM #Sales
ORDER BY FY
FOR XML PATH('')), 1, 1, '');
SELECT geometry::STGeomFromText( 'MULTIPOLYGON(' + @WKT + ')', 0 );
Granted, these charts aren't as pretty as those that can be produced in Microsoft Excel, but you can generate them quickly in SSMS without having to copy data from the result set window into a spreadsheet and taking extra steps to produce a chart there.
We have created a stored procedure named Chart and two supporting functions to turn the results of a given SELECT statement into a WKT string and display it in SSMS. The code for the stored procedure and functions is in the Chart_20111224.sql script, which you'll find in the zip file located via the "Download the Code" link at the top of the page. You can execute the script in a database of your choice, then use the Chart stored procedure to generate various charts and images from your SELECT queries.
You can produce a line or column chart by feeding a simple SELECT command to the Chart stored procedure, without writing code to compose a WKT string from table data. For example, the following call generates the same line chart shown in Figure 1:
Note that the Chart stored procedure supports SQL injection by definition because it executes the code given in the first parameter as a dynamic SQL batch. There's no security risk when the Chart stored procedure is executed from a query window in SSMS because the user executing it wouldn't receive any elevated privileges. Just don't implement applications that take parameters from a user and then execute the Chart stored procedure through a connection with elevated permissions that the end user isn't granted directly.
Besides line and column charts, the stored procedure supports area, bar, and pie charts. Each example that we have shown so far has depicted a single series of data. The Chart stored procedure can also handle multiple series, displaying them in different colors in the resulting graph.
Running Prebuilt Charts
To demonstrate the different chart types, we have written several stored procedures that create sample charts using data from system tables. You might find these charts useful in your job. You'll find the following stored procedures in the Chart_20111224.sql script:
Chart_DB_Size. Produces a column chart that compares SQL Server database sizes. Figure 3 shows a sample chart produced by this stored procedure.
Chart_Free_Disk_Space. Makes a pie chart of free space by disk drive. A sample chart is shown in Figure 4.
Chart_Job_Duration. Displays a line chart depicting the duration of a given scheduled job over time.
Chart_Job_Step_Duration. Is similar to the Chart_Job_Duration stored procedure, except it breaks down the overall job duration into steps and displays it as an area chart. Figure 5 shows a sample chart.
Chart_All_Jobs_History. Makes a floating bar chart that shows every scheduled job as a series of bars on a separate horizontal line. The horizontal axis represents time. Each bar illustrates the execution of one job, with the position of the bar on the time axis depicting its start time and end time. Thus, the width of the bar corresponds to the job's duration. This chart can help you analyze scheduled job conflicts when they overlap and the relative duration of each job over time. A sample chart is shown in Figure 6.
To turn your own data into a line, column, area, or bar chart using the Chart stored procedure, you need to design a SELECT query that serves as the first parameter in the stored procedure call. The SELECT query needs to retrieve data from three columns. The first column needs to provide the series names. Typically, they're text strings, but you can use any data type that's implicitly convertible to a string (e.g., int). The second and third columns need to provide numeric values for the X axis and Y axis, respectively. We recommend using the FLOAT data type.
For example, if you have a table named Sales with the FiscalYear, ProductCategory, and TotalSales columns, you could make a line chart with this call:
EXEC dbo.Chart
'SELECT ProductCategory,
FiscalYear, TotalSales
FROM Sales';
The SELECT query passed to the Chart stored procedure uses ProductCategory to provide the series names, so each product category will be represented as a separate line. The FiscalYear column provides the X values (horizontal axis). The TotalSales column provides the Y values (vertical axis).
As this example demonstrates, when calling the stored procedure, you must specify the columns in the order discussed. If you have only one series of data, you must still pass a value for the first column when calling the stored procedure. In this case, you can specify the 0 constant, as was done in the two sample calls in the "Building Charts from Primitive Shapes" section.
To turn your own data into a pie chart using the Chart stored procedure, you need to design a SELECT query that retrieves data from only two columns. The first column needs to provide a series of names. The second column needs to provide numeric values for the Y coordinates. (Pie charts don't contain X values.) For example, the following procedure call produces a pie chart based on the test data:
EXEC dbo.Chart
'SELECT FY, Sales FROM #Sales',
'PIE'
Notice that this sample call includes the PIE value as the second parameter. If you want to create a pie, column, bar, or area chart, you need to specify PIE, COLUMN, BAR, or AREA, respectively, as the second parameter. If you don't include a second parameter or if you include any other value, the stored procedure defaults to a line chart.
There are many optional parameters you can include. For example, the @Legend parameter enables or disables the display of a chart legend that shows the series names and corresponding colors. The default is 1 (enable), but you might want to suppress the legend for charts that display only one series, as was done in the two sample calls in the "Building Charts from Primitive Shapes" section. Similarly, you can use the @Y_Grid parameter to enable or disable the display of Y axis grid values. Like the @Legend parameter, the @Y_Grid parameter's default is 1 (enable). In the Chart stored procedure's code, you'll find inline comments describing other optional parameters that you can use. In addition, here are some other important notes about the stored procedure:
The bar charts implemented in the stored procedure are not column charts turned 90 degrees clockwise. The bar charts we use are also known as "floating bars" and are perfect for illustrating when events start and how long they last. The X and Y values you select for the query should use the same units of time, such as years or minutes. The X values are interpreted as the start times. The Y values are interpreted as the duration of each event.
The area charts are "stacked area" charts, and the column charts are "stacked columns" charts. This means that in a chart illustrating multiple series of data, each value is stacked up on top of each other.
You can use the ORDER BY clause in the SELECT queries to define the order in which you want each series shown. This influences the chart legend, the colors assigned to each series on the chart, and the order in which the series appear in the charts. For example, you can sort them alphabetically. For pie charts, we prefer to sort the Y values in reverse order so that larger pie slices appear first when going clockwise.
Overcoming the Challenges
We encountered several challenges while implementing the Chart stored procedure. Here are the main problems we ran into and how we worked around some of them.
Scaling. The first problem we encountered was the relative scaling of the X and Y axes, which is always 1:1 in SSMS. This means that a chart without scaling might turn out to be too narrow either vertically or horizontally to be useful. For example, suppose a chart has the X values of 0, 1, and 2, and corresponding Y values of 500, 650, and 700. If you draw this chart without scaling, it'll be 2 units wide and 700 units tall, which is too narrow to read. We resolved this problem by automatically scaling the Y axis so that a single unit of Y might be set to a coefficient (1, 2, or 5 multiplied by 10n), which makes the chart reasonably proportional on a typical screen. For example, the chart shown in Figure 3 has a scale of 200:1 on the Y axis, so 1 vertical unit of the grid is equal to 200GB of database size.
Additional text. Although simple charts, like those shown in Figure 1 and Figure 2, are reasonably self-explanatory without additional text, more complex charts, especially those with more than one data series, must have a legend in order to be useful. For example, Figure 3 through Figure 6 include legends for the data series.
In addition, we realized that our decision to scale the Y axis now required some way to correlate Y coordinates with actual data before scaling, so we needed to show Y-axis grid values in some charts. As you can see in Figure 3 and Figure 5, the Y-axis grid values are on the left.
Each letter, digit, and symbol in the additional text is drawn as a multipolygon. Some characters (e.g., the letter "L") require very few points to draw a polygon, whereas other characters require a lot more. For example, the @ character takes 120 points to approximate it reasonably well.
The Chart stored procedure uses two inline functions to generate WKT strings that embed scalable text in images: dbo.WKT_Letter (returns a WKT for a single character) and dbo.WKT_String (returns a WKT for a given text string of multiple characters). You can generate an image for the phrase "Hello World!" with this statement:
After you run this code and have it on your screen, you can zoom in closer to see how letters are approximated as polygons. (You can see the arc approximation with polygons best in round letters, such as the letter "o.") The zoom slider is on the panel to the right of the image. Alternatively, you can zoom in by holding down the Ctrl key while scrolling the mouse wheel forward.
We could've achieved smoother graphics with additional geometry methods supported by SQL Server 2012. These functions let you draw arcs and circles, whereas we had to approximate any curves with multiple short lines. (For example, our pie charts are actually polygons with 1,024 points, approximating a circle.) We opted not to use the new functionality so that our code would work on SQL Server 2008 and later.
Color palette. Another problem we ran into was the random color palette used by SSMS. When you select multiple geometry values in a single SELECT statement (one per row of the result set), each one is depicted with a different color. The sequence of colors appears to be predefined and immutable. You can execute the Chart_Colors stored procedure in Chart_20111224.sql to see the exact sequence of colors. The first color is teal, which is OK, but it's followed by orange and purple, making charts with three series of data Halloweenish-looking. The fourth color is magenta, which is followed by a dark gray and two almost indistinguishable shades of beige. We could go on, but you probably see by now why we were displeased with the default palette. After determining it wasn't possible to change the default colors, we decided to "skip" some of them by painting a tiny triangle for each unwanted color in the corner of a chart. For example, we did this for the charts in Figure 3 through Figure 6. Your color preferences might differ from ours, so you can use the @Skip_Colors parameter to specify the colors you don't want to use in your charts.
Grid lines. Unfortunately, there isn't a way to remove grid lines and values automatically drawn by SSMS. We don't mind X grid values because they correspond to actual X values from depicted data. However, we would have preferred to suppress the Y grid lines and values because we use our own Y scale and print our own Y grid, as previously explained.
Point and series limits. There are limits on the maximum number of points and the maximum number of series that can be depicted in a chart. For example, when we tried to chart more than a few thousand points, we received an error message to that effect. We haven't researched whether the limits are defined by design or depend on resources of the particular SQL Server instance or workstation.
Importing. Unfortunately, there isn't a way to automatically export charts into image files outside of SQL Server so that they can be attached to emails and sent to recipients. If you find a utility with a command-prompt interface converting WKT strings into image files, please let us know.
Generating Artwork
When we started this project, we had the goal of developing a practical tool to draw charts in SSMS for quick data analysis. However, we soon realized that the same method can be used to draw computer-generated art. This might not be useful in your daily work, but we wanted to share this functionality because it can help unleash your creativity and bring a little fun into your workspace.
In the Chart stored procedure, we included a special chart type, PAINT, that you can use to add a long-overdue splash of art to T-SQL output. With a SELECT query, you can generate an image for purely aesthetic purposes. We have found a few formulas that produce interesting images, such as the image shown in Figure 7. You can find even more examples in the zip file located via the "Download the File" link at the top of the page. The filenames start with Artwork_(PAINT)_.
We also included the Chart_Art stored procedure in Chart_20111224.sql. This stored procedure either paints a pseudo-random image or takes an integer seed as a parameter to draw a specific picture. Some of the images created with Chart_Art are shown in the background of the pages in this article. You can find more examples in the "Download the Code" link at the top of the page. The integer seeds with which the images were created are part of the filenames. For example, the image in Integer_Seed_319171077.jpg was created using the integer seed 319171077.
Try executing the Chart_Art stored procedure without any parameters to see what it comes up with in the Spatial results tab. If you like a particular image and want to reproduce it later, save the integer seed displayed on the Results tab and pass it to the Chart_Art procedure as a parameter.
Art Contest
If you've been suppressing your creative side because you've had no outlet for artistic expression in T-SQL, we challenge you to come up with new SELECT queries that can be passed to the Chart stored procedure to produce interesting SQL Server-generated artwork. SQL ServerPro will publish the most interesting submissions on its website, let readers vote for their favorite submissions, and announce the art contest winners. Send your entries to [email protected] to win great prizes from SQL Server Pro!
