How to create an index for two tables at once in SQL Server?

Question

I’m pretty sure there’s no way but, come on...

I have the following scenario: (I included only the fundamental fields for brevity)

 - Tabela Venda: 
   - id: int;
   - cliente_id: int;
   - cliente_tipo: char(1);
 - Tabela PessoaFisica:
   - id: int;
   - nome: varchar(100);
   - uf: char(2);
 - Tabela PessoaJuridica:
   - id: int;
   - razao_social: varchar(100);
   - uf: char(2);

I did not create the bank and I am obliged to work with it in that way.

My situation is: in certain cases, I have to make inquiries where I depend on the sale to reach my client and, based on the state where the same is, determine a working rule for the same.

The problem is that if mine query become very complex and at many levels of action I realize that the query is slower and slower. So I thought about the indexes.

The problem is that for this, I need to link an index in the sales table that can relate both to PessoaFisica how much to PessoaJuridica. Is there any way to do that?

Answer 1

It is not possible. It has a database that is as good as, but SQL Server cannot, even with computed functions or columns.

One way is to create a mechanism where you always bring the data from the other table to the one that needs the index and use the two columns there. It is the forced denormalization to meet a demand. Care must be taken not to lose consistency.

You need to think about if you have any other way of doing this. Maybe you can create a view can help.