Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

GOSQL vs. NOSQL Expand / Collapse
Author
Message
Posted Monday, July 12, 2010 4:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:04 AM
Points: 448, Visits: 3,336
GPO (7/11/2010)
Well, I guess it's time I posted another of my silly questions. What do people mean when they say SQL is broken? What is the reference point? What are the criteria?


SQL relies on multi-sets rather than a set-based query model. ie SQL has duplicate rows. That adds complexity in DBMS software - complexity which customers have to pay for even if they manage to avoid duplicate rows in their tables and queries. The need to support duplicates makes query optimisation more difficult and less effective, creates data quality problems and makes it harder to write and verify correctness of SQL queries.

SQL's version of three-value logic doesn't make sense in reality and just adds complexity without adding any expressive power to the language. The combination of nulls and 3VL yields results that are incorrect in reality. Even SQL itself is inconsistent in its use of nulls, leading to inconsistent results where nulls are used to mean different things in different places.

SQL doesn't provide good support for analytical, decision support type queries, which means customers have to use other technologies (such as Analysis Services). Typically those other technologies come at enormous cost of moving and duplicating data, data latency, additional software, hardware, management costs, etc. This is one of the most compelling reasons for needing a more relational query language in my opinion. A truly relational language could easily support both OLTP and decision support type applications and that single improvement would save database customers $billions.

SQL makes it impossible or hard to implement many business rules. Again the cost is that customers have to use other software to implement business rules or they build them into application code. That leads to inconsistency and latency between the implementation of rules, consequent bad data and the cost of maintaining data manipulation code in multiple places.

SQL lacks strong typing, user defined type support and type inheritance. Therefore its integration with other languages is poor.

The SQL SELECT syntax is unnecessarily complex, redundant and lacks orthogonality.

This is not a complete list. These are just some of the common criticisms of SQL.


David
Post #950660
Posted Monday, July 12, 2010 8:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:54 AM
Points: 865, Visits: 2,381
Personally, I believe in putting data into SQL databases where the SQL databases add value.

I recently had a discussion with someone who wanted to put non-critical, arbitrary string logging data into a database table for what boiled down to a reason of "Well, our other data comes from there too, so it's easy". I argued against that; the data was not relational. It would never be related to other data. It would never be subject to constraints that ensure integrity. It might be searched for the contents of strings. It has no ACID related requirements. It had extremely coarse granularity security requirements. In essence, the database was solely additional complexity without additional value: some variant of a text file or perhaps a transactional database (Unique Key, bunch of data) would be a much more appropriate form of storage; if the arbitrary substring search might be important, then consider that in the choice of storage and parsing technologies.

The upshot is: Think about what value any given storage technology adds to solving a particular business goal. Think about what cost any given storage technology has in solving a particular business goal. Make an informed choice, not a knee-jerk response (unless your corporate constraints are "everything goes in X, no matter what").
Post #950793
Posted Monday, July 12, 2010 10:08 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 824, Visits: 1,088
I'm terrified by NoSQL, but my problem with it isn't technical. I think we all know the issues with scaling out SQL databases, and I would love to be part of a team that used a NoSQL platform to solve that kind of problem. My problem with it is that both times it has come up seriously, it hasn't been in response to a need to scale out an application, it's been managers and senior developers trying to get around the process of proper data design. They are looking at the schemaless approach and thinking that it means they can just move forward and fix everything in code later when they know that they don't have enough business answers to properly design a relational schema. And down that road lies partially digested spaghetti code.

Post #950877
Posted Monday, July 12, 2010 10:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 6:37 PM
Points: 33,189, Visits: 15,329
rp-sqlsc (7/10/2010)
I've been taken on the carpet before, but I still don't understand why everyone is so enamored of the current SQL (90 or whatever) syntax for doing analysis of data stored in various table (or otherwise "Rectangular Objects").

We have views for some slicing, extremely complex procedures for rendering business rules or whatever. Vendors have introduced OLAP approaches but these are not universal. All of these SQL interfaces are based on a fairly ancient syntax.

In some ways I feel lots of what's being done in other languages is fairly ancient as well. We aren't evolving along the lines of LISP, PROLOG, etc. We seem to just be building more complex objects on top of lower ones in languages without really improving the way things work. Syntax gets easier, but not sure that the language is really evolving. It has felt like Java didn't substantially improve C++. It made things easier, but didn't improve the language.

In some sense I think that many OOP programmers have struggled with the paradigm shift to a SET based language that is different than the way OOP works. I bet that OOP programmers would see the same argument from procedural based programmers struggling with OOP.


