asterisk in select_list in queries...

I have been using a subset of the SQL language for queries in FoxPro. I have
been advised against the use of the asterisk in the select_list portion of
queries based on slower performance due to no explicit column names to
instruct SQL Server which indices to utilize. Do you have a short
explanation on the validity of this advice? If so, and it is completely
valid, what are some shortcuts for writing queries where tables contain large
numbers of columns?
Best practice is to avoid using SELECT * in queries (except in an EXISTS
subquery or other subqueries that don't return data and aren't referenced by
an outer query).
Listing the column names makes sense in an N-Tier environment because you
want to make efficient use of network resources by returning to the client
only the data that is actually needed. This is an important difference from
a desktop, ISAM database like FoxPro where you have to retrieve a whole
record whether all the data is required or not.
Listing only the required columns also increases the opportunities for SQL
Server to optimize your query by making use of indexes.
Also, listing column names improves reliability and ease of maintenance. If
you later add another column you don't want to break existing code that
doesn't require that column. Use a column list and then just modify the code
that needs to reference the new column. If you use an asterisk in your
SELECT list then potentially more code could need modification and more code
would need to be unit-tested for each schema change.
Shortcuts? In Query Analyzer you can drag a list of column names from the
Object Browser into the query window. That can save you a lot of typing.
David Portas
SQL Server MVP

asterisk in select_list in queries...

I have been using a subset of the SQL language for queries in FoxPro. I have
been advised against the use of the asterisk in the select_list portion of
queries based on slower performance due to no explicit column names to
instruct SQL Server which indices to utilize. Do you have a short
explanation on the validity of this advice? If so, and it is completely
valid, what are some shortcuts for writing queries where tables contain large
numbers of columns?Best practice is to avoid using SELECT * in queries (except in an EXISTS
subquery or other subqueries that don't return data and aren't referenced by
an outer query).
Listing the column names makes sense in an N-Tier environment because you
want to make efficient use of network resources by returning to the client
only the data that is actually needed. This is an important difference from
a desktop, ISAM database like FoxPro where you have to retrieve a whole
record whether all the data is required or not.
Listing only the required columns also increases the opportunities for SQL
Server to optimize your query by making use of indexes.
Also, listing column names improves reliability and ease of maintenance. If
you later add another column you don't want to break existing code that
doesn't require that column. Use a column list and then just modify the code
that needs to reference the new column. If you use an asterisk in your
SELECT list then potentially more code could need modification and more code
would need to be unit-tested for each schema change.
Shortcuts? In Query Analyzer you can drag a list of column names from the
Object Browser into the query window. That can save you a lot of typing.
--
David Portas
SQL Server MVP
--

Assistance building a query...

I am trying to generate some datasets with some queries...

With a given series information, it should return PART_NOs that has STD
= 1 and a unique price at that particular 'START', and keeping the
'TYPE' in consideration...

DB examples below:

Main DB

IDPART_NOSERIESSTD
1A-1A1
2A-2A1
3A-3A1
4D-1D1
5D-2D0

Price DB

IDPART_IDTYPESTARTPRICE
501X100050
511X1000040
521Y100060
531Y1000050
542X100050
552X1000040
562Y100060
572Y1000050
582X100090

etc.

main.ID and Price.PART_ID are paired together.

So in an example case, lets say I am querying for SERIES A, with TYPE
X. A table should be outputted something like

PART_NO
A-1100050
A-11000040
A-3100090

Note how it skipped printing A2 because the price is the same as A1.

I'm really looking for the SQL code here... I can't get it to filter on
distinct price.

SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
FROM MAIN, PRICING
WHERE (MAIN.SERIES LIKE 'A')
AND (MAIN.STD = '1')
AND (PRICING.PRICE != '')
AND (PRICING.TYPE = 'X')
AND (MAIN.ID = PRICING.PART_ID)

I've been trying to use GROUP BY and HAVING to get what I need but it
doesn't seem to fit the bill. I guess I'm not terribly clear on how I
can use the SQL DISTINCT command...? If I try and use it in my WHERE
statement it gives me syntax errors, from what I understand you can
only have distinct in the select statement? I'm not sure how to
integrate that into the query to suit my needs.

