You must Sign In to post a response.
  • Category: SQL Server

    Diference between Select Query in Sql (select * from TblPerson) and (select Name,Age from TblPerson)

    hi Developers i have a simple doubt in Ado.Net

    i have a table named TblPerson
    and i have the following column
    1.Id,2.Name 3.Age,4.Gender
    i want to show Name and Age From the TblPerson Table
    in this case i have two options
    1.select * from TblPerson or (where Some Conditions)
    2.select Name,Age from TblPerson or (where Some Conditions)
    if i use 1-st one i will get all the records. so no need 2-nd option.
    but some people using 2-nd Option like
    select Name,Age from TblPerson or (where Some Conditions)
    instead if first query we can get our expected output. then why peoples using 2-nd option.
    so i van guess they having a valid reason. but
    i can't able to understand the exact reason

    so please friends please clarify my doubt. if my question is incorrect don't take mistake me

    thanking you
    Paul.S
    Prayer throw, Impossible becomes Possible.
  • #768338
    Hi,

    Its as simple as that. In the second query you have specified the column name. So the SQL Compiler before running the query itself it know what to take and come and what going to the datatype and name of the column.

    But in first query we don't have any columns specified so it going to take the entire table with the column which we are already specified.

    By the way of simply answering. The second query will give you improved performance.
    you can check those performace with and without column specific. With column specified the query will be faster than the column with is not specified.

    This can be measured when we have bigger select query or bigger table.

    Thanks,
    Mani

  • #768458

    Hi Paul,

    Unnecessarily you are getting all the columns rather than what you want right? it's occupy some memory to store it, so why we should waste the memory that is the reason people are preferred selected columns only.


    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #768463
    here are some points that you needs to concentrate
    1-Selecting * will fetch all columns name which in result use more resources, more network and slower queries.
    2-Secondly if someone alters the structure of your table SELECT * will suddenly start bringing back a structure you weren't expecting.
    3-Fetching all Records using Select * prohibits the query from using Covered Indexes.
    4- SELECT * reduces your ability to do research on which columns are used as only the ones in JOIN, WHERE, GROUP BY, and ORDER BY (assuming you aren't silly enough to use the SELECT list position #s) clauses will show up when doing searches. If you need to rename or drop a column you need to be able to determine EVERYWHERE it is being used.
    5-SELECT * instead of SELECT field1, field2 then you will also get field3 which you don't need / want but it will be sent over the network to the application so for every row it will have unnecessary transport of field 3.

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #768477
    Hi,

    Yes, always make a habit to return only the necessary data from the database, In case of big database with many tables and much records in table, surely it will decrease the performance.
    Returning large amount of data increases query time, which will increases load on the data base server. So we should avoid select * in our queries and have to restrict only necessary columns and amount of data
    i.e use select columns with where clause. Which will reduces the network traffic in addition to reducing the processing on the database server.
    Indirectly which will reduces the performance issues of your application.



    Hope this will give you an idea

    Regards,
    SonyShiva
    Never lose hope..You never know what tomorrow will bring

  • #768502
    1) select * will take more time than selecting particular columns - perfomance issue

    2)Mentioning Column names is better to avoid conflicts when the column names can be changed or would be added newly in the future

    3) selecting some special fields can lead to a substantial performance increase if these fields all occur in an index; this allows the optimizer to query only the index and ignore the base table altogether.

    4) Example - Why are purchasing 1oth class text book while are you in 9th.

    Debasmit Samal


Sign In to post your comments