August 8, 2013 at 2:53 pm
This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 β Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 8, 2013 at 2:55 pm
Sean Lange (8/8/2013)
This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:
Not all languages make sense. In Dutch we say some of the numbers backwards. For example, we say 62 as 'twee en zestig', which translates to 'two and sixty'.
Just to confuse the hell out of everyone trying to learn our language π
And especially useful when you are dictating a phone number or house number over the phone...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2013 at 4:23 pm
Koen Verbeeck (8/8/2013)
Sean Lange (8/8/2013)
This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:Not all languages make sense. In Dutch we say some of the numbers backwards. For example, we say 62 as 'twee en zestig', which translates to 'two and sixty'.
Just to confuse the hell out of everyone trying to learn our language π
And especially useful when you are dictating a phone number or house number over the phone...
We used to do that here in England, with time of day - my Mother still does - "five and twenty to three" = 2:35 :w00t:
Oh, and I completely "got" your "Indeed it doesn't" comment - it's those crazy Americans that don't use English properly like what we do :hehe:
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
August 8, 2013 at 9:22 pm
Sean Lange (8/8/2013)
I can still remember one of those Eureka moments. I was reading a post from Wayne Sheffield and he explained that in order to understand set programming in sql you need to "stop thinking about what you want to do to a row and instead think about what you want to do to a column". Not sure I got the quote exactly right but for whatever reason that flipped a switch in my brain and it all became clear.
I believe Mr. Sheffield was quoting me in that post. Please see my signature line. π
--Jeff Moden
August 8, 2013 at 9:33 pm
Koen Verbeeck (8/8/2013)
Sean Lange (8/8/2013)
Koen Verbeeck (8/8/2013)
Jeff Moden (8/8/2013)
It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.Hmmm, this might be the language gap. I was being affirmative.
You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?
Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".
English: sense it makes none.
Must be because I obviously took it as the negative. BWAAA-HAAA!!!! No wonder people of different languages go to war over dumb things... they were saying the same thing, took it the wrong way, and decided to fight about it instead of talk about it. There are even "language gaps" right here in the U.S.A. In the mid west, if someone were to say "I have a silver colored truck", I would respond, "So do I" because I also have a silver colored truck. In Rhode Island (part of the north east or "New England" states), someone else would say "So don't I" if they also have a silver colored truck and I just don't get that.
--Jeff Moden
August 9, 2013 at 12:14 am
Jeff Moden (8/8/2013)
There are even "language gaps" right here in the U.S.A. In the mid west, if someone were to say "I have a silver colored truck", I would respond, "So do I" because I also have a silver colored truck. In Rhode Island (part of the north east or "New England" states), someone else would say "So don't I" if they also have a silver colored truck and I just don't get that.
That's just plain wrong π
I think they are playing with your head. π
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2013 at 12:19 am
Another one of those A-HA moments, was when I discovered you could select a block of text in SSMS while holding ALT (which is also possible in other Microsoft products).
Really useful for creating update scripts where the left hand and right hand are the same (for example when I update a dimension/fact table using the staging table):
UPDATE dest
SET
SK_ControlPanel_Status = tmp.SK_ControlPanel_Status
,SK_ControlPanel_Nihil = tmp.SK_ControlPanel_Nihil
,SK_Date_Expected = tmp.SK_Date_Expected
,SK_Date_Received = tmp.SK_Date_Received
,SK_Date_LastUpdate = tmp.SK_Date_LastUpdate
,Frequency = tmp.Frequency
,User_LastUpdate = tmp.User_LastUpdate
,UserIDResp = tmp.UserIDResp
,Deadline = tmp.Deadline
,Date_First_Received = tmp.Date_First_Received
,Date_Last_Received = tmp.Date_Last_Received
,Date_Last_Validated = tmp.Date_Last_Validated
,[Validation_Status] = tmp.[Validation_Status]
,[Validation_Rule] = tmp.[Validation_Rule]
,[Valid] = tmp.[Valid]
,[Job_ID] = tmp.[Job_ID]
,[Date_First_Imported] = tmp.[Date_First_Imported]
,[Date_Last_Imported] = tmp.[Date_Last_Imported]
,[Date_Loaded] = tmp.[Date_Loaded]
,[Date_Data_Fixed] = tmp.[Date_Data_Fixed]
,[Weeks_Missing] = tmp.[Weeks_Missing]
,[Cnt_Initial] = tmp.[Cnt_Initial]
,[Cnt_Valid] = tmp.[Cnt_Valid]
,[Cnt_Error] = tmp.[Cnt_Error]
,[Cnt_Nihil] = tmp.[Cnt_Nihil]
,[Cnt_Pending] = tmp.[Cnt_Pending]
,[Cnt_Waiting] = tmp.[Cnt_Waiting]
,[Cnt_Warning] = tmp.[Cnt_Warning]
,[Cnt_Fixed] = tmp.[Cnt_Fixed]
,[Cnt_NewVersion] = tmp.[Cnt_NewVersion]
,UpdateCtr = dest.UpdateCtr + 1
,ModifiedOn = tmp.ModifiedOn
,ModifiedBy = tmp.ModifiedBy
,RowChangeReason = tmp.RowChangeReason
FROM
dbo.FactControlPanel dest
INNER JOIN
stagin.FactControlPanel tmp
ON dest.SK_FactControlPanel = tmp.SK_FactControlPanel
I just script out the select statement in SSMS, select all the columns while holding alt and paste them into the script. Saves me quite some time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 9, 2013 at 7:11 am
Jeff Moden (8/8/2013)
Sean Lange (8/8/2013)
I can still remember one of those Eureka moments. I was reading a post from Wayne Sheffield and he explained that in order to understand set programming in sql you need to "stop thinking about what you want to do to a row and instead think about what you want to do to a column". Not sure I got the quote exactly right but for whatever reason that flipped a switch in my brain and it all became clear.I believe Mr. Sheffield was quoting me in that post. Please see my signature line. π
Or maybe I just gave the credit to the wrong person. It was several years ago so I very well have remembered it wrong. It is certainly possible that it was just from reading your signature. Regardless of where I first I heard it, the concept made a huge impact on me. So a big thanks Jeff!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 β Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2013 at 7:27 am
Koen Verbeeck (8/9/2013)
Another one of those A-HA moments, was when I discovered you could select a block of text in SSMS while holding ALT (which is also possible in other Microsoft products).
Really.....Used it in Word, but not in SSMS.
Works in Visual Studio 2008, 2010, 2012 as well.
Thanks,
Anton
August 9, 2013 at 7:28 am
Sean Lange (8/8/2013)
This whole English thing is the reason we American's don't know any other languages. All the others have actual rules that you follow all of the time. In English our rules are more like guidelines. We follow them most of the time, except for all the exceptions. It takes more than a lifetime to master such a complex and loose set of rules. We just don't have time to learn another language, and we would hate to realize that the other languages make far more sense than our own. :hehe:
It's not yours - it's ours! We don't even charge rent!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 9, 2013 at 7:34 am
Being mostly self taught in SQL Server, it has been more of a gradual slide into understanding, but I guess if I had to choose something it would come from this list:
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
August 9, 2013 at 8:28 am
Jeff Moden (8/8/2013)
Koen Verbeeck (8/8/2013)
Sean Lange (8/8/2013)
Koen Verbeeck (8/8/2013)
Jeff Moden (8/8/2013)
It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.Hmmm, this might be the language gap. I was being affirmative.
You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?
Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".
English: sense it makes none.
Must be because I obviously took it as the negative. BWAAA-HAAA!!!! No wonder people of different languages go to war over dumb things... they were saying the same thing, took it the wrong way, and decided to fight about it instead of talk about it. There are even "language gaps" right here in the U.S.A. In the mid west, if someone were to say "I have a silver colored truck", I would respond, "So do I" because I also have a silver colored truck. In Rhode Island (part of the north east or "New England" states), someone else would say "So don't I" if they also have a silver colored truck and I just don't get that.
I was very confused when I heard my cousin from Texas say "I'll carry you down to the grocery store", meaning that she would walk with me to the grocery store.
I'm not sure what she would say if she was actually going to carry me.
August 9, 2013 at 11:44 am
Sean Lange (8/9/2013)
Jeff Moden (8/8/2013)
Sean Lange (8/8/2013)
I can still remember one of those Eureka moments. I was reading a post from Wayne Sheffield and he explained that in order to understand set programming in sql you need to "stop thinking about what you want to do to a row and instead think about what you want to do to a column". Not sure I got the quote exactly right but for whatever reason that flipped a switch in my brain and it all became clear.I believe Mr. Sheffield was quoting me in that post. Please see my signature line. π
Or maybe I just gave the credit to the wrong person. It was several years ago so I very well have remembered it wrong. It is certainly possible that it was just from reading your signature. Regardless of where I first I heard it, the concept made a huge impact on me. So a big thanks Jeff!!!
Heh... it was actually a huge Eureka moment for me, as well. It definitely made thinking in sets easier.
--Jeff Moden
August 9, 2013 at 11:54 am
Koen Verbeeck (8/8/2013)
Jeff Moden (8/8/2013)
Koen Verbeeck (8/8/2013)
I think one of my first "eureka" moment - as Jeff calls it - was when I read Jeff's article about the Tally table π The "Numbers" or "Tally" Table: What it is and how it replaces a loop.[/url]Before that I used to create date dimensions with a WHILE loop, and I was so proud I didn't use a cursor. Ahem... :blush: Now I create my date dimensions in just a few milliseconds.
Another one was when I read another article by Jeff, called REPLACE Multiple Spaces with One[/url], which presents a really clever way to clean-up your data without nesting endlessly replace statements (it can replace any number of spaces with 3 REPLACE functions).
Regarding SSIS, which I do most of the time, this little nugget by Jamie Thomson really rocked my brain: FileNameColumnName property, Flat File Source Adapter. Simple, but effective.
Thanks for the feedback on thos, Koen. I really appreciate it. Unfortunately, I'm not always right.
Go back and read that article again the "Replace Multiple Spaces" thing, again. As so often happens, someone in the discussion brought up the nested replaces using a slightly different method and, as much as I hate to admit it, it's an order of magnitude faster than the method I came up with. IIRC, I provide a link to the post in the updated prologue of the article.
You've just gotta love this community. An article get's people thinking and then pure magic comes out of the discussion. It doesn't get any better than that. π
Indeed it doesn't π
I remember reading the long discussion about the splitter functions you wrote and all the alternatives. Good stuff.
I speak Ohio English and I understood that it meant "I agree - it doesn't get any better".
August 9, 2013 at 3:21 pm
Koen Verbeeck (8/8/2013)
Sean Lange (8/8/2013)
Koen Verbeeck (8/8/2013)
Jeff Moden (8/8/2013)
It doesn't? I just called the article up and here's the Prologue that I "remembered" adding to the article.Hmmm, this might be the language gap. I was being affirmative.
You say: "it doesn't get better ...", I say: "yes, it doesn't". Doesn't it work that way?
Koen the way you said it seems like it would be correct but yet again English has to be a bit strange. We would say "No, it doesn't".
English: sense it makes none.
It's better if you combine the two nouns, which of course forces you to change the word order. Then you get
English: it makes nonsense
which is a very accurate reflection of reality. Just change the verb from "makes" to "is" and the result is perfect.
Tom
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply