New keys

Waldo

Hi,
I would want your opinion.

I have created two extra indexes? Why? Because my customer is using NAV and Filters in the most exotic way...
The question is in which order you would place the fields... . I know, the most selective, but it's hard to say which are most selective... #-o

Index No. 1 on Sales Line table:
- No.
- Shipment Date
- Outstanding Quantity
- Type

I know No. is good, but what about the other fields?

Index No. 2 on Sales Line table:
- Shipment Date
- Production Group
- Shipment Day
- VendorNr

You would say "vendornr" is quite selective, but it isn't. They filter on it the way that they still get a very large number of records... , same for production group. In fact, the way they filter, you get about en even amount of records for all fields... ](*,) .
Is it a good idea to put a date first?

kriki

Waldo wrote:

Index No. 1 on Sales Line table:
- No.
- Shipment Date
- Outstanding Quantity
- Type

I know No. is good, but what about the other fields?

In SQL, a date and a time are of type "Datetime", so not really good in an index. So best leave it at the end of the index. Most of the time, I don't even put it in the index because the gain for reading is low and I do gain some speed for writing the index (and not writing the field).
Also a decimal in general is not really good for an index. (Only used it in Navision when I had to sort on it...)
So in this case I would say : "No.","Type" as index. the other fields are of no use (in general).
If you know that you only have 1 Type (e.g. Item), no need to put it in the index.
You can try something for checking if "Shipment Date" is usefull: With the "SQL Server Management Studio" of SQL2005, you can check the selectivity-statistics of the index and if you see that the "shipment date" helps a lot, you can put it in the index. BUT only if you really use it to filter.

Waldo wrote:

Index No. 2 on Sales Line table:
- Shipment Date
- Production Group
- Shipment Day
- VendorNr

You would say "vendornr" is quite selective, but it isn't. They filter on it the way that they still get a very large number of records... , same for production group. In fact, the way they filter, you get about en even amount of records for all fields... ](*,) .
Is it a good idea to put a date first?

First and for all : it is never a good idea to put the date first. (Except, maybe, if you don't have any other filter)

What field type is Shipment Day? I suppose it is an integer with 7 possible values (1 for each day of the week).
So also this one has not really a big selectivity. So I suppose you can choose between "Vendor No.","Production Group" or "Production Group","Vendor No.".
After these 2 fields, I would say "Shipment Day","Shipment Date" OR "Shipment Day" OR "Shipment Date". This depends if you filter on it or not and how useful it is (see again selectivity-statistics).

How to see selectivity-statistics IN SQL2005 (also useful on a SQL2000!).
Go in the database => table => Statistics => select your key. Doubleclick on it=>another form opens => Details : "All Density"-column. The smaller the number the better.

Waldo

kriki wrote:

How to see selectivity-statistics IN SQL2005 (also useful on a SQL2000!).
Go in the database => table => Statistics => select your key. Doubleclick on it=>another form opens => Details : "All Density"-column. The smaller the number the better.

I thought so about the dates, but in fact, the dates do give me some performance gain :-k ...

Thanks for the tip about selectivity. Didn't know that...

Marije_Brummel

Yes, dates in indexes can be very usefull. Discovered that again today.

It is always a matter of considerarion. Reads vs. Writes.