Category: SQL Server

21 Nov 2016 09:55

Joined:	13/08/2015
Level:	Silver
Points:	102

Points:	5

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

21 Nov 2016 12:34

Joined:	18/12/2008
Level:	Gold
Points:	2921

Points:	4

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

05 Dec 2016 10:14

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

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

05 Dec 2016 12:05

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

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

07 Dec 2016 11:49

Joined:	13/07/2011
Level:	Gold
Points:	4261

Points:	4

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

08 Dec 2016 15:06

Joined:	08/09/2004
Level:	Gold
Points:	854

Points:	4

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

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

About dotnetspider.com

Quick Links

General

Services