September 11, 2017 at 12:00 am
Comments posted to this topic are about the item Challenge Your Code Design
September 11, 2017 at 8:19 am
The basic design principles of abstraction, encapsulation, polymorphism, and inheritence can be (in a limited way) achieved by T-SQL. It's not the same as C# or Java, but it helps to think in those terms when developing views and stored procedures.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 13, 2017 at 9:22 am
Eric M Russell - Monday, September 11, 2017 8:19 AMThe basic design principles of abstraction, encapsulation, polymorphism, and inheritence can be (in a limited way) achieved by T-SQL. It's not the same as C# or Java, but it helps to think in those terms when developing views and stored procedures.
Thanks for your comment Eric. What's your opinion on the approach I prescribe here? I interpreted your comment as adding additional info but not really reflecting on the article.
I was rather hoping for a little more engagement in the discussion forum from the community. Unusually quiet in here, people normally offer an opinion for or against, perhaps my piece wasn't provocative enough.
I failed to ignite a debate 🙁
September 13, 2017 at 1:32 pm
gary.strange-sqlconsumer - Wednesday, September 13, 2017 9:22 AMThanks for your comment Eric. What's your opinion on the approach I prescribe here? I interpreted your comment as adding additional info but not really reflecting on the article.I was rather hoping for a little more engagement in the discussion forum from the community. Unusually quiet in here, people normally offer an opinion for or against, perhaps my piece wasn't provocative enough.
I failed to ignite a debate 🙁
Hi Gary,
You presented the case well for applying the principal of single responsibility when developing T-SQL queries, views and stored procedures. The code performs better and is easier to unit test, because it only retrieves those columns are required by a specific case usage. At the same time it provides more opportunity for reusability. It's not an object oriented programming language, but the same design principles still apply.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 14, 2017 at 2:10 am
Eric M Russell - Wednesday, September 13, 2017 1:32 PMgary.strange-sqlconsumer - Wednesday, September 13, 2017 9:22 AMThanks for your comment Eric. What's your opinion on the approach I prescribe here? I interpreted your comment as adding additional info but not really reflecting on the article.I was rather hoping for a little more engagement in the discussion forum from the community. Unusually quiet in here, people normally offer an opinion for or against, perhaps my piece wasn't provocative enough.
I failed to ignite a debate 🙁
Hi Gary,
You presented the case well for applying the principal of single responsibility when developing T-SQL queries, views and stored procedures. The code performs better and is easier to unit test, because it only retrieves those columns are required by a specific case usage. At the same time it provides more opportunity for reusability. It's not an object oriented programming language, but the same design principles still apply.
Thanks Eric,
The feedback is much appreciated.
November 1, 2019 at 6:04 am
This was removed by the editor as SPAM
November 1, 2019 at 6:07 am
This was removed by the editor as SPAM
November 1, 2019 at 11:15 am
Based on your conclusions about unit testing I'll ask if you've worked with tSQLt as a formal test framework? Without getting into the details of your UDF or stored procedure implementations, the breakdown you've used does indeed support robust unit testing. Using tSQLt for outcome based testing (given X input of data I expect Y output of data) you can end up with a bank of both functional and metadata tests. That bank will help you perform regression testing much more easily.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
November 1, 2019 at 2:22 pm
Overall, I like what you did. The code snippets are small and manageable. The only downside to it is when it comes time to debug it.
What I've seen where I work is that the bug is found in the application (C#) and from debugging that you narrow it down to a single stored procedure (hopefully). Using your method, you then jump into the stored procedure to jump into a function to jump into 1 or more functions. SSMS (as far as I am aware) doesn't have nice native ability like C#'s "go to definition".
On the opposite spectrum, testing this is going to be a lot more simple and understanding the code is also a lot more simple. Reading a lengthy stored procedure (we have some 8000+ line stored procedures here at work) is VERY easy to misread things and miss potential bugs and unit testing an 8000+ line stored procedure is impossible. It also results in the code not being reused, or if it IS reused, it is copy-pasted to be reused which is not very efficient.
My only gripe with the code is the lack of comments. Having comments explaining what the code is doing and what the expected input and output are make it a lot easier to support and reuse. That being said, your code is short and sweet, so not positive that comments would help; it is just part of our coding standards. Much easier to have a rule of putting comments everywhere rather than have subjective rules like "comments only required for complex code". I've seen that before and what is complex to me may be simple to you. I know some developers really dislike the WITH syntax and would much rather use nested selects or store the results in temp tables or table variables. I am liking the function approach and I actually have a report (SSRS) that I am working on now that could benefit from this approach.
But after reading through the whole article, I think I am going to be more inclined to try to enforce your suggestions going forward here at work. The code becomes reusable which is always a HUGE plus and if/when a bug is found, fixing it will fix it everywhere the code function is used. Having copy-pasted code means you may (likely will) need to bug fix things in more than once place when a bug is found.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 31, 2020 at 10:48 am
Hi Guys
Nice work, however functions only allow read only actions. No UPDATES etc will work.
Pity that, Im tired of trawling through 200 lines of tangled stored proc let alone 8000.
August 3, 2020 at 8:07 am
Thanks for the feedback Brian.
I'm glad you used the article to consider your approach, as it was my intention to get people thinking about code structure rather than prescribing the perfect method.
Regarding comments in code, there are some that say you shouldn't need them if your code is structured and self-describing. Is this just an excuse? because the developer can't be bothered to help another developer or his future self understand his code?
And there are some that say thou shall make comments always. Forcing the developer to include comments that have questionable value. "This is an integer parameter named customerid"
Personally, I'm in the grey area between.
Regarding code dependency tracking, I use Visual Studio SSDT to navigate dependencies and search code. It's a superior experience over SSMS.
August 4, 2020 at 4:45 pm
My preference with code comments is all code functions (stored procedures, triggers, tables, views, etc on the SQL side, methods, classes and functions on the .NET side) should have a boilerplate. You don't need to have comments on each line of code, but as soon as you are doing something that is abnormal or that a new developer would be confused by OR that is against the defined coding standards, a comment should be in place. Like if you are using a SQL_Variant datatype, I want to know why you aren't using a proper datatype for that and it must be documented in the code. The exceptions to this rule are functions that are single line functions. In .NET these would be things like the getter and setter functions. On the SQL side, this would fall into functions that are extremely obvious as to what they do based on the name and the short piece of code that exists such as a function called "ToUnixTime" and "FromUnixTime" which you can tell are just converting back and forth to Unix time.
On the SQL side, my preference is to do boilerplates even on simple things. The main reason for this is for revision control. I know people will tell me that the revision control should be done inside a source control system and I don't disagree here. But when I am in "emergency" mode because the whole company is down due to a stored procedure change, I want to be able to see what was changed in a human readable format quickly to see if it IS due to the stored procedure or if some process changed around the same time and it is a process issue. The fewer places I need to dig to see what changed and why, the better. Source Control lets me roll it back, but if I see it was changed 2 weeks ago and the problem started this morning, it is obviously not related to the stored procedure change. But if it was the same date, I can see what was changed (like "changed calculation to use a parameter for the datetime instead of the current SQL datetime" and the problem is with some conversion on the datetime (dd/mm/yyyy vs mm/dd/yyyy for example), then I know the probelm is likely related to the stored procedure change).
As for visual studio vs ssms, that is a tough one. I see pros and cons to both. As a DBA, I spend more time in SSMS than Visual Studio. But as a developer (both SQL and .NET), I spend a lot of time in Visual studio as well. I like having different tools as it helps me (visually) to "switch" mindsets for developing. .NET coding is very different than SQL coding and having a visual cue as to which way I should be thinking is nice. I think if I did all my coding in Visual Studio, i would end up with a LOT of row-based operations being done in SQL and get stuck on the .NET side when I start coding up a loop and immediately start questioning why I am using the loop.
I think if I was strictly a SQL developer or strictly a .NET developer, then sticking in the correct mindset would be easy and the tools at my disposal would be trivial. I run into similar problems when I go from PowerShell to SQL or Bash to bat or even linux to windows... I need a visual cue to remind myself of what I am doing and why the command doesn't work. "cd.." on linux I always alias since I type that SOOOO often, but remembering "vim" vs "notepad" is a fun one when you load up the terminal. I find the more visual cues I can give myself (like the task bar being at the "top" on Linux and "bottom" on Windows) helps me keep things straight.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply