Getting Started with Data Explorer Queries
Click the Compose Query button up there and start typing your T-SQL query. That's it, it's really that easy! If you're not the query-writing type, you can still make use of all of the community-created queries.
See something that could be improved with someone else's query? Fork it and make your own edits that they can come back to build on. And using the query editor, you can get easy access to things like the target database schema and your query's execution plan, as well as the ability to download the results as a CSV file.
Editing your queries each time you want to change a parameter value would be inconvenient, so Data Explorer allows you
to specify parameter placeholders in your query that will prompt the user for input values. These take the basic form of
##Parameter##, where the parameter name will be
used as the label for the input prompt.
##UserId## parameter is treated specially for
users who are logged in. If they have an account on the target site that shares the email used in their Data Explorer account,
their user ID on that site will be automatically populated.
Data Explorer parameters can be typed as
string. Value validation will be performed against user input
for typed parameters, and in the case of the
string type, the
value will be properly escaped and quoted. To type your parameter, declare the type after the variable name using a colon:
You can also specify default values for your parameters as part of the parameter definition by giving the default value after the variable name using a question mark:
By putting both syntaxes together, you can create a typed parameter with a default value:
Note that if you use the same parameter in more than one location in the query, you only need to specify the type and default value once.
Parameter names aren't always user-friendly, so Data Explorer allows you to provide additional metadata in the form of parameter hints.
These can help make the experience more pleasant for people running your queries, and take the form of single line comments in your query.
To illustrate this, a query with
##MyParameter## could have a
parameter hint like the following:
-- MyParameter: Whatever you want! "You can enter anything"
The label of the prompt for this parameter's value will contain the text "Whatever you want!", with a tooltip stating "You can enter anything". The tooltip portion is optional, so you only need to include it when your label text doesn't capture everything.
Magic Columns and Autolinking
Data Explorer will create special links to content on the target site when you alias id columns using one of the supported magic column names. For example, the following query will generate a link to ten most recent posts:
SELECT TOP 10 Id AS [Post Link] FROM Posts ORDER BY CreationDate DESC
The available magic columns are:
[Suggested Edit Link]
In addition to the magic columns listed above, Data Explorer will also try to transform columns named
[TagName] into stylized links to tag pages on the target site, like the
data-explorer tag on Meta Stack Exchange.
You can create your own links as well, if you return the data in the format Data Explorer expects:
…where the link text is optional. To make it easier to create links pointing currently-queried site, Data Explorer
supports the custom scheme
site://. You can use this to link to pages not supported by the built-in magic
columns, like a particular post's revision history:
'site://posts/' + CAST(Id AS nvarchar) + '/revisions|' +
'Revision History for Post ' + CAST(Id AS nvarchar) AS [Revision Link]
FROM Posts WHERE Id = ##PostId##
Being able to generate result sets is nice, but sometimes the output is more effectively conveyed as a graph. Data Explorer lets you create simple graphs by automatically plotting results that have one of the following column combinations:
To produce more than one series, additional
can be appended to the above combinations. The following query returns results of the first combination:
SELECT Score, COUNT(Score) FROM Posts WHERE Score > 10 AND Score < 100 GROUP BY Score ORDER BY Score
…and produces the following graph of number of posts versus score:
If you'd like to see Data Explorer's graphing support expanded to handle more complex use cases, please create a feature request on Meta Stack Exchange or the GitHub project issues page, or submit a pull request!
Frequently Asked Questions
- How often is the Stack Exchange Data Explorer updated?
- The data is updated early every Monday morning around 3:00 UTC. The last update was Jul 27 at 5:29.
- What's the point of logging in?
While anonymous users can run queries without any imposed restrictions, there are a few perks to registering:
- A history of queries you've executed for your future reference
- Automatic population of
##UserId##fields when possible (if you have a matching email)
- You get credit for the awesome queries you've written
- It proves you aren't part of the robot menace (no more CAPTCHAs!)
- I already have a Stack Exchange account, why am I jon.doe#### here?
- Data Explorer doesn't participate in the Stack Exchange network authentication scheme, so your account here is completely separate. Don't worry though, you can update your name in your profile at any time!
- What runs this thing? Is the Data Explorer powered by hamsters?
- No, because hamsters are allergic to SQL. This instance is currently running on SQL Server 2012 and ASP.NET MVC 3.0. For a more in-depth listing of what's under the hood, check out What technologies are used to build Data Explorer?
- You didn't cover my question about
_______, what's wrong with you?
- Well…that's a story for another day. To get help with your Data Explorer question though, or to provide feedback, please ask on Meta Stack Exchange!