Filters or Sort, which should be the first when creating an index in the database

Question

Given, for example, the following Query:

SELECT 
  ClienteId,
  Nome,
  DataNascimento,
  Cidade,
  Estado,
  DataCadastro
FROM
  Cliente
WHERE
  Estado = :Estado AND
  Cidade = :Cidade
ORDER BY
  DataCadastro, DataNascimento DESC

in terms of better use of the index and performance, my index should first consider ordering and then filter.

CREATE INDEX TESTE1 (DataCadastro ASC, DataNascimento DESC, Estado, Cidade)

Or first the filter and then the sort?

CREATE INDEX TESTE2 (Estado ASC, Cidade ASC, DataCadastro ASC, DataNascimento DESC

Answer 1

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

Answer 2

Index creation order does not change the use of the index.

As for leaving an index covering several columns I suggest you create two, one for the filters and the other for sorting because the DBMS engine uses the filters and does the sorting at different stages of the processing.

What indices to create...

It depends on the use you will make of the table, the table size of the amount of times/time you will scroll through the columns, make queries, Inserts, etc.

I advise to make trial and error and study the tools that your DBMS has like profiles, Wizards, query plans, etc.

Indexes d+ get in the way, wrong indexes idem. Read a lot because this is a very complex subject to fit in an answer. If you want a more detailed analysis create a more specific question, but then you will need to post more details, such as object creation queries and the most frequent queries being used, query plans, etc.

As for the clustered indexes mentioned by @ramaral it is a bad practice to use them with fields "Random"*.

*Random: In this case I mean that are values not sequential, can be a date of birth or a guid. The problem is that as the table will be kept ordered physically by this column(s) (s) when you enter a new value it will force a table reordering (causing page splits, etc.). This creates an immense overhead plus that potentially much of the table will get locked during the operation of Insert (due to page splits). So only create clustered indices in sequential fields (Obs, Pks in general are clustered indices)