Also you mentioned in an earlier post about the index recommender offering similar recommendations

e.g

col1_co2_col3

and

col1_col2 include(col3)

or something similar.

Now surely there must be a difference between having the column as...

Ok thanks for all your help today I dropped 2 of those older indexes and I've been hunting through the many many procs and queries that access the JOB table...

Ok so having bits/tinyints in a covering index is okay then its just when you create an index on one or a couple of columns that are very non-selective that...

Also regarding Live and TempMode.

Live is a tinyint -1, 0, 1

and Tempmode is a bit.

I was always under the impression that putting bits or other very non selective columns in...

Also you say include JobDescription as an included column in that first suggestion is it ok to include a column thats nvarchar(max) in the index? I didn't think that you...

Hi Gila

The reason that the filter is on JobPk and SiteFk is that there is only 1 database that handles multiple sites so its to ensure that only jobs belonging...

Yes I have noticed very similar indexes being suggested. What the DB generated from the script does though is increment a counter per index so you can order by most...

The original queries were

SELECTCount(JobPK)

FROMJOBS

WHEREClientFK in(X,X,X) AND

Live=1

OR

SELECTCount(JobPK)

FROMJOBS

WHEREClientFK = X AND

Liive=1

which I created a non clustered index with ClientFK,JobFk,Live (in that order)

I then replaced the COUNT(JobPk) with COUNT(*) and dropped the JobPk

however...

Another good explaination Gila about the ordering of columns in the index. Very valuable info which I will put to good practise in future now that I understand a...

Yes thanks for both those replies they really explained the issue very well.

So even though JobPk is the most selective item in that table (a different value per record) it...

Well I'm not in a position to do that am I seeing I'm only the developer! Maybe if it was my system or I had control of who else used...

Cheers for that SQL that will work a treat to output the correct GRANT permissions.

As for the role being deleted and re-created again, maybe but no-one will own up to...

I have actually just dropped the index I created and implemented the index suggested by the query optimiser wizard which had exactly the same columns but in a different order...

Thanks Steve thats done the trick nicely.

Great site by the way!

Hellol. Currently there is one login that has been given the same access to both databases. The owner of the tables is dbo. I suppose that just because the name...