declare @t table(i int, d datetime)
declare @d datetime
set @d='12/12/2005 12:12:12:201'
insert into @t values(1,'12/12/2005 12:12:12:201')
insert into @t values(1,'12/12/2005 12:12:12:202')
insert into @t values(1,'12/12/2005 12:12:12:203')
insert into @t values(1,'12/12/2005 12:12:12:204')
insert into @t values(1,'12/12/2005 12:12:12:205')
insert into @t values(1,'12/12/2005 12:12:12:206')
insert into @t values(1,'12/12/2005 12:12:12:207')
insert into @t values(1,'12/12/2005 12:12:12:208')
insert into @t values(1,'12/12/2005 12:12:12:209')
insert into @t values(1,'12/12/2005 12:12:12:210')
insert into @t values(1,@d)
select * from @t

USE Northwind
GO

SET NOCOUNT ON

CREATE TABLE tbl_events99(
	  product_id int
	, event_time datetime
	, event_ms int
	, PRIMARY KEY (product_id, event_time, event_ms)
)
GO

INSERT INTO tbl_events99(product_id, event_time, event_ms)
SELECT 243, LEFT('2005-06-27 06:23:39.703',19), RIGHT('2005-06-27 06:23:39.703',3) UNION ALL
SELECT 243, LEFT('2005-06-27 06:23:39.577',19), RIGHT('2005-06-27 06:23:39.577',3) UNION ALL
SELECT 243, LEFT('2005-06-27 06:23:23.813',19), RIGHT('2005-06-27 06:23:23.813',3) UNION ALL
SELECT 243, LEFT('2005-06-26 11:51:57.577',19), RIGHT('2005-06-26 11:51:57.577',3) UNION ALL
SELECT 243, LEFT('2005-06-26 11:51:57.453',19), RIGHT('2005-06-26 11:51:57.453',3)
GO

INSERT INTO tbl_events99 (product_id, event_time, event_ms)
VALUES (243, LEFT('06/27/2005 06:23:39:704',19),RIGHT('06/27/2005 06:23:39:704',3))
GO

SELECT * FROM tbl_events99
GO

SET NOCOUNT ON
DROP TABLE tbl_events99
GO

select Test = .9e

Test
------------------- 
0.90000000000000002

quote:

---

Originally posted by X002548
How did you get that millisecond value in the first place?

---

quote:

---

Originally posted by X002548

Don't take this personal (and this is in no way to defend M$), but I don't like your Primary key in the first place. Can you post the DDL of the table?

And how much data are we talking about?

Doing singleton inserts from flat files from disparite systems sounds like the looooooong way around the mountain.

You should bcp the data in.

---

CREATE TABLE [dbo].[tbl_insite_feu_radio_data_points] (
	[feu_radio_id] [int] NOT NULL ,
	[record_time] [datetime] NOT NULL ,
	[server_radio_id] [int] NULL ,
	[server_install_id] [int] NULL ,
	[insite_feu_id] [int] NULL ,
	[port_number] [int] NULL ,
	[current_state] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[last_response] [bit] NULL ,
	[last_response_time] [datetime] NULL ,
	[csafe_command] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csafe_data_1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csafe_data_2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[csafe_data_3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[insite_message_id] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_insite_feu_radio_data_points] WITH NOCHECK ADD 
	CONSTRAINT [PK_tbl_feu_radio_data_points] PRIMARY KEY  CLUSTERED 
	(
		[feu_radio_id],
		[record_time]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_tbl_insite_feu_radio_data_points] ON [dbo].[tbl_insite_feu_radio_data_points]([server_install_id]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

quote:

---

Originally posted by X002548

Whats an event? Is it likely that the same event could happen within the same millisecond. The reason I don't like it is because you will never use the datetime as a predicate I wouldn't think...plus I;m suprised you don't get hot spots on your database. Maybe that's why you do singleton inserts? Also, you incur a lot of logging, where a bcp would be minimally logged.

Can you show us an example of a SELECT Statement that's used to retrieve data from this table?

---

CREATE TABLE [dbo].[tbl_insite_feu_radio_data_points] (
	[feu_radio_id] [int] NOT NULL ,
	[record_time] [smalldatetime] NOT NULL ,
	[record_milliseconds] 	[smallint] not null
	[server_radio_id] [int] NULL ,
	...
	rest of table
	...
)

ALTER TABLE [dbo].[tbl_insite_feu_radio_data_points]
WITH NOCHECK ADD 
CONSTRAINT [PK_tbl_feu_radio_data_points]
PRIMARY KEY  CLUSTERED 
	(
		[feu_radio_id],
		[record_time],
		[record_milliseconds]
	) WITH  FILLFACTOR = 90  ON [PRIMARY]

quote:

---

Originally posted by Michael Valentine Jones

Here is another idea for a workaround on this. Convert column [record_time] to smalldatetime, and store the millseconds in a new column, [record_milliseconds]. This would let you continue to do lookups by date, and would actually use less storage, because the 4 byte smalldatetime and 2 byte samllint would be 2 bytes less than the 8 byte datetime. You would have to add the new column [record_milliseconds] to your primary key to make it unique.

CREATE TABLE [dbo].[tbl_insite_feu_radio_data_points] (
	[feu_radio_id] [int] NOT NULL ,
	[record_time] [smalldatetime] NOT NULL ,
	[record_milliseconds] 	[smallint] not null
	[server_radio_id] [int] NULL ,
	...
	rest of table
	...
)

ALTER TABLE [dbo].[tbl_insite_feu_radio_data_points]
WITH NOCHECK ADD 
CONSTRAINT [PK_tbl_feu_radio_data_points]
PRIMARY KEY  CLUSTERED 
	(
		[feu_radio_id],
		[record_time],
		[record_milliseconds]
	) WITH  FILLFACTOR = 90  ON [PRIMARY]

CODO ERGO SUM

---