August 19, 2019 at 4:28 pm
In my opinion unit testing in a relational database is basically fruitless. The whole idea is the data model is schema bound. So what are you testing but only to confirm the database works correctly?
Unit testing makes sense for Object Oriented code and server code because it's generally not schema bound. So when the code gets complicated major, major bugs are almost inevitable. At least that describes my C# programming level.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2019 at 4:42 pm
In my opinion unit testing in a relational database is basically fruitless. The whole idea is the data model is schema bound. So what are you testing but only to confirm the database works correctly?
Well, no. My guess is that you don't have much or any experience with stored procedures, functions, triggers, constraints or any other SQL code. All these things should be tested in some way to ensure they work properly. Whether that is unit tests or some other method of testing is debatable. You may also want to test your data model to ensure you've correctly limited what can be stored. For instance, a US phone number has certain characteristics, and if what someone is entering doesn't match that criteria, it probably shouldn't be allowed in. Many non-SQL developers think of a database as only a thing to store stuff; it's a lot more than that.
August 19, 2019 at 4:43 pm
David
the non-SQL parts of our C# app are tested with a few unit tests and a lot of manual tests.
We plan to reverse the quantity (unit testing first and manual testing for high value scenarios).
We have no strategy for the SQL part. That's why I left my post here.
Thank you for sharing your vision.
Arnaud
This is my point. Why even bother testing sql? Of course it works.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2019 at 4:49 pm
In my opinion unit testing in a relational database is basically fruitless. The whole idea is the data model is schema bound. So what are you testing but only to confirm the database works correctly?
Well, no. My guess is that you don't have much or any experience with stored procedures, functions, triggers, constraints or any other SQL code. All these things should be tested in some way to ensure they work properly. Whether that is unit tests or some other method of testing is debatable. You may also want to test your data model to ensure you've correctly limited what can be stored. For instance, a US phone number has certain characteristics, and if what someone is entering doesn't match that criteria, it probably shouldn't be allowed in. Many non-SQL developers think of a database as only a thing to store stuff; it's a lot more than that.
Sorry I didn't mean to write Sql and not make sure that it works. The first time you run it it never works. Of course you have to make sure it works before it's pushed to stage or production. However, once something is properly modeled and works it should continue to do so without any unit testing necessary.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2019 at 4:53 pm
coolman02 wrote:David
the non-SQL parts of our C# app are tested with a few unit tests and a lot of manual tests.
We plan to reverse the quantity (unit testing first and manual testing for high value scenarios).
We have no strategy for the SQL part. That's why I left my post here.
Thank you for sharing your vision.
Arnaud
This is my point. Why even bother testing sql? Of course it works.
So, you don't test that your query returns the expected result? It just magically returns exactly what you want? Or, are you calling a stored procedure that does? Hopefully somewhere in the chain of events, someone has made some effort of testing that you would get the results you expect.
August 19, 2019 at 4:58 pm
chris geswein wrote:In my opinion unit testing in a relational database is basically fruitless. The whole idea is the data model is schema bound. So what are you testing but only to confirm the database works correctly?
Well, no. My guess is that you don't have much or any experience with stored procedures, functions, triggers, constraints or any other SQL code. All these things should be tested in some way to ensure they work properly. Whether that is unit tests or some other method of testing is debatable. You may also want to test your data model to ensure you've correctly limited what can be stored. For instance, a US phone number has certain characteristics, and if what someone is entering doesn't match that criteria, it probably shouldn't be allowed in. Many non-SQL developers think of a database as only a thing to store stuff; it's a lot more than that.
Sorry I didn't mean to write Sql and not make sure that it works. The first time you run it it never works. Of course you have to make sure it works before it's pushed to stage or production. However, once something is properly modeled and works it should continue to do so without any unit testing necessary.
Ah, ok. So the reason you may want to keep unit tests is if anything needs to change with your data model, you've already documented how things should work and have things that break if you make a mistake while making those changes. If it is the functionality itself that needs to change, you know that your test has to reflect that new functionality and you may remove some of those unit tests that no longer apply. It's just a first step to make sure you're doing the things the way they were intended and don't break things unintentionally. It helps future you as well as others who may need to make changes.
August 19, 2019 at 5:24 pm
Ah, ok. So the reason you may want to keep unit tests is if anything needs to change with your data model, you've already documented how things should work and have things that break if you make a mistake while making those changes. If it is the functionality itself that needs to change, you know that your test has to reflect that new functionality and you may remove some of those unit tests that no longer apply. It's just a first step to make sure you're doing the things the way they were intended and don't break things unintentionally. It helps future you as well as others who may need to make changes.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2019 at 5:40 pm
What percentage of the total number of ways in which your data model could possibly be interacted with do you think your tests represent? If you have more than a few dozen objects in the database then the percent probably rounds down to 0%. Do you disagree?
Yes I would 100% disagree with that, every object in the database does not interact with every other object in the DB in some exponentially spirally level of complexity. For example if I have a SP that only uses a handful of columns out of 1 table it doesn't matter if the DB only has that one table or 10000 tables, the complexity of that SP stays the same and can easily be unit tested.
August 19, 2019 at 5:44 pm
Yes I would 100% disagree with that, every object in the database does not interact with every other object in the DB in some exponentially spirally level of complexity. For example if I have a SP that only uses a handful of columns out of 1 table it doesn't matter if the DB only has that one table or 10000 tables, the complexity of that SP stays the same.
If the complexity stays the same then why unit test it? Is it because the database works properly?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2019 at 5:47 pm
ZZartin wrote:Yes I would 100% disagree with that, every object in the database does not interact with every other object in the DB in some exponentially spirally level of complexity. For example if I have a SP that only uses a handful of columns out of 1 table it doesn't matter if the DB only has that one table or 10000 tables, the complexity of that SP stays the same.
If the complexity stays the same then why unit test it? Is it because the database works properly?
The same reason you unit test anything? It's either the new development or it's something you're modifying.
August 19, 2019 at 6:00 pm
Ah, ok. So the reason you may want to keep unit tests is if anything needs to change with your data model, you've already documented how things should work and have things that break if you make a mistake while making those changes. If it is the functionality itself that needs to change, you know that your test has to reflect that new functionality and you may remove some of those unit tests that no longer apply. It's just a first step to make sure you're doing the things the way they were intended and don't break things unintentionally. It helps future you as well as others who may need to make changes.
<li style="list-style-type: none;">
- What percentage of the total number of ways in which your data model could possibly be interacted with do you think your tests represent? If you have more than a few dozen objects in the database then the percent probably rounds down to 0%. Do you disagree?
<li style="list-style-type: none;">
- Suppose you want to write perfect tests. Tests to test every possible way to interact with your data model. How would you do it? You would query the schema. Do you see the circle yet?
You may want to take a look at the documentation for tSQLt. It may give you a better understanding of what things you may put in a unit test. My guess is that you're a C# developer exclusively and are not aware of the things you may want/need to test in the data model.
August 19, 2019 at 6:07 pm
[
The same reason you unit test anything? It's either the new development or it's something you're modifying.
New development and modifications should be tested over and over until they work as expected. Yes I test code until it works. Are you talking about a formal unit test framework or just making sure your code works before it's pushed? A "unit testing framework" is a set of rules and processes that formally assert a certain outcome is expected for a given input. Following a unit framework means maintaining the list of assertions going forward and applying the tests (all prior tests, cumulatively) in your ci/cd build process before pushing code changes. To my way to thinking it makes a lot more sense to look for anomalies if/when they happen, then it does to maintain a list of assertions and dream you're keeping Sql Server from reverting to making mistakes long since solved. That's Microsoft's job.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 19, 2019 at 6:19 pm
[
The same reason you unit test anything? It's either the new development or it's something you're modifying.
New development and modifications should be tested over and over until they work as expected. Yes I test code until it works. Are you talking about a formal unit test framework or just making sure your code works before it's pushed? A "unit testing framework" is a set of rules and processes that formally assert a certain outcome is expected for a given input. Following a unit framework means maintaining the list of assertions going forward and applying the tests (all prior tests, cumulatively) in your ci/cd build process before pushing code changes. To my way to thinking it makes a lot more sense to look for anomalies if/when they happen, then it does to maintain a list of assertions and dream you're keeping Sql Server from reverting to making mistakes long since solved. That's Microsoft's job.
The point of unit testing is not to validate that the basic functionality of SQL Server is working, as you noted we just have to trust MS on that one. And while your testing process should certainly include full testing of all the components including the database elements unit testing is supposed to catch potential issues before full end to end testing happens and generally smooth over as many issues as early on as possible.
I'm not sure why you think that is impossible or not worth the effort to do with database objects.
August 19, 2019 at 6:22 pm
[
The same reason you unit test anything? It's either the new development or it's something you're modifying.
New development and modifications should be tested over and over until they work as expected. Yes I test code until it works. Are you talking about a formal unit test framework or just making sure your code works before it's pushed? A "unit testing framework" is a set of rules and processes that formally assert a certain outcome is expected for a given input. Following a unit framework means maintaining the list of assertions going forward and applying the tests (all prior tests, cumulatively) in your ci/cd build process before pushing code changes. To my way to thinking it makes a lot more sense to look for anomalies if/when they happen, then it does to maintain a list of assertions and dream you're keeping Sql Server from reverting to making mistakes long since solved. That's Microsoft's job.
Take a look at tSQLt. It's a formal unit testing framework for TSQL, the sql language used by SQL Server. This isn't about keeping SQL Server from reverting. It's not Microsoft's job to test your sql objects to make sure they work and to keep others from making changes that may go against the original intentions of those objects. I'm not sure you understand the topic.
August 19, 2019 at 6:25 pm
<li style="list-style-type: none;">
- Yes, I disagree. Based on your comments, I think you don't understand relational databases, or unit tests, or both.
<li style="list-style-type: none;">
- See number 1.
You may want to take a look at the documentation for tSQLt. It may give you a better understanding of what things you may put in a unit test. My guess is that you're a C# developer exclusively and are not aware of the things you may want/need to test in the data model.
You're big on the ad hominem attacks. I like that about you. It's actually the other way around. I'm a longtime sql user now learning/using C# for the past few years. It's love/hate and my sql-centric mentality is definitely no help.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 61 through 75 (of 86 total)
You must be logged in to reply to this topic. Login to reply