Python enthusiasts will be able to use the programming language with Microsoft SQL Server 2017 database -- within...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
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.
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