STRING_AGG Return Types

Dave Mason SQL Server STRING_AGG

I had a chance to use the STRING_AGG function recently, or so I thought. Since I couldn't use it for "real world" work, I decided to give it a test drive on my own. My initial thought was to do some performance testing and blog about that, but I ran into an interesting problem that I'll discuss here for now.

I started by populating a table with some "string" data:


Formatting Dates with CONVERT

Dave Mason - SQL Server CONVERT

Displaying dates and times with different formats in TSQL is a task I run into quite a bit. I used to visit this page so many times, I'm surprised it doesn't have a "Welcome back, Dave!" banner on it at the top.


Installing SSRS 2017

Dave Mason SQL Server SSRS 2017

Back in 2017 before SQL Server 2017 reached its GA release, I wrote a post about installing multiple instances of SSRS on the same host. I used SQL Server 2017 RC2 to do my testing and provide some screen shots. But by the time the GA release of SQL 2017 arrived, the SSRS installation was decoupled from the main SQL Server installation package (much like SSMS 2017). I'd gotten a few questions and comments in the post asking about the "new" SSRS 2017 installation. I'll try to address that here, based on some brief installation testing.


T-SQL Tuesday #98: Technical Challenges

Dave Mason T-SQL Tuesday

For T-SQL Tuesday #98, Arun Sirpal (b|t) asks us to write about a time when we faced a technical challenge that we overcame. Thanks for asking, Arun!


Generating Random Passwords In Bulk

Idera Software hosted another SQL Chat on Twitter yesterday. One of the questions that came up was about security:


2017 Year End Review

Another year of blogging has come and gone. Thank you to everyone that's dropped by! For many other technical bloggers, one post per week is a common goal. After a few years, I'm pretty sure I won't hit that target, at least not for the duration of a year. For 2017 I managed to write 42 posts. Averaging more than 3 posts per month for a year is pretty good, I'd say.


Thoughts On Technical Interviews

For a long time, I have wanted to share my experiences with technical interviews, but the time never seemed right. Until now. A few months back I left a company where I was a full time employee. There have been many interviews since then that pushed me to write this post. Although I have been on the other side of the table as an interviewer many times, and I think I have a healthy understanding of what it's like from both perspectives, much of this is born out of my recent experiences as a candidate. Interviewing can be an emotional (and frustrating) process. I hope my thoughts presented here are received as sensible and reasoned.


Machine Learning Services and Result Sets part 3

Dave Mason SQL Server Machine Learning Services sp_execute_external_script WITH RESULT SETS

To quickly recap the last couple of posts, the set of data returned by sp_execute_external_script has no column names by default. The column names (and data types) can be defined by using the WITH RESULT SETS options of the EXECUTE statement. But you have to know the result sets definition (number of columns, column names, and data types) at design time (before running the query). And that's a shame: there are column names for R and Python data frames, but SQL Server doesn't have a built-in way to access that metadata dynamically at runtime.

Perhaps in a future version of SQL Server, there will be a new parameter for WITH RESULTS SETS or a way to specify the result sets definition without having to hard code it. Until then, we have to do without, or get creative. Let's take a look at one possible solution.


Machine Learning Services and Result Sets part 2

Dave Mason SQL Server Machine Learning Services WITH RESULT SETS R Python

In the last post, I pondered the use of WITH RESULT SETS along with sp_execute_external_script when we don't know the structure of the OutputDataSet until run time. Let's look at another example. Here I'll use the rvest package to scrape a web page: each HTML <table> found on the web page becomes a data frame in the "tables" list (line 5). The first data frame in the list is returned to SQL Server (line 6).


Machine Learning Services and Result Sets

Dave Mason SQL Server Machine Learning Services WITH RESULT SETS R Python

When you run an R or Python script in-database via the sp_execute_external_script stored procedure, result sets returned by the stored procedure are output with unnamed columns by default. Here's an example using R and the [WideWorldImporters] database. The input data is a simple query on the Application.Countries table. R creates a data frame from the input data and merely returns it back to SQL Server (as the "OutputDataSet" data frame).