Does SQL Server 2005 Support the List Statement?

We have a comprehensive windows based application which uses a database othe
r
than SQL Server. We have been considering moving our application to SQL
server but have been stymied by limitations in the ANS SQL implementation as
well has hidden restrictions in Decalarative Referential integrity.
One statement we use a lot in reporting is the ANS List statment (a scalar
aggregate function for strings which can return a set of results in a
appropriate delimited string).
Does anyone know if the List statement is implemented fully in SQL Server
2005.
If not, we would be interested in what techniques others use to get around
this limitation.
Thanks> One statement we use a lot in reporting is the ANS List statment (a scalar
> aggregate function for strings which can return a set of results in a
> appropriate delimited string).
> Does anyone know if the List statement is implemented fully in SQL Server
> 2005.
NO, it is not.

> If not, we would be interested in what techniques others use to get around
> this limitation.
http://www.aspfaq.com/2529|||"Glenn" wrote:

> We have a comprehensive windows based application which uses a database ot
her
> than SQL Server. We have been considering moving our application to SQL
> server but have been stymied by limitations in the ANS SQL implementation
as
> well has hidden restrictions in Decalarative Referential integrity.
> One statement we use a lot in reporting is the ANS List statment (a scalar
> aggregate function for strings which can return a set of results in a
> appropriate delimited string).
> Does anyone know if the List statement is implemented fully in SQL Server
> 2005.
> If not, we would be interested in what techniques others use to get around
> this limitation.
> Thanks
When you say "ANS SQL" do you mean ANSI SQL? If so, I think you are
mistaken. The LIST "aggregate" has never been part of standard SQL. It is a
proprietary feature in DB2 I believe (and possibly others).
There are a number of logical and practical problems with the concept of a
"string concatenation aggregate". The problems are to do with the fact that
concatentation implies order, which implies sorting. Furthermore, determinis
m
requires unique sorting. This means that "LIST" is A) potentially expensive
on performance B) difficult to ensure reliable results in queries C) totally
contrary to the way other SQL queries and aggregates work.
The ANSI response to "ordered" functions is the Windowed functions and these
*are* supported by SQL Server 2005. Theoretically you can kludge your own
string aggregate using standard SQL, provided you can set some reasdonable
upper limit to the number of items to be concatenated. Allternatively there
are non-standard workarounds in TSQL as there apparently are in your current
database.
Rather than try to support such a potential kludge in the database I suggest
you look at doing this client-side. In ADO you have the GetString method to
serve that purpose.
David Portas
SQL Server MVP
--|||Thanks very much for your response. This is a bit disappointing. We had so
hoped they would address this item as it is so commonly used in other SQL
implementations.
I took a look at the faq sample - and though helpful - and it looks like it
might add quite a lot of complexity to the SQL in many of our queries. I
think it is unfortunate that Microsoft continues to add many new features to
its SQL implementation, yet seems to have long ignored the ANS standard SQL.
It makes it difficult for developers and ISV's to move their applications
from other vendors to the M/S platform.
Glenn
"Aaron Bertrand [SQL Server MVP]" wrote:

> NO, it is not.
>
> http://www.aspfaq.com/2529
>
>|||> yet seems to have long ignored the ANS standard SQL.
Why do you think LIST is ANSI standard?
A|||Hi, this is pretty helpful, although it was my understanding that the SELECT
LIST command was ANSI standard although there were various vendor extensions
to the feature added for control of concatenation, separators, and order.
We are a bit spoiled by the iAnywhere implementation which works very
efficiently for our application and allows for ordering of the result (an
obvious Vendor extention). We have also tried doing this on the client side
,
and it was much-much slower.
Thanks for your help, looks like we are going to need to engage a SQL port
specialist.
BTW - We utilize quite a few Scalar Subqueries in our application, do you
feel that using these on SQL Server 2005 will introduce any special
performance or implementation problems.
Glenn
"David Portas" wrote:

> "Glenn" wrote:
>
> When you say "ANS SQL" do you mean ANSI SQL? If so, I think you are
> mistaken. The LIST "aggregate" has never been part of standard SQL. It is
a
> proprietary feature in DB2 I believe (and possibly others).
> There are a number of logical and practical problems with the concept of a
> "string concatenation aggregate". The problems are to do with the fact tha
t
> concatentation implies order, which implies sorting. Furthermore, determin
ism
> requires unique sorting. This means that "LIST" is A) potentially expensiv
e
> on performance B) difficult to ensure reliable results in queries C) total
ly
> contrary to the way other SQL queries and aggregates work.
> The ANSI response to "ordered" functions is the Windowed functions and the
se
> *are* supported by SQL Server 2005. Theoretically you can kludge your own
> string aggregate using standard SQL, provided you can set some reasdonable
> upper limit to the number of items to be concatenated. Allternatively ther
e
> are non-standard workarounds in TSQL as there apparently are in your curre
nt
> database.
> Rather than try to support such a potential kludge in the database I sugge
st
> you look at doing this client-side. In ADO you have the GetString method t
o
> serve that purpose.
> --
> David Portas
> SQL Server MVP
> --
>|||In our database documentation it was descibed as ANS 92/99 with optional
vendor extensions.
--
Glenn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why do you think LIST is ANSI standard?
> A
>
>|||LIST isn't ANSI Standard and never has been - I just checked the
standards docs. The reason is probably related to some of the logical
problems I mentioned. It just doesn't make sense as an "aggregate"
function.
I think SQL Server is actually reasonably good on standard SQL
compliance, and it goes further with 2005 - the SQL1999 OLAP functions
for example -although it certainly could do better.
David Portas
SQL Server MVP
--|||I do not wish to offend anyone, but this is - after all - the 21st century:
consider using XML (e.g. in SQL 2005) to replace the less useful LIST
function, if you really need to move sets of data about on the server.
ML|||Can you give me link to the ANSI SQL specification documents?
Thanks
--
Glenn
"David Portas" wrote:

