Using R code in Transact-SQL (R in SQL quickstart)

THIS TOPIC APPLIES TO: yesSQL Server (Windows only)noAzure SQL DatabasenoAzure SQL Data WarehousenoParallel Data Warehouse

This tutorial walks you through the basic mechanics of calling an R script from a T-SQL stored procedure.

What you'll learn

  • How to embed R in a T-SQL function
  • Some tips for working with R and SQL data types and data objects
  • How to create a simple model, and save it to SQL Server
  • How to create predictions and an R plot using the model

Estimated time

30 minutes, not including setup


You must have access to an instance of SQL Server with one of the following already installed:

  • SQL Server 2017 Machine Learning Services, with the R language installed
  • SQL Server 2016 R Services

Your SQL Server instance can be in an Azure virtual machine or on-premises. Just be aware that the external scripting feature is disabled by default, so you might need to perform some additional steps to get it working.

To run SQL queries that include R script, you can use any other application that can connect to a database and run T-SQL code. SQL professionals can use SQL Server Management Studio (SSMS) or Visual Studio.

For this tutorial, to show how easy it is to run R inside SQL Server, we've used the new mssql extension for Visual Studio Code. VS Code is a free development environment that can run on Linux, macOS, or Windows. The mssql extension is a lightweight extension for running T-SQL queries. To install it, see this article: Use the mssql extension for Visual Studio Code.

Connect to a database and run a Hello World test script

  1. In Visual Studio Code, create a new text file and name it BasicRSQL.sql.
  2. While this file is open, press CTRL+SHIFT+P (COMMAND + P on a macOS), type sql to list the SQL commands, and select CONNECT. Visual Studio Code prompts you to create a profile to use when connecting to a specific database. This is optional, but makes it easier to switch between databases and logins.
    • Choose a server or instance where R in SQL Server has been installed.
    • Use an account that has permissions to create a new database, run SELECT statements, and view table definitions.
  3. If the connection is successful, you should be able to see the server and database name in the status bar, together with your current credentials. If the connection failed, check whether the computer name and server name are correct.
  4. Paste in this statement and run it.

    EXEC sp_execute_external_script
      @language =N'R',
      @input_data_1 =N'SELECT 1 AS hello'
      WITH RESULT SETS (([hello] int not null));

    In Visual Studio Code, you can highlight the code you want to run and press CTRL+SHIFT+E. If this is too hard to remember, you can change it! See Customize the shortcut key bindings.





Next lesson

Now that your instance is ready to work with R, let's get started.

Lesson 1: Working with inputs and outputs

Lesson 2: R and SQL data types and data objects

Lesson 3: Using R functions with SQL Server data

Lesson 4: Create a predictive model

Lesson 5: Predict and plot from model