Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

A closer look at Python-SQL Server 2017 integration

Do you know everything you need to take advantage of SQL Server 2017's support of Python?

Python enthusiasts will be able to use the programming language with Microsoft SQL Server 2017 database -- within...

limits.

At first, the idea of integrating a programming language into SQL Server might seem a little bit odd; however, it is not entirely unprecedented. Microsoft added the R language to the previous version of SQL Server because it can handle complex mathematical data analysis. The R framework, T-SQL and Python can be used simultaneously within the same SQL Server database, if necessary.

Microsoft's support for Python, beginning with the Community Technology Preview 2.0 release of SQL Server 2017, will be at least somewhat limited, though. The preview of Python support is only available in the Windows version of SQL Server 2017, although Python integration will likely make it into the Linux version of SQL Server eventually.

SQL Server users can download and install the standard open source Python interpreter version 3.5 and some common Python packages during SQL Server setup. Microsoft only supports interpreter version 3.5. This version of Python was selected to avoid some of the compatibility issues that exist in newer versions of the Python interpreter, according to Microsoft.

Microsoft's decision to integrate a Python interpreter into SQL Server raises a few questions. How does SQL Server benefit from Python integration? And what is Microsoft doing to protect SQL Server against malicious Python scripts?

Python-SQL Server combo adds flexibility, scalability

Python is a general-purpose programming language that is known for being especially easy to learn. Although Python can be used for many purposes, it is primarily used for data analytics and machine learning. Although such capabilities are obviously useful, those alone do not address why Microsoft has integrated a Python interpreter into SQL Server. Why not simply write stand-alone code that queries a back-end SQL Server?

The answer is performance and scalability. While it is possible to write a script to query a back-end SQL Server, it must be transmitted to SQL Server, executed and the results sent back to the machine that initiated the query. In other words, this requires SQL Server data to be sent to the machine that analyzes the data. This is not only inefficient, but in some cases, it can also limit the volume of data that can be analyzed.

How the programming languages stack up

Running Python directly on a SQL Server also makes it possible to perform parallel analytics that would otherwise be difficult to do. A SQL query could, for example, send data to multiple, parallel Python processes that run simultaneously on the server.

In addition, production SQL Servers tend to run on high-performance hardware, so the Python code can take advantage of the server's hardware resources. And because the Python interpreter is integrated into SQL Server, Python can take advantage of SQL Server features, such as in-memory tables.

Will Python in SQL Server pose security risks?

Whether data is being transmitted back and forth through SQL Server queries or exported to an application server, the movement of data raises questions about its security. Running Python code directly on SQL Server enables data to be analyzed in place, without it ever being transmitted across the wire to another machine.

While enabling Python code to analyze data in place rather than transmitting the data to another machine can certainly help with security, Microsoft has also been careful to implement Python integration in a secure manner. Python code is stored within a database as a T-SQL Stored Procedure. More importantly, this code does not execute within the SQL Server address space, thereby helping to ensure that poorly written Python code cannot harm SQL Server.

Although Python-SQL Server integration may seem like an enterprise-class feature, all SQL Server 2017 editions will support Python, including the free Express Edition. Microsoft has also extended support for both R and Python in Azure SQL Database.

Next Steps

Should you consolidate SQL Server Enterprise Edition?

Machine learning in SQL Server 101

What does Linux in SQL Server mean?

Learn to use Python, Ruby and other languages 

This was last published in June 2017

Dig Deeper on SQL Server Data Warehousing

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What features in SQL Server 2017 are you most looking forward to?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close