While SQL Server itself has gone through innumerable changes and enhancements over the years, its command-line...
tools have essentially stayed the same since the old SQL Server 6.5 days. But as Microsoft expands its support of cross-platform tools, the company has released a new command-line query tool for SQL Server called MSSQL-CLI, which provides many significant enhancements over the old SQLCMD tool.
The venerable SQLCMD program has long been the primary command-line tool for running T-SQL commands. SQLCMD is used for both configuring SQL Server instances and developing application queries. It has also essentially remained unchanged.
With the release of SQL Server on Linux, Microsoft has started down a path to support multiple platforms with its command-line tool set. Microsoft initially made ports of SQLCMD and BCP (bulk copy program) for Linux and macOS, but these ports essentially functioned like the older Windows versions.
The new MSSQL-CLI is open sourced under the BSD 3-Clause License and is a contribution to the DBCLI organization. It uses the same microservices-based back end (sqltoolsservice) as the Visual Studio Code SQL extension and SQL Operations Studio.
Let's take a closer look at installing and running the new MSSQL-CLI tool.
Downloading and installing MSSQL-CLI and Python on Windows
The process for installing MSSQL-CLI on Windows vs. Linux is quite different. Like most SQL Server client tools, MSSQL-CLI is a separate download from the main SQL Server installation. Unlike many SQL Server tools, MSSQL-CLI is built using Python. You need to have Python installed on the target system before you can use MSSQL-CLI.
In contrast to macOS or Linux, Python is not typically installed on Windows. There are two separate Python installation packages: Python 3.6.5 and Python 2.7.14, both of which support MSSQL-CLI. The following listing illustrates how to install Python version 3.6.5 on Windows and how to use the pip command -- a tool for installing and managing Python packages -- to install MSSQL-CLI.
pip install mssql-cli
Downloading and installing MSSQL-CLI and Python on macOS
Installing MSSQL-CLI on macOS is a bit easier, as Python 2.7 is normally preinstalled on the Mac operating system. To install MSSQL-CLI on macOS, run the following commands:
$ sudo easy_install pip
$ sudo pip install -- upgrade pip
$ sudo pip install mssql-cli -- ignore-installed six
Downloading and installing MSSQL-CLI and Python on Linux
Installing MSSQL-CLI on Linux is also typically quite easy, as most Linux distributions come with Python preinstalled. To install MSSQL-CLI on Ubuntu 16.04, run the following commands in the Bash shell:
$ sudo apt-get update & sudo apt-get install -y libunwind8 python-pip libicu52
$ sudo pip install -- upgrade pip
$ sudo pip install mssql-cli
You can see the complete list of installation instructions for the different supported versions of Linux in the GitHub installation guide.
MSSQL-CLI runs exactly the same way on all of the different supported platforms. Like the older SQLCMD tool, MSSQL-CLI is a command-line tool that enables you to execute T-SQL commands. Unlike SQLCMD, MSSQL-CLI provides many modern enhancements that make it easier and faster to write and run T-SQL queries.
MSSQL-CLI provides T-SQL IntelliSense statement completion, syntax highlighting, a multiline edit mode and automatic formatting of query results. Once MSSQL-CLI is installed, you can run it from the Windows command line or the Linux Bash shell with SQL Server authentication using the following command and authentication parameters:
mssql-cli -S <server name> -U <user name> -d <database name>
You can run MSSQL-CLI using Windows authentication with the following command:
You can enter mssql-cli --help to see the complete list of command-line options for MSSQL-CLI. Figure 1 shows the new cross-platform MSSQL-CLI tool running from a Windows command shell.
Figure 1 illustrates the new MSSQL-CLI T-SQL IntelliSense support at work, prompting for the appropriate database name following the T-SQL USE statement.
You can move through the IntelliSense prompts by pressing Tab or the arrow keys. Pressing Enter selects the current item and returns to the command window. To end MSSQL-CLI, type the quit command and press Enter or press Ctrl + D.
MSSQL-CLI remembers the queries that you have previously executed and automatically displays them as you enter T-SQL commands, making it easy to rerun previous statements. By default, MSSQL-CLI runs in single-line mode.
In single-line mode, commands are executed when you finish typing the command and press Enter. If the T-SQL statement is long enough to continue on to the next line, then MSSQL-CLI fills in the line with dots until the next character is even with the beginning of the prompt.
In multiline mode, commands are not executed until you enter a semicolon (;) and press Enter. You can keep adding lines by pressing Enter. You can make changes to your query by using the arrow keys to move the cursor through the different lines of the multiline query.
MSSQL-CLI displays the results in either a neatly formatted vertical or horizontal list, depending on how many columns are returned by the query. If all the returned columns fit on the screen, then MSSQL-CLI formats the output horizontally, as shown in Figure 2.
If there are too many columns to fit horizontally on the screen, then MSSQL-CLI will automatically format the results to appear vertically, as shown in Figure 3.
As you can see in Figure 3, MSSQL-CLI formats the output data in a readable display showing the column names and the associated data for a single row vertically when the output is too wide to fit on the screen.
The -- More -- indicator at the bottom of the window indicates that there is more data to scroll through. Pressing the Enter key advances the output display one column at a time. Pressing the Spacebar advances the display by one row at a time. To end the output display and return to the MSSQL-CLI command prompt, you can press q or Ctrl + C.
Advanced MSSQL-CLI commands
MSSQL-CLI provides several other enhancements beyond the basic SQLCMD capabilities, including the ability to be configured with environment variables and the ability to execute a number of special commands that can list databases, save and run queries, and more.
Some of the more important environment variables include MSSQL_CLI_SERVER, MSSQL_CLI_DATABASE and MSSQL_CLI_ROW_LIMIT. MSSQL_CLI_SERVER is used to set a default SQL Server instance name or address. You can set environment variable MSSQL_CLI_DATABASE to use a default database. You can use MSSQL_CLI_ROW_LIMIT to apply a returned row limit or use 0 to disable the prompting.
You can see examples of setting these environment variables in the following listing:
If you are using macOS or Linux, then you need to use the export command instead of the set command.
MSSQL-CLI also provides a number of special commands that make it easier to create and run queries. Figure 3 shows a display of the list of special commands made using the \? command.
Figure 4 shows that these special commands all start with the backslash (\) character. Entering the backslash will display IntelliSense prompting to help you complete the command. For example, in the following listing, you can see how the \ sn command can be used to save a query called names, and then the \n command can be used to run the saved query.
Adventureworks2014> \sn names select LastName, FirstName, Title from Person.Person
Adventureworks2014> \n names