DEV Community

loading...

Discussion on: What is a SQL query you are proud of?

Collapse
peledzohar profile image
Zohar Peled

Personally, I find complex solutions not so attractive. I prefer solutions to be as simple as possible - and as readable as possible. This is a very important aspect of any code, since it allows for easier maintenance - and SQL is no different in that aspect.

Having said that, My list of my own favorite SQL solutions are things that require some knowledge and understanding of SQL Server, which is practically the only RDBMS I'm working with.

I do have to say, though, that most of these solutions are to problems other people have had - and I wouldn't have had the chance to write them if I wasn't an active member of StackOverflow, answering SQL related questions (among others).

Personally, I think that with proper database planning, most of the interesting, difficult problems simply do not occur.

An example of such a problem I've tried to solve for someone on stackoverflow is this:

The OP had a database that was being populated by a process they can't control. That process inserted information to the database in batches, each batch creating the same table over and over again, but on a new schema.

The problem was that the OP wanted a view that's selecting the information from the newest schema created, without having to manually change that view definition every time new data enters the system.

At first, it seemed that this can't be done, since it looks like the only solution was to use dynamic SQL, and that's impossible in a view and in a user-defined table valued function. I thought so too the first time I've seen this question.
For a long time, the question remained unanswered.

Then, 3 months after it was posted, I came up with a solution to this problem - use a view, but also have a DDL trigger listen to the CREATE_TABLE event on the database - and in that trigger, use dynamic SQL to alter the view to read the data from the new table - if the table created was the source table.

This is not a very complicated solution, but it was an interesting one to write and tests I've done confirmed that it should work - however the OP never commented or accepted the answer so I don't know if the even checked my suggested solution.
Here's the relevant Stackoverflow question:

Dynamic Schema name in SQL View

1

I have two datasets:

  1. one is data about dogs [my data]
  2. the second is a lookup table of matching keys [I have no control over this data]

The matching keys are updated regularly, and I want to create a View (or something that fulfills the same purpose) of the Dog…

And here's my answer:

The match updates in the lookup table are differentiated by their schema names, so to get the most recent, I just have to identify the latest schema name and swap it out of the query.

You can use a view to solve this problem, but you need some way of…