Select return no records

Question

Select return no records

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

mosaic-287947 wrote:

I completely agree with Celko.

Heh... being stuck with character-based identifiers due to a legacy system is one thing. Arbitrarily saying that all identifiers should be character-based because they're not used in calculations is another and completely incorrect position to take.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

>> Now it is you that is putting words in my mouth. ?? That's not what I said. <<

No I think you misread, this principle is from Brent Ozar. I hope I did not credit you with his work.

>> If people stored data as the way it would ultimately be used, then people would always store dates and times as strings, dollar amounts with $" signs, and all identifiers that used dashes and leading zeros for display with those dashes and leading zeros being stored. <<

No, I do not confuse storage with display. In fact if you look at most of my postings, I have a mania about the separation. If a value needs to have arithmetic computations, then I want to see it stored as INTEGER, FLOAT, REAL or whatever. If it is temporal data, then it needs an internal temporal storage. If it is text, then it needs to be stored as character data (probably with a hell of a lot of restrictions in the form of regular expressions on it).

To use the simple example in this thread, a ZIP Code is 5 digits. I can't do any math on it that makes any kind of sense. How do I prevent a moron from taking the square root of my ZIP Code? It is not an INTEGER! I also see that the zeros in the front of it have meaning,. Why do I want to make displaying it as difficult as possible by putting constraints on an INTEGER column, so that I have to re-pad it with those leading zeros (as a mathematician, please let me tell you that you know that they're not leading zeros because they have no numeric value).

Would you take a column that should be INTEGERs, such as quality on order, and model them with the date? A date is also sequential. Just like INTEGERs. And it is possible to do arithmetic on them. Oh sure, when I try to do an average or something. It gets a little messy but I can kludge this, just like you want to do.

Again, the Ozar principal is good; "store that it the data the way it is used and use it the way it is stored".

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

jcelko212 32090 wrote:

>> Now it is you that is putting words in my mouth. ?? That's not what I said. <<
No I think you misread, this principle is from Brent Ozar. I hope I did not credit you with his work.
>> If people stored data as the way it would ultimately be used, then people would always store dates and times as strings, dollar amounts with $" signs, and all identifiers that used dashes and leading zeros for display with those dashes and leading zeros being stored. <<
No, I do not confuse storage with display. In fact if you look at most of my postings, I have a mania about the separation. If a value needs to have arithmetic computations, then I want to see it stored as INTEGER, FLOAT, REAL or whatever. If it is temporal data, then it needs an internal temporal storage. If it is text, then it needs to be stored as character data (probably with a hell of a lot of restrictions in the form of regular expressions on it).
To use the simple example in this thread, a ZIP Code is 5 digits. I can't do any math on it that makes any kind of sense. How do I prevent a moron from taking the square root of my ZIP Code? It is not an INTEGER! I also see that the zeros in the front of it have meaning,. Why do I want to make displaying it as difficult as possible by putting constraints on an INTEGER column, so that I have to re-pad it with those leading zeros (as a mathematician, please let me tell you that you know that they're not leading zeros because they have no numeric value).
Would you take a column that should be INTEGERs, such as quality on order, and model them with the date? A date is also sequential. Just like INTEGERs. And it is possible to do arithmetic on them. Oh sure, when I try to do an average or something. It gets a little messy but I can kludge this, just like you want to do.
Again, the Ozar principal is good; "store that it the data the way it is used and use it the way it is stored".

It sure is but... go ask Brent if he recommends storing numeric identifiers (Sequence, column with an IDENTITY attribute, or even a date for a Calendar table) as text-based columns simply because you'll never use them for math. 😉 He lives 2500 or so miles from me but I'm sure I'll be able to hear his laugh from here followed by a mighty "It Depends but not usually".

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

mosaic-287947 wrote:

ScottPletcher wrote:
You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just id#).
Instead you could have assigned new, and non-overlapping, numbers to whichever type of employees there were fewer of.
Our database did not "own" the employee data. We merely received a revised daily extract from HR, and had no authority to redesign any of it.
In our database we used an identity column as the surrogate key for the table with the imported data, with a unique index on the imported employee ID. Not ideal but it worked well enough for over 10 years. I don't think that is too bad considering that it started out as a glorified spreadsheet to track a single project and eventually supported a couple of dozen automated in-house processes.

Really, it remained a spreadsheet. The biggest problem in tables is always the lack of doing any actual data design, including the normalization process, up front, before any physical table is created. Once you've skipped that part, you're destined for a less-than-good data situation from then on.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".