0 votes
1 view
in SQL by (20.3k points)

I've heard that SELECT * is generally bad practice to use when writing SQL commands because it is more efficient to SELECT columns you specifically need.

If I need to SELECT every column in a table, should I use

SELECT * FROM TABLE

or

SELECT column1, colum2, column3, etc. FROM TABLE

Does the efficiency really matter in this case? I'd think SELECT * would be more optimal internally if you really need all of the data, but I'm saying this with no real understanding of the database.

I'm curious to know what the best practice is in this case.

UPDATE: I probably should specify that the only situation where I would really want to do a SELECT * is when I'm selecting data from one table where I know all columns will always need to be retrieved, even when new columns are added.

Given the responses I've seen, however, this still seems like a bad idea and SELECT * should never be used for a lot more technical reasons that I ever thought about.

1 Answer

0 votes
by (40.4k points)

One important reason for selecting specific columns,  because it raises the probability that SQL Server will access the data from indexes rather than querying the table data.

Here's a post I wrote about it: The real reason select queries are bad index coverage

Selecting specific columns is less fragile to change since any code that consumes the data will get the same data structure regardless of the changes you make to the table schema in the future.

For more information, you can refer to this The real reason select queries are bad index coverage

Related questions

0 votes
1 answer
asked Jul 6, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 10, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...