1

What is the difference in this two SQL queries besides the lenght of the code?

SELECT * from tblPerson

SELECT id, name age from tblPerson

NOTE: I am selecting all columns in here, in the second code, that is all the columns of the table, I am not selecting specific locations.

  • 4
    `SELECT *` is a quick - but **dirty** way of selecting. You could run into unexpected and unpleasant surprises e.g. if someone else on your team adds three `XML` and `VARBINARY(MAX)` columns to your table without you knowing (and you suddenly select **much more** data than anticipated). **Don't do it** in production code - just don't. – marc_s Feb 28 '13 at 06:21
  • 1
    Also see: [Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc](http://stackoverflow.com/questions/65512) – mellamokb Feb 28 '13 at 06:23

2 Answers2

2

One obvious difference is indeed the length of the code. Another difference is the maintaineability (don't know how to write that word) i.e. adding a column later to 'tblPerson' will change the result.

Burkhard
  • 14,596
  • 22
  • 87
  • 108
  • yes I already said that to my question, in terms of maintainability yes ur right –  Feb 28 '13 at 06:23
1

There is no difference.

SELECT * means that you are selecting all columns to show on the result list.
SELECT col1, ... specifies only the column you want to be shown.

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    Even though they are *functionally* the same, I think there are some subtle differences in query plans. The conventional wisdom is to use column lists wherever possible. – mellamokb Feb 28 '13 at 06:21
  • ahh, even if I select a table with 20 columns without using the astersisk, it will have no difference? Some of my friends say that using asterisk is more faster than selecting all without asterisk. –  Feb 28 '13 at 06:21
  • 1
    @RoronoaZoro don't trust your friends. trust the optimizer `:D` – John Woo Feb 28 '13 at 06:22