August 16, 2017 at 11:18 pm
Comments posted to this topic are about the item Testing COALESCE with tSQLt
August 17, 2017 at 10:06 am
Hi, Steve. Love your articles, please keep it up.
The FakeTable @SchemaName parameter is deprecated. If you view the source, you'll see
@SchemaName NVARCHAR(MAX) = NULL, --parameter preserved for backward compatibility. Do not use. Will be removed soon.
August 17, 2017 at 10:37 am
Frankly, while this is a nice write up, I am a bit confused on what exactly is tested here or what is the point of this story at all?
is this a write up of how you build a test in/for SQL?
is this a write up on how you test for things?
or how you test specific rules?
what is the point here? what is a real life value of this?
really want to know...
your first SELECT
select SELECT Salutation + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName + ' ' + SuffixFROM dbo.Contacts;
is not what an experienced SQL dev would ever use. not even what an knowledgeable any kind of developer would use to do anything but simply do a quick record count and/or to test if there are any "NULL"s in the data.
that is all it is useful for.
esentially do a quick and dirty count and NULL test on the table. also test and confirm the output formating rule. nothing more or less.
the second select with actual COALESCE function used is more to the point
SELECT COALESCE(Salutation + ' ', '') + COALESCE(firstname + ' ', '') + COALESCE(SUBSTRING(middlename, 1, 1) + '. ', '') + lastname + COALESCE(', ' + suffix, '') FROM dbo.Contacts;
yet still not 100% correct. it will work but it is not properly as it does not check , for example, if middlename contains a "." already before adding it.
Yes I have read the blog and you do mentioned the issue, but you do not explore the solution.
and I ,personally, do not see a value of this test if it can not be used on a real data.
I can write a procedure to implement the logic I want and test it on the small set of data very easily. without going through all this shenanigans. and faster too. IF I need to do it in SQL.
and I had done it in real life.
August 17, 2017 at 9:04 pm
larry.blake - Thursday, August 17, 2017 10:06 AMHi, Steve. Love your articles, please keep it up.The FakeTable @SchemaName parameter is deprecated. If you view the source, you'll see
@SchemaName NVARCHAR(MAX) = NULL, --parameter preserved for backward compatibility. Do not use. Will be removed soon.
Thanks, didn't see that as I haven't been on that doc page lately. Not sure how long it's been there, but the parameter certainly hasn't been removed yet.
August 17, 2017 at 9:15 pm
vl1969-734655 - Thursday, August 17, 2017 10:37 AMFrankly, while this is a nice write up, I am a bit confused on what exactly is tested here or what is the point of this story at all?
is this a write up of how you build a test in/for SQL?
is this a write up on how you test for things?
or how you test specific rules?what is the point here? what is a real life value of this?
really want to know...Yes I have read the blog and you do mentioned the issue, but you do not explore the solution.
and I ,personally, do not see a value of this test if it can not be used on a real data.
I can write a procedure to implement the logic I want and test it on the small set of data very easily. without going through all this shenanigans. and faster too. IF I need to do it in SQL.
and I had done it in real life.
First, developers certainly do write (select a + ' ' + b + ' ' ... ) all the time. It's not a great idea, and there are problems, but that code exists. Testing, and maintaining tests over time, help to show developers where their code has issues, and prevent regressions. The point here is to have repeatable testing.
The case chosen is for names, which are complex. There are many more rules that could be applied, but to keep this relatively simple, I just chose a few. I didn't cover every case, because this is a simplified model. You certainly could expand this to check for existing periods or other issues. In fact, rather than just complaining about this piece not meeting your needs, perhaps you'd extend this to include another case, or explain how you would deal with the existing period? Remove it and always put it back? Check and make a decision on adding it? There are multiple implementations that could work, and one is likely more efficient than others, but how do you know that the code you've written works?
You wrote "I can write a procedure and test it on a small set of data easily". Sure. You can. It's definitely going to be faster for you if you haven't built testing skills. However, I can assemble this test quickly. Most of the test would include the same sample data I want. However, as you noted, I didn't include a case with a middle name that has a period already. Would that be in your data set? Maybe.
One of the issues with much code is that developers are busy and they focus on the problem from their view, which is often a limited view. Very few of us have time to explore every possibility. If you deployed this code (not saying you would), and QA reported this shows that "Steve A. Jones" is returned as "Steve A.. Jones", you'd know there's a bug. You could modify your code and assemble another data set, or run queries, but as the list of inputs grows, are you positive you check every output row correctly? I would argue that you, and most developers, don't perform that diligence as well as they could because we make mistakes as humans.
With this test, I can easily add another row of data to the rest (both the input, and the expected output) and then just exec tsqlt.run my test and it will check not only the new case, but every previous one as well. If I add more complex logic for Van Hewitt or MacDonald as a last name or multiple middle names, I can prevent regressions by having a formal test.
Not only can I prevent regressions, but I also prevent future individuals from changing the code without having a test run at some point (manual, CI, etc.) that validates previous cases we know about.
It seems heavyweight, which is one reason I didn't try to cover every case or make this a 100% solution. However, once you start to write these tests, they are fairly easy to put together quickly. I often need the input test data anyway, and the formal test makes me spend a minute ensuring I explicitly document the result set I want, not relying on my focus to ensure I read a result set correctly every time.
August 18, 2017 at 6:15 am
Steve, I never said that code like " (select a + ' ' + b + ' ' ... )" is not used.
I said that an experienced developer, be that DBA, SQL dev or any other, would not use it with some exceptions.
infact, at my current job I still have apps with just such code in place.
Worst yet, we still have somone who write this kind of code to this day.
he is not a professional dev. he is a self taught SQL/dBase 2and 3 / C# coder.
codding is not primary job. just something he does because there is a need for it..
when I have to work with his code I fix it as much as I can, but some things will be there for a long time.
Now to answer your other points.
please understand that I was not trying to knock you down or belittle the overall value of your post as a teaching instrument.
it has it's uses and intrinsic value to it. however I have this personality fault that always looking on a long term usefulness of things not what it can do here and now but what are the future benefits might be.
I have been in the position where my company have come upon the test driven development (TDD) principals and tried to implement it company wide. it was a living nightmare. adding TDD API and software to the existing projects and make it work.
especially to project that where written using old school technique. not strictly OO standards and more.
at the end we had many projects where all tests that we build where all green and nice, BUT the app still had bugs up to wazoo.
what good of the tests did to the devs? Nothing. no good at all. and all devs agreed on that ,except that management had drink the kool-aid and pushed hard to get there.
that said, you do have a point, and if properly implemented , from the beginning , and if the overall development practices is properly managed , TDD has it moments. but the overhead might just kill you 🙂
August 22, 2017 at 10:45 am
Apology accepted, and I'll apologize as well. Your comment came off as short and argumentative, and I was tired. I'm sorry for that.
In terms of TDD and implementing this. I'm not a huge TDD fan, often because people write tests for a thing (x or y), but they may end up with code that's slightly orthogonal to the item. I prefer to actually write tests that look for the issues I get. TDD and tests don't prevent bugs in software. They ensure that software passes the tests. If the tests aren't relevant, or don't test the functionality, then you can still have bugs.
However, bugs in software are separate from testing. Testing can improve the quality of software, but you need to write the tests that look for the bugs. For example, I wouldn't necessarily test this:select sum (notnullcolumn_x) from mytable
Why? It's a simple function, and I believe SUM() works. If I need to handle NULLs somehow, I might, but overall, I can't worry about this not working. However, COALESCE is more complex, and because I could get it wrong, or forget cases, I would write the test. If I need to ensure a non-trivial, multi-row result set, a test is good. The example I chose doesn't prevent bugs (as you pointed out with the period case), but it does ensure that I pass the cases I've added. If I add additional cases, I can improve the test. If I change the code, my test at least ensures I don't create regressions.
Adding testing to a project may or may not help. Your developers need to learn how to write tests. If bugs still slip through, they aren't testing the things they should.
October 9, 2017 at 7:13 am
Hey there, just reading this article for the first time, and am a little confused by some syntax. Shouldn't this part:
EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
INSERT dbo.Fullname
be:
EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';?
INSERT dbo.Contacts
October 9, 2017 at 9:13 am
nadair - Monday, October 9, 2017 7:13 AMHey there, just reading this article for the first time, and am a little confused by some syntax. Shouldn't this part:
EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
INSERT dbo.Fullnamebe:
EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';?
INSERT dbo.Contacts
Yes, sorry. Must have created a typo during editing.
October 9, 2017 at 9:19 am
Steve Jones - SSC Editor - Monday, October 9, 2017 9:13 AMnadair - Monday, October 9, 2017 7:13 AMHey there, just reading this article for the first time, and am a little confused by some syntax. Shouldn't this part:
EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';
INSERT dbo.Fullnamebe:
EXEC tSQLt.FakeTable @TableName = N'Contacts', @SchemaName = N'dbo';?
INSERT dbo.ContactsYes, sorry. Must have created a typo during editing.
No worries! I thought I must just be missing something.
October 9, 2017 at 1:11 pm
nadair - Monday, October 9, 2017 9:19 AMNo worries! I thought I must just be missing something.
Well, I was definitely missing something.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply