CODEWITHSUNDEEP
sql-serverrmsdnsql-server-2016
gkoul
gkoul

Reputation: 1287

Editions and Supported Features for SQL Server 2016

I was just wondering what is the difference between Basic R integration and Advanced R integration features that is mentioned in the official MS-SQL Server 2016 website?

The link with the comparison tables of the components is here.

In which category does the following piece of code belong to?

DROP TABLE IF EXISTS #TempTable

CREATE TABLE #TempTable (x NVARCHAR(MAX), y NVARCHAR(MAX))
INSERT INTO #TempTable
EXEC    [dbo].[proc_ReturnDataForCurveGraphsDoubleNorm]
    @sRAWFILEID = @sRAWFILEID, 
    @PREBLEACHVALUES = @sPREBLEACHVALUES, 
    @BLEACHVALUES = @sBLEACHVALUES, 
    @INITIALBLEACHVALUES = @sINITIALBLEACHVALUES

BEGIN TRY 
execute sp_execute_external_script    
  @language = N'R'    
, @script = N'
    df <- as.data.frame(c(InputDataSet));
    xdata <- as.numeric(as.character(df[,1]));
    ydata <- as.numeric(as.character(df[,2]));

    m = nls(ydata ~ yo - a * exp(-b * xdata),
    data = df, 
    start = list(yo = 0.9, a = 0.5, b = 0.563),
    trace = F,
    control = list(maxiter = 1000, warnOnly = TRUE), 
    lower = list(0, 0, 0),
    upper = list(1, 100, 100), algorith = "port");

    param <- coef(m);

    RSS.p <- sum(residuals(m)^2);
    TSS <- sum((ydata - mean(ydata))^2);
    r_square <- 1 - (RSS.p/TSS);

    yo <- param[1];
    a  <- param[2];
    b  <- param[3];

    xdata2 <- seq(0,max(xdata),0.01);

    fe2 <- yo - a*exp(-b*xdata2);
    mf <- ( (yo - fe2[1]) / (1 - fe2[1] ) ) ;

    thalf <- log(2) / b;

    OutputDataSet <- data.frame( round( yo , 4 ), 
    round( a, 4), 
    round( b, 4), 
    round( mf , 2 ), 
    round( thalf , 2 ), 
    round( r_square, 2) );
            '    
, @input_data_1 = N' SELECT * FROM #TempTable;  


 WITH RESULT SETS (([yo] NVARCHAR(MAX), 
 [a] NVARCHAR(MAX), 
 [b] NVARCHAR(MAX), 
 [mobile_fraction] NVARCHAR(MAX), 
 [t_half] NVARCHAR(MAX), 
 [r_square] NVARCHAR(MAX))); 



END TRY 

BEGIN CATCH  
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH 
END

Upvotes: 4

Views: 987

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

From what I read in this MSDN documentation, there are a number of tiers of R support available in SQL Server. I did not see anything called "Advanced R Integration," though SQL Server Enterprise Edition has the most powerful R support:

Includes both R Services, for in-database analytics in SQL Server 2016, as well as R Server (Standalone) on Windows, which can be used to connect to a variety of databases and pull data for analysis at scale, but which does not run in-database. Also includes DeployR, which can be used to deploy R scripts and models as a Web Service.

No restrictions. Optimized performance and scalability through parallelization and streaming. Suopprts analysis of large datasets that do not fit in the available memory, by using the ScaleR functions.

In-database analytics in SQL Server supports resource governance of external scripts to customize server resource usage.

All the other versions of SQL Server have more limited R support than this, with only a subset of the above features. Presumably one of them would be considered to have basic integration only.

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131728

You should look to the R Services section for such information, specifically Differences in R Features between Editions of SQL Server.

According to this, Standard and Express have process limitations and lower scalability. Your code will run but it will be slower and could saturate the server when running a heavy training task:

However, Standard Edition does not support Resource Governor. Using resource governance is the best way to customize server resources to support varied R workloads such as model training and scoring.

Standard Edition also provides limited performance and scalability in comparison to Enterprise and Developer Editions. Specifically, all of the ScaleR functions and packages are included with Standard Edition, but the service that launches and manages R scripts is limited in the number of processes it can use. Moreover, data processed by the script must fit in memory.

You could test the difference with SQL Server Express, eg in a VM:

Express Edition with Advanced Services

Express Edition is subject to the same limitations as Standard Edition.

Upvotes: 2

Related Questions