SQL Server Machine Learning Tutorials

Use these tutorials to learn about machine learning in SQL Server 2016 and SQL Server 2017. Topics covered include:

  • How to run R or Python from T-SQL
  • What are remote and local compute contexts? How to execute R or Python in a SQL Server compute context
  • Developing models in R and saving trained models in a SQL Server table
  • How to wrap R or code in a stored procedure
  • Optimizing R and Python code for a SQL production environment
  • Real-world scenarios for embedding machine learning in applications

Prerequisites

To use these tutorials, you must have installed one of the following server products:

  • SQL Server 2016 R Services (In-Database)

    Supports R. Be sure to install the machine learning features, and then enable external scripting.

  • SQL Server 2017 Machine Learning Services (In-Database)

    Supports either R or Python. You must select the machine learning feature and the language to install, and then enable external scripting.

For more information about setup, see Prerequisites.

Python Tutorials

Note

Support for Python is a new feature in SQL Server 2017 (CTP 2.0). Although the feature is in pre-release and not supported for production environments, we invite you to try it out and send feedback.

Learn how to call Python in T-SQL, using the extensibility mechanism pioneered in SQL Server 2016. Now supports Python!

Create a model using rxLinMod, and run it in SQL Server, using the new revoscalepy lilbrary from a remote Python terminal.

Create a machine learning model to predict demand for a ski rental business, and operationalize that model for day-to-day demand prediction using stored procedures. All code and data provided!

NEW! Build a complete Python solution using T-SQL stored procedures; all Python code supplied included.

Learn how to deploy a Python model using the latest version of Microsoft Machine Learning Server.

R Tutorials with SQL Server

This section lists tutorials that were developed for SQL Server 2016 R Services. All require Microsoft R, and make extensive use of features in the RevoScaleR package for SQL Server compute contexts.

Note

Unless otherwise indicated, these tutorials are expected to work without modification in SQL Server 2017.

Data Science Deep Dive

Learn how to use the functions in the RevoScaleR packages.

Move data between R and SQL, and switch compute contexts to suit a particular task. You will create models and plots and move them between your development environment and SQL Server.

Audience: For data scientists or developers who are already familiar with the R language, and who want to learn about the enhanced R packages and functions in Microsoft R by Revolution Analytics.

Requirements: Some basic R knowledge. Access to a server with SQL Server R Services or Machine Learning Services with R. For setup help, see Prerequisites.

In-Database Advanced Analytics for the SQL Developer

Build and deploy a complete R solution, using only Transact-SQL tools.

Focuses on moving a solution into production. You'll learn how to wrap R code in a stored procedure, save an R model to a SQL Server database, and make parameterized calls to the R model for prediction.

Audience: For SQL developers, application developers, or SQL professionals who will be supporting R solutions and want to learn how to deploy R models to SQL Server.

Requirements: No R environment is needed! All R code is provided and you can build the complete solution using only SQL Server Management Studio and familiar business intelligence and SQL development tools. However, some basic knowledge of R is helpful.

You must have access to a SQL Server with the R language installed and enabled. For setup help, see Prerequisites.

Using R Code in T-SQL

This quick-start covers the basic syntax for using R in Transact-SQL.

Learn how to call the R run-time from T-SQL, wrap R functions in SQL code, and run a stored procedure that saves R output and R models to a SQL table.

Audience: For people who are new to the feature, and want to learn the basics of calling R from a stored procedure.

Requirements: No knowledge of R or SQL required. However, you'll need either SQL Server Management Studio or another client that can connect to a database and run T-SQL. We recommend the free MSSQL extension for Visual Studio Code if you are new to T-SQL queries.

You must also have access to a server with SQL Server R Services or Machine Learning Services with R already enabled. For setup help, see Prerequisites.

Developing an End-to-End Advanced Analytics Solution

Demonstrates the data science process from beginning to end, as you acquire data and save it to SQL Server, analyze the data with R and build graphs.

You'll learn how to move graphics between SQL Server and R, and compare feature engineering in T-SQL with R functions.

Finally, you'll learn how to use the predictive model in SQL Server for both batch scoring and single-row scoring.

Audience: For people who are familiar with R and with developer tools such as SQL Server Management Studio.

Requirements: You should have access to an R development environment and know how to run R commands. You'll need to download the New York City taxi dataset using PowerShell. Access to a server with SQL Server R Services or Machine Learning Services with R already enabled. For setup help, see Prerequisites.

SQL Server Product Samples

These samples and demos provided by the SQL Server and R Server development team highlight ways that you can use embedded analytics in real-world applications.

Predicting customer demand using R in SQL Server

Learn how a ski rental business might use machine learning to predict future rentals, which helps the business plan and staff to meet future demand.

Customer segmentation using K-Means Clustering

Use unsupervised learning to segment customers based on sales data. (English only)

Learn RevoScaleR

Wondering what RevoScaleR offers that R doesn't? See these tutorials

This R Server quick-start demonstrates how you can write R code once, and deploy anywhere, using RevoScaleR data sources and remote compute contexts.

Learn how to use RevoScaleR for advanced modeling and data transformation, and optimize for different compute contexts.

Customizable End-to-End Solutions

The Microsoft Data Science Team has provided a number of solution templates that can be used for copy-paste creation of solutions for common scenarios. All T-SQL and R code is provided, along with instructions on how to train and deploy a model for scoring using SQL Server stored procedures.

For more information, see Machine Learning Templates with SQL Server 2016 R Services.

Resources and Reading

  • Want to know the real story behind R Services? Read this article from the development and PM team: Why did we build it?

  • The original SQL Server 2016 Product Samples, available on Github and on the Microsoft Download Center, contains some datasets and code samples for R Services, including a demo of insurance fraud detection based on Benford's law. To get only the samples for R Services (In-Database), select the zip file, and open the folder Advanced Analytics. The setup instructions are for earlier releases and should be disregarded.

Prerequisites

SQL Server 2016

To run any of these tutorials, you must download and install R Services (in-Database) as described here: Set up SQL Server R Services

SQL Server 2017

In SQL Server 2017 CTP 2.0, R Services has been renamed Machine Learning Services (in-Database). With this latest release, you can install either R or Python, or both. Otherwise the overall setup process, architecture, and requirements are the same.

After running SQL Server setup, don't forget these important steps:

  • Enable the external script execution feature by running sp_configure 'enable external script', 1
  • Restart the server
  • Ensure that the service that calls the external runtime has necessary permissions
  • Ensure that your SQL login or Windows user account has necessary permissions to connect to the server, to read data, and to create any database objects required by the sample

If you run into trouble, see this article for some common issues: Upgrade and Installation of SQL Server R Services

If you do not already have a preferred R development environment, you can install one of these tools to get started:

Note that standard R libraries are insufficient to use these tutorials; both your R development environment and the SQL Server computer running R must have the R packages provided by Microsoft. For more information about what's in Microsoft R, see this article: Microsoft R Products.

See Also

Getting Started with SQL Server R Services

SQL Server Machine Learning Tasks