Thanks for any help.A bit of clarification on my problem

If I just do a straight SQL distinct on my select statement, it does
what I want when you get down to it, but it completely destroys the
organization of the table. The part numbers were entered in a certain
manner and I do not believe they can be reorganized through any typical
sort. For example A-105A is higher then A-400B, but if you sorted in
Excel (for example) it would put 105 below 400

mazzarin@.gmail.com wrote:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
> = 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> IDPART_NOSERIESSTD
> 1A-1A1
> 2A-2A1
> 3A-3A1
> 4D-1D1
> 5D-2D0
> Price DB
> IDPART_IDTYPESTARTPRICE
> 501X100050
> 511X1000040
> 521Y100060
> 531Y1000050
> 542X100050
> 552X1000040
> 562Y100060
> 572Y1000050
> 582X100090
> etc.
> main.ID and Price.PART_ID are paired together.
>
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1100050
> A-11000040
> A-3100090
> Note how it skipped printing A2 because the price is the same as A1.
>
> I'm really looking for the SQL code here... I can't get it to filter on
> distinct price.
> SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> FROM MAIN, PRICING
> WHERE (MAIN.SERIES LIKE 'A')
> AND (MAIN.STD = '1')
> AND (PRICING.PRICE != '')
> AND (PRICING.TYPE = 'X')
> AND (MAIN.ID = PRICING.PART_ID)
> I've been trying to use GROUP BY and HAVING to get what I need but it
> doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> can use the SQL DISTINCT command...? If I try and use it in my WHERE
> statement it gives me syntax errors, from what I understand you can
> only have distinct in the select statement? I'm not sure how to
> integrate that into the query to suit my needs.
> Thanks for any help.|||Actually never mind, it doesn't do exactly what I want it to do... All
the prices are still duplicated, ideally I should only have at most 3
results being returned (according to the actual data being fed in)

I am beyond confused heh

I think I might have to do the filtering outside of SQL

mazzarin@.gmail.com wrote:
> A bit of clarification on my problem
> If I just do a straight SQL distinct on my select statement, it does
> what I want when you get down to it, but it completely destroys the
> organization of the table. The part numbers were entered in a certain
> manner and I do not believe they can be reorganized through any typical
> sort. For example A-105A is higher then A-400B, but if you sorted in
> Excel (for example) it would put 105 below 400
>
> mazzarin@.gmail.com wrote:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> > = 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > IDPART_NOSERIESSTD
> > 1A-1A1
> > 2A-2A1
> > 3A-3A1
> > 4D-1D1
> > 5D-2D0
> > Price DB
> > IDPART_IDTYPESTARTPRICE
> > 501X100050
> > 511X1000040
> > 521Y100060
> > 531Y1000050
> > 542X100050
> > 552X1000040
> > 562Y100060
> > 572Y1000050
> > 582X100090
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1100050
> > A-11000040
> > A-3100090
> > Note how it skipped printing A2 because the price is the same as A1.
> > I'm really looking for the SQL code here... I can't get it to filter on
> > distinct price.
> > SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICE
> > FROM MAIN, PRICING
> > WHERE (MAIN.SERIES LIKE 'A')
> > AND (MAIN.STD = '1')
> > AND (PRICING.PRICE != '')
> > AND (PRICING.TYPE = 'X')
> > AND (MAIN.ID = PRICING.PART_ID)
> > I've been trying to use GROUP BY and HAVING to get what I need but it
> > doesn't seem to fit the bill. I guess I'm not terribly clear on how I
> > can use the SQL DISTINCT command...? If I try and use it in my WHERE
> > statement it gives me syntax errors, from what I understand you can
> > only have distinct in the select statement? I'm not sure how to
> > integrate that into the query to suit my needs.
> > Thanks for any help.|||(mazzarin@.gmail.com) writes:
> I am trying to generate some datasets with some queries...
> With a given series information, it should return PART_NOs that has STD
>= 1 and a unique price at that particular 'START', and keeping the
> 'TYPE' in consideration...
> DB examples below:
> Main DB
> ID PART_NO SERIES STD
> 1 A-1 A 1
> 2 A-2 A 1
> 3 A-3 A 1
> 4 D-1 D 1
> 5 D-2 D 0
> Price DB
> ID PART_ID TYPE START PRICE
> 50 1 X 1000 50
> 51 1 X 10000 40
> 52 1 Y 1000 60
> 53 1 Y 10000 50
> 54 2 X 1000 50
> 55 2 X 10000 40
> 56 2 Y 1000 60
> 57 2 Y 10000 50
> 58 2 X 1000 90
> etc.
> main.ID and Price.PART_ID are paired together.
> So in an example case, lets say I am querying for SERIES A, with TYPE
> X. A table should be outputted something like
> PART_NO
> A-1 1000 50
> A-1 10000 40
> A-3 1000 90
> Note how it skipped printing A2 because the price is the same as A1.

