In SQL Server Management Studio what is SQLCMD mode?

"You use SQLCMD scripts when you have to process Windows System commands and Transact-SQL statements in the same script."

"By default, SQLCMD mode is not enabled in the Query Editor. You can enable scripting mode by clicking the SQLCMD Mode icon in the toolbar or by selecting SQLCMD Mode from the Query menu."

Reference: MSDN

I did some more research, so here's my understanding of this to extend what has been written so far:

What is SQLCMD

SQLCMD.exe is a console utility included in the instalation of SQL Server 2005 and higher. You can typically find it in a path like c:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE.

It is a simple scripting environment that allows automation of tasks related to SQL server. For example, you can write and execute a script that will login to a specific instance of SQL Server, execute scripts from a given directory on this connection and store the output in a specified file.

Invoke-Sqlcmd cmdlet was introduced with SQL Server 2008 as a mean to replace this tool with a standardized, Powershell-based approach, preserving most of the original syntax and functionality.

What is SQLCMD mode in SSMS

In SSMS, SQLCMD mode is a script execution mode that simulates the sqlcmd.exe environment and therefore accepts some commands that are not part of T-SQL language. Unlike sqlcmd.exe, it contacts the database using SqlClient (the same way as SSMS), not ODBC data provider, so in some aspects it might have different behaviour than sqlcmd.exe.

Executing scripts in SQLCMD mode allows to use commands typical to sqlcmd.exe environment. However, there's no IntelliSense or debugging support for SQLCMD mode, so maintaining scripts that mix clean T-SQL with SQLCMD-specific code can be a pain. Therefore, it should be used only when it's necessary.

Example use case

Let's suppose that a company has a naming convention for databases that include environment in the name, eg: MyDb_Prod, MyDb_Test, MyDb_Dev. This convention might be used to minimize chance of mistakes.

When a developer writes a T-SQL script, it will have to be executed in different environments in deployment/testing process, which would require many versions of the code:

 SELECT *
 FROM [MyDb_Dev].[dbo].[MyTable1] -- MyDb_Dev -> MyDb_Test -> MyDb_Prod

Instead, we can assume that database name will be provided as a SQLCMD variable in deployment process and have exactly the same file deployed to all environments:

 -- :setvar databaseName "MyDb_Dev" -- uncomment for testing in SSMS

 SELECT *
 FROM [$(databaseName)].[dbo].[MyTable1]

(in this simple example database name could be omitted altogether, but if you have cross-database joins, using database name is necessary)