SQL Server Performance

why not distinct work properly

Discussion in 'General Developer Questions' started by d17may, Jun 8, 2006.

  1. d17may New Member

    I have table which has one datetime field as primary key.This table contains
    some rows which has same value for datetime when i try to insert data into
    other table by selecting from this table to another table .It give error
    of primary key violation bcoz this datetime field is primary key in
    other table .i also use distinct but it return rows which has same value for
    datetime field
    Actually i make some test cases
    My query is this
    insert into ['vehicle_history_gsrtc-11']
    SELECT distinct top 100 [vehicle_id], [vehicle_sms_no], [vehicle_lattitude], [vehicle_longitude], [vehicle_off_lattitude], [vehicle_speed], [vehicle_off_longitude], [vehicle_key_status], [vehicle_inputpin_status], [vehicle_outputpin_status], [vehicle_datetime], convert(datetime,[vehicle_gpsdatetime],101) as vehicle_gpsdatetime, [vehicle_direction], [vehicle_string], [vehicle_location], [sms_datetime], [vehicle_message_type], [vehicle_distance], [vehicle_location_mapobjects], [vehicle_gpsdate], [vehicle_gpstime], [sms_datacall_record_ind], [report_message_type], [record_enable_ind] FROM [Fleetvigil_Web].[dbo].[vehicle_history]
    where vehicle_id='gsrtc-11'
  2. Madhivanan Moderator

    Distinct will remove duplicate rows only if there are identical rows. Post the result that are returned by the query


    Failing to plan is Planning to fail
  3. smy New Member

    i am curious..why do you use datetime as the primary key of your table?
  4. FrankKalis Moderator

  5. FrankKalis Moderator

    Btw, DATETIME values are "only" unique down to 3,33 milliseconds. Since you can insert one than 1 row within a timespan of 3,33 millisecond, it is likely that the current DATETIME value hasn't changed since the last INSERT. So, you a heavily inserted table choosing a DATETIME column as PRIMARY KEY might turn out not to be the best choice around.

    Frank Kalis
    Microsoft SQL Server MVP
    Heute schon gebloggt?http://www.insidesql.de/blogs
  6. d17may New Member

    in my application it is the most appropriate filed for primary key
    .Madhivanan reply has clear my doubt .There is one field which has different
    field .now i change my constraint

  7. FrankKalis Moderator

    Problem solved? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
  8. d17may New Member

    how i make sure that that only those rows are selected which has unique value
    in one field
  9. mmarovic Active Member

    So you have some distinct rows in vehicle_history table that have the same vehicle_dateTime value. Before answering your question we need to know the criteria what values you want to be returned for other columns when two rows have the same value for the vehicle_dateTime column.

Share This Page