SQL Server Commands: sqlcmd and T-SQL Basics with Examples (MS SQL)

Applies to: Microsoft SQL Server 2017 to 2025 on Windows and Linux; sqlcmd (Go) 1.10.0 tested in Docker; T-SQL statements from Microsoft’s reference (SQL Server itself can’t run in our arm64 lab)

Microsoft SQL Server (MS SQL) uses its own dialect of SQL called Transact-SQL (T-SQL). You type T-SQL in SQL Server Management Studio (SSMS) or on the command line with sqlcmd. Most basic statements (SELECT, INSERT, UPDATE, DELETE, CREATE TABLE) work as in MySQL; this page lists the commands that are specific to SQL Server or written differently.

Connect with sqlcmd

There are two versions of sqlcmd, according to Microsoft: the newer sqlcmd (Go), a standalone download for Windows, macOS and Linux, and the older ODBC-based one that comes with SQL Server and the mssql-tools package on Linux. Check yours with sqlcmd --version (Go) or sqlcmd -?:

$ sqlcmd --version
sqlcmd: Install/Create/Query SQL Server, Azure SQL, and Tools

Version: v1.10.0

Common ways to connect:

sqlcmd -S localhost -E                        # Windows login (trusted connection)
sqlcmd -S 203.0.113.10 -U sa                  # SQL login, asks for the password
sqlcmd -S 203.0.113.10,1433 -U sa -d shop     # port after a comma, start in database shop
sqlcmd -S localhost\SQLEXPRESS -E             # named instance on this computer

• -Q "query" runs one query and exits; -i script.sql runs a file; -o result.txt writes the output to a file.
• Avoid -P password: Microsoft calls it insecure. Let sqlcmd prompt you, or set the SQLCMDPASSWORD environment variable.
• -C trusts the server certificate without checking it, for servers with a self-signed certificate. Microsoft notes that for SQL Server 2025, encryption is mandatory by default (-N defaults to m), a change from SQL Server 2022 and earlier, where it was optional.

If nothing listens on the port, sqlcmd (Go) says so directly: unable to open tcp connection with host '127.0.0.1:1433': dial tcp 127.0.0.1:1433: connect: connection refused. Check that the SQL Server service runs (SQL Server service not starting) and that TCP port 1433 is open in the firewall.

GO: send the batch

In interactive sqlcmd, nothing runs until you type GO on its own line:

1> SELECT @@VERSION;
2> GO

GO is not a T-SQL statement: sqlcmd and SSMS use it to split a script into batches. It must be on a line of its own, without a semicolon after it, and variables declared before a GO no longer exist after it. GO 3 runs the batch three times. Leave the prompt with EXIT or QUIT.

Server and database information

SELECT @@VERSION;                                   -- version and edition
SELECT name, state_desc FROM sys.databases;         -- all databases
USE shop;
SELECT name FROM sys.tables;                        -- tables in the current database
EXEC sp_help 'dbo.customers';                       -- columns, keys and indexes
EXEC sp_who;                                        -- current sessions

Create a database and table

CREATE DATABASE shop;
GO
USE shop;
GO
CREATE TABLE dbo.customers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    email NVARCHAR(255) UNIQUE,
    created_at DATETIME2 DEFAULT SYSDATETIME()
);

Differences from MySQL: IDENTITY(1,1) instead of AUTO_INCREMENT, NVARCHAR for Unicode text, and objects live in a schema (usually dbo). Names with spaces or reserved words go in square brackets: [order details].

Read and limit rows: TOP and OFFSET

SQL Server has no LIMIT. Use TOP, always with ORDER BY, which Microsoft says is the only way to know which rows you get:

SELECT TOP (10) name, created_at
FROM dbo.customers
ORDER BY created_at DESC;

For pages of results, Microsoft recommends OFFSET and FETCH:

SELECT name FROM dbo.customers
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Logins, users and permissions

SQL Server separates logins (access to the server) from users (access to one database):

CREATE LOGIN shopapp WITH PASSWORD = 'a-strong-password';
GO
USE shop;
GO
CREATE USER shopapp FOR LOGIN shopapp;
ALTER ROLE db_datareader ADD MEMBER shopapp;
ALTER ROLE db_datawriter ADD MEMBER shopapp;

Add MUST_CHANGE, CHECK_EXPIRATION = ON after the password to force a new password at the first login. Locked out of sa? See reset the SQL Server sa password.

Back up a database

BACKUP DATABASE shop
TO DISK = 'D:\Backups\shop.bak'
WITH COMPRESSION;

On Linux, Microsoft’s examples write backups to /var/opt/mssql/data/, for example /var/opt/mssql/data/shop.bak. WITH COPY_ONLY makes a one-off full backup that doesn’t disturb your normal backup chain. Check a database for corruption with DBCC CHECKDB (shop) WITH NO_INFOMSGS;; if it reports errors, Microsoft recommends restoring from a backup rather than using the repair options. For restoring, see an automated SQL Server restore script.

Run a script from the command line

sqlcmd -S localhost -E -d shop -i C:\scripts\report.sql -o C:\scripts\report.txt

Microsoft’s best practices for scripts: prefer integrated security (-E) and use -X in automated jobs to disable sqlcmd commands that could affect the system.

Prefer a graphical tool? See install SSMS and connect to SQL Server. For MySQL and MariaDB syntax, see SQL commands with examples.

Official documentation: sqlcmd utility, GO and TOP.