Sharing Common SQL Queries Amongst a Team

Question

I don't even want to think about how many man hours have been spent writing the same queries to join over the exact same tables at my company.

When I first started at my job I identified this as an inefficiency and started writing views in a separate schema for the sole purpose of developer convenience.
My boss didn't like this very much and recommended I start committing my common queries to source control in a separate folder from the production SQL. This makes sense because some scripts require parameters and not all are read only.

What is a Common Query?

• Script used to diagnose certain problems
• Script to view relationships between several tables (doing multiple joins)
• Script that we don't want in a stored procedure because it is often tweaked to diagnose the issue of the day

Issues I want to Address

• Discoverability, queries will be rewritten if nobody can find them
• IDE integration, want to be able to easily view queries in IDE. I've tried the SQL Server Solutions but they really suck because they lock you into only working on that set of files.

I was wondering how all the pro's out there share their common SQL queries.

Thanks

Answer 1

If it is something that I would call "common" I would probably create a stored procedure that the folks with necessary permissions can run.

If the stored procedure route won't work well for your team, then the other option is to create a view. Creating a view comes with unique challenges though such as ensuring that everyone running the view has select permissions on all of the tables in the view as well.

Outside of storing the scripts in source control of some kind, maybe storing them on a Share Point site or a network file share would work OK for your team. The real challenge that you will have in sharing scripts is that people have different ways to identify what they are looking for. A wiki type of site that allows tagging the different types of things the queries do would be useful.

Answer 2

You create a view.

Answer 3

Lots of ways to do this (including some that you've mentioned already):

• Table-Valued User Defined Functions
• Stored Procedures
• Views
• Source Control
• Formal, shared Data Access Layer for client code

Answer 4

Views are the right way to handle this sort of thing. Or, in some cases, a stored procedure.

But there's no rule that says you can't also store the DDL for a View or a Stored Procedure in source control.

Answer 5

Seems like the op wants to know how to get the word out to the team about useful SQL that others can/should use so not to recreate.

How I have done this in the past is through two ways:

1. Create a team web wiki page that details the SQL with examples of how it is used.
2. Email the team when new SQL is created that should be shared.

Of course, we always include the SQL code in version control, just the wiki and email are use in the "getting word out there" part.