By: Ben Snaidero
Language
ANSI SQL is the specification that describes the features of any SQL language that is used to query a database. Every database platform I've ever worked with usually implements a subset of the features of this specification as well as adds some features of their own and SQL Server is no different. The language used to query a SQL Server database is called T-SQL and the commands that make up the T-SQL language can be broken down into a few categories:
- DDL - Data Definition Language statements are statements that are used to manage the objects within your database. Things like creating tables and indexes, renaming objects, updating statistics, etc. are all done using the following commands: ALTER, CREATE, DROP, ENABLE/DISABLE TRIGGER, RENAME, UPDATE STATISTICS.
- DML - Data Manipulation Language statements are statements that you use to insert, update, delete and select data from your database. The commands that allow you to work with your data are: SELECT, BULK INSERT, DELETE, INSERT, UPDATE, MERGE, TRUNCATE TABLE.
- SET - SET statements allow you to control the run-time settings of your database session. With the SET command you can change set the format of date data that is returned from a query, lock timeout settings, transaction level settings, etc. Keep in mind when using this statement, it only effects your current session.
- OTHER - Lastly and only because they don't really fit any of the above categories are the BACKUP and RESTORE commands. As the names suggest they are the commands you would use to backup and restore your database.
Many of the commands above don't really translate well to an OLAP database and because of this Analysis Services has its own query language called Multidimensional Expressions or MDX which is used to query an Analysis Services OLAP database. Similar to its relational counterpart MDX queries can be broken up into the following categories:
- MDX DDL - Data Definition Language statements are statements that allow you to define your cube structure. Things like defining measures, subcubes, calculations, etc. are all done using these commands: CREATE, DROP, ALTER, REFRESH and UPDATE MEMBER.
- MDX DML - Data Manipulation Language statements are statements that allow you to query and process data in your OLAP database. Commands in this category include CALL, CLEAR CALCULATIONS, DRILLTHROUGH, SELECT and UPDATE CUBE.
- Scripting - Scripting commands allow you to control the flow of an MDX script and define the context and scope of queries. Commands that allow you to do these operations are: CALCULATE, FREEZE, IF and SCOPE.
More details and the syntax for all of the above commands can be found by reading the links in the additional information section.
Tools
There are many third-party tools that you can use to query a SQL Server database besides the standard query and reporting tools that come installed with the product itself. These tools range from very simple command line query processing to full data visualization tools. Below we will look at a couple of offerings from Microsoft that will allow you to query your database as well as build some data visualization windows.
SQLCMD
With every database platform there is a command line tool that allows you to query the database. With SQL Server this tool is the SQLCMD executable and you can start it by typing "sqlcmd" at the command prompt. There are quite a few different options that you can specify when starting the tool. Below we will go through a few examples which will demonstrate some of the more frequently used options.
In this example we specify the "-S" parameter to identify the server we are connecting to and the "-d" parameter to specify the database. The "-E" parameter tells the utility to use windows authentication to connect (this is the default). Once connected, we can start interactively entering queries at the "1>" prompt.
C:\>sqlcmd –S localhost –d AdventureWorks2012 –E 1>
In the following example we again specify the server using "-S" but we also pass the "-Q" parameter which allows us to pass a query in on the command line that will be executed as soon as the connection completes. Note that after the query completes sqlcmd will exit and put you back to the windows command prompt. If you need to stay in interactive mode you can pass the query using "-q".
C:\>sqlcmd -S localhost -Q "SELECT top 2 * FROM AdventureWorks2012.Person.Person"
In this next example, we show how you can pass in many TSQL commands using a script file and specifying the "-i" parameter. You can also specify the "-o" parameter and save the results of your queries into an output file.
C:\>sqlcmd -S localhost -i MyScript.sql -o MyOutput.out
In this final example we show how you can connect to SQL Server using SQL authentication. This is accomplished using the "-U" parameter for the username and "-P" for the password. Alternatively, you can omit the "-P" parameter and sqlcmd will prompt you to input the password.
C:\>sqlcmd -S localhost –U sa –P *******
SSMS
As is the case with most Microsoft products there is a very intuitive GUI tool that you can use to query a SQL Server database called SQL Server Management Studio (SSMS). Starting with SQL Server 2016, SSMS is now installed as its own application (with previous versions it was installed with the database software). Once installed you can launch the query editor by clicking the "New Query" button as shown below. Once selected a dialog box will open where you can enter your connection details.
After clicking "Connect" a new query window will open where you can directly enter your query (red arrow). You can use three-part naming with your query or change the database name using the dropdown (green arrow). Once you are ready to run the query you can click the "Execute" button (orange arrow). Finally, for those of us that prefer a little less typing, if you open the "Object Explorer" you can drag and drop tables/columns into the query editor window (blue arrow).
Excel
One of the more popular tools that is used to query and display data is Microsoft Excel. People have been using spreadsheets for years so it's natural that users would use this tool to pull data into a spreadsheet and then use the spreadsheet functionality to both manipulate and display the data. Getting data from the database into a sheet is a very simple task. First, after starting Microsoft Excel, you can open the "DATA" menu item and the select "From SQL Server" from the "From Other Sources" button as shown below.
Once selected the following dialog box will open where you can input the connection details for the database server you wish to connect to. In this case we are going to pull data from a locally installed SQL Server instance using the sa account.
After clicking next (assuming you were able to connect successfully), the following dialog box will open where you can select the database and table you would like to query.
After clicking "Finish" you are presented with this final dialog box which allows you to define how you want to view the data in your workbook.
After clicking "OK" your table data should be pulled into your spreadsheet as shown below. Also shown here is the refresh button which allows you to re-query your table and refresh the data in your spreadsheet with any updated data from your database.
Power BI
Over the last number of years more and more industries are getting into analytics and using the data they collect to build interesting and informative reports and dashboards. Since in a lot of cases a simple chart report is not enough there are now quite a few software options available for creating these data visualizations that can have a lot of business intelligence built into them. One of these software options is Power BI. This product aims to give users a simple interface that they can use to query their data and build their own reports and dashboards.
Power BI has 3 different software options. Power BI Desktop which is free for a single user (and what we will use for this example). The other two are paid services, Power BI Pro and Power BI premium, and you can read more about their functionality and pricing options here.
As we did above for Excel, we’ll walk through a simple example showing how you can pull data from a SQL Server database using Power BI. The first thing we need to do after opening the tool is select the "Get Data" button which will open a dialog box where we can select our data source. In this case we’ll select "SQL Server" and click "Connect".
After clicking "Connect" the following dialog will open where we can select the server we want to connect to and well as the connectivity mode. For this example, we will select "DirectQuery" which will leave the data in the database rather than pull it all into Power BI. This is especially useful when dealing with really large datasets.
After selecting "OK" the following dialog box will appear where we can input our database credentials and select "Connect".
Once connected we are given a dialog where we can select the tables we want in our query (similar to object explorer in SSMS). For this example, we will query the SalesOrderDetail table. Once selected you can click "Load"
Once the table/query is loaded we are then brought back to the main screen where we can begin to build our report. For this example, we will build a simple line chart which will display the LineTotal for each ProductID. The first step is to select the "Line Chart" visualization as shown below.
Next we drag the ProductID field into the "Axis" textbox and the LineTotal field into the "Values" textbox and once both are populated you should see the report generated on the left.
Other examples of query tools
There are many other third-party tools that you can also use to perform administrative tasks and run database queries. While I tend to mainly use SSMS when working with any of the SQL Server products, since it integrates so well with them, there are cases where you could be working with many different database platforms and using one tool makes things easier. Below are a few software offerings that could help in this case:
- Firebird
- SQuirreL
- SQLite
- DBeaver
- AquaStudio
Since these tools are platform agnostic you won’t get the same number of GUI tools for SQL Server specific functionality that you have available in SSMS but nevertheless it gives you another option for running queries against your database.
Additional Information
- T-SQL Statements
- MDX Expressions Reference
- MDX Query - The Basic Query
- Microsoft Excel Integration
- Power BI
