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:
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…
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
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_TABLEevent 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:
I have two datasets:
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:
You can use a view to solve this problem, but you need some way of…