I work in an office where SQL Server is the backbone of everything we do, from data processing to cleaning to munging. My colleague specializes in writing complex functions and stored procedures to methodically process incoming data so that it can be standardized and put to work in reports, visualizations, and analytics projects. Prior to starting here, I had very little experience with SQL, aside from writing the most basic of queries. The vast majority of my analysis prep work was all done in R. My boss insists that I improve my SQL skills, even though there seem to be very few assignments that can't be done more efficiently and with far fewer lines of code using R packages like dplyr, data.table, and tidyr (to name a few). My question is--does this make sense?
A couple weeks ago, I found myself faced with the task of getting a list of column names for each row in a table that met certain criteria and concatenating them into a vector of strings. There was a tight deadline and at the time, I was experiencing some blockage and couldn't quite wrap my head around the problem. I asked my boss, who in turn asked my colleague to write a script TSQL to solve the problem. While he was working on it, I figured out a way to do it in R writing a fairly simple function and applying it over the data frame. My colleague came back with his script about two hours later. It was at least 75 lines comprising two nested for loops. I asked him to tell notify when it was finished running and he said it would take several hours. Meanwhile my R script was able to loop over the ~45,000 records in about 30 seconds.
Am I right to assume that R is a much better choice for cleaning and munging data? Maybe the SQL developer in my office is just inept? I'm curious if anyone who has worked with both R and SQL (or Python and SQL for that matter) have any thoughts on this.