Ingenious or Outrageous Thoughts
As I was reviewing my thoughts and responses today on the “Ingenious or Outrageous” editorial from yesterday I started thinking about the bigger issue.
Many times we try to find ways around the way things are designed in tools such as SQL Server because they don’t behave the way we want them to. I used to wrap non-deterministic functions in a view in order to use them in my own user defined functions.
You can’t use GETDATE() in a user defined function because GETDATE() is non-deterministic; it returns a different value each time it is called, without changing input values. So, if I wanted to create a user defined function to determine how many seconds have passed today since midnight, I would have to create a user defined function where I would pass a scalar value in the form of a DateTime data type, or something of the sort.
Instead, I created a view that had one column and one row…
CREATE VIEW SpoofSQLServer
AS
SELECT GETDATE() AS DateTimeNow
Now I could write a user defined function based on the view allowing me to take advantage of GETDATE() rather than having to pass it into the function as a parameter. The function is now self contained.
Using a CLR user defined function this would be a simple thing. But there are specific reasons SQL Server was designed with the constraints it has. It could be that time was running out to get the product to market. It could be that there was a specific design decision to make it work a specific way.
The point is, I believe we should take a second to think about what we are doing when we come up with esoteric methods to solve age old problems. There is probably a solution already available.
Here are some thoughts from some of you…
Andrew:
I’m going to give the classic DBA answer: “It depends”. Being a developer, I can understand the need to be have un-intrusive ways to work with a database. Sometimes you just need to get something done, and not have to negotiate rearranging the database with the DBA’s, or you need to have ways to add functionality without touching things that are already in place. Or the culture at your shop doesn’t like triggers*.
That said, this is definitely a kludge. It can be subverted and it works by using features in unobvious ways. If I were in a place where this looked tempting, I’d want to triple check my options before using it.
As I am still learning myself, ingenuity is a good thing to have on your side, but maintainability is better in most cases.
Wayne:
Using a view to enforce business rules.
I suspect this developer has worked on a very early version of MS SQL or another SQL server that didn’t have triggers or the other enforcement capabilities that are prevalent now.
Sometimes when a vendor implements a "solution" to a development problem, it’s impact on prior and current applications various on how different the developers solution is from the vendors solution.
If an older solution is working, there is no need to change it even if the new environment has better or different ways for attaining the same solution.
Well, do you agree? At what point do we leave being inventive and start creating solutions that really aren’t wise in the long run? Are there specific questions we should ask ourselves when solving these kinds of problems? Share your thoughts by writing to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with linked servers in SQL Server 2008 R2
This article describes the problems you can have with SQL Server 2008 R2 linked servers.
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)