January 22, 2009 at 2:23 pm
GSquared (1/22/2009)
Joe Celko (1/22/2009)
getdate()?? Didn't you mean to say CURRENT_TIMESTAMP? We want to use all the ANSI/ISO stuff that Microsoft will give us instead of UNIX dialect museum exhibits.Nah. That one's not what makes my wife laugh. That's far more important! 🙂
You mean your wife has never gone out on a TIMESTAMP? What does she think of things like "Born-on Dating?" Then again what do I think of such things? I am glad for a perspective like her's, you know the real-life SET logic of "GetDate!"
January 22, 2009 at 2:26 pm
LeeBear35 (1/22/2009)
GSquared (1/22/2009)
Joe Celko (1/22/2009)
getdate()?? Didn't you mean to say CURRENT_TIMESTAMP? We want to use all the ANSI/ISO stuff that Microsoft will give us instead of UNIX dialect museum exhibits.Nah. That one's not what makes my wife laugh. That's far more important! 🙂
You mean your wife has never gone out on a TIMESTAMP? What does she think of things like "Born-on Dating?" Then again what do I think of such things? I am glad for a perspective like her's, you know the real-life SET logic of "GetDate!"
Yep. It's important to remember that GetDate can result in unions, though not legally in "union all"s, and can, at times, produce non-cartesian products!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 2:42 pm
Joe Celko (1/22/2009)
getdate()?? Didn't you mean to say CURRENT_TIMESTAMP? We want to use all the ANSI/ISO stuff that Microsoft will give us instead of UNIX dialect museum exhibits.
No, he said and meant getdate() because this is a MICROSOFT SQL SERVER ONLY FORUM!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 22, 2009 at 2:45 pm
>>Any DBA who thinks an index on a bit field is a good idea.
You have a billion row table, with from 0 to 100 rows at any one time having mybitfield = 1. You routinely run queries with a WHERE clause that is or includes mybitfield = 1. Would you want an index on mybitfield?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 22, 2009 at 4:01 pm
TheSQLGuru (1/22/2009)
>>Any DBA who thinks an index on a bit field is a good idea.You have a billion row table, with from 0 to 100 rows at any one time having mybitfield = 1. You routinely run queries with a WHERE clause that is or includes mybitfield = 1. Would you want an index on mybitfield?
You have a billion rows that are going to have the bit as either 0 or 1, ideally the analyser will skip using the index and result to a table scan because the index sets will be too large. Remember if you have to read the index, then the table you have done two i/o's (actually when you account for paging, it is much more) to get a record. When creating indexes you want to get a value that will get you to a row or smaller set of rows.
January 22, 2009 at 4:46 pm
TheSQLGuru (1/22/2009)
No, he said and meant getdate() because this is a MICROSOFT SQL SERVER ONLY FORUM!
That's why my FoxPro can't Access the Oracle. How can I make any Progress this way? And I thought MySQL skills were good. Where can I get an operating system and a DB 2?
(It's funnier if you read it out loud.) 🙂
ATBCharles Kincaid
January 22, 2009 at 5:01 pm
Charles Kincaid (1/22/2009)
TheSQLGuru (1/22/2009)
No, he said and meant getdate() because this is a MICROSOFT SQL SERVER ONLY FORUM!That's why my FoxPro can't Access the Oracle. How can I make any Progress this way? And I thought MySQL skills were good. Where can I get an operating system and a DB 2?
(It's funnier if you read it out loud.) 🙂
No need to read aloud - it was funny as hell on the screen too!! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 22, 2009 at 5:18 pm
Joe Celko (1/22/2009)
Did you know that CURRENT_TIMESTAMP has worked in SQL Server for several release now? The only excuse for talking like a hillbilly is "job security programming" or ignorance.
1) Yes, I did know that. And I don't care. Did you know that Microsoft has not deprecated getdate(), therefore there is absolutely no reason for myself nor any of the other 99.873% of the Microsoft SQL Server developers out there who ARE NOT CREATING CROSS-PLATFORM CODE to make a shift to CURRENT_TIMESTAMP?
2) I will let the personal attack slide. You are't worth a reply on that one.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 22, 2009 at 7:32 pm
LeeBear35 (1/22/2009)
TheSQLGuru (1/22/2009)
>>Any DBA who thinks an index on a bit field is a good idea.You have a billion row table, with from 0 to 100 rows at any one time having mybitfield = 1. You routinely run queries with a WHERE clause that is or includes mybitfield = 1. Would you want an index on mybitfield?
You have a billion rows that are going to have the bit as either 0 or 1, ideally the analyser will skip using the index and result to a table scan because the index sets will be too large. Remember if you have to read the index, then the table you have done two i/o's (actually when you account for paging, it is much more) to get a record. When creating indexes you want to get a value that will get you to a row or smaller set of rows.
Be sure o read the question. IF I had a table with one billion rows, and ONLY 0 to 100 of those rows had a bit field, called mybitfield, set to 1 and I routinely ran queries that included in the WHERE clause mybitfield = 1, you can bet that an index on that field would be of GREAT benefit. The index would quickly get me to those 0 to 100 records out the total 1 billion records.
January 22, 2009 at 11:25 pm
With SQL Server 2008
CREATE NONCLUSTERED INDEX IX_MyBitField ON MyTable (MyBitField)
WHERE MyBitField = 1
N 56°04'39.16"
E 12°55'05.25"
January 23, 2009 at 12:05 am
TheSQLGuru (1/22/2009)
Joe Celko (1/22/2009)
Did you know that CURRENT_TIMESTAMP has worked in SQL Server for several release now? The only excuse for talking like a hillbilly is "job security programming" or ignorance.1) Yes, I did know that. And I don't care. Did you know that Microsoft has not deprecated getdate(), therefore there is absolutely no reason for myself nor any of the other 99.873% of the Microsoft SQL Server developers out there who ARE NOT CREATING CROSS-PLATFORM CODE to make a shift to CURRENT_TIMESTAMP?
2) I will let the personal attack slide. You are't worth a reply on that one.
As always, there is a proper way and there is a propriatary way.
It sure pays off knowing the proper way, but it is fun to know the propriatary one.
If you don't have a standard "use case" to solve requests, you will end up with hunderds of flavours to accomplish the same thing.
(not only) Juniors will struggle with that so it is prone to errors.
For any company, it is a good investment to have a guideline to apply.
And it is so hard to change a bad habit :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2009 at 6:05 am
Peso (1/22/2009)
With SQL Server 2008CREATE NONCLUSTERED INDEX IX_MyBitField ON MyTable (MyBitField)
WHERE MyBitField = 1
I was wondering if someone would bring up filtered indexes for this scenario. It certainly seems like a good candidate for them to me also.
BTW, this was a near real-world situation. IIRC was actually several million records with several hundred bit=1 rows, but the point was the same.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 23, 2009 at 12:34 pm
I'm just wondering what situation exists where you have 1 billion records with a tiny proportion having a bit field set to 1 AND that field being important enough to have an index on it.
Doesn't imply that the tiny proportion of records should exist in their own table?
On the subject of standards guys, look at the world around you. We have the 3 Abrahamic faiths perpared to commit satanic acts against each other to prove their loyalty to exactly the same God where 99% of the key teachings of their religions agree.
In a world in which such craziness achieves nothing other than to prove that stupidity is the only renewable resource an international standard has been agreed. Don't look a gift horse in the mouth!
If the proprietary and standard way do exactly the same thing with no detriment then you might as well use the standard way.
Ditto ISO standard data. If there is an international standard use it.
If there is no international standard conform to an industry standard
I'm sick of seeing CountryID as an proprietary int field and having external companies kick back because they expect the ISO standard country code.
Once a company grows beyond a certain point it has to talk to the outside world and proprietary stuff just gets in the way.
January 23, 2009 at 12:43 pm
Standards are a good thing. Just keep in mind, they do change over time, and something that is 100% compliant today may be 0% compliant tomorrow.
Take a look at Imperial vs Metric vs SI, for a great example.
If your thermometer says it's 80 degrees out, is it a bit warm, dangerously hot, or insta-death-cold? The "standard" has evolved so much in just a few decades that it makes that much difference.
I had to take a class in Fortran 74 when I was in college in 1988 (required credit). That was "the standard", and was expected (by some) to stay "the standard" indefinitely, but boy is it useless to me now!
So, yeah, comply to standards. It's often helpful. But it won't accomplish what the fanatics say it will, no matter how much they insist it will. In all things, moderation (extreme Buddhism!).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 1:59 pm
Have to agree with you on that Joe. Just want to be sure that people who follow standards understand that doing so will not be a panacea, and will not prevent having to rewrite code.
Standards are a good way to "learn from other people's mistakes". You just have to understand their limitations.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 211 through 225 (of 258 total)
You must be logged in to reply to this topic. Login to reply