June 20, 2011 at 7:22 am
David Portas (6/20/2011)
Eric M Russell (6/14/2011)
MS SQL and Oracle are first and formost relational database engines. If Microsoft thinks NoSQL datbase solutions are a market worth breaking into, then I'd expect they would branch off and develope a new non-relational database product, and call it something like Microsoft NoSQL. It could perhaps be a service (like they're OLAP product Analysis Services) that can integrate with and augment SQL Server or optionally be run standalone.MS SQL and Oracle are first and foremost SQL database engines, not relational ones. As Ed suggested, the SQL model and its implementations have failed miserably to meet modern business needs (for example due to poor support for declarative data integrity, physical data independence or rich, extensible datatypes). Many CIOs realise this. Most .NET and Java developers certainly realise this because they have to grapple with the SQL dinosaur every day. In fact it's a frequent topic of discussion among those groups.
Unfortunately the people who seem to notice the failings of SQL least are the ones whose voice ought to speak loudest, namely the database professionals who work with SQL DBMSs. That partly explains why most of the recent innovation in databases has come from the grassroots efforts of people outside the "traditional" data management community and DBMS vendors. I personally believe that the best thing SQL professionals could do for the future of our industry is to stop defending SQL and start talking about what its future replacement should be.
The lazy assumption that SQL=relational and relational is what people want just doesn't cut it any more. SQL is a 30 year-old non-relational database model that doesn't match modern data management needs. NOSQL represents a movement away from the SQL legacy towards a set of approaches that meet some business needs better than SQL ever did. But NOSQL doesn't have to mean non-relational. The piece of the jigsaw that's missing is a truly relational industrial-strength DBMS that is not based on SQL. That's the NOSQL that ought to be really worth talking about here.
That's strange. In my observable universe, SQL Server and Oracle support declarative data integrity, meet business needs, EAV tables exist only within the realm of niche applications, and CIOs and Java developers are not database experts.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 21, 2011 at 2:51 pm
Eric M Russell (6/20/2011)
That's strange. In my observable universe, SQL Server and Oracle support declarative data integrity, meet business needs, EAV tables exist only within the realm of niche applications, and CIOs and Java developers are not database experts.
I said SQL has poor (inadequate) support for declarative data integrity. The fact that SQL databases implement some such integrity doesn't disprove what I said. A great many non-trivial SQL databases have only a small subset of the relevant integrity rules in the data model. Most of the rest - the constraints that SQL can't support - are implemented either procedurally, in applications, in business rules engines or other data integrity systems. It's only SQL specialists who sometimes kid themselves that through implementing a few foreign key constraints and check constraints they have somehow achieved all the applicable business rules. Business analysts, end users and application programmers know otherwise.
If I give the SQL database designer the requirement that an invoice must match exactly one delivery note then he'll surely create a foreign key constraint for that rule. Give the same designer the requirement that an invoice must match at least one delivery note and he'll usually have to put the rule into procedures or in the application because it's virtually impossible to implement even such simple constraints in SQL Server. Ask for a join dependency constraint or a constraint based on an aggregation and he'll have even more problems.
That SQL fails to provide a decent platform for declarative business rule enforcement is probably one of its biggest failures. The multi-million dollar market for non-SQL based rules engines as a crutch for SQL is all the evidence required to demonstrate that.
Similar remarks can apply to rich type support and data independence (think OLAP storage engines for a classic example of how SQL has failed to achieve physical data independence). Again, the after-market in add-on products speaks volumes about the inadequacies of SQL. And it's a lousy state of affairs that we are stuck with a 30 year-old database language that can't even manage strong typing!
Seriously, if you were inventing a new database language from scratch, would you really create one that looked like SQL? I happen to think that the relational model has more to offer than the expensive immitation versions of it sold by Microsoft and Oracle. We should be working toward something better, not wasting time trying to defend SQL. Or do you really believe that SQL is somehow better than the relational model?
June 22, 2011 at 8:50 am
David Portas (6/21/2011)
Eric M Russell (6/20/2011)
That's strange. In my observable universe, SQL Server and Oracle support declarative data integrity, meet business needs, EAV tables exist only within the realm of niche applications, and CIOs and Java developers are not database experts.I said SQL has poor (inadequate) support for declarative data integrity. The fact that SQL databases implement some such integrity doesn't disprove what I said. A great many non-trivial SQL databases have only a small subset of the relevant integrity rules in the data model. Most of the rest - the constraints that SQL can't support - are implemented either procedurally, in applications, in business rules engines or other data integrity systems. It's only SQL specialists who sometimes kid themselves that through implementing a few foreign key constraints and check constraints they have somehow achieved all the applicable business rules. Business analysts, end users and application programmers know otherwise.
If I give the SQL database designer the requirement that an invoice must match exactly one delivery note then he'll surely create a foreign key constraint for that rule. Give the same designer the requirement that an invoice must match at least one delivery note and he'll usually have to put the rule into procedures or in the application because it's virtually impossible to implement even such simple constraints in SQL Server. Ask for a join dependency constraint or a constraint based on an aggregation and he'll have even more problems.
That SQL fails to provide a decent platform for declarative business rule enforcement is probably one of its biggest failures. The multi-million dollar market for non-SQL based rules engines as a crutch for SQL is all the evidence required to demonstrate that.
Similar remarks can apply to rich type support and data independence (think OLAP storage engines for a classic example of how SQL has failed to achieve physical data independence). Again, the after-market in add-on products speaks volumes about the inadequacies of SQL. And it's a lousy state of affairs that we are stuck with a 30 year-old database language that can't even manage strong typing!
Seriously, if you were inventing a new database language from scratch, would you really create one that looked like SQL? I happen to think that the relational model has more to offer than the expensive immitation versions of it sold by Microsoft and Oracle. We should be working toward something better, not wasting time trying to defend SQL. Or do you really believe that SQL is somehow better than the relational model?
In many cases the more complex rules should be implemented in the user interface, because they are tied to the workflow of the application. For example, if only three deliveries can be scheduled, and the user attempts clicks a button to enter a fourth, then the application should perform that lookup check and notify them without attempting to collect and insert any further data, or better yet the button could be disabled.
The full range of data integrity rules as defined by business can be limitless. Fortunately a table check constraint can reference a scalar function, accepting columns as input paramters, so virtually any type of business rules could be implemented at the database level using that method. In the example below, which leverages a function based check constraint, a maximum of three records for any given invoice can be inserted into the invoice_delivery table.
create table invoice_delivery
(
delivery_id int not null identity(1,1) primary key,
delivery_date datetime not null default getutcdate(),
invoice_id int not null
);
go
create function dbo.fn_cc_invoice_delivery( @invoice_id int ) returns int
begin; return (select count(*) from invoice_delivery where invoice_id = @invoice_id); end;
go
alter table invoice_delivery
add constraint cc_invoice_delivery
check (dbo.fn_cc_invoice_delivery(invoice_id) <= 3 );
go
insert into invoice_delivery ( invoice_id ) values ( 100 );
insert into invoice_delivery ( invoice_id ) values ( 100 );
insert into invoice_delivery ( invoice_id ) values ( 100 );
go
The 4th insert will violate the check constraint.
insert into v_invoice_delivery ( invoice_id ) values ( 100 );
The INSERT statement conflicted with the CHECK constraint "cc_invoice_delivery".
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 22, 2011 at 9:45 am
Eric, Do you really think that a "one or more" cardinality referential constraint is so much more complex than a "zero or more" constraint that it justifies a fundamentally different means to implement it? I don't and nor do ER modelling, ORM and other conceptual modelling methodologies. Such constraints (and many others that SQL cannot implement) are pretty much the most basic kinds of integrity constraints there are. Why should we be forced to implement straightforward integrity constraints in our applications with all the risks and costs that entails?
In my previous post I mentioned an inclusion dependency constraint, which is actually a simpler constraint than SQL's so-called "foreign key" constraint (a foreign key actually being just a special case of inclusion dependency). You have answered a different problem with your example function. Your example won't work properly because SQL Server evaluates such constraints on a row-by-row basis rather than in a declarative set-based fashion. If you update the invoice_id column you'll sometimes get errors when you shouldn't do - one of the perils of implementing constraints in procedural code. In SQL Server any CHECK constraint that contains a query against the table it applies to is suspect and cannot be relied upon for integrity. And if the constraint has to reference two tables then you would actually have to create the same constraint twice, once for each table.
Even if those problems were fixed (with Standard SQL-style ASSERTIONs for example) many important constraints still cannot be implemented in SQL. "A references at least one B" is one well-known example of a constraint that SQL cannot support.
My point here is not to argue implementation details but simply to point out that SQL has major deficiencies in constraint support, strong typing, extensible typing and data independence. We ought to welcome efforts to develop new models that overcome these long-standing problems.
October 26, 2015 at 10:29 am
The problem is that there is a big difference between being able to install and use SQL Server and being a responsible admin. And in most cases, management refuses to recognize that until there's data loss or even after.
Basically there's not enough competent organizations that truly understand the basics of data management.
October 27, 2015 at 4:39 am
Perhaps it would make sense to have the safety nets automatically selected (but optional) when using the GUI but not automatically provided (but available) via any command line.
This would create a generalisation of defaulting to safety nets to users except for the power users.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
October 27, 2015 at 7:13 am
Thanks for rerunning this commentary.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply