SQL (Transact-SQL) needs a proper FOR loop!
-
if your condition is never met (or not met "foo" times), your loop control will never increment (or not increment enough to break on the condition), thus the loop will be infinite.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
I think its a bad idea. SQL is the wrong place to perform that type of logic. If you can't perform it in a set type of operation, then move it out of SQL. Working with this limitation has made my SQL skills stronger and encouraged me to think differently about modifying data. I get that there are exceptions and if you have to do it in SQL, it already has cursors.
Hogan
-
I think its a bad idea. SQL is the wrong place to perform that type of logic. If you can't perform it in a set type of operation, then move it out of SQL. Working with this limitation has made my SQL skills stronger and encouraged me to think differently about modifying data. I get that there are exceptions and if you have to do it in SQL, it already has cursors.
Hogan
Absolutely. I'm a huge proponent of not doing the kinds of thing I'm doing at the moment in SQL*, but I'd rather have it for when it makes some sense.
snorkie wrote:
think differently about modifying data
Which isn't what I'm doing in this particular case.
snorkie wrote:
it already has cursors
Certainly, but still not applicable to this particular case. * This is just a quick-and-dirty little investigation into some data analysis. I might re-write these functions in C#, but it's really not worth the effort.
-
I worked at a company who preferred to do as much as possible in SQL Server. A stored procedure, as they claimed, is easy to change in production and you can write business logic in the one place where every user action ultimately ends up in: the database. The client applications were all WinForms. Of course, all of this is a fallacy. Yes, it's easy to change a view or stored procedure in production (we could make the change and hit F5), but the question is why wouldn't software be easy to update? This was about 10 to 14 years ago, but this company did not have DevOps practices and every deployment was manual work, which also required users to restart the software (which was of course pretty common at the time). That last part is a nuisance which really can't be helped at this point (unless you use stored procedures), but having a difficult and manual deployment process can be helped and should be helped. Which would remove a part of the need for stored procedures and logic in your database. The second reason, having everything in one place, can of course be solved by using services in a server-client, service-oriented and/or microservices architecture. And when you have services doing all the logic, you don't have to restart client applications either. Now, instead of using stored procedures and views you can use a service instead. A service is easy to debug, easy to put in source control and easy to automatically deploy, unlike SQL code. And you never have the risk of updating the wrong thing either (which happened from time to time) because you're simply not messing around in production environment "on the fly". So, I've made a choice to let a database do what it does best, store data, and keep it as "dumb" as possible. Everything else is handled by applications and services :)
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
I worked at a company who preferred to do as much as possible in SQL Server. A stored procedure, as they claimed, is easy to change in production and you can write business logic in the one place where every user action ultimately ends up in: the database. The client applications were all WinForms. Of course, all of this is a fallacy. Yes, it's easy to change a view or stored procedure in production (we could make the change and hit F5), but the question is why wouldn't software be easy to update? This was about 10 to 14 years ago, but this company did not have DevOps practices and every deployment was manual work, which also required users to restart the software (which was of course pretty common at the time). That last part is a nuisance which really can't be helped at this point (unless you use stored procedures), but having a difficult and manual deployment process can be helped and should be helped. Which would remove a part of the need for stored procedures and logic in your database. The second reason, having everything in one place, can of course be solved by using services in a server-client, service-oriented and/or microservices architecture. And when you have services doing all the logic, you don't have to restart client applications either. Now, instead of using stored procedures and views you can use a service instead. A service is easy to debug, easy to put in source control and easy to automatically deploy, unlike SQL code. And you never have the risk of updating the wrong thing either (which happened from time to time) because you're simply not messing around in production environment "on the fly". So, I've made a choice to let a database do what it does best, store data, and keep it as "dumb" as possible. Everything else is handled by applications and services :)
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
Sander Rossel wrote:
easy to change in production
That's my main argument against putting code in the database. Auditing what code is where becomes difficult. I've had to write systems for comparing procedures and functions between environments to ensure that what we thought was in production actually was. It would also be true of other systems where you don't deploy compiled executables -- e.g. Python.
Sander Rossel wrote:
a choice to let a database do what it does best, store data, and keep it as "dumb" as possible.
Totally agree. But, I still argue for having tools. What I'm working on is just an idle curiosity.
-
I worked at a company who preferred to do as much as possible in SQL Server. A stored procedure, as they claimed, is easy to change in production and you can write business logic in the one place where every user action ultimately ends up in: the database. The client applications were all WinForms. Of course, all of this is a fallacy. Yes, it's easy to change a view or stored procedure in production (we could make the change and hit F5), but the question is why wouldn't software be easy to update? This was about 10 to 14 years ago, but this company did not have DevOps practices and every deployment was manual work, which also required users to restart the software (which was of course pretty common at the time). That last part is a nuisance which really can't be helped at this point (unless you use stored procedures), but having a difficult and manual deployment process can be helped and should be helped. Which would remove a part of the need for stored procedures and logic in your database. The second reason, having everything in one place, can of course be solved by using services in a server-client, service-oriented and/or microservices architecture. And when you have services doing all the logic, you don't have to restart client applications either. Now, instead of using stored procedures and views you can use a service instead. A service is easy to debug, easy to put in source control and easy to automatically deploy, unlike SQL code. And you never have the risk of updating the wrong thing either (which happened from time to time) because you're simply not messing around in production environment "on the fly". So, I've made a choice to let a database do what it does best, store data, and keep it as "dumb" as possible. Everything else is handled by applications and services :)
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
Like with so much, I think it's a bit more of an "it depends". Database engines are very good at retrieving complex indexed data structures quickly. I don't know if there are any tests out there but I reckon for very large datasets, with multiple joins, a database engine probably still beats using something like Entity Framework - happy to be shown to be wrong on this. So I think for small simple datasets - yes, placing all the logic inside the service probably does make sense.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Like with so much, I think it's a bit more of an "it depends". Database engines are very good at retrieving complex indexed data structures quickly. I don't know if there are any tests out there but I reckon for very large datasets, with multiple joins, a database engine probably still beats using something like Entity Framework - happy to be shown to be wrong on this. So I think for small simple datasets - yes, placing all the logic inside the service probably does make sense.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
Yeah, but EF can just as well generate queries and let the database handle the retrieval. But you're right, in some cases writing a stored procedure could be more performant. Although EF has been getting better in that regard (like there are built-in DeleteAsync and UpdateAsync methods).
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
It's kind of a nasty alternative, admittedly, but I'm used to it because of a c pattern I employ all the time
while(count--) { ... }
I think because I'm so used to dealing with that I spotted your bug right away. These days I prefer to generate SQL, DML and DDL code because it's so sloppy, like JS. Really it should be transpiled from something more modern and cohesive.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
-
It's kind of a nasty alternative, admittedly, but I'm used to it because of a c pattern I employ all the time
while(count--) { ... }
I think because I'm so used to dealing with that I spotted your bug right away. These days I prefer to generate SQL, DML and DDL code because it's so sloppy, like JS. Really it should be transpiled from something more modern and cohesive.
Check out my IoT graphics library here: https://honeythecodewitch.com/gfx And my IoT UI/User Experience library here: https://honeythecodewitch.com/uix
honey the codewitch wrote:
It's kind of a nasty alternative, admittedly, but I'm used to it because of a c pattern I employ all the time C++ while(count--) { ... }
Or, for a slightly more readable (but slightly less efficient) code construct, you can use the fake 'down to' / 'tends to' operator
-->. It works in C, C#, JavaScript and other C derived languages but it does not actually exist as an operator. e.g.while (count --> 0) { ... }
as it is just an unusually laid out version of
while ((count--) > 0) { ... }
and the test for
> 0is redundant as when count is positive the result is already true and when 0 it is already false. One useful feature of the-->operator is that it allows counting down to non-zero endpoints as well as just down to zero. -
Sander Rossel wrote:
easy to change in production
That's my main argument against putting code in the database. Auditing what code is where becomes difficult. I've had to write systems for comparing procedures and functions between environments to ensure that what we thought was in production actually was. It would also be true of other systems where you don't deploy compiled executables -- e.g. Python.
Sander Rossel wrote:
a choice to let a database do what it does best, store data, and keep it as "dumb" as possible.
Totally agree. But, I still argue for having tools. What I'm working on is just an idle curiosity.
PIEBALDconsult wrote:
comparing procedures and functions between environments to ensure that what we thought was in production actually was.
Been there, done that :laugh:
Best, Sander Azure DevOps Succinctly (free eBook) Azure Serverless Succinctly (free eBook) Migrating Apps to the Cloud with Azure arrgh.js - Bringing LINQ to JavaScript
-
Like with so much, I think it's a bit more of an "it depends". Database engines are very good at retrieving complex indexed data structures quickly. I don't know if there are any tests out there but I reckon for very large datasets, with multiple joins, a database engine probably still beats using something like Entity Framework - happy to be shown to be wrong on this. So I think for small simple datasets - yes, placing all the logic inside the service probably does make sense.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
I don't know if there are any tests out there but I reckon for very large datasets, with multiple joins, a database engine probably still beats using something like Entity Framework
Not a standard test by any means, but definitely proven in my applications where I've used stored procedures as against queries within the software.
Happiness will never come to those who fail to appreciate what they already have. -Anon And those who were seen dancing were thought to be insane by those who could not hear the music. -Frederick Nietzsche
-
I think its a bad idea. SQL is the wrong place to perform that type of logic. If you can't perform it in a set type of operation, then move it out of SQL. Working with this limitation has made my SQL skills stronger and encouraged me to think differently about modifying data. I get that there are exceptions and if you have to do it in SQL, it already has cursors.
Hogan
Disagree a bit, and do not care a lick that it defies popular wisdom. Working with input/output as close to the source/destination tends to mean both less traffic and faster. I feel this 'wisdom' is somewhat rooted in the same vein as folk who love NoSQL because it means they don't have to learn databases.
