Bless SQL
-
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
-
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
You just uncovered a deep truth. But shush, there are those who would burn you at the stake for casting light on the shadow of the arcane arts! .... :rolleyes: :-O
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
-
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
Do. Not. Do. String manipulation. In. SQL. Having to do so is a sign that you have done something wrong upstream. You know this.
-
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
I don't know SQL but loved your rant nevertheless. And a :rose: too. :)
Robust Services Core | Software Techniques for Lemmings | Articles
The fox knows many things, but the hedgehog knows one big thing. -
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
-
Do. Not. Do. String manipulation. In. SQL. Having to do so is a sign that you have done something wrong upstream. You know this.
There's a very legitimate use case for it when you want to validate denormalized string fields like phone numbers if they're not stored numerically just as an example - in practice databases are messy in the real world often with fields that are denormalized lists of ints, json data, xml, etc. Doing string manipulation in a database function or stored procedure allows you to reject data that comes in in an invalid format before it gets into the database. Particularly these days, it's popular to store denormalized JSON in the database, for better or worse. With Norm and Reggie, you can target parsing code to SQL that will allow you to validate or normalize your string data at the database level. The reason is not for performance, but for security and data integrity. You wouldn't want to run something like this as part of a query. You primarily use it on data update where performance can be less of an issue depending on the scenario.
Real programmers use butterflies
-
I don't know SQL but loved your rant nevertheless. And a :rose: too. :)
Robust Services Core | Software Techniques for Lemmings | Articles
The fox knows many things, but the hedgehog knows one big thing.Greg Utas wrote:
I don't know SQL
You're a fortunate soul. I've worn many hats in my development lifetime. "DBA" was one of my least favorite.
Real programmers use butterflies
-
I think you just outed where in the US you're originally from with that title. Ssshhh, don't leak our secret :laugh:
If you're gauging based on the title, I'm probably not where you think I'm from but it means what you think it does. I also use various britishism's like "whilst" I steal vernacular from wherever. I'm a thief like that. :laugh:
Real programmers use butterflies
-
You just uncovered a deep truth. But shush, there are those who would burn you at the stake for casting light on the shadow of the arcane arts! .... :rolleyes: :-O
A new .NET Serializer All in one Menu-Ribbon Bar Taking over the world since 1371!
My next article will give SQL people fits, I'm sure.
Real programmers use butterflies
-
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
It's a shame you're targeting MS SQL, it is weak in many areas compared to other SQL offerings. Postgres supports regex natively, as well as having a bunch more capability with string manipulation functions. Is there an option for you to use .Net assemblies in MS SQL, or perhaps the R or Python environments? It seems MS is ignoring extending the base SQL capability, but offering these extensions. Regardless, kudos for the tenacity required to do what your doing. How are you finding the performance of the regex proc's in SQL? Also, remember SQL is designed way back, before syntax highlighting/intellisense and many other modern expectations that would influence its design were around. It's time to create SQLNext, and I've seen the odd concept put forward, but it would be really hard to shift the masses now. Perhaps it'd best be done with a translation from SQLNext to the various SQL variants. But ideally would have to exist closer to the database than the client application and eventually become a native database feature. Unfortunately, would be easiest to place in the client application - I'd hate to get into any DB Driver level stuff. Remember ODBC - yuck.
-
It's a shame you're targeting MS SQL, it is weak in many areas compared to other SQL offerings. Postgres supports regex natively, as well as having a bunch more capability with string manipulation functions. Is there an option for you to use .Net assemblies in MS SQL, or perhaps the R or Python environments? It seems MS is ignoring extending the base SQL capability, but offering these extensions. Regardless, kudos for the tenacity required to do what your doing. How are you finding the performance of the regex proc's in SQL? Also, remember SQL is designed way back, before syntax highlighting/intellisense and many other modern expectations that would influence its design were around. It's time to create SQLNext, and I've seen the odd concept put forward, but it would be really hard to shift the masses now. Perhaps it'd best be done with a translation from SQLNext to the various SQL variants. But ideally would have to exist closer to the database than the client application and eventually become a native database feature. Unfortunately, would be easiest to place in the client application - I'd hate to get into any DB Driver level stuff. Remember ODBC - yuck.
I intend to target others. I had to start somewhere, and I already had MSSQL, it's fairly popular, I'm familiar with it, and its many limitations, which actually made it a good place to start. After all, I stuck as close to the SQL spec as a could, so I can port it to other databases, and I will be adding some sort of "flavor" switch to the app to specify which SQL to target. It's impossible to target SQL92 for this (i think) or I would.
Real programmers use butterflies
-
It's a shame you're targeting MS SQL, it is weak in many areas compared to other SQL offerings. Postgres supports regex natively, as well as having a bunch more capability with string manipulation functions. Is there an option for you to use .Net assemblies in MS SQL, or perhaps the R or Python environments? It seems MS is ignoring extending the base SQL capability, but offering these extensions. Regardless, kudos for the tenacity required to do what your doing. How are you finding the performance of the regex proc's in SQL? Also, remember SQL is designed way back, before syntax highlighting/intellisense and many other modern expectations that would influence its design were around. It's time to create SQLNext, and I've seen the odd concept put forward, but it would be really hard to shift the masses now. Perhaps it'd best be done with a translation from SQLNext to the various SQL variants. But ideally would have to exist closer to the database than the client application and eventually become a native database feature. Unfortunately, would be easiest to place in the client application - I'd hate to get into any DB Driver level stuff. Remember ODBC - yuck.
I didn't even respond to the rest of your questions! Whoops, let me try again. I haven't tested the performance specifically. I wasn't impressed with the execution plan for the table version though. The compiled ones should perform "okay" once MSSQL gets done putting them into native code, but it won't be near as fast as outside of a DB. I wouldn't use .NET's engine because it's not available everywhere. With Reggie you write the spec once, and it runs identically across any supported target. That's important for business schemas or even just application behavior consistency, if you're doing like, triple tier validation for example. Also I don't think you can use the CLR in the database without turning it on explicitly, and I could see a lot of people (if not most) leaving it off. The takeaway here is Reggie will probably run where a .NET solution wouldn't, and it won't perform as well in the DB, unless you take the C# code reggie generates and run *that* in the database, which i suppose is doable as well. So for a DB, this is best used for validation during updates that aren't intended to be super fast, full metal writes, though you can probably use this modestly in such a scenario for normalizing snatches of JSON and such. I think I may solve my SQL problems with having structured chunks of sql that can render to multiple targets (like "advance the input position and move along a transition, with capture") I've never heard of SQLNext.
Real programmers use butterflies
-
There's a very legitimate use case for it when you want to validate denormalized string fields like phone numbers if they're not stored numerically just as an example - in practice databases are messy in the real world often with fields that are denormalized lists of ints, json data, xml, etc. Doing string manipulation in a database function or stored procedure allows you to reject data that comes in in an invalid format before it gets into the database. Particularly these days, it's popular to store denormalized JSON in the database, for better or worse. With Norm and Reggie, you can target parsing code to SQL that will allow you to validate or normalize your string data at the database level. The reason is not for performance, but for security and data integrity. You wouldn't want to run something like this as part of a query. You primarily use it on data update where performance can be less of an issue depending on the scenario.
Real programmers use butterflies
But you should do that up-front, before it hits the database. Or, more likely, when populating a landing/staging table. With what I'm doing now (mostly ETL), we land the data and then go through doing stuff like that, making IP addresses binary, splitting FQDNs into their parts, checking dates, etc. One of the things I challenge you to do in SQL is take big-endian DNS names and reformat them as little-endian FQDNs. Examples: .com.acme.southwest.anvils -- anvils.southwest.acme.com. .com.widgetco.sales -- sales.widgetco.com. This is something I need to do and I use a CLR function to do it. It's more complex than these examples. (This is not a programming question.)
-
But you should do that up-front, before it hits the database. Or, more likely, when populating a landing/staging table. With what I'm doing now (mostly ETL), we land the data and then go through doing stuff like that, making IP addresses binary, splitting FQDNs into their parts, checking dates, etc. One of the things I challenge you to do in SQL is take big-endian DNS names and reformat them as little-endian FQDNs. Examples: .com.acme.southwest.anvils -- anvils.southwest.acme.com. .com.widgetco.sales -- sales.widgetco.com. This is something I need to do and I use a CLR function to do it. It's more complex than these examples. (This is not a programming question.)
Of course you "should" - there are a lot of things that people "should" and "shouldn't" do that don't actually happen the way they "should" in the real world. The result is almost always a database that accepts denormalized data for some of its content. Over a long enough timeline / large enough project this is an inevitability. That means in an enterprise sized application of any significant age, you're dealing with denormalized data. Furthermore, in some cases, with an RDBMS it is more efficient to accept denormalized data and normalize it in a stored proc to get results based on that. A good example is passing an array of integer cache ids so you only get particular rows from the database (such as updating a stale list) - there is simply no good (standard SQL) way to pass a small array of ints to the database, so 9 times out of 10, you'll see a procedure like this that takes a string or even a varbinary that has the data encoded it in. This is far more efficient than updating some kind of local session table you use to talk to your routines with (the other way to get a list of rows by ids) With a tool like this you can harden that, and the bottleneck is network, not DB CPU, and not row locks (unless you're using the table based version, which I actually don't recommend) I'm not sure about reformatting your domain names. They're easy enough to parse, but I don't know enough about the allowable formats to discern the logic for it without delving into RFCs.
Real programmers use butterflies
-
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
honey the codewitch wrote:
It's impossible to remember it without using it every day
I USE SQL every day a week (Ok 5 out of 7)... Still there are things I have to look-up...
"The only place where Success comes before Work is in the dictionary." Vidal Sassoon, 1928 - 2012
-
My next article will give SQL people fits, I'm sure.
Real programmers use butterflies
Unless it gives you the fits first. Doing advanced string manipulation in SQL-Server is a sure sign of masochism. :laugh:
Wrong is evil and must be defeated. - Jeff Ello
-
Unless it gives you the fits first. Doing advanced string manipulation in SQL-Server is a sure sign of masochism. :laugh:
Wrong is evil and must be defeated. - Jeff Ello
That's why I'm writing a tool to generate SQL for the regex matching. I at least only have to write it once. =)
Real programmers use butterflies
-
That's why I'm writing a tool to generate SQL for the regex matching. I at least only have to write it once. =)
Real programmers use butterflies
In many cases you won't need to write it at all. Oracle, Postgres, DB2 and even MySQL have full support for Regex. For the big ones it's basically SQL-Server that's the exception. And for SQL-Server I wrote a CLR-function to take care of that. (REGEXP_INSTR, REGEXP_REPLACE and REGEXP_SUBSTR like on most other implementations) And yes, you can write CLR also for Linux, since Linux work fine with .Net Core.
Wrong is evil and must be defeated. - Jeff Ello
-
I feel like Structured Query Language was designed by someone who felt like everything should be *work* or it's not worth the effort. The syntax is inexplicable. Not so much designed by committee as designed by a committee on a bender in 'Vegas. It's impossible to remember it without using it every day, and that possibility makes me want to bash my head bloody on the nearest hard surface. Plus just doing simple things you'd be able to expect to do in other languages like type conversion or heaven forbid, string manipulation is like pulling teeth. Now I know there are reasons for *some* of this, but most of it just seems like it was a result of throwing things at a wall to see what sticks.
Real programmers use butterflies
honey the codewitch wrote:
The syntax is inexplicable
:confused:
honey the codewitch wrote:
It's impossible to remember it without using it every day,
As a long time web developer I feel that's true for most all languages. As a web developer I do html, css, javascript, jquery, xml, json, c#, and t-sql. I often have to google for syntax. But SQL is the one I probably look up least often.
-
But you should do that up-front, before it hits the database. Or, more likely, when populating a landing/staging table. With what I'm doing now (mostly ETL), we land the data and then go through doing stuff like that, making IP addresses binary, splitting FQDNs into their parts, checking dates, etc. One of the things I challenge you to do in SQL is take big-endian DNS names and reformat them as little-endian FQDNs. Examples: .com.acme.southwest.anvils -- anvils.southwest.acme.com. .com.widgetco.sales -- sales.widgetco.com. This is something I need to do and I use a CLR function to do it. It's more complex than these examples. (This is not a programming question.)
PIEBALDconsult wrote:
But you should do that up-front, before it hits the database.
True, we should. But in the real world, this doesn't necessarily happen. Like when dealing with a legacy Oracle system written in C++ (appears to be VS97 or VS6), where the DB design defies rational logic. Or any logic. My queries have as many as 20 nested REGEXP_REPLACE statements to make sense out of the mess that some fields present. Ugly as sin, but it gets the job done. I'm counting the days until the replacement system goes online!