> LIST isn't ANSI Standard and never has been - I just checked the
> standards docs. The reason is probably related to some of the logical
> problems I mentioned. It just doesn't make sense as an "aggregate"
> function.
> I think SQL Server is actually reasonably good on standard SQL
> compliance, and it goes further with 2005 - the SQL1999 OLAP functions
> for example -although it certainly could do better.
> --
> David Portas
> SQL Server MVP
> --
>

Does It Matter...

MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it very clear to anyone
who inherits the code (or myself, when I go senile) which criteria are for
the relationship and which criteria are meant to limit the end result.
For OUTER JOINs, it can certainly matter, but it depends on your desired
result. You may exclude rows by moving criteria from ON to WHERE or vice
versa. I don't know of any situations in INNER JOIN where this is true, but
I bet Itzik or Steve will reproduce one if it exists.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you
place additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows
before joining them? or should I put anything that is not pertinent to the
join itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Pre SQL 2000 SP4 I would say "Yes" it does matter.
Post SP4 I would say "No" it doesn't matter.
SP4 has given some huge performance gains at my site.
However, try it for yourself. Use Profiler / view the Execution plan etc.
To establish your version use SELECT @.@.VERSION.
http://www.aspfaq.com/SQL2000Builds.asp
--
HTH. Ryan
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.764
@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Thanks Aaron,
Actually I'm more concerned with performance at this point.
Any thoughts on that...
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:OUZNW4nfGH
A.2032@.TK2MSFTNGP02.phx.gbl...
> For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it
> very clear to anyone who inherits the code (or myself, when I go senile) w
hich criteria are for the relationship and which
> criteria are meant to limit the end result.
> For OUTER JOINs, it can certainly matter, but it depends on your desired r
esult. You may exclude rows by moving criteria from ON
> to WHERE or vice versa. I don't know of any situations in INNER JOIN wher
e this is true, but I bet Itzik or Steve will reproduce
> one if it exists.
>
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.7
64@.TK2MSFTNGP03.phx.gbl...
> MSSQl 2000
> When creating a select statement with joins... does it matter where you pl
ace additional where-clause criteria.
> Considering the two examples below, is it more efficient to place addition
al filtering criteria within the join section.? Does it
> weed out extra rows before joining them? or should I put anything that is
not pertinent to the join itself down below in its own
> where clause?
> EXAMPLE 1
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
> inner join table_c C ON C.column_2 = B.column_2
>
> EXAMPLE 2
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> inner join table_c C ON C.column_2 = B.column_2
> WHERE
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
>
>
>
>|||I think as a rule, the optimizer will do the same thing regardless, although
as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When optimizing
the engine will check only so many possible paths before determining which
one to use, so on larger more complex queries the order of the joins and
criteria can determine which paths get evaluated before it gives up and
chooses one.
I think the bottom line is theoretically it doesn't matter, but the only way
to be totally certain is to test it out both ways. Not just the location of
the criteria, but the order of the tables as well. For simpler queries with
a handful of joins and filter criteria, when the optimizer can afford to
calculate every possibility, it should work the same. If you have dozens of
tables and just as many filters involved, it is worth playing with different
scenarios to see if it makes a difference. With more complex queries the
optimizer can find literally billions of possible execution plans, and
influencing it to look at the right ones can be hit or miss.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron,
> Actually I'm more concerned with performance at this point.
> Any thoughts on that...
> Robert
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
the filtering criteria in the WHERE clause. This makes it
which criteria are for the relationship and which
result. You may exclude rows by moving criteria from ON
where this is true, but I bet Itzik or Steve will reproduce
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
place additional where-clause criteria.
additional filtering criteria within the join section.? Does it
is not pertinent to the join itself down below in its own
'01/31/06'
>|||I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what the others have said. MS considers is more correct
to place the items on the join itself, as this will help SQL to choose the
best execution plan. The idea is that placing more items on the join, means
that the selected table will return less results, before the Join is
executed.
The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the optimising and tuning course for SQL 2005!
My personal view is to use the Join in preference to the Where clause, I
find that it helps to make the syntax clearer and easier to understand.
Regards
Colin Dawson
www.cjdawson.com
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>I think as a rule, the optimizer will do the same thing regardless,
>although
> as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When
> optimizing
> the engine will check only so many possible paths before determining which
> one to use, so on larger more complex queries the order of the joins and
> criteria can determine which paths get evaluated before it gives up and
> chooses one.
> I think the bottom line is theoretically it doesn't matter, but the only
> way
> to be totally certain is to test it out both ways. Not just the location
> of
> the criteria, but the order of the tables as well. For simpler queries
> with
> a handful of joins and filter criteria, when the optimizer can afford to
> calculate every possibility, it should work the same. If you have dozens
> of
> tables and just as many filters involved, it is worth playing with
> different
> scenarios to see if it makes a difference. With more complex queries the
> optimizer can find literally billions of possible execution plans, and
> influencing it to look at the right ones can be hit or miss.
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> the filtering criteria in the WHERE clause. This makes it
> which criteria are for the relationship and which
> result. You may exclude rows by moving criteria from ON
> where this is true, but I bet Itzik or Steve will reproduce
> news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
> place additional where-clause criteria.
> additional filtering criteria within the join section.? Does it
> is not pertinent to the join itself down below in its own
> '01/31/06'
>|||> The blurb says that the Join syntax is evaluated before the Where syntax.
That is only the logical order. For inner joins, it doesn't matter, and I do
n't even think that the
optimizer know what join type you expressed (the query is transformed into a
tree structure before
the optimizer gets hold of it). The optimizer is free to transform the query
in any way as long as
it returns the same information as if it executed the query as per the rules
for the logical order.