I'd like to completely separate the business rules (and of course the presentation) from the database. If the business rules need to exist in the DB (why: performance, modularization), then make them completely separate from the purely transformational ones.


Most people say they would, but struggle to separate presentation from business. The MVC structure seems like it might help here. Not sure.

I would argue that the only business rules in RDBMSes should be those that are required to support ACID. Other ones, often presentation level rules, limit scalability.


How do we deal with the fuzzy logic of the current search engines using SQL? LIKE? CONTAINS? poor substitutes for real regex's.

Agree. REGEX should be there. However it's relatively trivial to implement this via SQL CLR, Oracle Package, etc. The bigger issue is that a RegEx is OOP operates at a trivial amount of data. In the SQL world, it doesn't. Google makes it seem easy, but they are distorting the problem with tremendous amounts of hardware.


Can we make queries that are optimized based on 'knowing' that the target result set is readily available?

What is "knowing?"


I'd love to be able to use a hierarchical naming scheme (db.project.appliction.table) but it ain't available in SQL Server (schemas aren't it.) I'd like to be able to specify via a 'use ##' a node within this hierarchy.

Aren't there any languages that can compete with SQL across all platforms? I don't think individual ORM solutions work.

Why the naming? It seems that you're confusing the db store with a project somewhere. This is more likely a tool issue.

As far as languages, ORM is not a language. It's a framework made to fit with languages. Ultimately sets and objects are two different beasts. A collection or name/value pair is somewhat of a bridge, but limited. As you expand the "pair" to columns, you run into the RDBMS.


Sorry if these questions just show my ignorance.


Good question, and worth putting forth. Don't be sorry.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #950906
Posted Monday, July 12, 2010 11:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 18, 2014 7:25 AM
Points: 989, Visits: 1,823
Question from one for whom the "NoSQL" idea is relatively new (I've been plugging away at SQL so long I didn't know some were trying to tear it down).

How do NoSQL systems dealing with things like secure transactions, especially financial transactions from bank accounts, or things like HIPPA or SOX that require auditing and severely-locked-down systems? On the surface it would seem that some of the NoSQL solutions I have seen would not be ideal for systems with strict accountability requirements. I could be wrong, though, thus the question.
Post #950945
Posted Monday, July 12, 2010 12:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:08 PM
Points: 8, Visits: 484

...

SQL doesn't provide good support for analytical, decision support type queries, which means customers have to use other technologies (such as Analysis Services). Typically those other technologies come at enormous cost of moving and duplicating data, data latency, additional software, hardware, management costs, etc. This is one of the most compelling reasons for needing a more relational query language in my opinion. A truly relational language could easily support both OLTP and decision support type applications and that single improvement would save database customers $billions.

SQL makes it impossible or hard to implement many business rules. Again the cost is that customers have to use other software to implement business rules or they build them into application code. That leads to inconsistency and latency between the implementation of rules, consequent bad data and the cost of maintaining data manipulation code in multiple places.

SQL lacks strong typing, user defined type support and type inheritance. Therefore its integration with other languages is poor.

The SQL SELECT syntax is unnecessarily complex, redundant and lacks orthogonality.

This is not a complete list. These are just some of the common criticisms of SQL.


This captures what I feel is my main difficulty with using SQL to get useful information from a data store. It can be incredibly time-consuming to discover the relationships that will yield the desired results without knowing the intent of the original designers.

I think that we will always need referential integrity, transactions, DB-enforced constraints, type checking, and the performance optimizations such as indexing and clustering. My current DB (MSQL) does these well enough for me. We need to make sure that our data is stored in a consistent state and can be rolled back and recovered as needed.

Ninety-nine percent (99%) of our interactions with the databases is for queries. Our data are financial indices and holdings that are updated infrequently throughout the day. But our queries are very complex and can pass through 10-20 DB objects (stored procedures, user-defined functions, etc.) It's in these queries that most of our business logic exists.

I guess what I am looking for is an easier way to define these queries - perhaps adding in an analysis engine would be a solution of a type.
Post #950959
Posted Tuesday, July 13, 2010 6:57 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:34 PM
Points: 36,978, Visits: 31,498
rp-sqlsc (7/12/2010)
This captures what I feel is my main difficulty with using SQL to get useful information from a data store. It can be incredibly time-consuming to discover the relationships that will yield the desired results without knowing the intent of the original designers.



I'm sure you know this but I have to say it out loud ... this captures what I feel is my main difficulty with.... designers.

SQL Server is quite capable of telling you just about everything you need to know the PK/FK relationships especially if a 3rd party drawing tool is available but also through its ability to produce such a diagram itself.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #951942
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse