It's funny that you should suggest that.

I've been considering the two possibilities:

1) An indexed view

OR

2) Adding an index to the RoutePosition table that would index the data more closely in...

I've spent quite a lot of time on this over Friday and Saturday and have got some new information.

1. The RoutePosition table contains 128 million rows.

2. The query I'm executing...

Table defs and index, query and execution plans below:

CREATE TABLE [RoutePosition] (

[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

[Vehicle] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Time_Stamp] [datetime]...

Damn, I'm sorry. I didn't realise that I'd posted in the 2k5 forum although it's pretty obvious when I look at the top of my page! It it possible to...

Sorry about the icons, they've taken the place of the colon and open braket characters.

Hi, thanks for taking a look at my problem:

This is the actual query and exectution plan:

SELECT E.FleetGroup,E.Time_Stamp,E.Reason,E.Vehicle,R.ID

FROM RoutePositionEvents AS E

LEFT OUTER JOIN RoutePosition AS R ON E.RPID = R.ID

WHERE...

Thanks guys, I'm sure you're right. I'll see if I can find the articles I read and post links to them.

To be homest it's just something I've heard from several different sources as I've learned about sql over the last couple of years. I was told it would aid performance.

Hi Kenneth, thanks for the reply.

I understand that part of how triggers work. Unfortunatley I wanted to be able to dynamically select the value of a column from the INSERTED/DELETED...

Data entry directly into the table. I know its possibly not the best way to operate, but for quickness when I'm developing I find it easier.

Excellent! That's the most succinct answer I've ever received on a forum! Thank you very much!

Thanks for the reply. We're considering working with an outside company who provide a routing and scheduling solution. Part of their system caclulates a routed distance between two points.

Part of...

Thanks for the feedback, both of you. Much appreciated.

Thanks for your help. It makes sense when you think about it because primary key constraints are unique within the database I believe.

Yes, I thought that was the case. I am using a single # though:

CREATE TABLE [#ICNReport] (

 [ID] [int] IDENTITY (1, 1) NOT NULL ,

 [CompanyID] [int] NOT NULL ,

 [Town] [varchar] (50)...