SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

Machine Learning in the Database

When SQL Server added the ability to execute R code, the decision seemed to split the customer base into two groups. One group was impressed and thought the idea of executing R code to analyze data in the database was a good idea. They were excited and impressed by the loan classification demo. If you haven't read about this or seen the demo, it's very interesting, and it's something you might take a few minutes to read or watch it.

The other group of customers felt this was a poor use of CPU cycles for a very expensive SQL Server CPU license. Running a complex analysis, training models, and other functions commonly associated with R scripts aren't a good use of scarce resources. They would rather have R code execute on a separate server, much like any large messaging workload might be better served by a service such as AWS' Simple Queue Service rather than Service Broker.

I tend to be in the first group, as is Dr. Low. He writes that there is a place where Machine Learning Services (MLS), with both R and Python, are a good use of resources. Not in all cases, and certainly not for all work. The difficult parts of training models and doing the hard work of coming up with new ways to perform an analysis is definitely better left to workstations and data scientists. Those actions might not be worth the resources they take.

Once the models are trained, however, the executable load of submitting parameters to a model and getting a prediction is small. SQL Server allows us to load pre-trained models into the database and just call them as needed. Plus, the R models run in a multi-threaded fashion, unlike the single threaded execution in clients such as R Studio.

As with any feature of SQL Server, it's important to test and evaluate the real world impact of new code on production sized workloads. Not only will you want to measure the load of your model execution, but you should also measure any changes in your existing workload with the additional R or Python code load. While I wouldn't prevent the use of MLS in SQL Server, just like SQL CLR code, I would be careful about introducing without extensive testing, including dark deployments and simulated loads.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL In The City

SQL in the City Streamed – Compliant DevOps: June 20 2018

Join Redgate’s livestream on June 20 and learn from Microsoft Data Platform MVPs, Steve Jones, Grant Fritchey and Kathi Kellenberger. The schedule will include sessions to help you learn how to demonstrate compliance with regulations such as the GDPR, HIPAA and SOX. Find out more and register for free

SQL Provision

NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps

Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial

Featured Contents


Dynamic File Column mapping in SSIS.

Hiske Bekkering from SQLServerCentral.com

An efficient and reliable method for reading CSV files with variable columns. More »


Indexes That Have Not Been Used but Are Being Updated

Additional Articles from Database Journal

Greg Larsen talks about how indexes that are not being used are costing you valuable resources and causing your update statements to run longer. More »


Using Power BI in a Hybrid Environment

Additional Articles from Microsoft MSDN

This paper describes best practices for security, networking, and component architecture for building a hybrid business intelligence (BI) environment by using a combination of Microsoft Office 365, Microsoft Azure, and on-premises data sources. Many organizations are taking advantage of the benefits of cloud computing such as lower capital expenditures and increased agility, while still maintaining data in on-premises data stores. More »


From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 106 – Ring Chart MAQ Software)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Ring Chart by MAQ Software. The Ring Chart is similar... More »


From the SQLServerCentral Blogs - Bring Your Own Key to Azure SQL Database TDE New UI

Bradley Schacht from SQLServerCentral Blogs

In a recent post I wrote about a new functionality for Azure SQL Database’s TDE feature: Bring Your Own Key.... More »

Question of the Day

Today's Question (by Thomas Franz):

An easy question today: what will the following query return?

  FROM (VALUES (1), (2), (3), (1)) AS t1(id)
  FROM (VALUES (4), (5))           AS t2(id)

Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: UNION.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Expert T-SQL Window Functions in SQL Server

Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

There is a basic Availability Group (BAG) setup on a server. The main DBA gave me access to the system and I connect with SSMS. I see the Availability Group node in SSMS and can expand the BAG to see my database. I want to change the secondary to read only access, but the checkbox doesn't work. What's the most likely cause?

Answer: Basic Availability Groups can't have readable secondaries


There are certain limitations for BAGs, among them is the inability to use a secondary in a read only fashion.

