January 31, 2015 at 2:24 pm
Don't know where to start, possibly EF choice of geography as varbinary(max) or XML as nvarchar(max). Other peculiarities would be Currency Exchange Rates as int, Social Security Number as datetime, phone number as decimal with the fraction as the extension, ISO-3166-2 and ISO-4217 as nvarchar(max), email addresses as varchar(25), dates as varbinary etc.. And of course not to mention any default conversion from Excel or Access!
😎
January 31, 2015 at 2:59 pm
Eirikur Eiriksson (1/31/2015)
...phone number as decimal with the fraction as the extension...
You have got to be kidding me!!! :w00t:
January 31, 2015 at 3:06 pm
Ha! - store *every* column as VARBINARY(max) and never worry about data types again!
January 31, 2015 at 3:29 pm
I remember the pain of working with a system where a developer had chosen the TIME data type to store the number of hours worked in a day. Anyone want to figure out how you add two TIME columns together? I'll wait…
Okay, at risk of noob humiliation, why not store these as a TIME data type? Doesn't seem too difficult to calculate to me:
[font="Courier New"]-- Set up a couple times to work with...
DECLARE @Start AS TIME = '08:05:00 AM', @End AS TIME = '01:22:00 PM';
-- Find the number of hours worked (CASTing DATEDIFF int output data type)...
SELECT CAST(DATEDIFF(MINUTE,@Start,@End) AS NUMERIC)/60 AS 'HoursWorked';[/font]
What's more, you'd want to convert this to a numeric format for future calculations, such as HoursWorked * PayRate = PaycheckAmount
Perhaps I'm not understanding the way you worded your statement correctly?
January 31, 2015 at 3:34 pm
Andy Hogg (1/31/2015)
Eirikur Eiriksson (1/31/2015)
...phone number as decimal with the fraction as the extension...You have got to be kidding me!!! :w00t:
Just like "The Evil That Men Do", but this time there's no Charles Bronson...
😎
January 31, 2015 at 3:48 pm
jwiseh (1/31/2015)
I remember the pain of working with a system where a developer had chosen the TIME data type to store the number of hours worked in a day. Anyone want to figure out how you add two TIME columns together? I'll wait…
Okay, at risk of noob humiliation, why not store these as a TIME data type? Doesn't seem too difficult to calculate to me:
[font="Courier New"]-- Set up a couple times to work with...
DECLARE @Start AS TIME = '08:05:00 AM', @End AS TIME = '01:22:00 PM';
-- Find the number of hours worked (CASTing DATEDIFF int output data type)...
SELECT CAST(DATEDIFF(MINUTE,@Start,@End) AS NUMERIC)/60 AS 'HoursWorked';[/font]
What's more, you'd want to convert this to a numeric format for future calculations, such as HoursWorked * PayRate = PaycheckAmount
Perhaps I'm not understanding the way you worded your statement correctly?
Guess this would be fine for a very short lived organism which goes into retirement around 24h of age;-)
😎
January 31, 2015 at 9:40 pm
Jeff, I think we must have run into the same consultants. I had one of my clients hire a data warehouse expert that did not know what a star schema was and refused to index any of his tables because it would only slow his system down. They ended up upgrading their SAN to keep their load process down to under a day. :w00t::rolleyes:
Another one was a RBAR process that walked through 124 million rows with a cursor because he thought a set based operation might misplace data because it was "just too much for the database to keep track of." I kid you not.
February 1, 2015 at 1:41 am
I worked at a place where consultants came up with a design for a high volume OLTP application that would completely replace SQL Server with XML flat files to store all data. They said it was more flexible because you could change the schema at any time.
When they presented this plan to the CTO he looked at them for a few seconds and said "You're fired. Get out of here by the end of the day."
February 1, 2015 at 3:01 am
But before swearing at someone, look at the data.
Quite a while back I looked at a table at a customer site designed by another vendor where a quick select * followed by quick scrolling showed loads of 'True' and 'False' values. I looked at the table definition and saw a meaningless column name followed by varchar(50). I was about to burst out laughing but decided to do a select distinct only to find that it also contained 'Never', 'Sometimes', 'Always', 'Forget it'... and loads of other words and expression. Thus I really burst out laughing.
February 1, 2015 at 9:21 am
How about storing all of the data in XML in a compressed binary string and using and using am image datatype to store it in. To read the data, they had to use a custom function to un-compress the XML string and then parse through that to look up data.
This is one that it took 14 hours to pull reporting data from.
February 1, 2015 at 9:54 am
Eirikur Eiriksson (1/31/2015)
jwiseh (1/31/2015)
I remember the pain of working with a system where a developer had chosen the TIME data type to store the number of hours worked in a day. Anyone want to figure out how you add two TIME columns together? I'll wait…
Okay, at risk of noob humiliation, why not store these as a TIME data type? Doesn't seem too difficult to calculate to me:
[font="Courier New"]-- Set up a couple times to work with...
DECLARE @Start AS TIME = '08:05:00 AM', @End AS TIME = '01:22:00 PM';
-- Find the number of hours worked (CASTing DATEDIFF int output data type)...
SELECT CAST(DATEDIFF(MINUTE,@Start,@End) AS NUMERIC)/60 AS 'HoursWorked';[/font]
What's more, you'd want to convert this to a numeric format for future calculations, such as HoursWorked * PayRate = PaycheckAmount
Perhaps I'm not understanding the way you worded your statement correctly?
Guess this would be fine for a very short lived organism which goes into retirement around 24h of age;-)
😎
@jwiseh,
To add a bit more to that, Gail wasn't talking about the Start and End times. She was talking about the result of the formula that you used to calculate the hours being stored as a Time datatype.
To further clarify my take on what you have, there's a problem with having start and end times as just TIMEs. If someone starts at 10PM and finishes at 6AM the next morning, then you have to go through the gyration of detecting if the end TIME is less than the start TIME to make the realization that a day has passed. Of course, that won't work for greater than 24 hours and it won't work if someone works from 8AM one day to 7AM the next (for example). Having the DATEs stored in separate columns would certainly allow you to get around that problem but it would take more calculations and hasn't saved any on storage space.
To summarize using your example as a starting point, consider the following:
--===== Set up a couple times to work with...
DECLARE @Start DATETIME = '31 Jan 2015 08:05:00 AM'
, @End DATETIME = '01 Feb 2015 01:22:00 PM'
;
--===== Find the number of hours worked (CASTing DATEDIFF int output data type)...
SELECT DATEDIFF(MINUTE,@Start,@End)/60.0 AS 'HoursWorked'
;
That solves the problem of having time periods that cross a date boundary with no extra sweat when the start and end times include both the date and the time. The addition of the ".0" to the number "60" also removes the need for an explicit conversion while others like it to be an explicit conversion because then you don't need to know/guess what the implicit conversion will be.
In any case, if you were to store the result of the formula in a column, you wouldn't store it as a TIME datatype or even a DATETIME datatype.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2015 at 10:04 am
jarick 15608 (1/31/2015)
Jeff, I think we must have run into the same consultants. I had one of my clients hire a data warehouse expert that did not know what a star schema was and refused to index any of his tables because it would only slow his system down. They ended up upgrading their SAN to keep their load process down to under a day. :w00t::rolleyes:Another one was a RBAR process that walked through 124 million rows with a cursor because he thought a set based operation might misplace data because it was "just too much for the database to keep track of." I kid you not.
I'd laugh at all of that if it weren't so very common. In fact, it seems to be the norm rather than the exception. I just don't understand how such companies or individuals survive. I think it truly ironic that those same people blast us for being "too specialized", yet it's often their own "specialization" that prevents them from doing things right.
As "Red" Adair has been quoted as saying, "If you think it's expensive to hire a professional, wait until you hire an amateur".
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2015 at 10:14 am
Michael Valentine Jones (2/1/2015)
I worked at a place where consultants came up with a design for a high volume OLTP application that would completely replace SQL Server with XML flat files to store all data. They said it was more flexible because you could change the schema at any time.When they presented this plan to the CTO he looked at them for a few seconds and said "You're fired. Get out of here by the end of the day."
<HeadDesk> At least your CTO caught that and stopped it. I'm going through similar (really stupid DB Design) with a 3rd party product that, according to the powers that be, is "desperately needed" and "only this 3rd party company can do it". Unfortunately, that also means that I've been hog-tied by everyone from the Dev Manager up through the GM. I've fired out the strong warnings both in my company and to the 3rd party company but there's an idiot lead "developer" there that has managed to convince everyone that he's following someone's idea of "best practices". He's also the one that designed the tables with no clustered indexes or DRI (heh... in his words, "to make it portable") and thinks it's ok to change column names every time it's used in a different table.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2015 at 10:28 am
jarick 15608 (2/1/2015)
How about storing all of the data in XML in a compressed binary string and using and using am image datatype to store it in. To read the data, they had to use a custom function to un-compress the XML string and then parse through that to look up data.This is one that it took 14 hours to pull reporting data from.
WOW! Seriously? Someone actually did that and someone let them? :sick: Heh... and let me guess... it was a scalar or multi-statement function on top of all that. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2015 at 12:02 pm
Jeff Moden (2/1/2015)
jarick 15608 (2/1/2015)
How about storing all of the data in XML in a compressed binary string and using and using am image datatype to store it in. To read the data, they had to use a custom function to un-compress the XML string and then parse through that to look up data.This is one that it took 14 hours to pull reporting data from.
WOW! Seriously? Someone actually did that and someone let them? :sick: Heh... and let me guess... it was a scalar or multi-statement function on top of all that. :hehe:
Seen this far too often, usually starts with "Code first" ORM.... then Ooops the DB is bloated and then this, ugly every time!
😎
Viewing 15 posts - 16 through 30 (of 121 total)
You must be logged in to reply to this topic. Login to reply