January 29, 2022 at 1:42 am
Comments posted to this topic are about the item Model Good Behavior
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 29, 2022 at 7:04 am
I totally agree, Grant.
I'm looking at introducing "software engineering" as a topic to discuss at work with the data engineers, and introduce them to all of those concepts like encapsulation, modular design, data hiding, low coupling, high cohesion... it's like all of this goes straight out of the window when people are writing code for a database. From my experience, the database platform doesn't matter, it's the fact that the code resides on a database is what seems to trigger this regression.
So yes, a pattern of teaching people poor coding habits is a problem. The way to resolve it is, I believe, to teach them good coding practice. Expose people to excellent code, and excellent coding practices; show, don't tell.
January 29, 2022 at 9:14 am
But isn't a semi colon required I the CTE is in a stored procedure?
January 29, 2022 at 1:50 pm
But isn't a semi colon required I the CTE is in a stored procedure?
the point is to end the previous statement with a semi colon
and showing examples of CTE's not as ";with .." but straightforward "with ...", as you should be able to do that because you nicely implemented a best practice doing so.
It's always the little things that make a day.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 29, 2022 at 2:11 pm
I totally agree, Grant.
I'm looking at introducing "software engineering" as a topic to discuss at work with the data engineers, and introduce them to all of those concepts like encapsulation, modular design, data hiding, low coupling, high cohesion... it's like all of this goes straight out of the window when people are writing code for a database. From my experience, the database platform doesn't matter, it's the fact that the code resides on a database is what seems to trigger this regression.
So yes, a pattern of teaching people poor coding habits is a problem. The way to resolve it is, I believe, to teach them good coding practice. Expose people to excellent code, and excellent coding practices; show, don't tell.
Where in or in which of the book(s) of "software engineering" does it explain where type conversions are necessary and desirable? Isn't that what "data access" is really all about? It's a grand type conversion necessitated by the lack of a common interface. Re SQL Server imo there are two common interfaces available but which don't get used as they might be or so it seems. JSON and XML. If you ask proceduralists to follow "best practices" imo they'll soon be evaluating NoSQL solutions. Why? Because from their perspective SQL is nothing but a headache. The more they use their tools to solve the "data access" problem the worse it gets for them. Why? Because it's code written from the wrong perspective to solve a "problem" which exists because of the lack of a common interface
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 29, 2022 at 2:27 pm
Grant, I heartily agree with your feelings regarding responsibility for creating and maintaining good code. Unlike a good bottle of scotch, code does NOT get better with age. Throughout my working years I was fortunate to have received mentoring from some quite talented folks and tried to do my best a being a mentor to others. And on the other hand, also knew some far less talented developers.
My last position before becoming a DBA was even titled SQL Developer, and that entailed both database design and development of hundreds of SQL stored procedures for a large multi-year multi-application development effort. We actually had a separate Testing and Quality Control group even with their own server environments for validation of code before release. On the other hand we did not often have actual code reviews among developers and DBAs
While the QC folks were not highly experienced in development, they did provide an independent environment for putting code through its paces in something nearer to the real world that our users experienced.
In spite of all this care in the process, we found that in fact most of the responsibility did still remain with the DBA's and SQL Developers.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
January 29, 2022 at 5:24 pm
Speaking of modeling good behavior... Shouldn't we be pointing the finger at Microsoft for choosing the word WITH to start a CTE.
January 29, 2022 at 5:48 pm
So - we all say that using SELECT * is a 'bad' example. However - there are many places where it can be used that is not only safe but should be the recommendation.
For example - EXISTS. Too many times I have seen SELECT 1, SELECT 2, SELECT 3, etc... in a single stored procedure just to avoid using SELECT * and absolutely no idea why the developer chose to change the value for each statement. In fact, the developers only reason for avoiding SELECT * is because they were told it is a bad practice.
And how about SELECT * in CTE's themselves? OUTER/CROSS APPLY - derived tables, etc. If using '*' here will it actually cause issues when the underlying schema changes?
Just saying don't use SELECT * is no longer good enough. I think it needs much better explanation as to why it should be avoided - and where exactly it should be avoided. You know, that whole thing about teaching...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 29, 2022 at 6:00 pm
Totally agree with you Grant.
Also think this is due to SQL syntax, not forcing you to add a termination character at the end of previous line and errors only when using 'with'. Enforcing rules some of the time causes ambiguity and teaching/learning also becomes harder.
January 29, 2022 at 10:27 pm
Examples of CTE's with semicolons has always bothered me. I started out in Oracle and the semi-colon is required and it only took about a year to finally include it in the first attempt at DML, procedures and functions. That instilled the habit in my coding and I have always used the semi-colon on every statement even when using SQL Server. I don't think people understand the GO statement any better and abuse that as well.
I totally agree, using good coding style in examples is often over looked or left out in an effort to quickly get an example to answer a poster's question. The syntax being explained is often very good but the prep code of creating objects and populating data or additional statements in the procedure or function are really bad. And even though someone may be researching how to do one thing, they also notice all the other code to understand what is going on and pick up some bad habits from that as well. Most examples I have seen of stored procedures never use TRY/CATCH blocks. Their use is debated frequently and I don't want to do that here, but if you use them in your code it would be a good idea to always remind people that this is an example and you would normally include try catch blocks in production code. Or maybe your formatting is bad (like 10 statements separated by semi-colons on one line) but state that it is advisable in production code to follow formatting standards.
I believe that most people don't fully understand why the CTE was created. Why is it called Common Table Expression? Does that mean it is in common use and everyone is using them? No, it was originally designed to encapsulate a common select statement that may be used in multiple places in a single select statement in the FROM, WHERE and GROUP BY clause and possibly the ORDER BY clause with complex analytic functions. The new windowing functions have replaced many places where a CTE is needed.
A good practice for using the CTE is to keep from cluttering up the FROM clause. If it is used one time in the FROM clause it could just as easily be left in line in the FROM clause but often the CTE keeps it cleaner.
January 30, 2022 at 4:49 pm
Hey Grant, thank you very much for saying that semi-colon isn't necessary when beginning to declare a CTE!! I didn't know that. Honestly, I thought that the semi-colon was necessary! I thought it was a part of the syntax for starting the declaration of a CTE! I thought that, because that is exactly how it was introduced to me, but at work and the examples I'd see online. So, now I understand that a CTE is started simply by using the WITH keyword.
Starting now, I'll stop using that semi-colon. Unless whoever wrote the code didn't end the previous statement with a semi-colon.
Kindest Regards, Rod Connect with me on LinkedIn.
January 31, 2022 at 2:13 pm
But isn't a semi colon required I the CTE is in a stored procedure?
100%. Absolutely. The semi colon is a statement terminator. Meaning, the previous statement has to have it before the CTE definition (there are a couple of other requirements for this too). Instead of using statement terminators, some people just put it before the WITH clause. Online documentation does it a lot, on the assumption that people aren't using the terminator. That leads to people trying to put it in front of every WITH statement as if it was a requirement that it goes there, not terminating the previous line, which is the case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 31, 2022 at 2:16 pm
Hey all, a bit rushed this AM. Thanks for all the feedback. Appreciate.
And yeah, fully teaching the why's & how-fors & 'causes around stuff like SELECT * or ;WITH or whatever, yeah, exactly, we need to do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 31, 2022 at 4:31 pm
We need to think about why someone (admittedly, like myself) would make a habit of prepending the WITH statement of a CTE with a semi-colon. For me it's because I like that Microsoft doesn't require every statement to have a semi-colon terminator. Since I like that it's not required I'm not keen on the idea of adding them to the end every statement just to satisfy the few cases where it's needed. Therefore when I encounter syntax that Microsoft couldn't or wouldn't avoid the necessity like a CTE definition or a THROW statement etc. I add the semi-colon to be included with the code that needs it.
We could say, "Well since a MERGE statement must be semi-colon terminated and a CTE must be the first statement or the previous statement must be terminated, we should now terminate every statement to avoid issues in these cases where it is required." That is a valid reason for making that the standard. Such a standard would go against my personal preferences since I don't like the statement terminator nor do I like languages that require it (I'm sneering at you Oracle). However, that's not a good enough reason to ditch a standard.
We need to be careful here though. That same logic leads us to other conclusions. Take for instance the optional syntax of enclosing identifiers in brackets "[]" (tables, columns, logins etc.). Since there are plenty of cases where brackets are required in order to keep things from breaking it's easy to conclude that we should make it standard to use brackets where ever the option is afforded. That's another topic that has very passionate and differing opinions.
IMO, if your coding standards require you to use semi-colon statement termination keep doing that at the end of statements and this is a non-issue for you. If your coding standards do not require statement termination put the semi-colon with the statements that require it. Either in the front, like in a CTE, or at the end, like in a MERGE.
-
January 31, 2022 at 7:03 pm
So...this doesn't work...where does the semicolon go? I'd put it after the END myself, but it looks weird. Is "END" supposed to be terminated? I rarely do that, shoudl I start? Or do we terminate it only in a case where the following statement requires it? Or do we put the semicolon in front of the "WITH" so that SQL Server doesn't bark about it? I'm wondering if it's the latter situation that might have originally driven this, and people saw it, didn't understand the "why" and just copy-pasted it everywhere.
IF 1 = 0
BEGIN
PRINT 'Oops, something is wrong with the universe.';
END
WITH SomeCTE AS
(
SELECt 1 AS Number1
)
SELECT Number1 FROM SomeCTE
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply