I view index creation as something that requires a "trial and error".
However there are basic rules to follow:
1 - Indexes do not add performance in small tables.
2 - Many indexes may decrease performance in INSERT, UPDATE and DELETE
3 - The indexes should contain few columns.
4 - The columns used in the clauses WHERE and BETWEEN or participating in a JOIN should be placed first. The remaining columns should be organised on the basis of their level of distinction.
In view of this the answer to your question is:
CREATE INDEX TESTE2 (Estado ASC, Cidade ASC, DataCadastro ASC, DataNascimento DESC)
However, if the clause ORDER BY Datacadastro, Datanascimento DESC is often used and prevails upon ordering by primary key , the creation of a CLUSTERED INDEX in those columns.
Then two would be created Indexes:
CREATE INDEX index1 (Estado ASC, Cidade ASC)
CREATE CLUSTERED INDEX index2 (DataCadastro ASC, DataNascimento DESC)
The data would be recorded ordered by DataCadastro ASC, DataNascimento DESC
instead of by primary key.
UPDATE
The exchange of comments between me and jean, led me to research more on the subject.
The use of a CLUSTERED INDEX in a field other than "ever-growing" may give rise to times of INSERT higher.
However, ordinations are always longer than a INSERT.
The cost/benefit ratio should be assessed.
In the case of the question, we can ensure that the index is "ever-growing" if DataCadastro
be the type Datetime.
If you want to become one expert in the creation of Indexes follow this link.
See also this explanation of how the CLUSTERED INDEX works
downvote because of the misuse of the clustered index. See my answer for explanation (the other answer is correct).
– jean
@jean You are right. In principle this type of index should be created with the clause UNIQUE, however, this is not a problem if the set of columns represents a large number of unique records. This is what happens in this case, it will not be easy to have situations where on the same day you register more than one person with the same date of birth.
– ramaral
The problem is not one of uniqueness. Imagine that we have a table of people with 100 million records. Inserting a person of average age will cause the table to be split in half so that the record is inserted in the "correct" position. Now imagine the overhead of moving 50 million records from one position within pages
– jean
@jean I can find nothing in the documentation that refers to this situation. Follow this link and see the section on Column Considerations where it is suggested the creation of a CLUSTERED INDEX in the fields Lastname, Firstname, Middlename this suggestion is justified on the same grounds as I gave in my reply.
– ramaral
You really won’t find this level of detail in msdn but check this out: Columns that undergo Frequent changes This causes in the Whole Row to move, because the Database Engine must Keep the data values of a Row in Physical order
– jean
Although this refers to columns that have their value edited something similar occurs in insertion, where inserting will give this kind of problem. You will find lots of references to this more specialized sites for each DBMS, in the case of MSSQL suggest the SQL Server Central
– jean
@So I don’t understand why you can create yourself CLUSTERED INDEX’s. Since there can only be one per table this would have to be the primary key, by default this is what happens. But, to agree with what you say, the primary key I’d always have to be the AUTOINCREMENT to ensure that it is sequential.
– ramaral
Do not confuse Logical Order with Physical Order. When I said that data is recorded in that order I did not mean physically. CLUSTERED INDEX just forces the logical order. This is achieved by the way the b-Tree is created, which is different from b-Tree of UNCLUSTERED INDEX’s
– ramaral
I refer physically (within the logic of B-Tree). DBMS lets you create the indexes as you want. I’ve seen large production bases without qq FK and with clustered Indexes in GUID columns. Not necessarily autoincrement but it is the ideal yes.
– jean