> The course is the optimizing and tuning course for SQL 2005!
Interesting. Which one? There are two such courses, one for "admins" and one
for "developers". Also,
can you point to the module and perhaps even page number and I'll have a loo
k at how they phrase it.

> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what
>the others have said. MS considers is more correct to place the items on t
he join itself, as this
>will help SQL to choose the best execution plan. The idea is that placing
more items on the join,
>means that the selected table will return less results, before the Join is
executed.
> The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the
> optimising and tuning course for SQL 2005!
> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||Hello Tibor
The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
The bit that I was referring two use in Unit 3. Specifically the Query
logical flow diagram on page 2.
Basically it shows the flow as
From & Join --> Where --> Select -- > .... (lots more stuff)
From experience I do agree that it doesn't seem to matter as the query
optimiser does make changes to the query as typed, into how it wants to
produce the results.
Regards
Colin Dawson
www.cjdawson.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> That is only the logical order. For inner joins, it doesn't matter, and I
> don't even think that the optimizer know what join type you expressed (the
> query is transformed into a tree structure before the optimizer gets hold
> of it). The optimizer is free to transform the query in any way as long as
> it returns the same information as if it executed the query as per the
> rules for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and
> one for "developers". Also, can you point to the module and perhaps even
> page number and I'll have a look at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>|||My personal preference is to use the primary key/foreign key relation
columns in the join clause, and all other predicates in the where
clause. This is a very consistent syntax that underscores the table
relations and automatically moves all filters to the where clause.
As mentioned before it is a different story for outer joins...
Gert-Jan
Tibor Karaszi wrote:
>
> That is only the logical order. For inner joins, it doesn't matter, and I
don't even think that the
> optimizer know what join type you expressed (the query is transformed into
a tree structure before
> the optimizer gets hold of it). The optimizer is free to transform the que
ry in any way as long as
> it returns the same information as if it executed the query as per the rul
es for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and o
ne for "developers". Also,
> can you point to the module and perhaps even page number and I'll have a l
ook at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...|||Colin,

> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
Thanks. I had a quick look through the courses after I posted prior reply, a
nd I guessed this was
the one. The important part here is that it is the *logical* flow. Quote fro
m the same page:
"
Note that
although there is a guaranteed logical order, this is not true of the actual
physical order. The
query
processor can process the query in a different order but still ensure the sa
me results, if it can
find a
more efficient method for doing so.
"
If the optimizer had to respect the logical flow, then almost every query wo
uld give us horrendous
performance:
FROM, grab all columns, and even cross join if old style join syntax
WHERE remove the rows that doesn't satisfies the conditions (including the j
oin if old-style join)
GROUP BY
HAVING
SELECT, until now we had all the columns from all the tables
ORDER BY, not until now could we sort the rows
TOP, ouch, all rows had to be sorted until we throw away all but "top n".
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:VsIcg.74297$wl.32163@.text.news.blueyonder.co.uk...
> Hello Tibor
> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
> Basically it shows the flow as
> From & Join --> Where --> Select -- > .... (lots more stuff)
>
> From experience I do agree that it doesn't seem to matter as the query opt
imiser does make changes
> to the query as typed, into how it wants to produce the results.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
>