precisions of DateTime

Hi,
I am trying to add a uniqueness constraint on a DATETIME column of a table,
which contains rows with timestamp values differ by several milliseconds.
Upon adding the unique index, SQL complains that table contains duplicate
values are found as they are only comparing up to the second interval. Sinc
e
my database has a fixed schema, it is not possible to introduce additional
columns. Any ideas to solve this problem? Thanks.> I am trying to add a uniqueness constraint on a DATETIME column of a
table,
This is not a great idea. How can you possibly enforce that two events
can't happen at the same time? Why would you want to identify a row based
on date and time down to the millisecond? I can see why you would want a
clustered index on a datetime column, but I can't comprehend the purpose of
enforcing uniqueness there.
A|||We have an automated data loader application that loads data into the
database. In order to avoid loading duplicate rows, we are trying to add
some checking mechanism. Since the column schema is fixed, we are not able
to add Identity columns so we will have to add uniqueness constraints agains
t
the existing columns. There are 15 columns in total and I am unsure whether
it is a good idea to make add the uniqueness constraints against all 15
columns. Please advise.
"Aaron [SQL Server MVP]" wrote:

> table,
> This is not a great idea. How can you possibly enforce that two events
> can't happen at the same time? Why would you want to identify a row based
> on date and time down to the millisecond? I can see why you would want a
> clustered index on a datetime column, but I can't comprehend the purpose o
f
> enforcing uniqueness there.
> A
>
>|||> the existing columns. There are 15 columns in total and I am unsure
whether
> it is a good idea to make add the uniqueness constraints against all 15
> columns. Please advise.
Is it really only a duplicate if ALL 15 rows are identical? That seems to
be a much different set of criteria than just checking the datetime value.
Why does the application load duplicate rows in the first place? Can't you
fix that problem? You seem to be trying to solve application problems with
constraints...|||If all 15 rows are identical then the row is identical. The application is
maintained by another team and changing its design would be my last resort.
Because the INSERT INTO statement is "hard-coded" within the application, I
am trying to find solutions at the database level to fix this. Another thin
g
is, users can load data manually into the database and we are trying to
eliminate rows that were manually loaded. Do you have any ideas to solve
this problem?
Thanks.
"Aaron [SQL Server MVP]" wrote:

> whether
> Is it really only a duplicate if ALL 15 rows are identical? That seems to
> be a much different set of criteria than just checking the datetime value.
> Why does the application load duplicate rows in the first place? Can't yo
u
> fix that problem? You seem to be trying to solve application problems wit
h
> constraints...
>
>|||Does the application *only* insert? If so, you could make a new table with
the structure of the real table, swap the names, and have the app insert
into the copy. Then, use a scheduled job to insert *unique* rows into the
real table, and then either log or discard the duplicates.
Better yet, make the app call a stored procedure instead of running its own
queries, then you can have a lot more control.
I'm still not clear on how defining a unique constraint on a datetime column
would ever have done anything to solve this problem.
"Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> If all 15 rows are identical then the row is identical. The application
is
> maintained by another team and changing its design would be my last
resort.
> Because the INSERT INTO statement is "hard-coded" within the application,
I
> am trying to find solutions at the database level to fix this. Another
thing
> is, users can load data manually into the database and we are trying to
> eliminate rows that were manually loaded. Do you have any ideas to solve
> this problem?
> Thanks.
> "Aaron [SQL Server MVP]" wrote:
>
15
to
value.
you
with|||Defining unique constraint on datetime is just a way to avoid loading
duplicate rows. With few exceptions, the values in my datetime column will
be able to determine whether the rows are re-imported. This is why I though
t
of that solution at the beginning.
Thanks for your tips. I will take this up with the application owner.
"Aaron [SQL Server MVP]" wrote:

> Does the application *only* insert? If so, you could make a new table wit
h
> the structure of the real table, swap the names, and have the app insert
> into the copy. Then, use a scheduled job to insert *unique* rows into the
> real table, and then either log or discard the duplicates.
> Better yet, make the app call a stored procedure instead of running its ow
n
> queries, then you can have a lot more control.
> I'm still not clear on how defining a unique constraint on a datetime colu
mn
> would ever have done anything to solve this problem.
>
>
> "Terence Ip" <TerenceIp@.discussions.microsoft.com> wrote in message
> news:8E1596D8-BE76-488E-B107-B48A46A94F2B@.microsoft.com...
> is
> resort.
> I
> thing
> 15
> to
> value.
> you
> with
>
>