Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
 New Feature: Community Sites:
Create your own .NET community website and start earning from Google AdSense !
It's Free !
|
When to use SELECT *
Posted Date: 11 Jan 2008 Resource Type: Articles Category: Databases
|
Posted By: kumar kunda Member Level: Silver
Rating:  Points: 5
|
Introduction
Paragraph Heading 1
In sql server, when ever we are working on complete table, we are using select * from table_name. it is an easy thing to write, but it takes much IO resources of sql server cache. This small article is regarding
Reasons why not to use SELECT *
1. Effeciency: Only the data you need
Reduces the amount of I/O that has to occur
2. Better use of indexes
May use Index intersection or make use of covered indexes
3. Isolate code from Table object changes
For example, the following would through an error INSERT INTO myTable99(Col1, Col2, Col3,etc) SELECT * FROM myTable00 WHERE
4. Increases network traffic, requires more buffers and processing
Similar to Item #1
5. Constantly accessing the system tables to figure out what these columns are.
6. When a VIEW is Created a SELECT * , and the Table is altered or dropped and recreated, the view will refernce the old table structure as ehorn points out
Here is a test case that shows why 'SELECT *' in a view is a bad thing.
1. Create the table and then seed some data
create table foo ([id] int identity, value1 varchar(20), value2 char(10));
insert into foo values ('foo', 'bar');
insert into foo values ('bar', 'bar');
insert into foo values ('foo', 'foo');
2. Create the view
Create view v_foo as Select * from foo
go
3. Test the view with an initial select
select * from v_foo
4. Alter the base table and add a column, insert more data
alter table foo add value3 varchar(30);
insert into foo values (NULL, NULL, 'foobar');
5. Do another select from our view. Notice a problem?
select * from v_foo
It doesn't work even if I select the specific column i want:
select value3 from v_foo
If you drop and recreate the view it will work. Restarting the database will fix it also. The other solution is to not use a SELECT * from inside your view.
When to Use SELECT *
In some instances, it is more effecient to use SELECT *.
1. In a coorelated query, SELECT * is actually optimized
SELECT Col1, Col2, Col3 FROM myTable99 a WHERE EXISTS (SELECT * FROM myTable00 b WHERE a.id = b.id)
2. In an existence check as well
IF EXISTS(SELECT * FROM myTable99)
3 Let's say that we are making a derived table, especially when there may be many derived tables that are nested, what then would be the harm in passing up the columns using SELECT * ? Also this is not to say that SELECT * is appropriate for this initial derived table, which would again be selecting way more than we need.
Paragraph Heading N
Summary
|
Responses
|
| Author: ChandraShekar Thota 11 Jan 2008 | Member Level: Diamond Points : 0 | please do check artcle priview before posting
place u r paragraph in place of "paragraph heading" etc
| | Author: balamurugan 19 Sep 2008 | Member Level: Silver Points : 1 | it is not good way to select *
good way to access select specific column name.
ex
select empname from emp;
| | Author: Vignesh 03 Oct 2008 | Member Level: Silver Points : 1 | TOP keyword could also be used like
SELECT TOP 1* FROM
for access to the column names and the type of data each column stores (datatype, nullable, etc)
| Author: Sriram 05 Oct 2008 | Member Level: Gold Points : 1 | Select Statement is used to Retrive a Full Record or Particular Record use for select Statement.
select * from Products
select * from products where pro_id=111;
select * from products where proname LIKE '%a%'
SriramRamaswamy
|
|
Contact Us
Privacy Policy
Terms Of Use
|