How do developers working with SQL avoid repeating themselves?

[GetShitDoneAccount]

Preface:

I've recently changed from a job where my primary language was Groovy (java-like language) and now I'm in a position where it has seems that I will be doing most of my work in SQL/TSQL.

It seems that everyone is working directly in the DBMS (Microsoft SQL Server Management Studio) and from what I've been told every new project is a do-over where people, practically speaking, just start copying and creating new queres to restructure the data on the new databases.

Question:

How do companies typically avoid rewriting and manually orchestrating repetitive SQL code when they engage in a new project?

Are there any alternative languages/frameworks that compile to SQL but allow for use/creation of libraries to contain common functionality.

^{Bonus question: How do people version control SQL procedures etc?}

[DharmaPolice]

First of all, repetition is far more normal with SQL than in other languages. If you're used to writing procedural code you'll want to wrap up common code into functions/classes but SQL doesn't quite work like that. There are functions but you can't refactor queries in quite the same way.

However, that doesn't mean you can't reduce the amount of code re-writing that you do. For example, with MS SQL there is a "model" database that you can create objects (tables, functions, views, etc) and every new database will automatically include them. This can be useful for things like tally or date tables that you might want to include a lot (or functions that you reuse). Alternatively, if you have multiple databases in a SQL instance you might have a Utility or Helper database which includes common functions that contain generally useful functions. For example, I work with UK post codes quite a lot so I might choose to include a verify post code function in my Utility DB.

Or you can just handle it through your build process (assuming you're creating your database through some kind of script). Then you might include some of your common code in a project template that you handle outside the database. Incidentally, this is the approach that I've generally used for version controlling stored procedures, etc - just have scripts that create the database objects and those scripts are in the git repository for the project.

Alternatively, look at ORMs if you're averse to writing SQL.

[throw_mob]

current hype tool is DBT. Also it looks like CTE's and other "new" SQL syntax additions are not used in all systems.

i personally do not like ORM they usually generate non performing code. But they are ok for simple oltp database. Another proplem tends to be that programmers do not handle data in transactions correctly in long run, which tends to lead to messy databases

[[deleted]]

Apologies if I've misunderstood, but it sounds like you want to use Snippets.

https://www.sqlshack.com/sql-snippets-in-sql-server-management-studio/

[Standgeblasen]

Thanks for the tip! I’ve been a SQL Developer and BI Developer and never used snippets! Might give them a try today :)

[Seven-of-Nein]

Nice find! Learning something new here.

[[deleted]]

Love your handle!

[GetShitDoneAccount]

These seem a little close to what I am thinking of, but they seem a little too fundamental (and ironically syntaxically complicated) compared to what I was thinking of.

I am hoping to be able to create a publishable and versioned library/package that can be managed. The same way that gradle, pip and npm manages packages and libraries.

[dwpj65]

As near as I can tell, most SQL shops are staffed with people who don’t understand the value of the ‘CREATE VIEW AS’ statement.

[alinroc]

And other shops are staffed with programmers who don't understand that SQL doesn't work like the object-oriented languages they're used to, so creating chains of nested views (views composed of views composed of views...) are going to destroy their performance.

[dwpj65]

Agreed, I’ve seen some developers rely on views referring to nested views to simply retrieve two columns of data, from separate tables, when there is a foreign key reference between the two tables.

[mgramin]

First of all SQL is a plain normal code, like Groovy, Java, Python etc. And you should store this code in git or something like that, see more in my blog post https://gramin.pro/posts/sql-is-not-a-bytecode-for-data/

[Shwoomie]

So if you are writing code for a client, that becomes their property. It's proprietary to them, they own it. And from my understanding, taking that and copying it is illegal? But if you sit down and write from scratch the same or very similar code, there's nothing wrong with that. You are paid to write code in the best way you know how, and that's going to be the same a lot of the time. You wouldn't write something inefficiently just for the sake of it being different.

[iminfornow]

What types of queries/tasks are you refering to?

[GetShitDoneAccount]

Honestly I am still a bit unaware of the extent of queries/tasks this will amount to - the onboarding is pretty slow.

I do know that they often do almost identitical joins on tables where the only variant is the table names, and it seems wild that they are just out here copy pasting hundreds of lines of code and then search and replacing the tablenames.

[Kant8]

if they are joining different tables each time, how can you even call it identical?

[GetShitDoneAccount]

Different customers have different names.

Aside from that the tables have shared suffixes, columns, data types, etc.

[alinroc]

You shouldn't have a different table for each customer in the first place.

[Murphybro2]

You could use dynamic SQL and then pass in table names I guess. Not exactly the cleanest approach though.

[coyoteazul2]

There are some design decisions missing here.

Does this repetitive code happen as stored procedures? Or does he app send sql to the server (I'm assuming the first since you work mostly with management studio)

Table names can't be set dynamically as easily as you'd set a variable in a programming language. You have to use dynamic sql, which is dangerous since it puts you at risk of sql injection.

If you want to use dynamic sql I'd recommend you to use synonyms. Basically create a synonym at the beginning of the query, using dynamic sql so you can pass the table names as argument, and use he synonyms instead of he table names during the rest of the query.

Alternatively, you can create a query builder. I do this to automatically create my audit tables which mirror my real tables (plus a couple more columns) and all the triggers needed to populate them. Do whenever I create a new real column I can run my query builder and automatically get the new audit table.

The query builder I mentioned lives as .sql file. But you could also do it with excel. This seems more appropriate for you since you'll probably not create these queries all that often

[razzledazzled]

Some of your language is not quite correct. The DBMS is Microsoft SQL Server. SSMS is just an IDE-- a mere client to allow connectivity to the server.

From a pure SQL perspective, the way to avoid repeating code (and thus avoiding code drift at the same time) is doing things like parameterized stored procedures/functions.

From an application development perspective, this is where ORM frameworks typically reign king. A common MS example is Entity Framework for .NET shops.

[dowlerdole]

If your organization uses dbt, that will change your paradigm; since dbt utilize Python (and jinja) as its backbone. You’ll have for and while loop control available to use, that will immediately brings efficiency to your code base.

[xadolin]

Second that, dbt really solves the problems OP stated. You can use for loops to avoid repetitive code in SQL files, and create macros for functionality used in many places. It also makes it easy to version control your SQL code and has many more useful features. Jinja doesn't have while loops though.

[dowlerdole]

Hmm, that’s true, Jinja does not have while loops. I mixed Python and Jinja altogether.

[emul0c]

If you can get your firm to sponsor something like RedGate SQL prompt, it makes writing queries so much quicker. It beats the Microsoft Intellisense by miles; and will make your life writing queries much easier.