Data is an asset for organizations, requiring protection and auditing, and choosing predictive data models and validating them is essential for business success before deploying them for application usage. The growth of data and AI is closely associated, and with SQL Server's built-in R and Python support, endless possibilities for innovation are opened for collaboration between database professionals, developers, and the data science and AI communities.
AI relies on vast amounts of heterogeneous data, and the cost of data movement for organizations is undeniable. However, by performing data science and AI directly where the data resides, numerous benefits can be achieved. This approach allows organizations to leverage the enterprise-grade performance, scalability, security, and reliability of SQL Server while eliminating the need for expensive data movement.
By incorporating machine learning and AI models into SQL Server stored procedures, AI can seamlessly work with the data within the SQL Server environment. This approach offers additional advantages for implementing machine learning and AI, simplifying the integration process.
Python and R versions supported SQL server editions.
While installing SQL Server, under the features option, we must select the features including “Python” under Machine Learning Services and Language Extensions and Machine Learning Server.
To enable the use of the sp_execute_external_script system stored procedure for remote script execution in SQL Server, we can follow these steps:
- Open SQL Server Management Studio (SSMS) and connect to our SQL Server instance.
- Click on "New Query" to open a new query window.
Execute the following code to enable external script execution:
sp_configure 'external scripts enabled', 1;
RECONFIGURE;
This code enables the execution of external scripts in SQL Server.
Once we have enabled external script execution, we can use the sp_execute_external_script system stored procedure to run Python scripts inside SQL Server. Here's an example:
EXEC sp_execute_external_script
@language = N'Python',
@script = N'
# Our Python script goes here
# For example:
import pandas as pd
df = pd.DataFrame({"Column1": [1, 2, 3], "Column2": ["A", "B", "C"]})
OutputDataSet = df
';
In this example, we are running a simple Python script that creates a pandas DataFrame and assigns it to the OutputDataSet variable.
We can modify the Python script inside the @script parameter to perform the desired data analysis or machine learning tasks using Python libraries.