January 31, 2018 at 5:19 pm
Jeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
January 31, 2018 at 7:35 pm
Darko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2018 at 11:10 pm
Jeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Agree to disagree. π
February 1, 2018 at 1:09 pm
Darko Martinovic - Wednesday, January 31, 2018 1:27 PMBut main point is that usually customer do not like to enter calendar and expect some kind of automatization.
My experiences been that users have no choice about entering calendar data by hand. Holidays and company policies are too hard to be done with computations. I always ask people if they know the computations for figuring out Easter. I then told her story. I had about working with two Orthodox programmers, who got their dates for Easters at their church. Sometimes the Catholic Easter and the Orthodox Easter match; sometimes they don't. Some holidays and business days are determined by law or events, such as natural disasters. I found the best way to fill out holidays and other calendar table information is to go down to the accounting department (remember the Dilbert cartoons about the the trolls and accounting?) And ask them to provide information from their spreadsheets. This will make sure that the business is working on one and only one calendar.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 1, 2018 at 1:12 pm
Darko Martinovic - Wednesday, January 31, 2018 11:10 PMJeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Agree to disagree. π
Heh... not going to let you off the hook quite so easily on that one, Darko. The code is a nest of RBAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2018 at 12:47 am
jcelko212 32090 - Thursday, February 1, 2018 1:08 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMBut main point is that usually customer do not like to enter calendar and expect some kind of automatization.
My experiences been that users have no choice about entering calendar data by hand. Holidays and company policies are too hard to be done with computations. I always ask people if they know the computations for figuring out Easter. I then told her story. I had about working with two Orthodox programmers, who got their dates for Easters at their church. Sometimes the Catholic Easter and the Orthodox Easter match; sometimes they don't. Some holidays and business days are determined by law or events, such as natural disasters. I found the best way to fill out holidays and other calendar table information is to go down to the accounting department (remember the Dilbert cartoons about the the trolls and accounting?) And ask them to provide information from their spreadsheets. This will make sure that the business is working on one and only one calendar.
Yes, I share the same experience. In some situations customers appreciate if you help them to determine Easter or Corpus Christi day. In some companies the calendar table is based per employee or per cost centre. So, there are various situations and various experiences.
February 2, 2018 at 12:51 am
Jeff Moden - Thursday, February 1, 2018 1:12 PMDarko Martinovic - Wednesday, January 31, 2018 11:10 PMJeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Agree to disagree. π
Heh... not going to let you off the hook quite so easily on that one, Darko. The code is a nest of RBAR.
Have a Nice Day.π
February 2, 2018 at 6:39 am
Darko Martinovic - Friday, February 2, 2018 12:51 AMJeff Moden - Thursday, February 1, 2018 1:12 PMDarko Martinovic - Wednesday, January 31, 2018 11:10 PMJeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Agree to disagree. π
Heh... not going to let you off the hook quite so easily on that one, Darko. The code is a nest of RBAR.
Have a Nice Day.π
Heh... tell your customers that when they try to join to the result of your code. π
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2018 at 7:13 am
Jeff Moden - Friday, February 2, 2018 6:39 AMDarko Martinovic - Friday, February 2, 2018 12:51 AMJeff Moden - Thursday, February 1, 2018 1:12 PMDarko Martinovic - Wednesday, January 31, 2018 11:10 PMJeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Agree to disagree. π
Heh... not going to let you off the hook quite so easily on that one, Darko. The code is a nest of RBAR.
Have a Nice Day.π
Heh... tell your customers that when they try to join to the result of your code. π
Jeff, there is no joins at all. It is tool which help in filling physical tables. Please, provide your solution to determine Easter day, generate holidays in your country etc.
I will be glad to review it.
All you are talking about is already written in the script comment. I pointed out down bellow.
------------------------------------------------
Comments about MTVF
https://blogs.msdn.microsoft.com/psssql/2010/10/28/query-performance-and-multi-statement-table-valued-functions/
If you don’t plan to join a multi-statement TVF with other tables, you are OK because the low cardinality estimate
doesn’t matter.
If you know that your multi-statement TVF will always return small number of rows, you are OK as well.
If you anticipate large number of rows will result from executing the multi-statement TVF and you will need to join
this TVF with other tables, consider putting the results from the TVF to a temp table and then join
with the temp table.
------------------------------------------------
February 2, 2018 at 8:48 am
Darko Martinovic - Friday, February 2, 2018 7:13 AMJeff Moden - Friday, February 2, 2018 6:39 AMDarko Martinovic - Friday, February 2, 2018 12:51 AMJeff Moden - Thursday, February 1, 2018 1:12 PMDarko Martinovic - Wednesday, January 31, 2018 11:10 PMJeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Agree to disagree. π
Heh... not going to let you off the hook quite so easily on that one, Darko. The code is a nest of RBAR.
Have a Nice Day.π
Heh... tell your customers that when they try to join to the result of your code. π
Jeff, there is no joins at all. It is tool which help in filling physical tables. Please, provide your solution to determine Easter day, generate holidays in your country etc.
I will be glad to review it.
All you are talking about is already written in the script comment. I pointed out down bellow.
------------------------------------------------
Comments about MTVF
https://blogs.msdn.microsoft.com/psssql/2010/10/28/query-performance-and-multi-statement-table-valued-functions/
If you don’t plan to join a multi-statement TVF with other tables, you are OK because the low cardinality estimate
doesn’t matter.
If you know that your multi-statement TVF will always return small number of rows, you are OK as well.
If you anticipate large number of rows will result from executing the multi-statement TVF and you will need to join
this TVF with other tables, consider putting the results from the TVF to a temp table and then join
with the temp table.
------------------------------------------------
What I'm getting at is that you can't actually control what the customer uses the MTVF for. Unless I'm misinterpreting something you previous stated, you also claim that some customers don't want a physical table and that's a part of the reason you wrote the code in such a fashion. That strongly implies that you condone the use of the code to create a derived table in the form of an MTVF and if someone joins a large table to it, performance is going to suffer greatly.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2018 at 3:56 pm
Jeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
I've used functions before, I run one that we got from a vendor that is multiline, literally character by character (ie., much worse than row by row) and paid a nice stack of $ to have it and avoid the work of writing one. Its performance was a concern of mine, but its use case is applicable for its design and I know I would have struggled with a set based design and there were no complaints so theres that. Its rare that I never read about unknown code before using it, so I think simply noting the nature of the code is enough here. If someone starts introducing layers with set based code then with any experience they'll know to troubleshoot when things go south. With nested procedures, you already get the invocation penalties anyways.
In this case, the comments practically bit us on the nose for folks who cared enough to look. I'm not going to disagree with the multi line functions disadvantages but in this case I think the penalty should be understood especially since Darko took the trouble to understand and write it up in the function's code. I think it would be an adequate source for populating calendars but I might have to fix another cup of coffee while I'm waiting if my calendar table were really big.
Code reuse in SQL is notoriously bad anyways. We always caution against gratuitiously nesting views for instance because even with set based code, sometimes the planner isn't up to it and besides this function literally discusses it in the comments. If we wouldn't stack views excessively, I would hope we would understand to not just include multi line functions everywhere without acknowledging any subsequent disadvantages of such. Obviously this is sort of a "best practice" but even best practices are despised here so there it is LOL
A set based calendar would be interesting but I still think I'd probably use it to populate a calendar table anyways, that seems to be a popular paradigm when dealing with this sort of thing.
PS speaking of code, is this forum sucking hard or what????
February 8, 2018 at 9:21 pm
patrickmcginnis59 10839 - Thursday, February 8, 2018 3:56 PMJeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Yeah gotta side with Darko on this one, I think things were covered with the comments. I'm not so sure I would use this as a source for a giant data set as seems to be the major concern and even then advice was included in the code on that. If people run code from the internet and TLDR the comments then well, let them get their just rewards LOLI've used functions before, I run one that we got from a vendor that is multiline, literally character by character (ie., much worse than row by row) and paid a nice stack of $ to have it and avoid the work of writing one. Its performance was a concern of mine, but its use case is applicable for its design and I know I would have struggled with a set based design and there were no complaints so theres that. Its rare that I never read about unknown code before using it, so I think simply noting the nature of the code is enough here. If someone starts introducing layers with set based code then with any experience they'll know to troubleshoot when things go south. With nested procedures, you already get the invocation penalties anyways.
In this case, the comments practically bit us on the nose for folks who cared enough to look. I'm not going to disagree with the multi line functions disadvantages but in this case I think the penalty should be understood especially since Darko took the trouble to understand and write it up in the function's code. I think it would be an adequate source for populating calendars but I might have to fix another cup of coffee while I'm waiting if my calendar table were really big.
Code reuse in SQL is notoriously bad anyways. We always caution against gratuitiously nesting views for instance because even with set based code, sometimes the planner isn't up to it and besides this function literally discusses it in the comments. If we wouldn't stack views excessively, I would hope we would understand to not just include multi line functions everywhere without acknowledging any subsequent disadvantages of such. Obviously this is sort of a "best practice" but even best practices are despised here so there it is LOL
A set based calendar would be interesting but I still think I'd probably use it to populate a calendar table anyways, that seems to be a popular paradigm when dealing with this sort of thing.
PS speaking of code, is this forum sucking hard or what????
That's all well and good but there shouldn't be such a struggle with the development of code to build even a complex Calendar table. I can't side with you or Darko on this because, as you say, someone in a pinch may, in fact, do a TLDR on the comments and implement the code for something that it was never written to support. If it had been written as a stored procedure instead of a function, we wouldn't be having this conversation.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2018 at 9:31 pm
And, yeah... agreed. Very difficult to post good looking code directly.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2018 at 6:30 am
Jeff Moden - Thursday, February 8, 2018 9:21 PMpatrickmcginnis59 10839 - Thursday, February 8, 2018 3:56 PMJeff Moden - Wednesday, January 31, 2018 7:35 PMDarko Martinovic - Wednesday, January 31, 2018 5:19 PMJeff Moden - Wednesday, January 31, 2018 4:11 PMDarko Martinovic - Wednesday, January 31, 2018 2:25 PMJeff Moden - Wednesday, January 31, 2018 1:35 PMDarko Martinovic - Wednesday, January 31, 2018 1:27 PMI was wondering why people are not so enthusiastic with my solution
'Building calendar table using MTVF’
http://www.sqlservercentral.com/scripts/T-SQL/153468/This is basically modified Bob's solution as Steve mentioned earlier.
Maybe the reason is that holidays are not generated for USA, rather than Croatia.But main point is that usually customer do not like to enter calendar and expect some kind of automatization.
Probably because it's an mTVF rather than an iTVF. I've not tested your function but mTVFs are notoriously slow compared to iTVFs.
It seems that you didn't read my comments. π
If you're talking about any comments in the code then correct. TLDR; You should point them out. π If you mean the comment about customers not liking a physical Calendar Table in their database because they would have to maintain it, then it's time to educate the customer and make it easy for them to maintain otherwise it could prove useless because many companies change holidays from year to year. Either way, no comment will change the fact that the code is an mTVF that's dependent on many Scalar Functions.
MTVF do not exclude physical tables, rather it helps to fill them out. It is just utility which replace .Net coding, or error prone data entry. π
So what note were you speaking of or was that the note?
Also, I wouldn't put such an mTVF on a customer's server for fear that someone that's unaware might use it as a derived table or CROSS APPLY in a query and then join to it. I also don't know of anyone that would actually try to manually input a calendar table but, yes, I agree... almost any method is better than trying to create one manually.
Yeah gotta side with Darko on this one, I think things were covered with the comments. I'm not so sure I would use this as a source for a giant data set as seems to be the major concern and even then advice was included in the code on that. If people run code from the internet and TLDR the comments then well, let them get their just rewards LOLI've used functions before, I run one that we got from a vendor that is multiline, literally character by character (ie., much worse than row by row) and paid a nice stack of $ to have it and avoid the work of writing one. Its performance was a concern of mine, but its use case is applicable for its design and I know I would have struggled with a set based design and there were no complaints so theres that. Its rare that I never read about unknown code before using it, so I think simply noting the nature of the code is enough here. If someone starts introducing layers with set based code then with any experience they'll know to troubleshoot when things go south. With nested procedures, you already get the invocation penalties anyways.
In this case, the comments practically bit us on the nose for folks who cared enough to look. I'm not going to disagree with the multi line functions disadvantages but in this case I think the penalty should be understood especially since Darko took the trouble to understand and write it up in the function's code. I think it would be an adequate source for populating calendars but I might have to fix another cup of coffee while I'm waiting if my calendar table were really big.
Code reuse in SQL is notoriously bad anyways. We always caution against gratuitiously nesting views for instance because even with set based code, sometimes the planner isn't up to it and besides this function literally discusses it in the comments. If we wouldn't stack views excessively, I would hope we would understand to not just include multi line functions everywhere without acknowledging any subsequent disadvantages of such. Obviously this is sort of a "best practice" but even best practices are despised here so there it is LOL
A set based calendar would be interesting but I still think I'd probably use it to populate a calendar table anyways, that seems to be a popular paradigm when dealing with this sort of thing.
PS speaking of code, is this forum sucking hard or what????
That's all well and good but there shouldn't be such a struggle with the development of code to build even a complex Calendar table. I can't side with you or Darko on this because, as you say, someone in a pinch may, in fact, do a TLDR on the comments and implement the code for something that it was never written to support. If it had been written as a stored procedure instead of a function, we wouldn't be having this conversation.
I'm not saying its not a bad thing for people running wild on company's codebases and databases. Its a bad thing. I'm also not saying that undisciplined actions or ignoring best practices is undesirable, because its very very undesirable. Heck I'm not saying it doesn't happen either, but bad moves should have consequences.
February 9, 2018 at 6:56 am
patrickmcginnis59 10839 - Friday, February 9, 2018 6:30 AMHeck I'm not saying it doesn't happen either, but bad moves should have consequences.
That's precisely why I'm pinging on Darko a bit. π
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply