4 ms·

My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything.

My first job out of university was on an analytics team at a consulting firm (big enough that you know them) that used MS SQL Server for absolutely everything. Data cleaning? SQL. Feature engineering? SQL. Pipelines of stored procedures, stored in other stored procedures. Some of these procedures were so convoluted that they outputted tables with over 700 features, and had queries that were hundreds of lines long. Every input, stored procedure, and output timestamped, so a change to one script involved changing every procedure downstream of it. My cries to use git were unheeded (would have required upskilling everyone on the team). It was probably the worst year of my life. By the end of it I built a framework in T-SQL that would generate T-SQL scripts. In the final week of a project (which had been consistent 60-70 hour weeks), the partner on the project came in, saw the procedures written in my framework and demanded that they all be converted back into raw SQL. I moved teams a few weeks later. The only good bit looking at it, is that now I'm REALLY good with SQL. It's incredibly powerful stuff, and more devs should work on it.

dwd 8y ago
You can do some insanely complicated stuff in a stored procedure. A favourite was versioning and updating a set of data across multiple tables as an atomic transaction. Also MS SQL let you throw a data object (say XML) at a stored procedure, convert it to a table structure with some XPath (another useful black art like regex) and use that as the input to a single INSERT.
- AmericanChopper 8y ago
  >You can do some insanely complicated stuff in a stored procedure. You’re giving me flashbacks to stored procs that directly invoke java methods, and batch scrips that load client supplied data from and FTP server into external tables.
  - dwd 8y ago
    xp_cmdshell always seemed like a really bad idea. The sql server agent was easy and reliable for scheduling so it made sense to let it run the whole process.
  - Shacklz 8y ago
    > You’re giving me flashbacks to stored procs that directly invoke java methods A legacy application I once worked at used this extensively, and good grief it gave me nightmares. How anyone ever thought this is a good idea to use is beyond me; that stuff is not maintainable at all.
    - AmericanChopper 8y ago
      Yeah, the business logic was all over the place. Everything needed to be reverse engineered any time there was a problem, or if you wanted to make any changes. To make it worse, the app had about 300 scheduled tasks that were a mixture of batch files, SQL scripts and java classes. None of which had source code, most of which were slightly different and essentially operated as mysterious black boxes. We ended up having a copy of JD-GUI on all the production servers because we needed to decompile for debugging so often.
sk5t 8y ago
The only question that remains is--Accenture or KPMG?
- jessaustin 8y ago
  It could have been Deloitte, or as I convinced most of the contractors on several projects to pronounce it, "Delouche".
  - JimmyAustin 8y ago
    Both incredibly close, but not right on the money.
tartoran 8y ago
That happens in the wild unfortunately, I ve seen some sql-Frankensteins here and there but generally, if done properly, sql is the easiest to grok and understand the domain from. I was introduced to SQL circa 2000(by my dad) and I've been heavily using it since. My bulk of experience in Sql was in MsSql. Recently I got into an Oracle shop and the dialect is a bit different though it didnt take too long to be productive in. However, being productive and such, if you ask me, oracle smells clunky and has way too many features, let's say it's not my cop of tea...
beefield 8y ago
> My cries to use git were unheeded (would have required upskilling everyone on the team). What is the best practice workflow using git with SQL server views/procedures? Can you actually somehow track changes in the views/procs themselves so that if someone happens to run ALTER VIEW, git diff is going to show something?
- manigandham 8y ago
  It's mostly about using git to ensure a consistent snapshot as changes are made rather than updating one procedure at a time. If you are asking about SQL text then a good formatter would make it easy to see the physical diffs. Otherwise there are various vendors and tools that parse SQL and show the logical diffs in queries and schemas, along with doing deployments, backups, syncing changes, etc.
- dwd 8y ago
  Every conversation I've ever had came back to using RedGate SQL Compare to diff databases and TeamCity for CI. You basically shouldn't allow anyone to modify anything without it being scripted (bonus points if it comes with a rollback and is repeatable for testing). Your scripts then all go into Git.
  - RegBarclay 8y ago
    RedGate's SQL Change Automation (formerly ReadyRoll) is pretty slick. DbUp is a good, free alternative. I second the rollback and repeatability bonus. Every script should leave the database in either the new state or the previous good state no matter how many times it's run.
    - beefield 8y ago
      > Every script should leave the database in either the new state or the previous good state no matter how many times it's run. I wonder if there were somewhere a website to describe good idioms to achieve this?
      - 8y ago
ako 8y ago
There's no reason you can't store your sql/tsql/plsql in version control. We were doing this 20+ years ago, all code was in csv (we upgraded from rcs to csv), and we had a productized distributed scheduling system that would deploy all the sql scripts every night on a number of oracle databases running from aix, to solaris, to vms, to hpux, to irix, and later linux and windows NT. Similar like you would now use jenkins to build, deploy and test your java apps. Developers would never touch the test/production databases, only commit sql to csv. Develop local, sql text files, test on a local or shared development database, and then commit to csv.
- lmm 8y ago
  There's no reason you can't - but there's limited tooling support and limited worker mindshare for that kind of approach.
  - ako 8y ago
    What tooling do you need? They're just source code files that you can edit with a code editor or an IDE (DataGrip). In addition you need a file to deploy your sql to a database, this can be a sql or shell script, or a gradle file or even make.
    - lmm 8y ago
      > What tooling do you need? Jump-to-definition when something calls something else. Unit tests. Atomic build/install. > this can be a sql or shell script, or a gradle file or even make. Precisely the problem. There's too many different ways to do it, and no consistency.
      - jve 8y ago
        Atomic build/install - well, you get transactions OOTB. Unit tests - In Microsoft world, 1st class citizen: https://docs.microsoft.com/en-us/sql/ssdt/verifying-database-code-by-using-sql-server-unit-tests?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/ssdt/verifying-database... Debugging leaves more to wish, but still available in some limited form.

