Best use of relational data ever
-
That's just crazy! They should have used an int! :laugh:
"Go forth into the source" - Neal Morse
There's a reason I included the schema :)
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
-
My week was made today while working on one of our old production applications. Trying to hunt down a particularly obtuse relation, among the piles of tables, I found this gem: x.dbo.tbl50 number float notnull SELECT * FROM [x].dbo.tbl50 number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
It's what's known as a "tally table". There's a CP article on them here.[^]
-
It's what's known as a "tally table". There's a CP article on them here.[^]
Thank you. The fact that this is actually a thing made me a little sad, and afraid for the world.
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
-
Thank you. The fact that this is actually a thing made me a little sad, and afraid for the world.
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
It's a thing because the alternatives sucks. Tally tables are a well established trick to gain performance, and we're talking huge differences here. It's all about not using procedural code. Databases works best with sets. A tally table is a set. Now I want you to read the conclusion in Graus's article, because it's good.
Wrong is evil and must be defeated. - Jeff Ello
-
It's a thing because the alternatives sucks. Tally tables are a well established trick to gain performance, and we're talking huge differences here. It's all about not using procedural code. Databases works best with sets. A tally table is a set. Now I want you to read the conclusion in Graus's article, because it's good.
Wrong is evil and must be defeated. - Jeff Ello
-
It's what's known as a "tally table". There's a CP article on them here.[^]
-
My week was made today while working on one of our old production applications. Trying to hunt down a particularly obtuse relation, among the piles of tables, I found this gem: x.dbo.tbl50 number float notnull SELECT * FROM [x].dbo.tbl50 number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
-
Wow, learn something new every day. Thanks.:thumbsup:
When you are dead, you won't even know that you are dead. It's a pain only felt by others. Same thing when you are stupid.
Once you stop learning, you may as well be dead.
#SupportHeForShe Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
-
It's a thing because the alternatives sucks. Tally tables are a well established trick to gain performance, and we're talking huge differences here. It's all about not using procedural code. Databases works best with sets. A tally table is a set. Now I want you to read the conclusion in Graus's article, because it's good.
Wrong is evil and must be defeated. - Jeff Ello
Honestly, for anyone that works regularly with LINQ "thinking in sets" is the norm. It's just that those sets are considered after a data layer call has completed, not before. I guess it's all about the abstraction layer that we live in. On your suggestion I re-read Graus's article and, with inspiration from the article, I found that the table did indeed act as a tally for 2 SPs, which are executed annually, on about 1000 rows between them. So ultimately tbl50 does indeed have a purpose that would have been better served by an incremental (in this specific case). I appreciate the guidance!
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
-
Honestly, for anyone that works regularly with LINQ "thinking in sets" is the norm. It's just that those sets are considered after a data layer call has completed, not before. I guess it's all about the abstraction layer that we live in. On your suggestion I re-read Graus's article and, with inspiration from the article, I found that the table did indeed act as a tally for 2 SPs, which are executed annually, on about 1000 rows between them. So ultimately tbl50 does indeed have a purpose that would have been better served by an incremental (in this specific case). I appreciate the guidance!
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
Well, I wouldn't dirty down the database with a tally table if performance isn't of any importance for something that's used twice a year. :laugh: The right tools for the right job is a good rule.
Wrong is evil and must be defeated. - Jeff Ello
-
My week was made today while working on one of our old production applications. Trying to hunt down a particularly obtuse relation, among the piles of tables, I found this gem: x.dbo.tbl50 number float notnull SELECT * FROM [x].dbo.tbl50 number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
Just a couple of days ago I found a table like that in the SQL-Server DB of Team Foundation Server 2015. It is called Number. So it seems there are uses for this out there. Haven't read the Graussian article yet that is mentioned in the thread, but I think I really should. :D Cheers!
"I had the right to remain silent, but I didn't have the ability!"
Ron White, Comedian
-
Honestly, for anyone that works regularly with LINQ "thinking in sets" is the norm. It's just that those sets are considered after a data layer call has completed, not before. I guess it's all about the abstraction layer that we live in. On your suggestion I re-read Graus's article and, with inspiration from the article, I found that the table did indeed act as a tally for 2 SPs, which are executed annually, on about 1000 rows between them. So ultimately tbl50 does indeed have a purpose that would have been better served by an incremental (in this specific case). I appreciate the guidance!
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
So now go and rename the table "TallyTable" or add an Description property to the table or something to help the next pilgrim who emerges from the brambles.
cheers Chris Maunder
-
So now go and rename the table "TallyTable" or add an Description property to the table or something to help the next pilgrim who emerges from the brambles.
cheers Chris Maunder
It's a nice thought CM, but honestly I'm gutting the thing for useful logic. SPs will be moved into application code so that I can properly abstract this thing, and that table will have no place in the new version.
"There are three kinds of lies: lies, damned lies and statistics." - Benjamin Disraeli
-
It's what's known as a "tally table". There's a CP article on them here.[^]
I think tally tables should be banned. A tallyban.
-
I think tally tables should be banned. A tallyban.
You sound Indian... are you from punjab?
DURA LEX, SED LEX GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- ++>+++ y+++* Weapons extension: ma- k++ F+2 X If you think 'goto' is evil, try writing an Assembly program without JMP. -- TNCaver When I was six, there were no ones and zeroes - only zeroes. And not all of them worked. -- Ravi Bhavnani
-
That is a good one. I've seen the: select * from [day] Monday Tuesday Wednesday Thursday Friday Saturday Sunday And when I asked what this is for the response was, "a list of the days of the week, duh!"
A while back I had to support a client's bloated d/b schema. The customer table had a column "Gender", of type INT. There was a tblGender, with an INT key and a text column. It had two rows; 1/M and 2/F. Then there was another table called tblGenderTexts. It also had two columns, both text. Yep, values M/Male and F/Female. :doh:
-
A while back I had to support a client's bloated d/b schema. The customer table had a column "Gender", of type INT. There was a tblGender, with an INT key and a text column. It had two rows; 1/M and 2/F. Then there was another table called tblGenderTexts. It also had two columns, both text. Yep, values M/Male and F/Female. :doh:
:doh: That DBA needed to be whacked with a clue-wrecking-ball.
What do you get when you cross a joke with a rhetorical question? The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism. Do questions with multiple question marks annoy you???