February 18, 2009 at 1:32 am
Hi brewmanz,
You are right, i did get the ...127 and not .128.
My apologies for posting wrongly.
Regarding,
Ramu
Ramu
No Dream Is Too Big....!
February 18, 2009 at 1:35 am
knechod (2/17/2009)
Hugo Kornelis (2/17/2009)
Anyhow, never indexing a datetime is not a wise decision.
More of an avoidance than a hard-and-fast rule. As an ordering method, not a problem. As a clustered index, ok. I'm a little concerned about how a clustered datetime would affect multiple users inserting at the same time.
No problem at all. A clustered index doesn't have to be unique. Admitted, there are drawbacks to having a nonunique clustered index, but if the benefits exceed those drawbacks, then by all means go for it. 😉
Your mentioning inserting also reminds me that I forgot to include an important benefit of clustering on a datetime - if said datetime is the moment of inserting, you'll avoid time-consuming page splits. (The same advantage applies to identity and to GUID with a default of NEWSEQUENTIALID(), but not to GUID with a default of NEWID()).
But try querying for 2/16/09 8:17pm (the time right now MST), in a table where the datetime is acting like a timestamp. It's more like indexing a real number.
If I need to find rows with that EXACT time (to the millisecond), I would filter for DateInserted = '2009-02-16T20:17:00.000'. If I need to find all rows inserted in that minute, I'm back at the old interval problem already discussed at length in this thread, except the interval is now a minute instead of a day:
WHERE DateInserted >= '2009-02-16T20:17:00.000'
AND DateInserted < '2009-02-16T20:18:00.000'
Both the exact match and the range will benefit from a clustered index. The exact match will also benefit from a nonclustered index; the range match might benefit.
As for high selectivity, I DO index GUIDs. But I would never make a user enter one!
Finally, the datetime datatype is not inexact.
Yes, dates are integers at some level. But my users' concept of dates don't extend to 3.333 milliseconds.
The reason why inexact values are generaly less useful in an index is not tied to the users' concept of the values, but to conversion and representation issues. Even though conversion from float to text and back is (probably) documented somewhere, it's too complicated to predict. And that can cause weird results:
SET @a = 134.7;
SET @b-2 = 10.0;
SET @b-2 = 13.47;
IF @a = @b-2 PRINT 'Equal' ELSE PRINT 'Inequal';
For datetime, I could make a similar repro, using a millisecond value that doesn't end in a 3, 7, or 0. But that is very predictable, and hence easily avoidable. (The floating point issue is not predictable at all - I had to try various combiniations of values in the code above before I got the desired results...)
Actually, the above is mainly a discussion on why you should not equality search on inexact values, such as floating point numbers. You can safely use range search (e.g., match all values between 13.46999 and 13.47001). And just as with datetime, an index can still be valuable even in range search operations.
Bottom line: Inexact numbers make a bad primary key column - this goes 100% for floating point numbers, and, hmmm, let's say 95% for datetime values. But depending on use, inexact numbers can be a prime candidate for a clustered index, and can also be considered for nonclustered indexes.
February 18, 2009 at 6:51 am
Thanks Hugo for the explanation. I learn so much from this forum. It does seem to be a pretty odd way to handle time. Does this change in SQL 2008 with separate date and time data types?
Thanks again.
February 18, 2009 at 7:09 am
I'd like to hear Hugo's answer to your question, but IMHO not much really changes in SQL Server 2008 with regard to the DATE and TIME data types. When working with time, you still will most likely need to use ranged searches in most of your queries. The DATE type, if used, would make it easier to find all records entered on a specific date, as you won't have to do a ranged search in that case.
February 18, 2009 at 7:30 am
skjoldtc (2/18/2009)
Thanks Hugo for the explanation. I learn so much from this forum. It does seem to be a pretty odd way to handle time. Does this change in SQL 2008 with separate date and time data types?Thanks again.
Hi skjoldtc,
SQL Server 2008 adds several new data types, but doesn't change the way the current datatypes ([font="Courier New"]datetime[/font] and [font="Courier New"]smalldatetime[/font]) work. And everything considered, that is a good thing - you don't want your existing code to break when upgrading, right?
Many of the issues discussed here apply to the new data types ([font="Courier New"]date[/font] and [font="Courier New"]time[/font], but also [font="Courier New"]datetimeoffset[/font] and the hideously named [font="Courier New"]datetime2[/font]) as well, though obviously not the weird 1/300 second precision - the new datatypes that include a time portion all allow the user to specify 0 to 7 digits of fractional second precision.
As Lynn says, searching for a particular day becomes easier with the [font="Courier New"]date[/font] datatype, as this is now one single value so an equality search can be used (though you still will get correct results when using the range search technique). For all other datatypes, I would still continue to use the half-open interval search outlined earlier in this discussion, as it will always work even when the datatype later changes, and it saves me research time. Sure, I can save myself some typing by first checking the datatype ([font="Courier New"]datetime2(5)[/font] - so that is 5 digits after the decimal) and then using BETWEEN with an end datetime value of '2009-01-31T23:59:59.99999' - but I save myself a lot more time by not having to check the datatype at all and just using < '20090201'. (And since the default time part is still midnight, I can omit the time portion in this case).
There might or might not be other, new issues with the new date/time datatypes, but I haven't played with them quite enough to find them.
March 6, 2009 at 12:32 pm
as mentioned in a prior comment, the answer to the question is wrong!!
Try runing this and you'll see a 2/1/09 value....
create table #a (InsertedOn datetime)
insert into #a values ('20090131');
insert into #a values ('01/31/09 23:59:59.999');
insert into #a values ('01/31/09 12:59:59.999');
insert into #a values ('20090131');
insert into #a values ('20090131');
SELECT [InsertedOn] FROM #a WHERE [InsertedOn] BETWEEN '20090131' AND '20090131 23:59:59.999'
drop table #a
Dennis Parks
MCSE, MCDBA, MCSD, MCAD, MCTS
November 2, 2009 at 9:53 pm
in the question never showed an implicit date time conversion, therefore you can not require that condition as granted.
Your question is bad formulated or you are trying to trick (fool) people.
November 2, 2009 at 9:56 pm
in the question never showed an implicit date time conversion, therefore you can not require that condition as granted.
Your question is bad formulated or you are trying to trick (fool) people.
Viewing 8 posts - 46 through 52 (of 52 total)
You must be logged in to reply to this topic. Login to reply