dwd 8y ago

You can do some insanely complicated stuff in a stored procedure. A favourite was versioning and updating a set of data across multiple tables as an atomic transaction. Also MS SQL let you throw a data object (say XML) at a stored procedure, convert it to a table structure with some XPath (another useful black art like regex) and use that as the input to a single INSERT.

AmericanChopper 8y ago
>You can do some insanely complicated stuff in a stored procedure. You’re giving me flashbacks to stored procs that directly invoke java methods, and batch scrips that load client supplied data from and FTP server into external tables.
- dwd 8y ago
  xp_cmdshell always seemed like a really bad idea. The sql server agent was easy and reliable for scheduling so it made sense to let it run the whole process.
- Shacklz 8y ago
  > You’re giving me flashbacks to stored procs that directly invoke java methods A legacy application I once worked at used this extensively, and good grief it gave me nightmares. How anyone ever thought this is a good idea to use is beyond me; that stuff is not maintainable at all.
  - AmericanChopper 8y ago
    Yeah, the business logic was all over the place. Everything needed to be reverse engineered any time there was a problem, or if you wanted to make any changes. To make it worse, the app had about 300 scheduled tasks that were a mixture of batch files, SQL scripts and java classes. None of which had source code, most of which were slightly different and essentially operated as mysterious black boxes. We ended up having a copy of JD-GUI on all the production servers because we needed to decompile for debugging so often.

sk5t 8y ago

The only question that remains is--Accenture or KPMG?

jessaustin 8y ago
It could have been Deloitte, or as I convinced most of the contractors on several projects to pronounce it, "Delouche".
- JimmyAustin 8y ago
  Both incredibly close, but not right on the money.

tartoran 8y ago

That happens in the wild unfortunately, I ve seen some sql-Frankensteins here and there but generally, if done properly, sql is the easiest to grok and understand the domain from. I was introduced to SQL circa 2000(by my dad) and I've been heavily using it since. My bulk of experience in Sql was in MsSql. Recently I got into an Oracle shop and the dialect is a bit different though it didnt take too long to be productive in. However, being productive and such, if you ask me, oracle smells clunky and has way too many features, let's say it's not my cop of tea...

beefield 8y ago

> My cries to use git were unheeded (would have required upskilling everyone on the team). What is the best practice workflow using git with SQL server views/procedures? Can you actually somehow track changes in the views/procs themselves so that if someone happens to run ALTER VIEW, git diff is going to show something?

manigandham 8y ago
It's mostly about using git to ensure a consistent snapshot as changes are made rather than updating one procedure at a time. If you are asking about SQL text then a good formatter would make it easy to see the physical diffs. Otherwise there are various vendors and tools that parse SQL and show the logical diffs in queries and schemas, along with doing deployments, backups, syncing changes, etc.
dwd 8y ago
Every conversation I've ever had came back to using RedGate SQL Compare to diff databases and TeamCity for CI. You basically shouldn't allow anyone to modify anything without it being scripted (bonus points if it comes with a rollback and is repeatable for testing). Your scripts then all go into Git.
- RegBarclay 8y ago
  RedGate's SQL Change Automation (formerly ReadyRoll) is pretty slick. DbUp is a good, free alternative. I second the rollback and repeatability bonus. Every script should leave the database in either the new state or the previous good state no matter how many times it's run.
  - beefield 8y ago
    > Every script should leave the database in either the new state or the previous good state no matter how many times it's run. I wonder if there were somewhere a website to describe good idioms to achieve this?
    - 8y ago

ako 8y ago

There's no reason you can't store your sql/tsql/plsql in version control. We were doing this 20+ years ago, all code was in csv (we upgraded from rcs to csv), and we had a productized distributed scheduling system that would deploy all the sql scripts every night on a number of oracle databases running from aix, to solaris, to vms, to hpux, to irix, and later linux and windows NT. Similar like you would now use jenkins to build, deploy and test your java apps. Developers would never touch the test/production databases, only commit sql to csv. Develop local, sql text files, test on a local or shared development database, and then commit to csv.

lmm 8y ago
There's no reason you can't - but there's limited tooling support and limited worker mindshare for that kind of approach.
- ako 8y ago
  What tooling do you need? They're just source code files that you can edit with a code editor or an IDE (DataGrip). In addition you need a file to deploy your sql to a database, this can be a sql or shell script, or a gradle file or even make.
  - lmm 8y ago
    > What tooling do you need? Jump-to-definition when something calls something else. Unit tests. Atomic build/install. > this can be a sql or shell script, or a gradle file or even make. Precisely the problem. There's too many different ways to do it, and no consistency.
    - jve 8y ago
      Atomic build/install - well, you get transactions OOTB. Unit tests - In Microsoft world, 1st class citizen: https://docs.microsoft.com/en-us/sql/ssdt/verifying-database-code-by-using-sql-server-unit-tests?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/ssdt/verifying-database... Debugging leaves more to wish, but still available in some limited form.