June 10, 2014 at 2:11 pm
Eirikur Eiriksson (6/10/2014)
Somehow I struggle with accepting the justification for zero division used for projection (Riemann) when it counts for a fraction of a permille in terms of application.😎
a fraction of a permille? That's a gross overstatement: "a permille of microscopic bit of a miniscule part of a very small fraction of a permille of a picopermille" would be much more in keeping with reality, albeit somewhat verbose. :hehe: How much do we use SQL for quantum mechanical computations, after all?
Tom
June 10, 2014 at 2:24 pm
TomThomson (6/10/2014)
Eirikur Eiriksson (6/10/2014)
Somehow I struggle with accepting the justification for zero division used for projection (Riemann) when it counts for a fraction of a permille in terms of application.😎
a fraction of a permille? That's a gross overstatement: "a permille of microscopic bit of a miniscule part of a very small fraction of a permille of a picopermille" would be much more in keeping with reality, albeit somewhat verbose. :hehe: How much do we use SQL for quantum mechanical computations, after all?
Guess that is more like a fraction of a figure of a speech:hehe:
😎
June 10, 2014 at 2:50 pm
Sean Lange (6/10/2014)
below86 (6/10/2014)
Having a consistent naming convention for fields. I am as guilty as anybody at not doing this, but it is driving me nuts now. Years ago we were dealing with software that started the field name of with the 'type' of field it was. d = datefield, n = numeric, c = char, and so on. So I got in the habit when I built a table to use d_inserted for the date the record was inserted into the table. Then came along another software that had it more like insert_DT. Lately I've found myself just naming it insert_date. It's really frustrating when the only one to blame is yourself. 🙂Another thing that really stinks of including the datatype in the name is when the datatype needs to change. For example what do you do if you have a column named nProductID and business makes some changes that forces you to allow characters. Now you either have to update every single line of code or deal with the knowledge that your prefix no longer matches the actual datatype. UGH!!!
As Eric Cartman would add: "The bast*rds!" 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 6:02 am
Koen Verbeeck (6/10/2014)
Sean Lange (6/10/2014)
below86 (6/10/2014)
Having a consistent naming convention for fields. I am as guilty as anybody at not doing this, but it is driving me nuts now. Years ago we were dealing with software that started the field name of with the 'type' of field it was. d = datefield, n = numeric, c = char, and so on. So I got in the habit when I built a table to use d_inserted for the date the record was inserted into the table. Then came along another software that had it more like insert_DT. Lately I've found myself just naming it insert_date. It's really frustrating when the only one to blame is yourself. 🙂Another thing that really stinks of including the datatype in the name is when the datatype needs to change. For example what do you do if you have a column named nProductID and business makes some changes that forces you to allow characters. Now you either have to update every single line of code or deal with the knowledge that your prefix no longer matches the actual datatype. UGH!!!
As Eric Cartman would add: "The bast*rds!" 😀
I so very much agree with the naming conventions. Don't use Hungarian notation (in any form) when naming tables, columns, functions, etc. Local variables are one thing, but database objects are another.
BTW, I believe it was Kyle who said "You bast*rds" whenever someone killed Kenny. Eric's notable quote was "Screw you guys...I'm going home." 😀
June 11, 2014 at 6:09 am
Ed Wagner (6/11/2014)
BTW, I believe it was Kyle who said "You bast*rds" whenever someone killed Kenny. Eric's notable quote was "Screw you guys...I'm going home." 😀
Ah you're right. Shame on me.
As punishment, I'll rewatch the first 10 season tonight 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 12, 2014 at 3:40 am
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.
😀
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
June 12, 2014 at 5:39 am
Andrew Kernodle (6/9/2014)
andrew gothard (6/9/2014)
Andrew Kernodle (6/5/2014)
Argh. I too suffer under the burden of working with code littered with NOLOCK :(.The vendor software we use at my current company has NOLOCK all over the place, however it can manage it. INSERTs, UPDATEs, DELETEs... Who cares, throw NOLOCK on all the joins!
Oh, so as well as unreliable queries, they're happy about data corruption as well? http://support.microsoft.com/kb/2878968
Unreliable queries? Data corruption? The vendor's never seen either of those happen in their test environment (which, actually, they don't have), so it's not a problem! :-D.
Seriously, they can feign ignorance (or is it feigning? It could be genuine!) to any problem our company raises, and management just shrugs and accepts the idea that, when the vendor says they don't know what their coding is doing, we're pretty much stuck. It helps the vendor immeasurably that they're a corporation that a few government entities have purchased from, so they don't have to worry about losing customers. They just submit the lowest bid each year, which is still plenty sizable, and onward they go.
EDIT: Ack, and reading that KB article, it seems that problem was fixed in a CU for SQL Server 2008 R2 SP2. We're stuck on SP1 by the vendor's service agreement. And yes, it makes absolutely no sense for them to constrain the SP level we're on, especially since their coding uses no version-specific functionality besides TEXT-type columns, but that certainly doesn't slow them down 😛
Hell, we've got vendors that only support 2000 compatability. Which is interesting, given 2008R2 is out of mainstream support on the 8th of next month, and this is the last version that supports 2000 compat.
Soooo - if there's a problem, MS support's only offering is "Upgrade, or we're not speaking to you". However, on the vendor side "Upgrade and you've breeched your support contract". Excellent.
One vendor, in spite of a support ticket being raised, won't even tell us which compat. levels are supported (Mind, they basically refuse to support their garbage product anyway), others didn't even realise 2012 + does't support 2000 compatability.
Big peeve for me at the minute.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
June 12, 2014 at 12:18 pm
Yep, we're in the same boat as far as compatibility and versions go. We're technically not supposed to be using 2008R2 by the vendor's contract (2008 is the latest version supported by them), but they mistakenly think that 2008 R2 is just a service pack level, so... :-P. No plans on the vendor's part to move beyond 2008, though.
Also, their queries are fun, fun little things. The attached execution plan is one I pulled from some casual observation of the system. It may induce a feeling of overwhelming despair to see the first few operations :w00t:
- 😀
June 12, 2014 at 12:37 pm
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.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 12, 2014 at 12:39 pm
Andrew Kernodle (6/12/2014)
Yep, we're in the same boat as far as compatibility and versions go. We're technically not supposed to be using 2008R2 by the vendor's contract (2008 is the latest version supported by them), but they mistakenly think that 2008 R2 is just a service pack level, so... :-P. No plans on the vendor's part to move beyond 2008, though.Also, their queries are fun, fun little things. The attached execution plan is one I pulled from some casual observation of the system. It may induce a feeling of overwhelming despair to see the first few operations :w00t:
Why is that so bad? It uses mostly index seeks and an ocasional index scan :hehe:
Who cares if the table is hitted zillions of times?
June 12, 2014 at 1:35 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: 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. 😉
_______________________________________________________________
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 12, 2014 at 2:00 pm
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.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
June 12, 2014 at 2:28 pm
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.
_______________________________________________________________
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 12, 2014 at 2:29 pm
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:
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2014 at 2:43 pm
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.
I was hoping you would way in on this Sean, I think we had a similar discussion when I first heard that this might be done on another post. I would love to have that article to show them the error of their ways.
What I get from others here is "You just used that dateID field to join to the tblDate table to get the real date." I'm like "are you F'in kidding me?" I was also told we should use the views that pull the data together for us instead of hitting these tables. And I said I only need the data for the date of 05/31/2014, the view is going to try an put all the data together for all the dates before giving me what I want. It may be quick now I say, but when we get more data it is going to get slower and slower.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 15 posts - 196 through 210 (of 272 total)
You must be logged in to reply to this topic. Login to reply