But why does A-3 appear? Ir does not seem to appear in the Price DB
at all?

If there is an A-4 with the same values as A-1 would that be printed?

I'm sorry, but as you have presented the problem there are two many
unknowns. Furthermore, there is a standard recommendation that you include
in your post:

o CREATE TABLE statements for you table(s).
o INSERT statemetns with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer
to play around with the data and develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I actually ended up doing multiple queries, passing info between VBA
and SQL. Its not as nice of a solution but it works. Thank you for your
advice though. I will keep it in mind next time!

Erland Sommarskog wrote:
> (mazzarin@.gmail.com) writes:
> > I am trying to generate some datasets with some queries...
> > With a given series information, it should return PART_NOs that has STD
> >= 1 and a unique price at that particular 'START', and keeping the
> > 'TYPE' in consideration...
> > DB examples below:
> > Main DB
> > ID PART_NO SERIES STD
> > 1 A-1 A 1
> > 2 A-2 A 1
> > 3 A-3 A 1
> > 4 D-1 D 1
> > 5 D-2 D 0
> > Price DB
> > ID PART_ID TYPE START PRICE
> > 50 1 X 1000 50
> > 51 1 X 10000 40
> > 52 1 Y 1000 60
> > 53 1 Y 10000 50
> > 54 2 X 1000 50
> > 55 2 X 10000 40
> > 56 2 Y 1000 60
> > 57 2 Y 10000 50
> > 58 2 X 1000 90
> > etc.
> > main.ID and Price.PART_ID are paired together.
> > So in an example case, lets say I am querying for SERIES A, with TYPE
> > X. A table should be outputted something like
> > PART_NO
> > A-1 1000 50
> > A-1 10000 40
> > A-3 1000 90
> > Note how it skipped printing A2 because the price is the same as A1.
> But why does A-3 appear? Ir does not seem to appear in the Price DB
> at all?
> If there is an A-4 with the same values as A-1 would that be printed?
> I'm sorry, but as you have presented the problem there are two many
> unknowns. Furthermore, there is a standard recommendation that you include
> in your post:
> o CREATE TABLE statements for you table(s).
> o INSERT statemetns with sample data.
> o The desired result given the sample.
> This makes it possible to easily copy and paste into Query Analyzer
> to play around with the data and develop a tested solution.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Asp.net queries with sqlserver2000

Sir,i am running .net1.1 on my system with sqlserver as my database.I am devaloping an e-trading project in
ASP.NET with VB.net.My problem is that,I am not getting the correct syntax to query the database with
SELECT(with WHERE clause)in vb.net.
Please help me.Please show your code and give the EXACT details of the error, inclusing message.

ASP.NET 1.1 Component for creating pivote tables with MS SQL Server and with MS SQL Server

Превед!

Prompt me please some asp.net 1.1 pivote table component that can use both SQL Server database (SQL queries) and Analyse Services database (MDX queries).

Thanks.

I have two links one is for 1.1 or 2.0 and the other is 2.0 and 3.0. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en
http://sqljunkies.com/WebLog/mosha/archive/2006/10/08/xaml_pivottable.aspx

|||

Thanks, but this links about Windows components. And I need asp.net component.

|||

Try these.

http://www.microsoft.com/downloads/details.aspx?familyid=4599B793-B3C6-4ED5-ACB3-820D0E832151&displaylang=en

http://www.mosha.com/msolap/util.htm#ExcelAddIns