Ref: Basic Availability Groups - click here

» Discuss this question and answer on the forums

Featured Script

SQL Server WaitStats Script

Yusuf Kahveci from SQLServerCentral.com

With SQL Server Instance IN, we can find the types that are waiting on this script to determine our main problem sources and plan our optimization methods according to this process.
I will take all the details of the waiting type in another place.
By archiving the result of this script with certain periods, you can compare the wait type ratios between periods more clearly after and after your improvements.

More »

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

Correlated subquery using two different tables... - I am trying to figure out how to pull the data from two tables where I can return firstname, lastname,...

SQL Server 2017 : SQL Server 2017 - Development

Get Index Fragmentation Cheaply - Is there a cheaper way to write this (from a query cost perspective)?  It seems to churn quite a bit...

SQL Server 2016 : SQL Server 2016 - Administration

AlwaysOn Availability Group Without Shared Storage. How does it work? - Hi, I am using SQL Server 2016 Enterprise. I am confused as to how each SQL Server can have its own...

AlwaysOn DR Solution - Hi, I am looking into setting up a SQL 2016 AlwaysOn Availability Group consisting of 1 Primary server, 1 on-site...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Compare comma separated values - Hi,    let's say I have this: DROP TABLE IF EXISTS #CSV CREATE TABLE #CSV (ID INT NOT NULL, LIST VARCHAR(30) NOT NULL ) INSERT...

Any way to include SSIS execution messages in an alert? - Hello experts, When I get an error with an SSIS package, I follow the manual process of right-clicking on the project...

SQL Server 2014 : Administration - SQL Server 2014

querying on view takes long - Please help. I tried to check on DTA it says there is a syntax error. I couold not find what...

SQL Server 2014 : Development - SQL Server 2014

Oracle's ROWTYPE equivalent in Sql Server 2014 - I appreciate ROWTYPE equivalent didn't exist in SQL Server versions prior 2014 and I could find no information re SQL...

Removing HTML tags - I've read many pages on the web about doing this and many come back to this page: https://www.sqlservercentral.com/Forums/1197668/Strip-HTML-Tags I have a SQL...

SQL Server 2012 : SQL 2012 - General

T-SQL help needed to eliminate duplicates from table - To make a long story short, one of our tables that have rx data has got messed up because the...

SQL Server 2012 : SQL Server 2012 - T-SQL

Cross Apply not summing correctly - I have a group of records that I can sort of get working  but the inner cross apply is doubling...

Single value in selection with groups - I am trying sum some columns and have a column that is different in each row but I only want...

SQL Server locking issue with insert and read - Hi, we are experiencing locking issues recently. We use Stored Procedures extensively for our database calls. We have a stored...

SQL Server 2008 : SQL Server 2008 - General

Affinity Mask... - Hey Folks, Appreciate if somebody can share knowledge on factors one has to take into account before setting processor affinity mask for SQL instances....

Same query, two users, different performance - Hey Gurus, I have a query that performs differently depending upon the user executing it. This was first brought to my...

SQL Server 2008 : T-SQL (SS2K8)

Return row as null instead of empty - Hi, below the sample structure, Declare @sample table(id int, name varchar, datetime); select * from @sample this will return empty row. but i...

Data Warehousing : Integration Services

Connecting to SSIS instance with different original server name - Hi, I am trying to  connect  to a  SSIS server  with a different  host name, have anyone tryied this configuration ????, it...

Mismatch or data overflow on MySQL Link Server - I try to Insert some records to my remote MySql from my SQL Server. The MySql is connected as Link server. The...

Object variables, ADO Recordsets, & decrypting column - Ihope someone can help me out. My C# skills are nill, so I need a lot ofinformation. A bit of background:...

SQL Server 7,2000 : T-SQL

BCP error -Unable to open BCP host data-file - SQLState = S1000, NativeError = 0 Error = Unable to open BCP host data-file NULL   I am getitng the above error when i...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com