June 12, 2014 at 7:17 pm
Heh... I got carried away on another post on the subject of "pet peeves". Thought I'd repost what I wrote there on this thread because it seems appropriate. The original subject was on whether or not folks thought that the use of BEGIN/END was a good thing in stored procs and started into things like Try/Catch, etc. Here's what I wrote...
Because of today's systems, I can no longer prove it but, on many of the systems I worked on almost 2 decades ago, BEGIN/END caused my high hit ratio procs to run slower than without. With that in mind, I never developed the habit.
My problem of late with it is indentation. I've drilled the idea of proper indentation into the heads of the Developers that I work with along with several other "company standards". One of those standards is also a max line length of 119 characters (stop when the cursor is on 120) and so wasting 4 or 8 characters of every line except the BEGIN/END seems like both a chore and a waste.
As a bit of a sidebar, the 119 character limit is for 2 reasons. The first is that with the Object Explorer open, that's about the most you can see on "average" monitors without having to scroll horizontally. It's also the max number of characters that will fit on a piece of paper in the landscape mode with a half inch left and right margins at 10 pt Courier New (or other mono-spaced font) without wrapping automatically.
Even if we didn't indent for it (Begin/End), it also really ticks me off when I try to run a part of the proc and it says "Error" at the first line because I accidentally included the last line of the proc, which would be the "End;".
As for being more like functions and, therefor, supposedly more standard, I usually try to avoid any type of function that requires the use of BEGIN/END (scalar and mTVFs require it) for reasons of performance. I generally use only iTVFs and iSFs (an iTVF that returns a single value), neither of which will take BEGIN/END.
Shifting back to some of the previous posts on this thread, I always use alias = expression in SELECTs and INSERTs for two reasons. Much like people use BEGIN/END in procs because some functions require it, I've found it real easy to convert and test an UPDATE if the SELECT is written that way. I also write some fairly long formulas a lot and we have column names that are anywhere from 2 characters to a sometimes very high number of characters (sometimes necessary, sometimes not, sometimes bloody ridiculous). As a result, it's easy to "lose" an AS alias in the clutter. With the alias = expression method, I know that all aliases are going to start in the same vertical column and that makes them really easy to find for troubleshooting purposes. People who end up reading my code seem to really like it even if they don't follow the paradigm in their own code.
As Bill Cosby said in his famous "Fat Albert/Buck-Buck" story, "I told you that story so I could tell you this one"... 😛
One of my biggest pet peeves is inconsistency in code style within a proc or other type of SQL module. Oh, sure... I insist that a Developer making a mod to a piece of undocumented code add the "company standard" header and add comments for each Insert, Select, Update, and Delete (a whole 'nuther story) that they touch (more if they have an extra minute or two), but if the majority of the code is written with a certain type of standard alignment and uses AS alias instead of alias = expression, then I tell them to continue the trend. Of course, some of our legacy code was written and modified by many people who either had a standard that sucks or no standard at all and that's when I encourage the Developers to reformat the areas they change and, if they have time, at least reformat some of the ridiculously long lines that are well in excess of the 119 character standard. Of course, any new code modules (including total rewrites) must meet all of the "company standards".
I had folks do the same things in a company that I previously worked at. The existing code was so poorly written it sometimes took a couple of days just to figure out where a change needed to be made in the longer procs (many were 500 to 4000 lines long... 'nuther story there that can be summarized as extremely poor performing, highly resource intensive, "crap code" on steroids). The rule was, "If you have to touch it, you have to fix it" for the shorter code or the sections you worked on for the longer code. It normally only took a Developer 5 or 10 minutes extra each time but after a while, there was less and less to fix, reformat, or document. After just two years, the average rework time dropped from 1-2 work days (for the easy stuff) down to just several minutes. Better than that, the number of reworked-code failures (couldn't tell that a fix in one part of the code usually broke another) dropped from an average of 3 cycles back and forth with QA down to no returns from QA.
As for it taking longer to follow standards, it does take a second or two longer per line when you first start. Once you get used to it, it takes no extra time and, compared to the amount of rework that needed to be done and the time it took to research code to figure out how to modify it, it was definitely worth it by a couple of orders of magnitude. Surprisingly, the overall performance of the code benefited, as well. During the reformatting (as we've done here on SSC more times than not), the Developer would spot an "anomoly" and fix it. I lost count at the old company of how many 8-12 hour "nightly jobs" that would suddenly start running in 10 to 15 minutes. It was amazing to behold the look on the faces of Developers when I told them how well their rework or their new code actually performed in production. I made sure their respective bosses knew, too.
That brings me to my largest pet peeves of all... no "company standards", no peer reviews to enforce the standards, no purposeful in-house training, no thoughtful mentoring by the DBAs, Developers being allowed to promote their own code to production, and no management buy-in to control any of that.
For all you Managers out there, remember just one thing... "If you want it real bad, that's the way you'll get it". :sick: Help your Team make YOU look like a star. Insist on company standards, peer review, in-house training, mentoring by seniors, etc, etc, and provide management buy in for it all. And give them the power to say "Guh!!! No... that ain't right"! It'll take things a little longer to get done until the Team settles into the new groove, but then watch them soar and drag you up with them.
Sorry about the long winded post but I've got more than 40 years of pet peeves and thought I'd share some of them with you. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2014 at 11:49 pm
below86 (6/12/2014)
I'm just looking at our 'new' datawarehouse design, a former DBA helped start. All the table names start with 'tbl'. :w00t:
And views start with vw_? And then suddenly, a view needs to be converted into an actual table. Whoops.
Time to do some refactoring! 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 13, 2014 at 12:04 am
Sean Lange (6/12/2014)
below86 (6/12/2014)
Sean Lange (6/12/2014)
below86 (6/12/2014)
I'm just looking at our 'new' datawarehouse design, a former DBA helped start. All the table names start with 'tbl'. :w00t: This person also had the idea to store the dates as integers, like 20140612, and then join to a 'Date' table with this 'key' to get a real date field.Maybe this is why they are a former DBA. 😉
Not really, I think the pace of change was not quick enough for this individual.
OK I just had a heated debate at break with another member of my team on the use of storing a date as an integer. This person believes that using the integer is quicker than using a date field. He was explaining to me that there are views set up to pull all the data together for a certain group. So all these tables have indexes on the ID fields, I looked at one table and it had 6 fields, 5 of them are ID's. I was trying to explain to him that if I query the view looking for a given date for one of these date fields it returns that no index is going to be used to extract that data. Am I wrong? I thought the indexes are going to help pull the data together for the view, but it has to put all the data together first, then it will look to pull the data I want based on the date I gave it. So no index will be used for that.
You are absolutely correct. Storing dates as anything other than dates is flat out stupid. Where do people come up with the nonsense??? Ask how he is going to date math. He will be forced to wrap those stupid date as int columns in a function (probably convert) which will negate any indexing on said column.
I have recently written a rant about this very topic that I guess I should submit for publication. It comes up around here all the time. I don't get it. I bet this same person would call you a moron for storing an int in a varchar. For some reason people are scared of the datetime datatype in sql. They come up with some very creative ways to support their horrible decision. Stick to your guns on this one. The right way to store ANY data (which happens to include datetime) is in a column with the proper datatype.
People come up with the nonsense because other people - like Kimball - tell them to.
Kimball advocates that every surrogate key should be a meaninglessinteger (which is a good thing). He is against using "smart surrogate keys", keys where there is some meaning baked into the surrogate key. However, he allows one exception: dates. He says it is OK to use something like 20140501 because it is still an integer, the meaning will never change (I think we will probably never change our gregorian calanders?) and it allows for easier debugging.
So most likely, these people just followed the Kimball methodology. I checked the data warehouse of my current project - which was designed by people who are much longer in the BI business than I am - and apparently they also use integer surrogate keys to the date dimension. I can imagine they did it out of conformity: why should one surrogate key suddenly have a different data type than the others?
Regarding the "how do you do date calculations on those fields" argument: you are not supposed to. It is a surrogate key, you are not supposed to do calculations on it, because that goes against the entire philosophy of surrogate keys. You need to join against the date dimension, retrieve the column that has the actual date data type and do your calculations on that one.
So it basically boils down to this: semantics. You guys are correctly arguing a date should not be stored in an integer field. Very much correct, except that it is not a date field, it is a surrogate key. Kimball and others are arguing to store the surrogate keys as an integer, and they allow an exception for date surrogate keys to have it look like a date. But that's all it is: it looks like a date. It isn't one.
However, if your colleague uses that integer field to do date calculations: he is incorrect. Because, as I stated already a few times, it is a surrogate key and no calculations should be performed on it 🙂
A side note: don't forget that maybe that person is designing data warehouses for quite some time. Maybe even in SQL Server 2005, where there was no date data type, only datetime. So an integer makes more sense: only 4 bytes against the 8 bytes of datetime.
Don't be so quick to judge these people as morons, they might have their reasons 😉
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 13, 2014 at 12:38 am
Koen Verbeeck (6/13/2014)
Sean Lange (6/12/2014)
below86 (6/12/2014)
Sean Lange (6/12/2014)
below86 (6/12/2014)
I'm just looking at our 'new' datawarehouse design, a former DBA helped start. All the table names start with 'tbl'. :w00t: This person also had the idea to store the dates as integers, like 20140612, and then join to a 'Date' table with this 'key' to get a real date field.Maybe this is why they are a former DBA. 😉
Not really, I think the pace of change was not quick enough for this individual.
OK I just had a heated debate at break with another member of my team on the use of storing a date as an integer. This person believes that using the integer is quicker than using a date field. He was explaining to me that there are views set up to pull all the data together for a certain group. So all these tables have indexes on the ID fields, I looked at one table and it had 6 fields, 5 of them are ID's. I was trying to explain to him that if I query the view looking for a given date for one of these date fields it returns that no index is going to be used to extract that data. Am I wrong? I thought the indexes are going to help pull the data together for the view, but it has to put all the data together first, then it will look to pull the data I want based on the date I gave it. So no index will be used for that.
You are absolutely correct. Storing dates as anything other than dates is flat out stupid. Where do people come up with the nonsense??? Ask how he is going to date math. He will be forced to wrap those stupid date as int columns in a function (probably convert) which will negate any indexing on said column.
I have recently written a rant about this very topic that I guess I should submit for publication. It comes up around here all the time. I don't get it. I bet this same person would call you a moron for storing an int in a varchar. For some reason people are scared of the datetime datatype in sql. They come up with some very creative ways to support their horrible decision. Stick to your guns on this one. The right way to store ANY data (which happens to include datetime) is in a column with the proper datatype.
People come up with the nonsense because other people - like Kimball - tell them to.
Kimball advocates that every surrogate key should be a meaninglessinteger (which is a good thing). He is against using "smart surrogate keys", keys where there is some meaning baked into the surrogate key. However, he allows one exception: dates. He says it is OK to use something like 20140501 because it is still an integer, the meaning will never change (I think we will probably never change our gregorian calanders?) and it allows for easier debugging.
So most likely, these people just followed the Kimball methodology. I checked the data warehouse of my current project - which was designed by people who are much longer in the BI business than I am - and apparently they also use integer surrogate keys to the date dimension. I can imagine they did it out of conformity: why should one surrogate key suddenly have a different data type than the others?
Regarding the "how do you do date calculations on those fields" argument: you are not supposed to. It is a surrogate key, you are not supposed to do calculations on it, because that goes against the entire philosophy of surrogate keys. You need to join against the date dimension, retrieve the column that has the actual date data type and do your calculations on that one.
So it basically boils down to this: semantics. You guys are correctly arguing a date should not be stored in an integer field. Very much correct, except that it is not a date field, it is a surrogate key. Kimball and others are arguing to store the surrogate keys as an integer, and they allow an exception for date surrogate keys to have it look like a date. But that's all it is: it looks like a date. It isn't one.
However, if your colleague uses that integer field to do date calculations: he is incorrect. Because, as I stated already a few times, it is a surrogate key and no calculations should be performed on it 🙂
A side note: don't forget that maybe that person is designing data warehouses for quite some time. Maybe even in SQL Server 2005, where there was no date data type, only datetime. So an integer makes more sense: only 4 bytes against the 8 bytes of datetime.
Don't be so quick to judge these people as morons, they might have their reasons 😉
+1 😎
June 13, 2014 at 12:56 am
Koen Verbeeck (6/13/2014)
...Don't be so quick to judge these people as morons, they might have their reasons 😉
One extra advantage of the integer date key I forgot:
it is very easy to partition your fact table using this surrogate key.
It may be my inexperience with partitioning, so I'm not sure how well a date data type can be partitioned.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 13, 2014 at 5:06 am
Jeff Moden (6/12/2014)
andrew gothard (6/12/2014)
Jeff Moden (6/6/2014)
A recent post just reminded me of one of my greatest pet peeves... peoplethatwho correct your English, spelling, or punctuation on forum posts. I've also found that they usually do that when they consider it beneath them to defend any point that they've been trying to make or can't actually do so.😀
Oddly enough, both "that" and "who" are acceptable. 😉 I typically use "that" because of the ring-knockers that will beat one to death about "who" and "whom". :sick:
Oh, I know. That's why I just couldn't resist
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
June 13, 2014 at 5:20 am
Jeff Moden (6/12/2014)
Oddly enough, both "that" and "who" are acceptable. 😉 I typically use "that" because of the ring-knockers that will beat one to death about "who" and "whom". :sick:
Those being the posters who make more grammatical and spelling errors in their post than exist in the post they are complaining about. :hehe:
June 13, 2014 at 7:40 am
Koen Verbeeck (6/13/2014)
Sean Lange (6/12/2014)
below86 (6/12/2014)
Sean Lange (6/12/2014)
below86 (6/12/2014)
I'm just looking at our 'new' datawarehouse design, a former DBA helped start. All the table names start with 'tbl'. :w00t: This person also had the idea to store the dates as integers, like 20140612, and then join to a 'Date' table with this 'key' to get a real date field.Maybe this is why they are a former DBA. 😉
Not really, I think the pace of change was not quick enough for this individual.
OK I just had a heated debate at break with another member of my team on the use of storing a date as an integer. This person believes that using the integer is quicker than using a date field. He was explaining to me that there are views set up to pull all the data together for a certain group. So all these tables have indexes on the ID fields, I looked at one table and it had 6 fields, 5 of them are ID's. I was trying to explain to him that if I query the view looking for a given date for one of these date fields it returns that no index is going to be used to extract that data. Am I wrong? I thought the indexes are going to help pull the data together for the view, but it has to put all the data together first, then it will look to pull the data I want based on the date I gave it. So no index will be used for that.
You are absolutely correct. Storing dates as anything other than dates is flat out stupid. Where do people come up with the nonsense??? Ask how he is going to date math. He will be forced to wrap those stupid date as int columns in a function (probably convert) which will negate any indexing on said column.
I have recently written a rant about this very topic that I guess I should submit for publication. It comes up around here all the time. I don't get it. I bet this same person would call you a moron for storing an int in a varchar. For some reason people are scared of the datetime datatype in sql. They come up with some very creative ways to support their horrible decision. Stick to your guns on this one. The right way to store ANY data (which happens to include datetime) is in a column with the proper datatype.
People come up with the nonsense because other people - like Kimball - tell them to.
Kimball advocates that every surrogate key should be a meaninglessinteger (which is a good thing). He is against using "smart surrogate keys", keys where there is some meaning baked into the surrogate key. However, he allows one exception: dates. He says it is OK to use something like 20140501 because it is still an integer, the meaning will never change (I think we will probably never change our gregorian calanders?) and it allows for easier debugging.
So most likely, these people just followed the Kimball methodology. I checked the data warehouse of my current project - which was designed by people who are much longer in the BI business than I am - and apparently they also use integer surrogate keys to the date dimension. I can imagine they did it out of conformity: why should one surrogate key suddenly have a different data type than the others?
Regarding the "how do you do date calculations on those fields" argument: you are not supposed to. It is a surrogate key, you are not supposed to do calculations on it, because that goes against the entire philosophy of surrogate keys. You need to join against the date dimension, retrieve the column that has the actual date data type and do your calculations on that one.
So it basically boils down to this: semantics. You guys are correctly arguing a date should not be stored in an integer field. Very much correct, except that it is not a date field, it is a surrogate key. Kimball and others are arguing to store the surrogate keys as an integer, and they allow an exception for date surrogate keys to have it look like a date. But that's all it is: it looks like a date. It isn't one.
However, if your colleague uses that integer field to do date calculations: he is incorrect. Because, as I stated already a few times, it is a surrogate key and no calculations should be performed on it 🙂
A side note: don't forget that maybe that person is designing data warehouses for quite some time. Maybe even in SQL Server 2005, where there was no date data type, only datetime. So an integer makes more sense: only 4 bytes against the 8 bytes of datetime.
Don't be so quick to judge these people as morons, they might have their reasons 😉
I did not judge these people as morons or even state as such.
The semantics here seem to be a little bit mixed. It seems that some of these integers are dates and others are keys. The keys one I have no issue with because they are keys.
Keep in mind that the discussion between below86 and their coworker is that the coworker has some notion that storing dates as integers makes queries faster.
_______________________________________________________________
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/
June 13, 2014 at 8:19 am
Koen Verbeeck (6/13/2014)
Koen Verbeeck (6/13/2014)
...Don't be so quick to judge these people as morons, they might have their reasons 😉
One extra advantage of the integer date key I forgot:
it is very easy to partition your fact table using this surrogate key.
It may be my inexperience with partitioning, so I'm not sure how well a date data type can be partitioned.
Partitioning on a date data type or datetime data type is extremely fast and easy. It works rather efficiently.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 13, 2014 at 11:02 am
I really don't like integer dates. They're tough to do date math with and they have no built in checks for whether an entry is a legal date or not. For example, it's easy to add a date of 20140229 as an integer and no one would be the wiser. None of the DATE or DATETIME datatypes would allow it.
As for surrogate keys necessarily being totally meaningless, I strongly disagree. What key would anyone use for a Customer table that would be more effective than a simple INTEGER IDENTITY or INTEGER SEQUENCE column?
I guess that brings up another pet peeve of mine. Experts that talk in absolutes in a world where "It Depends" should rule.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 13, 2014 at 11:07 am
Jeff Moden (6/13/2014)
I really don't like integer dates. They're tough to do date math with and they have no built in checks for whether an entry is a legal date or not. For example, it's easy to add a date of 20140229 as an integer and no one would be the wiser. None of the DATE or DATETIME datatypes would allow it.
+10
As for surrogate keys necessarily being totally meaningless, I strongly disagree. What key would anyone use for a Customer table that would be more effective than a simple INTEGER IDENTITY or INTEGER SEQUENCE column?
+100
I guess that brings up another pet peeve of mine. Experts that talk in absolutes in a world where "It Depends" should rule.
There are so few absolutes in the world of SQL Server.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 13, 2014 at 11:17 am
Jeff Moden (6/13/2014)
I really don't like integer dates. They're tough to do date math with and they have no built in checks for whether an entry is a legal date or not. For example, it's easy to add a date of 20140229 as an integer and no one would be the wiser. None of the DATE or DATETIME datatypes would allow it.
I so very much agree. I know we've talked about this, but what is a datetime under the hood? 😉
Jeff Moden (6/13/2014)
As for surrogate keys necessarily being totally meaningless, I strongly disagree. What key would anyone use for a Customer table that would be more effective than a simple INTEGER IDENTITY or INTEGER SEQUENCE column?
I agree with your disagreement. Anything else could change, making it far less ineffective.
Jeff Moden (6/13/2014)
I guess that brings up another pet peeve of mine. Experts that talk in absolutes in a world where "It Depends" should rule.
How very, very true. There are many ways to skin a cat, but some ways are better than others.
June 13, 2014 at 11:51 am
Jeff Moden (6/13/2014)
I guess that brings up another pet peeve of mine. Experts that talk in absolutes in a world where "It Depends" should rule.
Come now, there is at least one absolute in the world of SQL Server...
It's an absolute that the correct answer to every performance tuning, indexing, maintenance strategy and backup strategy question is "It depends!"
:hehe:
A recent peeve of mine...
WHY can't MS come up with a better way to store the duration of Agent jobs? Saving it as an INT? Really? I mean, I can see not using any of the date / date time, but it's a royal PITA if you're trying to average out how long a job has been taking...
June 13, 2014 at 11:57 am
Jeff Moden (6/13/2014)
I really don't like integer dates. They're tough to do date math with and they have no built in checks for whether an entry is a legal date or not. For example, it's easy to add a date of 20140229 as an integer and no one would be the wiser. None of the DATE or DATETIME datatypes would allow it.As for surrogate keys necessarily being totally meaningless, I strongly disagree. What key would anyone use for a Customer table that would be more effective than a simple INTEGER IDENTITY or INTEGER SEQUENCE column?
I guess that brings up another pet peeve of mine. Experts that talk in absolutes in a world where "It Depends" should rule.
I agree on using DATE for dates. Has anyone done any performance testing on joins using DATE vs INTEGER?
Something no one has mentioned is DATETIME in the fact table for situations where it may be important. I have seen people break date and time into different columns. That just makes for horrible coding for queries when you need to select data for a DATETIME range. Example: from 10:00 am on the second day of the month through 6:00 pm yesterday. I believe Kimball called a dimensional column like this with no dimension table a "degenerate" dimension. Of course, you could also have Date and time of day dimensions in addition to the "degenerate" datetime dimension.
June 13, 2014 at 12:07 pm
Michael Valentine Jones (6/13/2014)
Jeff Moden (6/13/2014)
I really don't like integer dates. They're tough to do date math with and they have no built in checks for whether an entry is a legal date or not. For example, it's easy to add a date of 20140229 as an integer and no one would be the wiser. None of the DATE or DATETIME datatypes would allow it.As for surrogate keys necessarily being totally meaningless, I strongly disagree. What key would anyone use for a Customer table that would be more effective than a simple INTEGER IDENTITY or INTEGER SEQUENCE column?
I guess that brings up another pet peeve of mine. Experts that talk in absolutes in a world where "It Depends" should rule.
I agree on using DATE for dates. Has anyone done any performance testing on joins using DATE vs INTEGER?
Do you mean with the date stored as an int or just flat out performance difference between the two?
In the joins on date that I have done, the performance is on par with int.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 211 through 225 (of 271 total)
You must be logged in to reply to this topic. Login to reply