Using COALESCE and also trying to multiply and divide
-
CELKO (9/3/2014)
Putting each element of Rents into its own table is a design flaw called “attribute splitting” and laugh at it.Well, if that had been what he actually did, you could have done some "side splitting" laughing. But it's not. He put each element into its own schema. A curious design, I admit, but not one that breaks any rules I know of.
rental_month CHAR(10) NOT NULL
CHECK (rental_month LIKE '[12],[0-9][0-9][0-9]-[01][0-9]-00')
Why not do it like this? It uses less space and ensures you don't get any invalid months or years.
rental_month date CHECK(DATEPART(dd,rental_month) = 1)John
-
CELKO (9/3/2014)
Celko, you could really use a charm school course.
A few decades ago, when I started posting on CompuServe, etc. I was super-polite and politically correct. Nobody heard me. Nobody learned a damn thing. :w00t:
Nobody read the front of the forums and followed Netiquette. Instead, they demanded kludge, NOW!! They did not want to hear about RDBMS, or ANSI Standards, or what their mistakes were. Most of them were children screaming: “I want a kludge! I want a kludge! I want a kludge! Do my job! Write my homework assignment for me!! ” :angry::crying:
But when I moved to the Zen Keisaku replies on SQL forums, the entitled children got mad! They read more than the quick answer! Some of then began to follow Netiquette! DDL! Wow! A few even bothered to look up the ANSI and ISO standards! Or read a book on RDBMS! Wow! Maybe, just maybe, this guy will spend a hour with Google and learn enough basic modeling to avoid attribute splits. 😛 Hooray!
Look at what VerBeeck did for him. The kid now thinks that a stinking awful design error is just fine. He can kludge around his problem! Why is that good? :crying:
Ever see the TV show HOUSE?
You mean the show about the conceited narcissistic prick who is unable to properly interact with other humans because he's convinced of his superiority, and constantly causes pain and annoyance to everyone around him?
Yep, seen it. Wouldn't think anyone would assume that was a recommendation of a communication style, but good luck with that.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
jcrawf02 (9/4/2014)
You mean the show about the conceited narcissistic prick who is unable to properly interact with other humans because he's convinced of his superiority, and constantly causes pain and annoyance to everyone around him?Yep, seen it. Wouldn't think anyone would assume that was a recommendation of a communication style, but good luck with that.
House was a genius though, with a wicked sense of humour.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (9/4/2014)
jcrawf02 (9/4/2014)
You mean the show about the conceited narcissistic prick who is unable to properly interact with other humans because he's convinced of his superiority, and constantly causes pain and annoyance to everyone around him?Yep, seen it. Wouldn't think anyone would assume that was a recommendation of a communication style, but good luck with that.
House was a genius though, with a wicked sense of humour.
I fail to see how your comment is relevant to the current discussion 😉
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
-
CELKO (9/3/2014)
Celko, you could really use a charm school course.
A few decades ago, when I started posting on CompuServe, etc. I was super-polite and politically correct. Nobody heard me. Nobody learned a damn thing. :w00t:
Nobody read the front of the forums and followed Netiquette. Instead, they demanded kludge, NOW!! They did not want to hear about RDBMS, or ANSI Standards, or what their mistakes were. Most of them were children screaming: “I want a kludge! I want a kludge! I want a kludge! Do my job! Write my homework assignment for me!! ” :angry::crying:
But when I moved to the Zen Keisaku replies on SQL forums, the entitled children got mad! They read more than the quick answer! Some of then began to follow Netiquette! DDL! Wow! A few even bothered to look up the ANSI and ISO standards! Or read a book on RDBMS! Wow! Maybe, just maybe, this guy will spend a hour with Google and learn enough basic modeling to avoid attribute splits. 😛 Hooray!
Look at what VerBeeck did for him. The kid now thinks that a stinking awful design error is just fine. He can kludge around his problem! Why is that good? :crying:
Ever see the TV show HOUSE?
I'm terribly sorry that I took the bait the first time. I won't make that mistake again.
-
jcrawf02 (9/4/2014)
Koen Verbeeck (9/4/2014)
jcrawf02 (9/4/2014)
You mean the show about the conceited narcissistic prick who is unable to properly interact with other humans because he's convinced of his superiority, and constantly causes pain and annoyance to everyone around him?Yep, seen it. Wouldn't think anyone would assume that was a recommendation of a communication style, but good luck with that.
House was a genius though, with a wicked sense of humour.
I fail to see how your comment is relevant to the current discussion 😉
I guess the point could be that if you're a genius and star in a TV show, you're allowed to interact like that. Until then, you just remain a PITA.
-
CELKO (9/3/2014)
the truncated YYYY-MM-DD is allowed in ISO 8061. But DD is constrained to be in the range 01 to 31 so the CELKO YYYY-MM-00 is guaranteed non-conformant to the ISO standard.
Let me try this again. The SQL Standard picked one and only one display format. This means we do not string function like FORMAT, CONVERT, and other proprietary nightmares that violate the concept of a tiered architecture.
This is not my creation, but I did vote for it. 😉 And I had nothing to do with the MySQL convention. That is under consideration and a handy trick. Shall I go thru the reasons again?
Want to talk about the INTERVAL data type that T-SQL does not have yet?
Well, if some committee is ignorant enough of date usage around the world to attempt to limit dates to the ambiguous, and thus inherently data corrupting, format of yyyy-nn-nn, then it's hard to take any of their recommendations seriously. I'll follow standardized recommendations when I can, but not at the expense of bad data or serious structure issues. The idea of a "standard SQL language" is a Fata Morgana anyway, a mirage, so I won't waste my time pretending that it's a useful thing to try to do.
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".
-
CELKO (9/4/2014)
He put each element into its own schema. A curious design, I admit, but not one that breaks any rules I know of.
Attribute splitting. You take a whole entity, and break it into multiple tables. Personnel gets split n sex_code to become {Male_Personnel, Female_Personnel }. The parts are constantly being re-assembled back to the true entity. The most common split is temporal periods; this mimics the way we kept magnetic tape files
rental_month CHAR(10) NOT NULL
CHECK (rental_month LIKE '[12],[0-9][0-9][0-9]-[01][0-9]-00')
Why not do it like this? It uses less space and ensures you don't get any invalid months or years.
Well, that is fine, too. But the extra '-00' on the end makes the column the same length and visual layout as the ISO-8601 display format used by Standard SQL. It also matches with MySQL, which is part of the LAMP on most websites on Earth.
I do not think saving 3 characters per row is worth the extra computing needed to handle the shorter string.
You need to stop confusing how data's stored with how it's output/displayed!
If you want a specific format for output/display, add a computed column or create a view that does that. It's not just 3 bytes, it's 3 bytes per date. If the row contained 6 dates, that's at least 18 bytes (and the extra 18 bytes per row will cause more page overflows, meaning slightly more space overall than just a strict 18 bytes per row).
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".
-
CELKO (9/4/2014)
...rental_month CHAR(10) NOT NULL
CHECK (rental_month LIKE '[12],[0-9][0-9][0-9]-[01][0-9]-00')
Why not do it like this? It uses less space and ensures you don't get any invalid months or years.
Well, that is fine, too. But the extra '-00' on the end makes the column the same length and visual layout as the ISO-8601 display format used by Standard SQL. It also matches with MySQL, which is part of the LAMP on most websites on Earth.
I do not think saving 3 characters per row is worth the extra computing needed to handle the shorter string.
First, your check constraint above will allow the following 2014-19-00. I sorry Mr. Superior Intellect, but there is NO SUCH THING as a 19th month! Try checking your work before posting such nonsense!
Also this is Microsoft SQL Server not MySQL so keep the Oracle product capabilities out of here, you are only going to confuse people who will think that SQL Server can support this capability.
-
I must admit, I find it funny that Mr. Celko sees fit to break ANSI standard when he bashes other people for not adhering to it. Why are you using a CHAR to record Temporal data? the Date data type runs from year 1 to year 9999, records only valid dates, and only takes 3 bytes. That is less than 1/3 the size of a CHAR(10) and less than half the size of a CHAR(7). AND it conforms to ANSI standard. If you are looking at only 10 rows, then the bytes don't matter. If you are looking at 10,000,000 rows that 7 byte difference is now 70 MB of data storage and processing. Remember, CHAR(10) IS NOT TEMPORAL DATA. It is a string! It could contain wildly incorrect data (such as 2014-19-00). Just because MySQL jumps off a bridge, doesn't mean we should in the SQL Server land.
If you are going to "try" to contribute, please learn the local dialect. For example just because you speak Mexican Spanish, doesn't mean that the natives of Spain won't think you a Foreigner.
linky to Date Data type:
http://msdn.microsoft.com/en-us/library/bb630352.aspx
CELKO (9/4/2014)
He put each element into its own schema. A curious design, I admit, but not one that breaks any rules I know of.
Attribute splitting. You take a whole entity, and break it into multiple tables. Personnel gets split n sex_code to become {Male_Personnel, Female_Personnel }. The parts are constantly being re-assembled back to the true entity. The most common split is temporal periods; this mimics the way we kept magnetic tape files
rental_month CHAR(10) NOT NULL
CHECK (rental_month LIKE '[12],[0-9][0-9][0-9]-[01][0-9]-00')
Why not do it like this? It uses less space and ensures you don't get any invalid months or years.
Well, that is fine, too. But the extra '-00' on the end makes the column the same length and visual layout as the ISO-8601 display format used by Standard SQL. It also matches with MySQL, which is part of the LAMP on most websites on Earth.
I do not think saving 3 characters per row is worth the extra computing needed to handle the shorter string.
-
CELKO (9/4/2014)
Attribute splitting. You take a whole entity, and break it into multiple tables. Personnel gets split n sex_code to become {Male_Personnel, Female_Personnel }. The parts are constantly being re-assembled back to the true entity. The most common split is temporal periods; this mimics the way we kept magnetic tape files
Yes, ignore me on that - I was talking complete nonsense.
Well, that is fine, too. But the extra '-00' on the end makes the column the same length and visual layout as the ISO-8601 display format used by Standard SQL. It also matches with MySQL, which is part of the LAMP on most websites on Earth.
I do not think saving 3 characters per row is worth the extra computing needed to handle the shorter string.
What extra computing? And there's no shorter "string". Date is stored as a number, meaning arithmetic is easier and illegal values are impossible. And, as others have pointed out, I save 7 bytes, not 3. Visual layout counts for nothing in a database - that's what the front end is for. I'm sure I've heard you say something similar in the past.
John
-
CELKO (9/3/2014)
Celko, you could really use a charm school course.
A few decades ago, when I started posting on CompuServe, etc. I was super-polite and politically correct. Nobody heard me. Nobody learned a damn thing. :w00t:
Personally, when I see your name at the start of your standard rant, I move on without reading, so this technique isn't working either!
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
Whoah whoah whoah. A problem with leading comma's?
Don't you care about nice formatted code?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
CELKO (9/11/2014)
The single line is a bouma; a semantic unit read as a whole. My PROGRAMMING STYLE goes into some of the research we collected at AIRMICS on this. I wish we had had the modern equipment for the eye movement studies ..The problem is that a column list is not a single unit, it's a list.
You'll find that reviewing lists will be easier if every item is in a single line. When counting the columns (and you'll need to count them more often than you could argue), having each column on it's own line will be a relief. Unless our brains don't match your studies.
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply