September 13, 2009 at 7:39 pm
Alex (9/13/2009)
Codd and Date (http://en.wikipedia.org/wiki/Relational_model). I am not aware of any others...
I don't define a modern relational database that way, no. But then I'm not an expert on the Relational Model either!
Does the Relational Model have a neat way to avoid the whole issue of changing meta data and 'SELECT *' then?
I'm not sure it helps the situation with SQL Server any, but it would still be interesting to know.
Alex (9/13/2009)
I admit that perhaps my expectations of SQL Server as a relational database are too high and the issue really is that I/we should be content with whatever the tins says, regardless of the underlying theory and the expectations created by it.
No, I think both can co-exist quite happily though they're certainly not the same thing at all.
SQL Server is what it is - for all its faults, it is probably the most broadly capable database product available per $Currency.
I don't think strict adherence to the Relational Model is one of Microsoft's design priorities however.
Good job too - very few people would possess the skills needed to use it effectively.
September 13, 2009 at 8:48 pm
I don't define a modern relational database that way, no.
I am curious then: how would you define a relational database if it is not one that tries to adhere to the relational model?
Does the Relational Model have a neat way to avoid the whole issue of changing meta data and 'SELECT *' then?
No, it does not deal with implementation issues, other than trying to ensure that they can be resolved in principle. It is not a product but a specification for products, one that companies like Oracle, IBM, Sybase, Microsoft etc are trying very hard to follow.
SQL Server is what it is - for all its faults, it is probably the most broadly capable database product available per $Currency.
Except for Firebird, which is free, and "broadly capable" enough for many applications:-) But I agree that SQL Server is a good and cost effective tool.
I don't think strict adherence to the Relational Model is one of Microsoft's design priorities however.
Not "strict" no, but I think that non-adherence is mostly the result of design compromise (because it would be too hard or too expensive) rather than the preferred outcome. And the problem we are discussing is the result one of those compromises.
The reason that views do not return last week's data is that no RDBMS vendor would get away with such a deviation from what you would expect from the intent of the relational model. But on lesser issues they can and do.
September 13, 2009 at 8:50 pm
And I am sorry to keep you guys awake: I am in Australia trying to wake up. 🙂
September 13, 2009 at 9:25 pm
Alex (9/13/2009)
I am curious then: how would you define a relational database if it is not one that tries to adhere to the relational model?
I don't really offer a definition - it often leads to trouble. I just use 'relational database' in the colloquial sense.
The rest of your post represents your point of view well, even though I don't share it (for the most part).
It seems we might agree to (mildly) disagree on the more subjective points raised.
September 13, 2009 at 9:41 pm
It seems we might agree to (mildly) disagree on the more subjective points raised.
Yes, no progress without disagreement. 🙂
September 14, 2009 at 12:47 am
This is really a good article.
The thing is so simple but can lead to serious issues, since we do not get any error.
September 14, 2009 at 12:47 am
Hi Charles Pockert,
<< Search for
<< {()}
<< Replace with '\1',
with the later comment
<< Search for
<< ^(.+)$
<< Replace with '\0',
Thank you for that trick. Easy when you know how to. Most useful.
Best regards,
Henrik Staun Poulsen
September 14, 2009 at 1:26 am
Alex (9/13/2009)
Except for Firebird, which is free, and "broadly capable" enough for many applications
You must be joking, right?
I wouldn't compare Firebird to SQL Server, way too many differences between them.
I'm using both, and like both, but I would never use Firebird in place of SQL Server, exactly as I wouldn't use SQL Server in place of Firebird.
-- Gianluca Sartori
September 14, 2009 at 3:35 am
You must be joking, right?
I wouldn't compare Firebird to SQL Server, way too many differences between them.
I'm using both, and like both, but I would never use Firebird in place of SQL Server, exactly as I wouldn't use SQL Server in place of Firebird.
No I wasn't, actually. I agree that there are many differences but both are mature SQL-based client-server database engines, with SQL Server having a lot more extras beyond the engine if that's what you mean.
It is not like comparing MS Access with SQL Server or calling Excel a relational database (as one might 'colloquially'). 🙂
September 14, 2009 at 3:48 am
Alex (9/14/2009)
It is not like comparing MS Access with SQL Server or calling Excel a relational database (as one might 'colloquially'). 🙂
Hahah - please don't make me spit out my coffee!! 😀
I did interview a guy once who was trying his best to convince me that Excel was the best way to transfer data between two SQL Servers. When I asked him what a 'big database' meant to him, he replied that one with 1,000 rows or more was 'pretty huge'... Oh, and he said the best way to find the IDENTITY of a row after inserting it would be to truncate the table before inserting it, so that the IDENTITY would always be 1. I didn't want to even start on seed values :w00t:
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 4:05 am
Oh, and he said the best way to find the IDENTITY of a row after inserting it would be to truncate the table before inserting it, so that the IDENTITY would always be 1.
I assume you would have hired him had he also mentioned the performance benefit of this approach? :laugh:
September 14, 2009 at 4:15 am
Specify the columns you need and schema bind where possible and appropriate. When requirements change, so does the code.
I can't think of a case where it is sensible to use SELECT * in production code, except after EXISTS.
There is even less reason to use * in EXISTS I generally use EXISTS (SELECT 1 FROM...)
Then there is no need to look up anyu columns or values from any columns at all you just get the literal. Much more efficient.
[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]
September 14, 2009 at 4:18 am
Alex (9/14/2009)
I assume you would have hired him had he also mentioned the performance benefit of this approach? :laugh:
Oh, certainly! It was a close call... 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 14, 2009 at 4:26 am
Rob Fisk (9/14/2009)
Specify the columns you need and schema bind where possible and appropriate. When requirements change, so does the code.
I can't think of a case where it is sensible to use SELECT * in production code, except after EXISTS.
There is even less reason to use * in EXISTS I generally use EXISTS (SELECT 1 FROM...)
Then there is no need to look up anyu columns or values from any columns at all you just get the literal. Much more efficient.
That's what I do too because I was told to do so many years ago, but I don't think there's a real performance gain in it.
I think the exists subquery has no output list itself, but is always correlated to the outer main query. That's what I guess from the execution plan, but I could be wrong. Try and see for yourself:
-- SELECT 1 VERSION
select *
from syscolumns as a
where exists (
select 1
from syscolumns as b
where a.id = b.colid
)
-- SELECT * VERSION
select *
from syscolumns as a
where exists (
select 1
from syscolumns as b
where a.id = b.colid
)
The execution plans look quite the same...
-- Gianluca Sartori
September 14, 2009 at 4:30 am
Then there is no need to look up anyu columns or values from any columns at all you just get the literal. Much more efficient.
I am just guessing here but wouldn't SQL Server simply ignore the subquery's Select clause in this situation anyway?
Still, I agree '1' is even easier to type than '*'.
Viewing 15 posts - 106 through 120 (of 151 total)
You must be logged in to reply to this topic. Login to reply