A Function Gotcha with the Use of an Asterisk

Question

Post reply

A Function Gotcha with the Use of an Asterisk

Viewing 15 posts - 106 through 120 (of 151 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 2

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.

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 3

And I am sorry to keep you guys awake: I am in Australia trying to wake up. 🙂

Paul White SSC Guru Points: 150467 More actions · Answer 4

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 5

It seems we might agree to (mildly) disagree on the more subjective points raised.

Yes, no progress without disagreement. 🙂

swapanak Newbie Points: 5 More actions · Answer 6

This is really a good article.

The thing is so simple but can lead to serious issues, since we do not get any error.

Henrik Staun Poulsen SSCertifiable Points: 6551 More actions · Answer 7

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

http://www.stovi.com

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

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

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 9

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'). 🙂

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 10

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

Alex Fekken Ten Centuries Points: 1109 More actions · Answer 11

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:

Rob Fisk SSCommitted Points: 1861 More actions · Answer 12

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]

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 13

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

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