Difference in performance when Duplicate a table

Asked

Viewed 67 times

5

There is significant difference in performance between these two examples?

SELECT * INTO  [IP].BA_exemplo.dbo.TabelaB  
FROM BA_Temp..TabelaA

and

SELECT A,B,C,D... -- (todos os campos da tabela `TabelaA`)
INTO  [IP].BA_exemplo.dbo.TabelaB
FROM  BA_Temp..TabelaA

to use in a procedure

sql sql-server
  • 1

    If I’m not mistaken, both examples do the same thing. If in the second example you put only a part of the columns, then yes, there could be differences of performance.

2 answers

1


There is significant difference in performance between these two examples? ...
to use in a previous

Not.

No difference in performance.

Internally, the same execution plan is generated, in both cases, with the same content in the read operator’s "Output List" property. in Article "The Perfect Plan"there is explanation of what is the plan of execution, how to generate it and basic interpretation of it.

  • 1

    Thanks José Diz, I really liked your article. Thanks!

0

I agree with João, the only difference that affects performance is that you are using the * which will bring all the columns, which is a bad practice, and when bringing all the columns SQL has no information if you can opt for the use of a given Word.

For example, if you have a table with 10 columns (C1, C2 ... C10) and you have an input in the "C1" column when doing SELECT C1, C2 FROM Tabela C=1 SQL uses Intel because it knows where to look for information. Unlike you SELECT * FROM Tabela C=1 can’t do it.

More SELECT * is not a recommended writing pattern because:

  • If you specify columns in an instruction the SQL execution mechanism gives and I if that column is removed from the table.
  • You should always write queries that return minimum data.
  • If you have to use joins between tables, * set all columns to Join tables
  • 1

    Although using SELECT * is a bad habit, it seems to me that it does not influence the use or not of index, if the index is clustered and the predicate in the WHERE clause contains the column that is the primary key. In the case of SELECT * from Tabela where C1 = 1, will occur Seek if the index is clustered and by C1 column.

  • Right @Josédiz, but in this case it does not refer clause WHERE, so that doesn’t apply to the scenario.

  • 1

    @Joãomartins My comment was posted directly in Sergio’s reply for containing statement that is not correct.

  • 1

    @Josédiz in my reply assumed that the columns were not clustered Indice. I will update the answer so that there are no doubts

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.