Roy, I understand the point you are trying to make, but this part of
your argument just doesn't hold up to scrutiny:
> But if you see * in one of my SELECT lists in production code it does
> not mean I was lazy. It means that the REQUIREMENT is to return EVERY
> row in the table.
If it's a requirement, why don't you type the columns out? Even if a
column is added, it's no easier (or more difficult) to recompile the
view than it is to add the column as required.
I understand the point that you're trying to make, and I think you're
right about the potential value of SELECT * when used correctly, but
I'm hard pressed to find a scenario where SELECT * is preferable to
typing column names out.
Stu
Roy Harvey wrote:
> On Mon, 03 Jul 2006 13:41:44 -0700, darter <dd@.email.com> wrote:
>
> Personally I am quite tired of this conventional wisdom.
> Of course, if you do not need every column returned you should specify
> just the columns you need. I have no problem with that.
>
> Simple example: a view that starts with an existing table and adds
> some columns by joining or a subquery. This calls for a qualified *:
> CREATE VIEW Something_V
> AS
> SELECT A.*,
> (<subquery> ) as X,
> (<subquery> ) as Y
> FROM Something as A
> If a new column is added to A there is no question that it belongs in
> the view, and no question that a recompile will add it.
> With * the sequence of the columns is predictable. There is no chance
> that a column will be left out. There is no chance that a comma will
> be left out, skipping a column and assigning its name to a different
> column.
> Yes, * is often abused, but it is also a valuable feature when used
> correctly.
> Roy Harvey
> Beacon Falls, CT> How about comparing a table against an Oracle or DB2 table? Naturally
> you would want to grab all the columns, as any disrepancy in column
> names or their count would mean tables are not idential.
This sounds like an administrative task, not something that should naturally
appear in your application.
Anyway, I would rely on the metadata tables / catalog views to describe my
tables, not SELECT *.|||On 3 Jul 2006 19:42:49 -0700, "Stu" <stuart.ainsworth@.gmail.com>
wrote:
>Roy, I understand the point you are trying to make, but this part of
>your argument just doesn't hold up to scrutiny:
>
>If it's a requirement, why don't you type the columns out?
Because it is no longer obvious that it is a requirement.
>Even if a
>column is added, it's no easier (or more difficult) to recompile the
>view than it is to add the column as required.
Adding the column name is certainly not hard, but pulling up the ALTER
script and hitting execute is certainly easier, and provides less
opportunities for errors.
>I understand the point that you're trying to make, and I think you're
>right about the potential value of SELECT * when used correctly, but
>I'm hard pressed to find a scenario where SELECT * is preferable to
>typing column names out.
OK, suppose I have a table with 30 columns. Rather than use *, I have
included all 30 column names. Six months later, someone else is
spending their day going through the code making sure they understand
what it does. They see all those column names, and they wonder, is
that all of them? Or only most of them? And they have to start
counting, ormatching them up. And now imagine that the order was
changed. I simply do not see what has been accomplished by replacing
the clarity of * with the relative obscurity of a list column names.
>Stu
Roy
Showing posts with label col2. Show all posts
Showing posts with label col2. Show all posts
Wednesday, March 28, 2012
Performance Difference between SELECT * and SELECT col1, col2, ...coln
A co-workers is using SELECT *. Instead of just repeating that SELECT *
should not be used, I wanted to demonstrate why it is not used, even if
all of the column should be returned.
I've run server traces, statistics IO and time and statistics profile
but I haven't seen a difference between explicitly listing the columns
and using SELECT *.
Is there a performance difference and if there is, how do you show it?
I'm running SQL Server 2000 sp4.
Thank you in advance.
If there needs to be an example...
CREATE TABLE t (a int, b int, c int, d int)
INSERT INTO t ( a, b, c, d)
VALUES (1,2,3,4)
INSERT INTO t ( a, b, c, d)
VALUES (11,12,13,14)
/*Should there be any difference between the two queries below?*/
SELECT *
FROM t
WHERE a = 11
SELECT a,b,c,d
FROM t
WHERE a = 11
DROP TABLE t
*** Sent via Developersdex http://www.examnotes.net ***>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
CREATE TABLE dbo.MyTable
(
Col1 int,
Col2 int
)
GO
INSERT INTO dbo.MyTable VALUES(1,2)
GO
CREATE VIEW dbo.vw_MyTable
AS
SELECT * FROM dbo.MyTable
GO
ALTER TABLE dbo.MyTable
DROP COLUMN Col1
GO
SELECT * FROM dbo.vw_MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||I doubt that you would find any significant differences if you are s
ing
the return of all columns of data.
However, that said, it seems like SELECT * is used out of laziness even when
all of the columns returned are not required.
If all columns are not required, it may be possible to return the resultset
from INDEX joins -in which case, there could be a substaintial penalty for
SELECT *. There could also be an negative impact upon network traffic.
Most of us consider it a 'best practice' to explicitly denote what data is
required for the operation at hand. With 'drag and drop' from most of the
current query development tools, it doesn't even require any more typing.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||Inside of SQL Server, it's not all about performance, IMHO. (And I believe
SQL Server has become better over the years at materializing the column list
at little or no cost.) I think you may see a marginal jump in overhead if
you get down to it over a remote connection and through layers like ADO, as
they will have to translate that list to a column list and get the
datatypes, etc. I'm not sure if the data providers have made strides in
that area.
Even within SQL Server, a simple example to show the *potential* performance
impacts:
Add 12 NVARCHAR(MAX) columns, that this portion of the application doesn't
need, fill them with data, and leave the two queries as is (e.g. don't
explicitly add the new columns to the explicit column list). Because they
used SELECT *, they are retrieving all that data over the wire even though
they didn't mean to ask for it. They probably won't even know that the
columns were added.
Others point out the logical problems with using SELECT *. It's lazy and
convenient, but it sure can bite you in the a**. The problem is that people
save the query using SELECT * because that's easy to do right now, and there
is no forethought whatsoever into the reality that applications and schemas
change. Or the ability to figure out that dragging the comma-separated list
of columns from the Object Browser is a 2-second job, no typing required.
Other than the fact that it doesn't suck any worse, what are your
co-worker's arguments FOR using SELECT *?
A
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||On Mon, 03 Jul 2006 13:41:44 -0700, darter <dd@.email.com> wrote:
>Instead of just repeating that SELECT *
>should not be used
Personally I am quite tired of this conventional wisdom.
Of course, if you do not need every column returned you should specify
just the columns you need. I have no problem with that.
But if you see * in one of my SELECT lists in production code it does
not mean I was lazy. It means that the REQUIREMENT is to return EVERY
row in the table.
Simple example: a view that starts with an existing table and adds
some columns by joining or a subquery. This calls for a qualified *:
CREATE VIEW Something_V
AS
SELECT A.*,
(<subquery> ) as X,
(<subquery> ) as Y
FROM Something as A
If a new column is added to A there is no question that it belongs in
the view, and no question that a recompile will add it.
With * the sequence of the columns is predictable. There is no chance
that a column will be left out. There is no chance that a comma will
be left out, skipping a column and assigning its name to a different
column.
Yes, * is often abused, but it is also a valuable feature when used
correctly.
Roy Harvey
Beacon Falls, CT
should not be used, I wanted to demonstrate why it is not used, even if
all of the column should be returned.
I've run server traces, statistics IO and time and statistics profile
but I haven't seen a difference between explicitly listing the columns
and using SELECT *.
Is there a performance difference and if there is, how do you show it?
I'm running SQL Server 2000 sp4.
Thank you in advance.
If there needs to be an example...
CREATE TABLE t (a int, b int, c int, d int)
INSERT INTO t ( a, b, c, d)
VALUES (1,2,3,4)
INSERT INTO t ( a, b, c, d)
VALUES (11,12,13,14)
/*Should there be any difference between the two queries below?*/
SELECT *
FROM t
WHERE a = 11
SELECT a,b,c,d
FROM t
WHERE a = 11
DROP TABLE t
*** Sent via Developersdex http://www.examnotes.net ***>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
CREATE TABLE dbo.MyTable
(
Col1 int,
Col2 int
)
GO
INSERT INTO dbo.MyTable VALUES(1,2)
GO
CREATE VIEW dbo.vw_MyTable
AS
SELECT * FROM dbo.MyTable
GO
ALTER TABLE dbo.MyTable
DROP COLUMN Col1
GO
SELECT * FROM dbo.vw_MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||I doubt that you would find any significant differences if you are s
ingthe return of all columns of data.
However, that said, it seems like SELECT * is used out of laziness even when
all of the columns returned are not required.
If all columns are not required, it may be possible to return the resultset
from INDEX joins -in which case, there could be a substaintial penalty for
SELECT *. There could also be an negative impact upon network traffic.
Most of us consider it a 'best practice' to explicitly denote what data is
required for the operation at hand. With 'drag and drop' from most of the
current query development tools, it doesn't even require any more typing.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||Inside of SQL Server, it's not all about performance, IMHO. (And I believe
SQL Server has become better over the years at materializing the column list
at little or no cost.) I think you may see a marginal jump in overhead if
you get down to it over a remote connection and through layers like ADO, as
they will have to translate that list to a column list and get the
datatypes, etc. I'm not sure if the data providers have made strides in
that area.
Even within SQL Server, a simple example to show the *potential* performance
impacts:
Add 12 NVARCHAR(MAX) columns, that this portion of the application doesn't
need, fill them with data, and leave the two queries as is (e.g. don't
explicitly add the new columns to the explicit column list). Because they
used SELECT *, they are retrieving all that data over the wire even though
they didn't mean to ask for it. They probably won't even know that the
columns were added.
Others point out the logical problems with using SELECT *. It's lazy and
convenient, but it sure can bite you in the a**. The problem is that people
save the query using SELECT * because that's easy to do right now, and there
is no forethought whatsoever into the reality that applications and schemas
change. Or the ability to figure out that dragging the comma-separated list
of columns from the Object Browser is a 2-second job, no typing required.
Other than the fact that it doesn't suck any worse, what are your
co-worker's arguments FOR using SELECT *?
A
"darter" <dd@.email.com> wrote in message
news:e0vwZEunGHA.4124@.TK2MSFTNGP03.phx.gbl...
>A co-workers is using SELECT *. Instead of just repeating that SELECT *
> should not be used, I wanted to demonstrate why it is not used, even if
> all of the column should be returned.
> I've run server traces, statistics IO and time and statistics profile
> but I haven't seen a difference between explicitly listing the columns
> and using SELECT *.
> Is there a performance difference and if there is, how do you show it?
> I'm running SQL Server 2000 sp4.
> Thank you in advance.
> If there needs to be an example...
> CREATE TABLE t (a int, b int, c int, d int)
> INSERT INTO t ( a, b, c, d)
> VALUES (1,2,3,4)
> INSERT INTO t ( a, b, c, d)
> VALUES (11,12,13,14)
> /*Should there be any difference between the two queries below?*/
> SELECT *
> FROM t
> WHERE a = 11
> SELECT a,b,c,d
> FROM t
> WHERE a = 11
> DROP TABLE t
> *** Sent via Developersdex http://www.examnotes.net ***|||On Mon, 03 Jul 2006 13:41:44 -0700, darter <dd@.email.com> wrote:
>Instead of just repeating that SELECT *
>should not be used
Personally I am quite tired of this conventional wisdom.
Of course, if you do not need every column returned you should specify
just the columns you need. I have no problem with that.
But if you see * in one of my SELECT lists in production code it does
not mean I was lazy. It means that the REQUIREMENT is to return EVERY
row in the table.
Simple example: a view that starts with an existing table and adds
some columns by joining or a subquery. This calls for a qualified *:
CREATE VIEW Something_V
AS
SELECT A.*,
(<subquery> ) as X,
(<subquery> ) as Y
FROM Something as A
If a new column is added to A there is no question that it belongs in
the view, and no question that a recompile will add it.
With * the sequence of the columns is predictable. There is no chance
that a column will be left out. There is no chance that a comma will
be left out, skipping a column and assigning its name to a different
column.
Yes, * is often abused, but it is also a valuable feature when used
correctly.
Roy Harvey
Beacon Falls, CT
Monday, February 20, 2012
Percentage Calulation in RS
Hey all,
I need to calculate % bases on 2 columns (Col1 and Col2), and my result that
I want is in Col3(%). Please see the layout below. Is that possible?
Col1 Col2 Col3(%)
--
A 5 50%
B 2 20%
C 3 30%
D 0 0%
Total 10 100%
Thanks in advance!
Calculate Percentage> Col1 Col2 Col3(%)
> --
> A 5 (textbox2) 50%
> B 2 (textbox2) 20%
> C 3 (textbox2) 30%
> D 0 (textbox2) 0%
> Total 10(textbox34) 100%
Assuming you have Tabular report Design,
Expression in Col3 can be -
= Reportitems!textbox2.value/Reportitems!textbox34.value
and Format field with %
(My formula syntax can be wrong, use textbox reading syntax)
I need to calculate % bases on 2 columns (Col1 and Col2), and my result that
I want is in Col3(%). Please see the layout below. Is that possible?
Col1 Col2 Col3(%)
--
A 5 50%
B 2 20%
C 3 30%
D 0 0%
Total 10 100%
Thanks in advance!
Calculate Percentage> Col1 Col2 Col3(%)
> --
> A 5 (textbox2) 50%
> B 2 (textbox2) 20%
> C 3 (textbox2) 30%
> D 0 (textbox2) 0%
> Total 10(textbox34) 100%
Assuming you have Tabular report Design,
Expression in Col3 can be -
= Reportitems!textbox2.value/Reportitems!textbox34.value
and Format field with %
(My formula syntax can be wrong, use textbox reading syntax)
Subscribe to:
Posts (Atom)