Why can’t recursive queries contain...?
klem s
I hope I didn’t put too many questions
1) Why can’t recursive queries contain
• GROUP BY,
• LEFT/RIGHT JOINS
• SELECT DISTINCT
• AGGREGATE FUNCTIONS
• SUBQUERIES
• hints on the recursive CTE reference
BTW - What is meant “by hints on the recursive CTE reference”?
2) Anchor queries can be unioned together with UNION operator, so why
can’t recursive queries also be unioned together with UNION operator
( instead they must use UNION ALL )?
thanx
Hugo Kornelis
>hi
>
>I hope I didn�t put too many questions
>1) Why can�t recursive queries contain
>� GROUP BY,
>� LEFT/RIGHT JOINS
>� SELECT DISTINCT
>� AGGREGATE FUNCTIONS
>� SUBQUERIES
>� hints on the recursive CTE reference
Hi Klem,
Simple - because that are the limitation the development team chose to
put in place.
<speculation mode on>
Allowing these options would probably allow users to write queries that
were too hard to figure out how to do, or that would require more
development time than was necessary. Evey option allowed adds to the
complexion of the final product. Remember that if an option is allowed,
customers will expect it to work in EVERY situation, even the most wacky
ones!
</speculation mode off>
>BTW - What is meant �by hints on the recursive CTE reference�?
You can put in "hints" in several places in your query. And the name
"hints" is quite bad, since they are actually directives. You can use
them to force the optimizer to use a specific index, force it to use
some specific join strategy, or force it to follow a certain locking
strategy.
In the very simple recursive CTE below, the lines marked "-- Here!" are
the lines that containt references to the recursive CTE. So you can not
use any hints there (though you still can use hints in the rest of the
query).
WITH Recursive AS
(SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM Recursive -- Here!
WHERE Num < 10)
SELECT Num
FROM Recursive; -- Here!
>2) Anchor queries can be unioned together with UNION operator, so why
>can�t recursive queries also be unioned together with UNION operator
>( instead they must use UNION ALL )?
Probably the same reason as above. Plus, there's some ambiguity if you
allow this. Normally, rows produced in the third iteration are input for
the fourth. But what if one of the rows in the third iteration is a
duplicate of a row in the first or second iteration - the UNION (without
ALL) causes it to be removed from the final results, but is it also
removed from the input for the fourth iteration or is it only removed at
the end? If I were on the dev team, I'd figure that an unanswerable
question such as this is already a strong case to simply disallow the
option.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
--CELKO--
Standard SQL, the WITH clause is "WITH [RECURSIVE]<cte name>" so that
the compiler gets to change modes when a CTE is recursive. There are
other differences. I am at speaking at PASS now and then going to SQL
Connections, so I do not have my Standards with me; Google around for
it.
I think the Standard might allow for any set operator between the
fixpoint table expression that does not cause infinite recursion.
Farmer
1. You can use subqueries but they have to be expressed as CROSS (like JOIN)
or OUTER (like LEFT JOIN) APPLYs
WITH bla AS
(
SELECT
FROM ancor
UNION ALL
SELECT
FROM bla r
CROSS APPLY -- using OUTER APPLY will be like LEFT JOIN
(
SELECT
FROM someotherplace t
WHERE t.key = r.key
)v
)
SELECT DISTINCT
COUNT(*)
FROM bla
GROUP BY
2. You can define one CTE that uses LEFT JOIN (think of views) but in
recursive CTE you can then JOIN to that "LEFT JOIN" view.
For exampe, see code posted for this bug. Look past bug and notice how vw is
defined and how it is used in CTE. that vw can be any derived table source,
and can use LEFT JOINs. RIGHT JOIN can be re-expressed as LEFT JOIN, easily.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=496271#details
WITH vw AS
(
SELECT
FROM here
JOIN there
LEFT JOIN overthere
)
, bla AS
(
SELECT
FROM ancor
UNION ALL
SELECT
FROM bla r
JOIN vw v ON v.key = r.key
)v
)
SELECT DISTINCT
COUNT(*)
FROM bla
GROUP BY
3. You can apply DISTINCT or aggregates on the final SELECT
4. You can have query hints at the end of a query but not at the recursive
reference.
I hope this helps in your understanding.
thanks
vlad
"klem s" <sqlsu...@gmail.com> wrote in message
news:bda19704-b1ab-45fe...@d5g2000yqm.googlegroups.com...
hi
I hope I didn�t put too many questions
1) Why can�t recursive queries contain
� GROUP BY,
� LEFT/RIGHT JOINS
� SELECT DISTINCT
� AGGREGATE FUNCTIONS
� SUBQUERIES
� hints on the recursive CTE reference
BTW - What is meant �by hints on the recursive CTE reference�?
2) Anchor queries can be unioned together with UNION operator, so why
can�t recursive queries also be unioned together with UNION operator
Farmer
WITH bla AS
(
SELECT
FROM ancor
UNION ALL
SELECT
FROM bla r
OUTER APPLY
(
SELECT
FROM someplace t
WHERE t.key = r.key
)v
UNION ALL
SELECT
FROM bla r
CROSS APPLY
(
SELECT
FROM someotherplace t
WHERE t.key = r.key
)v
)
SELECT DISTINCT
COUNT(*)
FROM bla
GROUP BY
Let us say you have hierarchy
HOUSE
ROOM1
ROOM2
But each room has things in it and you know link from ROOM1 or ROOM2 to
these things
then you can build
HOUSE
ROOM1
thingA
thingB
ROOM2
thingD
CLOSET
This can be built using CTE recursive model above. One recursive query gets
hierarchy and the other things that are in specific hierarchy node.
"Farmer" <som...@somewhere.com> wrote in message
news:ekKEJw1X...@TK2MSFTNGP02.phx.gbl...
Farmer
Therefore, at each recursive level you can use aggregates with OVER clause.
Why some success? well check that MS Connect link. Some don't work as
expected. If they did, you could row_number() at each level, get max( from
derived table where row_number (level data set)) OVER () at each level and
then start the next ireration level with r.max_prev_iteration + max(from
derived table, row_number(level data set)).
WITH bla AS
(
SELECT
FROM ancor
UNION ALL
SELECT
MAX(somevalue)OVER () -- and other aggregate values
FROM bla r
OUTER APPLY
(
SELECT
FROM someplace t
WHERE t.key = r.key
)v
UNION ALL
SELECT
MAX(somevalue)OVER () -- and other aggregate values
FROM bla r
CROSS APPLY
(
SELECT
FROM someotherplace t
WHERE t.key = r.key
)v
)
SELECT DISTINCT
COUNT(*)
FROM bla
GROUP BY
"Farmer" <som...@somewhere.com> wrote in message
news:eQis751...@TK2MSFTNGP04.phx.gbl...
Michael C
> 1) Why can�t recursive queries contain
SQL server is full of limitations that don't make sense. We've only recently
got the ability to do something as basic as define a variable of infinite
length string. When we got CLR integration we were given a limit of 8K of
memory to work with (E I G H T K!!!!!!). When we got the ability to define
our own aggregates we couldn't pass any parameters into that aggregate.
I think the cause for all this is backwards compatibility. While for many
this is a good thing, for others like me, it is just holding sqlserver back
in the dark ages. Hopefully soon we get a brand new product based on LINQ.
2) Anchor queries can be unioned together with UNION operator, so why
can�t recursive queries also be unioned together with UNION operator
( instead they must use UNION ALL )?
I guess it's just assuming anything with a UNION is recursive. You can just
use a second CTE to do a DISTINCT on the first.
thanx
Erland Sommarskog
> SQL server is full of limitations that don't make sense. We've only
> recently got the ability to do something as basic as define a variable
> of infinite length string. When we got CLR integration we were given a
> limit of 8K of memory to work with (E I G H T K!!!!!!). When we got
> the ability to define our own aggregates we couldn't pass any parameters
> into that aggregate.
>
> I think the cause for all this is backwards compatibility.
Some limitations or funky behaviour is certainly due to backwards
compatibility issue. But a more common reason is that even Microsoft has
limited resources and at some point they have to ship.
As for the limitation to 8K in the CLR, this restriction has been lifted
in SQL 2008, so that you can define CLR UDTs and CLR aggregates that can
produce more than 8000 bytes.
> Hopefully soon we get a brand new product based on LINQ.
That is very unlikely. After all, SQL is the standard for relational
databases, not LINQ. And LINQ also buys you lots of limitations. For
instance, apparently you cannot use table-valued parameters with LINQ.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
klem s
Michael C
news:Xns9CBE89529...@127.0.0.1...
> Some limitations or funky behaviour is certainly due to backwards
> compatibility issue. But a more common reason is that even Microsoft has
> limited resources and at some point they have to ship.
That's true, I think there is also a mindset in the sqlserver team. The dot
net team seems to have a policy of giving users as much flexibility as
possible where sqlserver team seems to restrict users. That's obviously not
the only reason but it's got to be a contributing factor.
> As for the limitation to 8K in the CLR, this restriction has been lifted
> in SQL 2008, so that you can define CLR UDTs and CLR aggregates that can
> produce more than 8000 bytes.
That I know but to place an 8K restriction on this is a perfect of example
of the sqlserver team mindset I was talking about. This sort of thing is
unheard of in anything else modern I have used.
>> Hopefully soon we get a brand new product based on LINQ.
>
> That is very unlikely. After all, SQL is the standard for relational
> databases, not LINQ.
So what you're saying is we can't sell cars because we are a motorcycle
shop. :-) I think all the elements are in place for a LINQ database.
Obviously we have linq, we have the .net framework as a language to replace
store procs etc and we have an IDE in visual studio that could be modified
to suit a linq DB. I think this could sit along side sqlserver as a seperate
product.
> And LINQ also buys you lots of limitations. For
> instance, apparently you cannot use table-valued parameters with LINQ.
I don't think that is true. The equivelant of a table in Linq is an
IEnumerable and you can pass an IEnumerable parameter into and out of any
LINQ query in all sorts of ways with far more flexibility than sql. For
example, an IEnumerable can be a simple list of integers or it can be an
object which is the eqivelant of a row. It can even be an object with a
grouping of subobjects (eg customer with many invoices). The way a result
set from a query in sql can only be a certainly number of rows and columns
(no sub collections etc) seems very limiting.
Michael
Erland Sommarskog
> That's true, I think there is also a mindset in the sqlserver team. The
> dot net team seems to have a policy of giving users as much flexibility
> as possible where sqlserver team seems to restrict users. That's
> obviously not the only reason but it's got to be a contributing factor.
That must explain ahy in VB .Net can write:
Using cn As SqlConnection = setup_connection(), _
cmd As SqlCommand = cn.CreateCommand()
But I in C# has to write:
using (SqlConnection cn = setup_connection()) {
using (SqlCommand cmd = cn.CreateCommand()) {
> That I know but to place an 8K restriction on this is a perfect of example
> of the sqlserver team mindset I was talking about. This sort of thing is
> unheard of in anything else modern I have used.
So in any other product you work with there is a perfect functionality
matrix? Everything works with everything else?
>> That is very unlikely. After all, SQL is the standard for relational
>> databases, not LINQ.
>
> So what you're saying is we can't sell cars because we are a motorcycle
> shop. :-)
No, I'm saying that selling cars with a joystick instead of a steering
wheel is dead in the water.
>> And LINQ also buys you lots of limitations. For
>> instance, apparently you cannot use table-valued parameters with LINQ.
>
> I don't think that is true. The equivelant of a table in Linq is an
> IEnumerable and you can pass an IEnumerable parameter into and out of any
> LINQ query in all sorts of ways with far more flexibility than sql. For
> example, an IEnumerable can be a simple list of integers or it can be an
> object which is the eqivelant of a row. It can even be an object with a
> grouping of subobjects (eg customer with many invoices). The way a result
> set from a query in sql can only be a certainly number of rows and columns
> (no sub collections etc) seems very limiting.
OK, can you then provide a sample in a C# program using LINQ that calls
a stored procedure with a table-valued parameter?
Or write in LINQ a query that in Northwind database (or AdventureWorks)
retrieves this information from the first order of each customer:
CustomerID, CustomerName, order date, number of articles, and the total
order order amount.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Michael C
> That must explain ahy in VB .Net can write:
>
> Using cn As SqlConnection = setup_connection(), _
> cmd As SqlCommand = cn.CreateCommand()
>
> But I in C# has to write:
>
> using (SqlConnection cn = setup_connection()) {
> using (SqlCommand cmd = cn.CreateCommand()) {
That's is about as minor an issue as you can get. Doing what you've
described would be handy in C# but it doesn't stop you doing something.
While you picked on something completely trivial you've missed all the
massive amounts of extensibility they give you in c#. As an example, I can
write my own connection object which connects to "Mikes Propriotry File(c)"
and do anything I like with it. In sqlserver if you've got an object, eg a
cursor, then you're pretty much stuck with what they give you. What
extensibility they give you is usually extremely restrictive.
> So in any other product you work with there is a perfect functionality
> matrix? Everything works with everything else?
I never said there was. What I meant is that I have never worked with any
product (except sqlserver of course) that does something as silly as give
the programmer only 8k to work with. I don't think you understand what a
HUGE step back into the 80s that that is. This stands out as pretty much the
silliest restriction I have ever encountered in my time as a programmer.
> No, I'm saying that selling cars with a joystick instead of a steering
> wheel is dead in the water.
There was a time when people thought selling cars with headlights was a bad
idea. Maybe we'll never sell cars with a joystick but I don't think that
linq is comparable to a joystick. LINQ has really taken off and could easily
become a real competitor to sql. You've got to take into account that you
can pretty much write standard sql but back to front (select statement last)
but have the huge advantage of writing the less friendly but more functional
version eg:
From i in SomeCollect Where i.STATE = 'VIC' select i.Postcode
or
SomeCollection.Where(i => i.State == Vic).Select(i => i.POstcode)
> OK, can you then provide a sample in a C# program using LINQ that calls
> a stored procedure with a table-valued parameter?
But that's not linq, that's calling a stored proc in sqlserver. In a pure
linq environment you just pass in an IEnumerable instance.
> Or write in LINQ a query that in Northwind database (or AdventureWorks)
> retrieves this information from the first order of each customer:
> CustomerID, CustomerName, order date, number of articles, and the total
> order order amount.
I think I see what you're friend was talking about, you can't use a table
variable when working with linq that is used as a wrapper for sqlserver?
Linq to sql I think they call it. This is just crappy auto generated code,
the limitation is in what generates the code, not linq itself. IMO, linq to
sql isn't going to go anywhere.
If you're going to ignore linq-to-sql and write code yourself to call a
stored proc then surely you can pass in a table variable. I haven't used
sql2008 so don't know about this feature. If this is a limitation then the
limitation is in ado.net, not linq itself. If ado.net can pass a table
variable to sqlserver then linq can.
Michael
Michael Coles
be fair, it does a fair job of basic optimization (properly parameterizing
variables, etc.), but it doesn't really have an "industrial" optimizer built
into its code generator. Not sure how you feel LINQ will replace SQL--who's
to say EF won't replace LINQ next year?
> As an example, I can write my own connection object which connects to
> "Mikes Propriotry File(c)" and do anything I like with it. In sqlserver if
> you've got an object, eg a cursor, then you're pretty much stuck with what
> they give you. What extensibility they give you is usually extremely
> restrictive.
I was following your conversation intently, but you completely lost me on
this comparison... What problem are you trying to solve with a cursor here,
and what's the comparison between a cursor and "an object" that you're
making? If you really want to compare apples to apples (though possibly
different varieties) then you'd be better off comparing a "SqlDataReader"
"object" to a cursor. I guess you don't see a lot of comparisons of
"Connection" objects (like "SqlConnection") to "cursors", in much the same
way that you don't see people comparing grizzly bears to drywall.
--
Thanks
Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------
"Michael C" <mi...@nospam.com> wrote in message
news:etDz7xZ...@TK2MSFTNGP04.phx.gbl...
Erland Sommarskog
>>
>> using (SqlConnection cn = setup_connection()) {
>> using (SqlCommand cmd = cn.CreateCommand()) {
>
> That's is about as minor an issue as you can get. Doing what you've
> described would be handy in C# but it doesn't stop you doing something.
Certaintly it's a minor issue. But I write less than 50 lines of C#
code per month an average, so I don't run into that many limitations.
My point was just that any language and environment has its limitations.
> While you picked on something completely trivial you've missed all the
> massive amounts of extensibility they give you in c#. As an example, I
> can write my own connection object which connects to "Mikes Propriotry
> File(c)" and do anything I like with it. In sqlserver if you've got an
> object, eg a cursor, then you're pretty much stuck with what they give
> you. What extensibility they give you is usually extremely restrictive.
On the other hand, imagine that you have a gross amount of data spread
on disk in a couple of flat files, and you need to compute some sort of
report from this data. Furthermore, there is a desire that execution time
is as quick as possible. All you have is C#. Your code may be extensible,
but unless you first implement a relational engine, your code will use
the same access path every time, even if the data distribution changes
over time. And the amount of code you write will be considerable,
possible several thousands lines of code.
In an RDBMS you may be able to write a query in less of 50 lines of
SQL, and the optimizer will make sure that you get a new query plan as
data changes.
Or put it another ways: boats are extremely crappy on motorways. Even
if you put wheels on them, they are overtaken by the slowest cars. Now,
put the cars into the water...
> I never said there was. What I meant is that I have never worked with
> any product (except sqlserver of course) that does something as silly as
> give the programmer only 8k to work with. I don't think you understand
> what a HUGE step back into the 80s that that is. This stands out as
> pretty much the silliest restriction I have ever encountered in my time
> as a programmer.
Reality check here. When did you last see a C# program that on its own
was able to deal with terabytes of data?
A C# program exists under the assumption that it will never deal with
that much data. Never more than the amount of main memory in the box.
So there is no problem with having an unbounded string. There will not
be terribly many of them anyway.
SQL Server on the other hand works in a realm of potentially unbounded
amount of data. There is a real cost for handling volumes. The reason
for the border between a limited and unlimited (well 2GB) string at
8000 bytes relates to the page size on disk. There is nothing that
says that this size has to be 8000, or the page size has to be fixed.
I've worked with a product where you could set the page size yourself.
But it's also easy to see that a uniform page size, permits quite a few
simplifications which are likely to be good for performance. And an
RDBMS is lot about performance.
If you really think the limitation of 8000 chars gets in your way,
you can always declare you string variables and string columns as
(n)varchar(MAX), but I advice against it. I've found that there is a
performance penalty, even if the actual length is below 8000.
>> No, I'm saying that selling cars with a joystick instead of a steering
>> wheel is dead in the water.
>
> There was a time when people thought selling cars with headlights was a
> bad idea. Maybe we'll never sell cars with a joystick but I don't think
> that linq is comparable to a joystick.
I think you missed the point. The analogy was not meant to denigrate LINQ
as such. Rather, even if joysticks would prove to be a better way to
manoever a car, it would be a hard sell, since people are used to steering
wheels.
>> OK, can you then provide a sample in a C# program using LINQ that calls
>> a stored procedure with a table-valued parameter?
>
> But that's not linq, that's calling a stored proc in sqlserver. In a pure
> linq environment you just pass in an IEnumerable instance.
Well, you made a number of that T-SQL had so many limitations. You
will have to admit that if I cannot call a stored procedure with a
table-variable from LINQ, be that LINQ-to-SQL or LINQ-to-EF, that is
quite a limitation, since it deprives you of a very useful feature in
SQL 2008.
(To be fair, VS 2008 was released before SQL 2008 came out. Maybe they
are adressing it in VS 2010?)
>> Or write in LINQ a query that in Northwind database (or AdventureWorks)
>> retrieves this information from the first order of each customer:
>> CustomerID, CustomerName, order date, number of articles, and the total
>> order order amount.
It seems that you missed this one. But I'm still interested in seeing
the query.
> If you're going to ignore linq-to-sql and write code yourself to call a
> stored proc then surely you can pass in a table variable. I haven't used
> sql2008 so don't know about this feature. If this is a limitation then the
> limitation is in ado.net, not linq itself. If ado.net can pass a table
> variable to sqlserver then linq can.
You can pass data to TVPs from ADO .Net, using either a DataSet, a
List<SqlDataRecord> or an IDataReader. But how to do it in LINQ? Or
by using EF?
Michael C
> Certaintly it's a minor issue. But I write less than 50 lines of C#
> code per month an average, so I don't run into that many limitations.
> My point was just that any language and environment has its limitations.
I never said otherwise. What I did say is that the limitations in sqlserver
are simply bigger and sillier than the limitations in other products I have
encountered. Sometimes there are valid reasons for this, eg indexes would
place a large number of limitations on things but in other cases there is no
good reason except maybe compatibility with stuff in the past.
> On the other hand, imagine that you have a gross amount of data spread
> on disk in a couple of flat files, and you need to compute some sort of
> report from this data. Furthermore, there is a desire that execution time
> is as quick as possible. All you have is C#. Your code may be extensible,
> but unless you first implement a relational engine, your code will use
> the same access path every time, even if the data distribution changes
> over time. And the amount of code you write will be considerable,
> possible several thousands lines of code.
>
> In an RDBMS you may be able to write a query in less of 50 lines of
> SQL, and the optimizer will make sure that you get a new query plan as
> data changes.
>
> Or put it another ways: boats are extremely crappy on motorways. Even
> if you put wheels on them, they are overtaken by the slowest cars. Now,
> put the cars into the water...
C# with linq would be more than capable of being the language for a database
(not sure of the technical term here). It is certainly not a boat out of
water.
> Reality check here. When did you last see a C# program that on its own
> was able to deal with terabytes of data?
Currently it does not but we don't have that ability yet. MS would need to
write a database that uses linq instead of sql for this to be a reality.
> A C# program exists under the assumption that it will never deal with
> that much data. Never more than the amount of main memory in the box.
That's just not true. C# can easily handle more data than there is memory
available. I have a C# program that handles 80GB files. SQLServer is simply
C code, there is no reason C# can't replicate anything sqlserver does.
Besides, I'm not suggesting this "Linq database" be actually written in C#,
just that C# is the front end for it.
> SQL Server on the other hand works in a realm of potentially unbounded
> amount of data. There is a real cost for handling volumes. The reason
> for the border between a limited and unlimited (well 2GB) string at
> 8000 bytes relates to the page size on disk. There is nothing that
> says that this size has to be 8000, or the page size has to be fixed.
> I've worked with a product where you could set the page size yourself.
> But it's also easy to see that a uniform page size, permits quite a few
> simplifications which are likely to be good for performance. And an
> RDBMS is lot about performance.
There is absolutely no reason sqlserver would have an 8K limit today, except
for hangovers from the past. Maybe if we go past 8K we get a performance hit
but that would be fine.
> If you really think the limitation of 8000 chars gets in your way,
> you can always declare you string variables and string columns as
> (n)varchar(MAX), but I advice against it. I've found that there is a
> performance penalty, even if the actual length is below 8000.
It's not varchar that's the problem for me, it's calling CLR code for a
custom aggregate that is the issue.
> I think you missed the point. The analogy was not meant to denigrate LINQ
> as such. Rather, even if joysticks would prove to be a better way to
> manoever a car, it would be a hard sell, since people are used to steering
> wheels.
Ah, ok, that might be true but then again it might not. Certainly the
industry hasn't had any problem picking up .net and C# or vb.net.
> Well, you made a number of that T-SQL had so many limitations. You
> will have to admit that if I cannot call a stored procedure with a
> table-variable from LINQ, be that LINQ-to-SQL or LINQ-to-EF, that is
> quite a limitation, since it deprives you of a very useful feature in
> SQL 2008.
>
> (To be fair, VS 2008 was released before SQL 2008 came out. Maybe they
> are adressing it in VS 2010?)
But my point was this is not a limitation of linq. If it is not possible
then this is because ado.net does not allow it. If ado.net allows it then
linq can do it.
> It seems that you missed this one. But I'm still interested in seeing
> the query.
I don't have sql2008 here and it would take me several hours to get it
installed and write a sample so I cannot do this. Even if this is a
limitation of linq then it's really just a limitation interacting with sql.
> You can pass data to TVPs from ADO .Net, using either a DataSet, a
> List<SqlDataRecord> or an IDataReader. But how to do it in LINQ? Or
> by using EF?
In Linq I would just cast the data I have to a List<SqlDataRecord>.
Something like this:
MyCollection.Select(i => i.GetSqlDataRecord).ToList();
where GetSqlDataRecord is a function that would return an SqlDataRecord.
Michael
Michael C
news:eI7wW6Za...@TK2MSFTNGP06.phx.gbl...
> The LINQ provider for SQL actually generates SQL code behind the scenes.
> To be fair, it does a fair job of basic optimization (properly
> parameterizing variables, etc.), but it doesn't really have an
> "industrial" optimizer built into its code generator. Not sure how you
> feel LINQ will replace SQL--who's to say EF won't replace LINQ next year?
Linq to sql is rubbish imo. Why bother writing in a similar language with a
translation when you can write in sql directly. Writing native linq against
a linq database would be much better :-))
> I was following your conversation intently, but you completely lost me on
> this comparison... What problem are you trying to solve with a cursor
> here, and what's the comparison between a cursor and "an object" that
> you're making? If you really want to compare apples to apples (though
> possibly different varieties) then you'd be better off comparing a
> "SqlDataReader" "object" to a cursor. I guess you don't see a lot of
> comparisons of "Connection" objects (like "SqlConnection") to "cursors",
> in much the same way that you don't see people comparing grizzly bears to
> drywall.
I'm not comparing the 2 because they have the same functionality, just using
2 random objects as an example. In sqlserver you have a cursor. You can not
extend this object in any way at all. You cannot write your own cursor
object in C++ and then reference that in sqlserver, you just have a cursor
object as is. In C# when we have an object it can usually be extended in
some sort of way. As an example, people or companies can write their own
data providers for C#, as I'm sure Oracle have done for their database. If
we had a cursor object in C# we would be able to extend it using extension
methods, most likely inherit from it, we could possible implement its
interface and pretend to be a cursor object or we could write our own cursor
object from scratch.
Michael
Michael Coles
> "Michael Coles" <ad...@geocodenet.com> wrote in message
> news:eI7wW6Za...@TK2MSFTNGP06.phx.gbl...
>> The LINQ provider for SQL actually generates SQL code behind the scenes.
>> To be fair, it does a fair job of basic optimization (properly
>> parameterizing variables, etc.), but it doesn't really have an
>> "industrial" optimizer built into its code generator. Not sure how you
>> feel LINQ will replace SQL--who's to say EF won't replace LINQ next year?
>
> Linq to sql is rubbish imo. Why bother writing in a similar language with
> a translation when you can write in sql directly. Writing native linq
> against a linq database would be much better :-))
I didn't realize there was such an animal as a "linq database". LINQ
generally provides a common interface to query a variety of different
datasources--SQL databases, Objects, websites, or anything else that you
care to write a provider for. LINQ's strength is its ability to expose
disparate data sources via a common query interface, so I'm not sure about a
couple of things: 1) what would a "linq database" even look like, and 2) how
it would be "better", given LINQ's primary purpose.
>> I was following your conversation intently, but you completely lost me on
>> this comparison... What problem are you trying to solve with a cursor
>> here, and what's the comparison between a cursor and "an object" that
>> you're making? If you really want to compare apples to apples (though
>> possibly different varieties) then you'd be better off comparing a
>> "SqlDataReader" "object" to a cursor. I guess you don't see a lot of
>> comparisons of "Connection" objects (like "SqlConnection") to "cursors",
>> in much the same way that you don't see people comparing grizzly bears to
>> drywall.
>
> I'm not comparing the 2 because they have the same functionality, just
> using 2 random objects as an example. In sqlserver you have a cursor. You
> can not extend this object in any way at all. You cannot write your own
> cursor object in C++ and then reference that in sqlserver, you just have a
> cursor object as is. In C# when we have an object it can usually be
> extended in some sort of way. As an example, people or companies can write
> their own data providers for C#, as I'm sure Oracle have done for their
> database. If we had a cursor object in C# we would be able to extend it
> using extension methods, most likely inherit from it, we could possible
> implement its interface and pretend to be a cursor object or we could
> write our own cursor object from scratch.
As Erland pointed out, different languages have different strengths and
weaknesses. SQL's strength is it's ability to efficiently store and
retrieve data in sets and to abstract away a lot of the details so you can
focus on your end results instead of focusing on the steps needed to get
there. C#'s strengths lie in other areas that are more procedural, such as
bit manipulation, looping, string manipulation, etc. While it is entirely
possible to write your own data providers for C# and create your own
databases using C++ and generate your own query engine using VB, you could
potentially run into a couple of issues.
The first question is how much time and money is it worth to write your own
database engine using C# and create your own query language built to your
own specifications? How much of your resources do you want to throw at a
project of this scale?
Another issue is do you want to make your database engine general enough
that it can be reused, or is it a one-off thing that will only ever be used
for one dedicated project? Obviously you'll get better performance out of a
dedicated one-off solution, but you won't be able to use it for any other
database projects down the road.
Finally, who are you going to hire to run it and administer it for you?
Since it's a homemade project that's not standards-compliant you'll have to
teach someone from scratch and won't really be able to leverage your admin's
and developer's experience on SQL-based DBMS's. This could be a fairly
expensive proposition.
Michael C
> I didn't realize there was such an animal as a "linq database".
Currently there is not but the main point I am trying to make is that MS
should create a native linq database.
> LINQ generally provides a common interface to query a variety of different
> datasources--SQL databases, Objects, websites, or anything else that you
> care to write a provider for. LINQ's strength is its ability to expose
> disparate data sources via a common query interface, so I'm not sure about
> a couple of things: 1) what would a "linq database" even look like, and
A linq database would be similar to sqlserver except you would write in C#
code instead of stored procs and linq instead of sql. There would be a
collection of tables and each table would have a collection of rows.
2) how
> it would be "better", given LINQ's primary purpose.
It would be more extendable, giving you the ability to write your own sql
keywords. Linq would make it more flexible and it would be more consistant.
There would be less of the restrictions that are common in sql. It would be
more modern as it wouldn't need to be compatible with sql server from 1956.
> As Erland pointed out, different languages have different strengths and
> weaknesses. SQL's strength is it's ability to efficiently store and
> retrieve data in sets and to abstract away a lot of the details so you can
> focus on your end results instead of focusing on the steps needed to get
> there. C#'s strengths lie in other areas that are more procedural, such as
> bit manipulation, looping, string manipulation, etc.
But now we have linq C# potentially has all the strengths of sql, with not
so many of it's weaknesses.
> While it is entirely possible to write your own data providers for C# and
> create your own databases using C++ and generate your own query engine
> using VB, you could potentially run into a couple of issues.
You've missed my point there. I'm not suggesting writing my own database.
I'm just giving examples of how C# is extensible. I might not want to write
my own database but I might want to add an extra function to the cursor
object. In C# I could inherit from the cursor object and add my own
functions. This would be a few minutes work potentially and add some very
useful functions to the cursor object. My point is that these sort of things
are possible in C# while in sqlserver there is much less facility for these
sort of things. When they do give extensibility it's usually very difficult
to use and restrictive.
Here's another example. In SQL Server if we want to include some C++ code we
have created we can create an extended stored proc. This works but is pretty
painful and very restricted. In C# we have a whole array of methods
available for adding C++ code.
Michael Coles
deprecated and will be going away at some point? They have been replaced by
SQL CLR, which supports .NET code written in C#, with which you can roll
your own custom "cursor-like" objects all day long. I don't quite
understand your infatuation with inheriting from and extending cursors to
create your own custom cursors -- cursors tend to be one of the worst
possible way to get things done in SQL. What functionality would you like
to add to cursors anyway?
BTW, your dates seem to be a teeny weeny little bit off. The direct
descendants of SQL weren't implemented until the 1970s, and SQL did not
become a standard until 1986. Dr. Codd didn't even publish his seminal
paper on the relational model until 1970. Perhaps you're confusing SQL
(circa 1986) with COBOL (circa 1959)?
"Michael C" <mi...@nospam.com> wrote in message
news:efZ$jwKbKH...@TK2MSFTNGP04.phx.gbl...
Erland Sommarskog
> C# with linq would be more than capable of being the language for a
> database (not sure of the technical term here). It is certainly not a
> boat out of water.
Today it is. Of course, you could implement a number of classes, and also
add language constructs for the querying part. Well, it is already there.
But you would also need the memory management, the storage management,
the process management etc that are core parts of a database engine. SQL
Server is *lot* more than just T-SQL.
> That's just not true. C# can easily handle more data than there is
> memory available. I have a C# program that handles 80GB files. SQLServer
> is simply C code, there is no reason C# can't replicate anything
> sqlserver does. Besides, I'm not suggesting this "Linq database" be
> actually written in C#, just that C# is the front end for it.
Again, you can of course write classes etc. But C# is just a language.
SQL Server is far more than just a language.
Can you put all those 80 GB in one single string variable? Or even 2GB,
even if machine only has a virtual address space of 1GB?
> There is absolutely no reason sqlserver would have an 8K limit today,
> except for hangovers from the past. Maybe if we go past 8K we get a
> performance hit but that would be fine.
And how do you know? The page size in SQL Server is 8192. It could
be made larger or variable, but that would be a major change, particularly
the latter. And the page size sets the limit between small objects
and big objects.
>> If you really think the limitation of 8000 chars gets in your way,
>> you can always declare you string variables and string columns as
>> (n)varchar(MAX), but I advice against it. I've found that there is a
>> performance penalty, even if the actual length is below 8000.
>
> It's not varchar that's the problem for me, it's calling CLR code for a
> custom aggregate that is the issue.
Eh, that limit has been lifted in SQL 2008. If you want to play that
game I can add complaints on that C# 1.0 did not have generics. After all,
that is a quite essential feature in an object-oriented language. Quite
a silly restriction. I mean, if you want to play that game.
> But my point was this is not a limitation of linq. If it is not possible
> then this is because ado.net does not allow it. If ado.net allows it then
> linq can do it.
ADO .Net can do it. LINQ can't.
> I don't have sql2008 here and it would take me several hours to get it
> installed and write a sample so I cannot do this. Even if this is a
> limitation of linq then it's really just a limitation interacting with
> sql.
Well, since I'm SQL person and think TVPs are a great feature, I think
that is quite a limitation.
Michael C
> You do realize that extended stored procs (unmanaged C++ code) are
> deprecated and will be going away at some point? They have been replaced
> by SQL CLR, which supports .NET code written in C#, with which you can
> roll your own custom "cursor-like" objects all day long. I don't quite
> understand your infatuation with inheriting from and extending cursors to
> create your own custom cursors -- cursors tend to be one of the worst
> possible way to get things done in SQL. What functionality would you like
> to add to cursors anyway?
Mike, it's just AN EXAMPLE :-)))
> BTW, your dates seem to be a teeny weeny little bit off. The direct
> descendants of SQL weren't implemented until the 1970s, and SQL did not
> become a standard until 1986. Dr. Codd didn't even publish his seminal
> paper on the relational model until 1970. Perhaps you're confusing SQL
> (circa 1986) with COBOL (circa 1959)?
I was just joking of course :-) My point was sqlserver is a very backwards
compatible product. This has advantages if you need backwards compatbility
but if you don't it's just one HUGE disadvantage, holding large amounts of
the product back in the past.
Michael
Michael Coles
>> What functionality would you like to add to cursors anyway?
>
> Mike, it's just AN EXAMPLE :-)))
That's OK; do you have a more useful example? Like I said, you have SQL CLR
where you can code with C# to interact with the database to your heart's
content.
> I was just joking of course :-) My point was sqlserver is a very backwards
> compatible product. This has advantages if you need backwards compatbility
> but if you don't it's just one HUGE disadvantage, holding large amounts of
> the product back in the past.
Hard to tell on here sometimes. Backwards compatibility is seen by some as
a positive, especially companies that have invested hundreds of thousands,
or millions, of dollars in developing for a specific platform.
--
Thanks
Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------
> "Michael Coles" <michaelcoAToptonlineDOTnet> wrote in message
> news:erbBpcRb...@TK2MSFTNGP05.phx.gbl...
>> You do realize that extended stored procs (unmanaged C++ code) are
>> deprecated and will be going away at some point? They have been replaced
>> by SQL CLR, which supports .NET code written in C#, with which you can
>> roll your own custom "cursor-like" objects all day long. I don't quite
>> understand your infatuation with inheriting from and extending cursors to
>> create your own custom cursors -- cursors tend to be one of the worst
>> possible way to get things done in SQL.
>
Michael C
> Today it is. Of course, you could implement a number of classes, and also
> add language constructs for the querying part. Well, it is already there.
> But you would also need the memory management, the storage management,
> the process management etc that are core parts of a database engine. SQL
> Server is *lot* more than just T-SQL.
That's true, it would be a lot of work for MS (or whoever). Nothing they
couldn't achieve however.
> Again, you can of course write classes etc. But C# is just a language.
> SQL Server is far more than just a language.
Aren't all the elements there is C# to be the front end for a database?
Triggers would be events, stored procs would be functions, views would be
linq statements, tables would be collections etc. Imagine writing your
application in C# and stepping through code and straight into a C# stored
proc (insert sound of Homer Simpson drooling here :-)
> Can you put all those 80 GB in one single string variable? Or even 2GB,
> even if machine only has a virtual address space of 1GB?
No, but you can use a filestream to parse the data sequentially from the
disk. Strings are an in memory object but there are plenty of classes for
dealing with stuff that is not.
>> There is absolutely no reason sqlserver would have an 8K limit today,
>> except for hangovers from the past. Maybe if we go past 8K we get a
>> performance hit but that would be fine.
>
> And how do you know? The page size in SQL Server is 8192. It could
> be made larger or variable, but that would be a major change, particularly
> the latter. And the page size sets the limit between small objects
> and big objects.
I just said "There is absolutely no reason sqlserver would have an 8K limit
today, except for hangovers from the past." and you replied that the reason
it's 8K is because that's the way it is and changing it would be a major
change. Didn't you just say exactly what I said in different words? :-)
> Eh, that limit has been lifted in SQL 2008. If you want to play that
> game I can add complaints on that C# 1.0 did not have generics. After all,
> that is a quite essential feature in an object-oriented language. Quite
> a silly restriction. I mean, if you want to play that game.
Ok, fair point. But C# 1.0 was a brand new language and the restriction
wasn't really that silly. They could have held out to put it in but decided
to release without it. On the other hand sqlserver is 20+ years old and
shouldn't have 8k restrictions on anything.
> ADO .Net can do it. LINQ can't.
If ADO.Net can do it then linq can do, you would just cast whatever
collection you have into whatever ADO.net requires.
> Well, since I'm SQL person and think TVPs are a great feature, I think
> that is quite a limitation.
It's not a limitation. It can be done very easily with the most minor of
code.
Michael
Michael Coles
> Triggers would be events, stored procs would be functions, views would be
> linq statements, tables would be collections etc. Imagine writing your
> application in C# and stepping through code and straight into a C# stored
> proc (insert sound of Homer Simpson drooling here :-)
There have actually been several front-end languages for database access
invented over the years. SQL is just one (albeit a very widely adopted)
possibility. You can definitely use C# as a front end for a database; in
fact, people do exactly this every day when they write .NET Windows and Web
client applications.
If you want C# as the native language for a database you're going to need to
rearchitect the DBMS. C# would probably make a better native language for
an object-oriented database as opposed to a database derived from the
relational model though. If you want to create a DBMS whose native language
is C#, I would say go for it--you may strike gold. Usually these things are
created to solve a problem--is there a particular problem you're trying to
solve with this idea (other than your dislike of SQL, that is)?
--
Thanks
Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------
"Michael C" <mi...@nospam.com> wrote in message
news:OvRR2AWb...@TK2MSFTNGP06.phx.gbl...
Michael C
> That's OK; do you have a more useful example? Like I said, you have SQL
> CLR where you can code with C# to interact with the database to your
> heart's content.
Custom aggregates is a good example. While sqlserver can do it, it is kludgy
and difficult. You need to recompile and add your assembly back into
sqlserver. You can't pass parameters into the custom aggregates which means
you need to write different custom aggregates where normally you would pass
in a parameter. As an example, I wrote a custom aggregate to comma seperate
varchar values when grouping. This worked well and was use by all the
programmers here. But then someone wanted it seperated by a space so I had
to write a whole new aggregate. Then someone wanted it sorted, then sorted
descending, then one to remove duplicates, and the remove duplicates sorted
descding seperate by a semi colon.
In comparison with C# and linq this would be very straight forward. Its
really just calling a function which is something very basic.
It's interesting that you're selling me the CLR as a good feature (which it
is), yet having a C#/Linq database is only 1 step away where we directly
write C# code in Management Studio. Wouldn't that be a good feaure also?
> Hard to tell on here sometimes. Backwards compatibility is seen by some
> as a positive, especially companies that have invested hundreds of
> thousands, or millions, of dollars in developing for a specific platform.
That is true of course and sqlserver is certainly the area where you get
crusified if you don't continue to support it. Image if they did to
sqlserver what they did to VB6? :-) That brings up an interesting point
though. They dumped VB6 for C#/vb.net and while this did upset those who
used VB6 it did produce a far far superior product. Obviously they couldn't
do this with sqlserver without being lynched but they could bring out a new
product to sit along side sqlserver.
Michael
Michael C
> There have actually been several front-end languages for database access
> invented over the years. SQL is just one (albeit a very widely adopted)
> possibility. You can definitely use C# as a front end for a database; in
> fact, people do exactly this every day when they write .NET Windows and
> Web client applications.
I think I was using the wrong term when I said front end. The term you used
"Native Language" is much better.
> If you want C# as the native language for a database you're going to need
> to rearchitect the DBMS. C# would probably make a better native language
> for an object-oriented database as opposed to a database derived from the
> relational model though.
Quite possibly, although I've heard that most attempts at making an OOP
database have failed. I'd be interested to see why.
> If you want to create a DBMS whose native language is C#, I would say go
> for it--you may strike gold. Usually these things are created to solve a
> problem--is there a particular problem you're trying to solve with this
> idea (other than your dislike of SQL, that is)?
Don't get me wrong, I do like sqlserver but I think it is getting a bit aged
and the attempts at giving it more modern features are a bit kludgy. The
problem I am trying to solve is just to modernise sqlserver, add a few
features and make things more consistant. As for me doing it there is no
chance of that, this is something that would require a big team of
developers to get working effectively. Can you imagine the complexity of
creating optimised indexes? And that is just one part of the database.
Michael
Michael Coles
> "Michael Coles" <ad...@geocodenet.com> wrote in message news:OW%23bv%
>> That's OK; do you have a more useful example? Like I said, you have SQL
>> CLR where you can code with C# to interact with the database to your
>> heart's content.
>
> Custom aggregates is a good example. While sqlserver can do it, it is
> kludgy and difficult. You need to recompile and add your assembly back
> into sqlserver. You can't pass parameters into the custom aggregates which
> means you need to write different custom aggregates where normally you
> would pass in a parameter. As an example, I wrote a custom aggregate to
> comma seperate varchar values when grouping. This worked well and was use
> by all the programmers here. But then someone wanted it seperated by a
> space so I had to write a whole new aggregate. Then someone wanted it
> sorted, then sorted descending, then one to remove duplicates, and the
> remove duplicates sorted descding seperate by a semi colon.
I think there are some advantages that could be realized by adding
additional parameters to SQL CLR custom aggregates, and from what I
understand that is on the radar for future versions of SQL Server. In the
meantime you can work around the issue by using SQL CLR UDTs and passing
them to the aggregate. Adding the ability to pass multiple parameters to a
SQL CLR UDA seems like quite a distance from completely rewriting the entire
DBMS engine to support a new procedural front-end language.
> In comparison with C# and linq this would be very straight forward. Its
> really just calling a function which is something very basic.
Again, if you really want a DBMS with procedural C# as it's native language
the relational model is probably not your best bet. If you want an OODBMS
with a procedural interface there are actually choices out there. I believe
IBM implements such a DBMS in their Notes line
> It's interesting that you're selling me the CLR as a good feature (which
> it is), yet having a C#/Linq database is only 1 step away where we
> directly write C# code in Management Studio. Wouldn't that be a good
> feaure also?
Oh I'm not selling you on CLR. It certainly has areas in which it can be
used to enhance and extend SQL Server's functionality; however, I'm just
pointing out to you that you have the option to talk directly to SQL Server
in C# on the server. That seemed to be your main point, so I'm simply
pointing out that hey, there it is!
>> Hard to tell on here sometimes. Backwards compatibility is seen by some
>> as a positive, especially companies that have invested hundreds of
>> thousands, or millions, of dollars in developing for a specific platform.
>
> That is true of course and sqlserver is certainly the area where you get
> crusified if you don't continue to support it. Image if they did to
> sqlserver what they did to VB6? :-) That brings up an interesting point
> though. They dumped VB6 for C#/vb.net and while this did upset those who
> used VB6 it did produce a far far superior product. Obviously they
> couldn't do this with sqlserver without being lynched but they could bring
> out a new product to sit along side sqlserver.
Well, for one thing VB6 was comparatively cheap back in the day--I remember
picking up a copy of VB6 Pro at a trade show for about $50 way back in the
day. SQL Server is significantly more expensive. Throw in licensing costs,
etc., and you are now implementing an enterprise solution to run the
business on. The main issue that VB6ers had was primarily with support,
particularly support for the COM-based technologies developed with it/for it
back in the day. But that's another discussion for another day. In the
meanwhile, what product do you want to sit alongside SQL Server? I doubt MS
is willing to split their development resources down the middle to create an
entirely new enterprise-class DBMS just so they can bypass SQL and use C# as
the native language. I mean, have you seen the economy lately? From what I
understand the SQL Server team is focusing on improving SQL Server--I could
be wrong, but I doubt there's a lot of interest in cutting the budget in
half to create a competing enterprise DBMS. :)
Michael C
> I think there are some advantages that could be realized by adding
> additional parameters to SQL CLR custom aggregates, and from what I
> understand that is on the radar for future versions of SQL Server. In the
> meantime you can work around the issue by using SQL CLR UDTs and passing
> them to the aggregate.
Interesting, I will give that a go. Thanks.
> Adding the ability to pass multiple parameters to a SQL CLR UDA seems like
> quite a distance from completely rewriting the entire DBMS engine to
> support a new procedural front-end language.
With VB6 you could do everything that was needed within reason. It could
have been argued we could have had VB7 instead of a full rewrite. But by
doing a full rewrite they came up with something a whole level above. We got
features that most users never even dreamed about or thought of requested
(how many people asked for linq? Or WPF?). The same could be done with
sqlserver. And it's not just a matter of rewriting the whole thing to fix
custom aggregates, it would be fixing so many issues and making so many
great new features.
BTW, Why do you say C# is procedural as opposed to what we have in
sqlserver? When you take into account linq, don't we have something that is
fairly similar? SQL has loops and if statement, variable declaration etc
just like C# and the real sql statements are equivelant to linq.
Just to go off on a tangent ..... the one thing I really like about C# is
that it is such a departure from anything I've used before in that
everything is designed from the ground up by industry experts using best
practices. Every other product I have used you can tell it was designed in a
more hap hazard way with at least some decisions made by possibly one
person. For example, at some point in the history of sqlserver someone said
that we need a way to list the tables in a database using sql. So someone
created the sysobjects table. But that wasn't quite right so they reinvented
it with INFORMATION_SCHEMA objects but that was missing a whole lot of
information so they added other stored procs to get other details about a
table. I'm sure there are other alternate ways to do the same thing which
are all inconsistant with each other. It would be good to bring that same
best practices into sqlserver (or an alternate product).
> Again, if you really want a DBMS with procedural C# as it's native
> language the relational model is probably not your best bet. If you want
> an OODBMS with a procedural interface there are actually choices out
> there. I believe IBM implements such a DBMS in their Notes line
I really should get off my butt and investigate one of these solutions. I'd
be interested to see how it is done and why OOP DBs are less popular.
> Oh I'm not selling you on CLR. It certainly has areas in which it can be
> used to enhance and extend SQL Server's functionality; however, I'm just
> pointing out to you that you have the option to talk directly to SQL
> Server in C# on the server. That seemed to be your main point, so I'm
> simply pointing out that hey, there it is!
Fair enough. :-)
> Well, for one thing VB6 was comparatively cheap back in the day--I
> remember picking up a copy of VB6 Pro at a trade show for about $50 way
> back in the day. SQL Server is significantly more expensive. Throw in
> licensing costs, etc., and you are now implementing an enterprise solution
> to run the business on.
That's true. Maybe big business never took VB6 too seriously and it was only
small to medium size workshops that used it.
> The main issue that VB6ers had was primarily with support, particularly
> support for the COM-based technologies developed with it/for it back in
> the day. But that's another discussion for another day. In the
> meanwhile, what product do you want to sit alongside SQL Server? I doubt
> MS is willing to split their development resources down the middle to
> create an entirely new enterprise-class DBMS just so they can bypass SQL
> and use C# as the native language. I mean, have you seen the economy
> lately? From what I understand the SQL Server team is focusing on
> improving SQL Server--I could be wrong, but I doubt there's a lot of
> interest in cutting the budget in half to create a competing enterprise
> DBMS. :)
Who says the people need to come from the SQL Server team. Possibly it could
be a new project, or maybe it could be considered a .net project and people
could come from the .net team.
Michael Coles
reliance on the ever-sprawling Ruby engine. Talk about a ton of old
features maintained for backwards-compatibility! And VB6 also didn't
support true OO programming.
The sys* tables were a vendor extension, like all other SQL DBMS platforms
have to some degree or another. IIRC the sys* tables were actually
implemented before the INFORMATION_SCHEMA tables were added to the SQL
standard. When INFORMATION_SCHEMA was added to the standard, it was added
to SQL DBMS implementations as well to maintain standards compliance. Of
course INFORMATION_SCHEMA does not account for the vendor extensions to SQL,
so vendors have to expose that information to admins and developers in other
ways. In SQL 2005+ the method of choice is catalog views. We could discuss
the SQL standard and "compliance" with the standard, but if you Google Celko
you'll find plenty on that topic.
> BTW, Why do you say C# is procedural as opposed to what we have in
> sqlserver? When you take into account linq, don't we have something that
> is fairly similar? SQL has loops and if statement, variable declaration
> etc just like C# and the real sql statements are equivelant to linq.
C# is a procedural language, SQL is declarative. C# does have a touch of
LINQ, but C# is not LINQ. SQL has a few procedural extensions, but SQL is
not procedural. They're basically at opposite ends of the spectrum. If you
were writing your SQL code like you would write C# programs, I can almost
guarantee that your SQL Server is working much harder than it needs to and
your code is much less efficient than it could be. This is why I suggest
that a SQL DBMS might not the best DBMS to use if you want to use a
procedural language as its native language. LINQ provides several similar
constructs to SQL (which are actually converted to SQL when you use LINQ to
SQL). Microsoft has a website for enhancement requests like the ones you're
suggesting - http://connect.microsoft.com. :)
--
Thanks
Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------
"Michael C" <mi...@nospam.com> wrote in message
news:eSJSvHZb...@TK2MSFTNGP04.phx.gbl...
Erland Sommarskog
>> Again, you can of course write classes etc. But C# is just a language.
>> SQL Server is far more than just a language.
>
> Aren't all the elements there is C# to be the front end for a database?
> Triggers would be events, stored procs would be functions, views would be
> linq statements, tables would be collections etc. Imagine writing your
> application in C# and stepping through code and straight into a C# stored
> proc (insert sound of Homer Simpson drooling here :-)
Again: a relational database is just so much more than the language
elements. You need memory management. You need process management. You
need storage management. You need a cost-based optimizer.
Keep in mind that C# is a procedural language. SQL is declarative. What you
write in C# not only instructs the computer what to do, but also how. In
SQL you only say what. This means that when you use LINQ, you essentially
tell the compiler what SQL to generate.
> I just said "There is absolutely no reason sqlserver would have an 8K
> limit today, except for hangovers from the past." and you replied that
> the reason it's 8K is because that's the way it is and changing it would
> be a major change. Didn't you just say exactly what I said in different
> words? :-)
Whatever, there will be a limit in page size. You don't handle 8K in the
same way you handle 8 MB or 8 GB.
> Ok, fair point. But C# 1.0 was a brand new language and the restriction
> wasn't really that silly. They could have held out to put it in but
> decided to release without it. On the other hand sqlserver is 20+ years
> old and shouldn't have 8k restrictions on anything.
So what? I learnt Simula in 1983, and I learnt to use generics. And C# was
not a brand-new language, it was constructed from existing languages. It's
really silly not to put basic things in from day one.
No, I don't really mean that, but as you say they had to ship, and there
you have the answer why there are limitations in SQL Server as well.
>> ADO .Net can do it. LINQ can't.
>
> If ADO.Net can do it then linq can do, you would just cast whatever
> collection you have into whatever ADO.net requires.
So prove it by giving a program that demonstrates how to do it.
>> Well, since I'm SQL person and think TVPs are a great feature, I think
>> that is quite a limitation.
>
> It's not a limitation. It can be done very easily with the most minor of
> code.
How? If you are so dead sure that it's possible, then prove it by doing it.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
Michael C
> VB6 actually fell short in several areas, not the least of which was the
> reliance on the ever-sprawling Ruby engine. Talk about a ton of old
> features maintained for backwards-compatibility! And VB6 also didn't
> support true OO programming.
Sounds a lot like sqlserver :-)))
> C# is a procedural language, SQL is declarative. C# does have a touch of
> LINQ, but C# is not LINQ. SQL has a few procedural extensions, but SQL is
> not procedural. They're basically at opposite ends of the spectrum. If
> you were writing your SQL code like you would write C# programs, I can
> almost guarantee that your SQL Server is working much harder than it needs
> to and your code is much less efficient than it could be.
Don't worry, although I'm no expert I know how to write fairly efficient
sql.
> This is why I suggest that a SQL DBMS might not the best DBMS to use if
> you want to use a procedural language as its native language. LINQ
> provides several similar constructs to SQL (which are actually converted
> to SQL when you use LINQ to SQL). Microsoft has a website for enhancement
> requests like the ones you're suggesting - http://connect.microsoft.com.
> :)
Makes sense but it is certainly a gray area now we have linq and sqlserver
has procedural commands.
Michael
Michael C
> Again: a relational database is just so much more than the language
> elements. You need memory management. You need process management. You
> need storage management. You need a cost-based optimizer.
Again, yes I know. That is why someone would need to write a linq database.
> Keep in mind that C# is a procedural language. SQL is declarative. What
> you
> write in C# not only instructs the computer what to do, but also how. In
> SQL you only say what. This means that when you use LINQ, you essentially
> tell the compiler what SQL to generate.
Sounds like a gray area to me. Certainly linq could be optimised by an
optimiser in the same way sql is.
> So prove it by giving a program that demonstrates how to do it.
>
>>> Well, since I'm SQL person and think TVPs are a great feature, I think
>>> that is quite a limitation.
>>
>> It's not a limitation. It can be done very easily with the most minor of
>> code.
>
> How? If you are so dead sure that it's possible, then prove it by doing
> it.
I have provided a code sample (check back through the posts, did you miss
it?) but as I said I am not going to install sql2008 just to write some
sample code.
Michael
Michael Coles
> "Michael Coles" <ad...@geocodenet.com> wrote in message
> news:Okx4EqZb...@TK2MSFTNGP05.phx.gbl...
>> VB6 actually fell short in several areas, not the least of which was the
>> reliance on the ever-sprawling Ruby engine. Talk about a ton of old
>> features maintained for backwards-compatibility! And VB6 also didn't
>> support true OO programming.
>
> Sounds a lot like sqlserver :-)))
I give SQL Server a lot of credit in that regard -- various older features
are deprecated with every release, and newer more powerful features are
built in to replace them. The deprecation process in SQL Server gives
customers the ability to phase out old code and learn the newer features, as
opposed to the wholesale replacement you mentioned in regards to VB6 and
.NET.
>> C# is a procedural language, SQL is declarative. C# does have a touch of
>> LINQ, but C# is not LINQ. SQL has a few procedural extensions, but SQL
>> is not procedural. They're basically at opposite ends of the spectrum.
>> If you were writing your SQL code like you would write C# programs, I can
>> almost guarantee that your SQL Server is working much harder than it
>> needs to and your code is much less efficient than it could be.
>
> Don't worry, although I'm no expert I know how to write fairly efficient
> sql.
>
>> This is why I suggest that a SQL DBMS might not the best DBMS to use if
>> you want to use a procedural language as its native language. LINQ
>> provides several similar constructs to SQL (which are actually converted
>> to SQL when you use LINQ to SQL). Microsoft has a website for
>> enhancement requests like the ones you're suggesting -
>> http://connect.microsoft.com. :)
>
> Makes sense but it is certainly a gray area now we have linq and sqlserver
> has procedural commands.
SQL Server has always had procedural statements--many think of them as tools
of "last resort". They do have a place, but are best avoided in most cases
because they tend to override SQL Server's automatic optimization processes.
LINQ is not currently in a position to replace SQL, and from the ADO.NET
team's postings it sounds as if EF is preferred over LINQ to SQL for
relational database access scenarios
(http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx).
So I don't know whether huge investments in bringing LINQ to SQL up to the
same level as EF features (or future EF features) for OR/M solutions is in
the cards or not. I doubt that designing an entirely new enterprise
database engine just for LINQ to SQL (to avoid the SQL part) is probably not
in the cards, but you can definitely suggest it over at Connect :) You
might have a slightly better chance of convincing MS to create an Enterprise
OODBMS optimized for EF as opposed to LINQ, although convincing them of the
need to split their enterprise DBMS resources in half to build two competing
enterprise DBMS's might be a tough sell...
Michael Coles
>> Keep in mind that C# is a procedural language. SQL is declarative. What
>> you
>> write in C# not only instructs the computer what to do, but also how. In
>> SQL you only say what. This means that when you use LINQ, you essentially
>> tell the compiler what SQL to generate.
>
> Sounds like a gray area to me. Certainly linq could be optimised by an
> optimiser in the same way sql is.
The SQL Server optimizer is the result of 2 decades of R&D, production
implementations and response to customer feedback. To completely start
creating a new transactional database engine from scratch based on the OO
model (as opposed to relational), complete with an "industrial-strength"
optimizer would require huge investments of time, money and resources. It
would also require huge investments in marketing, trying to convince people
they need this new DBMS while not annihilating the client base for your SQL
DBMS. I'm sure it could be done, but is it worth it to anyone? Who
knows...
>> So prove it by giving a program that demonstrates how to do it.
>>
>
> I have provided a code sample (check back through the posts, did you miss
> it?) but as I said I am not going to install sql2008 just to write some
> sample code.
You spoke previously of your dislike of backwards-compatibility features in
SQL Server, etc. But it is precisely because people don't immediately
upgrade to the newest versions and convert all their existing code to use
all of the latest and greatest features that these backwards-compatibility
features even exist...
Michael C
> I give SQL Server a lot of credit in that regard -- various older features
> are deprecated with every release, and newer more powerful features are
> built in to replace them. The deprecation process in SQL Server gives
> customers the ability to phase out old code and learn the newer features,
> as opposed to the wholesale replacement you mentioned in regards to VB6
> and .NET.
I'm not saying that the approach they use with sqlserver is wrong, just that
either approach (compatibility vs whole new product) has it's advantages and
disadvantages. I think the vb6 to .net switch has a lot of similarity to
what I'm suggesting with a move from sql to a linq database. Certainly they
upset a lot of people abandoning vb6 but then again they made a product that
was far far ahead of vb6. Obviously they can't abandon sqlserver.
> SQL Server has always had procedural statements--many think of them as
> tools of "last resort". They do have a place, but are best avoided in
> most cases because they tend to override SQL Server's automatic
> optimization processes.
That depends how you use them. If you look at any stored proc written by MS
they all have a bunch of If statements at the start verifying the parameters
passed in. This hardly causes an slow down and could actually be considered
a performance improvement because the query does not need to be executed if
the parameters and invalid.
> LINQ is not currently in a position to replace SQL, and from the ADO.NET
> team's postings it sounds as if EF is preferred over LINQ to SQL for
> relational database access scenarios
> (http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx).
> So I don't know whether huge investments in bringing LINQ to SQL up to the
> same level as EF features (or future EF features) for OR/M solutions is in
> the cards or not. I doubt that designing an entirely new enterprise
> database engine just for LINQ to SQL (to avoid the SQL part) is probably
> not in the cards, but you can definitely suggest it over at Connect :)
> You might have a slightly better chance of convincing MS to create an
> Enterprise OODBMS optimized for EF as opposed to LINQ, although convincing
> them of the need to split their enterprise DBMS resources in half to build
> two competing enterprise DBMS's might be a tough sell...
Quite possibly, doesn't mean it's not a good idea though. The thinking in
the sqlserver camp seems to be more towards compatibility than advancement.
This is probably good if you're a bank but not so good for me. In 2009 I
still can't inherit from a person table to create staff and customer tables.
Michael
Michael C
> The SQL Server optimizer is the result of 2 decades of R&D, production
> implementations and response to customer feedback.
Do you really think this declarative approach works that well? I still find
that I tell that database what I want to do and a lot of the time it does it
the wrong way and I have to go back in and tell it how to do it anyway.
To completely start
> creating a new transactional database engine from scratch based on the OO
> model (as opposed to relational), complete with an "industrial-strength"
> optimizer would require huge investments of time, money and resources. It
> would also require huge investments in marketing, trying to convince
> people they need this new DBMS while not annihilating the client base for
> your SQL DBMS. I'm sure it could be done, but is it worth it to anyone?
> Who knows...
Nothing is impossible. No one was screaming for .net but it's been a big
success.
> You spoke previously of your dislike of backwards-compatibility features
> in SQL Server, etc. But it is precisely because people don't immediately
> upgrade to the newest versions and convert all their existing code to use
> all of the latest and greatest features that these backwards-compatibility
> features even exist...
That's true but I am only 1 version behind. Besides, I'm not against
backwards compatibility, I just think that after a certain point in time it
becomes necessary to make a complete break from the past sins. We're still
in the very early stages of computing in 2009, do we really want sql2050 to
still only be able to return a square block of data from a stored proc?
Michael
Michael Coles
>> SQL Server has always had procedural statements--many think of them as
>> tools of "last resort". They do have a place, but are best avoided in
>> most cases because they tend to override SQL Server's automatic
>> optimization processes.
>
> That depends how you use them. If you look at any stored proc written by
> MS they all have a bunch of If statements at the start verifying the
> parameters passed in. This hardly causes an slow down and could actually
> be considered a performance improvement because the query does not need to
> be executed if the parameters and invalid.
One-off administrative tasks are the bulk of the procedures you're talking
about (or possibly you're talking about the IF EXISTS ... code that SSMS
adds to build scripts). The recommended use for procedural statements is in
fact cases where you have code that deals in very small data sets and
performs administrative tasks, and one-offs like checking for existence of
objects before executing DDL statements. Using IF statements in production
stored procs that perform DML will affect performance; the extent of the
effect is dependent on several factors.
>> LINQ is not currently in a position to replace SQL, and from the ADO.NET
>> team's postings it sounds as if EF is preferred over LINQ to SQL for
>> relational database access scenarios
>> (http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx).
>> So I don't know whether huge investments in bringing LINQ to SQL up to
>> the same level as EF features (or future EF features) for OR/M solutions
>> is in the cards or not. I doubt that designing an entirely new
>> enterprise database engine just for LINQ to SQL (to avoid the SQL part)
>> is probably not in the cards, but you can definitely suggest it over at
>> Connect :) You might have a slightly better chance of convincing MS to
>> create an Enterprise OODBMS optimized for EF as opposed to LINQ, although
>> convincing them of the need to split their enterprise DBMS resources in
>> half to build two competing enterprise DBMS's might be a tough sell...
>
> Quite possibly, doesn't mean it's not a good idea though. The thinking in
> the sqlserver camp seems to be more towards compatibility than
> advancement. This is probably good if you're a bank but not so good for
> me. In 2009 I still can't inherit from a person table to create staff and
> customer tables.
You can easily design your tables to simulate OO inheritance if you so
desire, so I don't really see this as being an issue. What you just stated
is actually really, really easy to do if you so desire. If you look at the
SQL Server 2005/2008 Catalog views you will see that they expose data using
this same inheritance design pattern. I might be missing your point on this
as well...
Getting a chance to refactor your code to take advantage of new features
while not instantaneously breaking all of your existing code is not
necessarily a bad thing. Not sure who you think in the "SQL Server camp"
doesn't want advancement--there were lots of advances made in SQL 2008
covering a wide range of functionality; from B.I. to DB Admin and Security
to SQL Development. 2008 R2 adds more advances, and I suspect there are
plenty of additional advances in the works for the next version or two as
well, so I suppose I'm missing your point on this... Of course as I
understand from your previous posts you haven't actually installed SQL 2008
yet, so you may not have even used any of the existing advancements at this
point. Is that an accurate assessment?
Michael Coles
> find that I tell that database what I want to do and a lot of the time it
> does it the wrong way and I have to go back in and tell it how to do it
> anyway.
Actually I do think it works well. Can you give some examples of your issue
here? I'm very interested, as I've been doing this for a while now and only
very rarely run into issues like you describe here. And they are usually
fixed with a hotfix or SP. I would be really interested to know why the
DBMS does it "in the wrong way" for you "a lot of the time".
> Nothing is impossible. No one was screaming for .net but it's been a big
> success.
Everything is possible, just throw time and money at it. The question is
this: is it worth it? If you can convince MS that it is, then they'll go
for it. If not, then they won't.
> That's true but I am only 1 version behind. Besides, I'm not against
> backwards compatibility, I just think that after a certain point in time
> it becomes necessary to make a complete break from the past sins. We're
> still in the very early stages of computing in 2009, do we really want
> sql2050 to still only be able to return a square block of data from a
> stored proc?
In addition to "square blocks of data" (i.e., tabular result sets), how
about returning scalar values? Or XML? Or a .NET *object*? Obviously with
2005 (and 2008) all of these are possible today. Maybe I'm missing your
point here... Perhaps you could describe in more detail what else you would
like to be able to return from a stored proc?
Michael C
> One-off administrative tasks are the bulk of the procedures you're talking
> about (or possibly you're talking about the IF EXISTS ... code that SSMS
> adds to build scripts). The recommended use for procedural statements is
> in fact cases where you have code that deals in very small data sets and
> performs administrative tasks, and one-offs like checking for existence of
> objects before executing DDL statements. Using IF statements in
> production stored procs that perform DML will affect performance; the
> extent of the effect is dependent on several factors.
Pretty much an stored proc I write will start off with an IF statement. For
example, when writing a stored proc for a daily sales report, the stored
proc will have a single parameter of date. The first line will raise an
error if the date parameter is null. Surely this doesn't cost more than
1/10000th of a second to execute.
> You can easily design your tables to simulate OO inheritance if you so
> desire, so I don't really see this as being an issue. What you just
> stated is actually really, really easy to do if you so desire. If you
> look at the SQL Server 2005/2008 Catalog views you will see that they
> expose data using this same inheritance design pattern. I might be
> missing your point on this as well...
I'm not exactly sure what you mean but surely it's a bit like doing fake
inheritance is VB6? You can do it but it's a bit kludgy.
> Getting a chance to refactor your code to take advantage of new features
> while not instantaneously breaking all of your existing code is not
> necessarily a bad thing.
I never said it was, it's just one way to do things that has advantages and
disadvantages. While it's great to have backwards compatibility it does hold
things back *significantly*. Can you imagine how far behind .net would have
been if it was really just vb7?
> Not sure who you think in the "SQL Server camp" doesn't want
> advancement--there were lots of advances made in SQL 2008 covering a wide
> range of functionality; from B.I. to DB Admin and Security to SQL
> Development. 2008 R2 adds more advances, and I suspect there are plenty
> of additional advances in the works for the next version or two as well,
> so I suppose I'm missing your point on this... Of course as I understand
> from your previous posts you haven't actually installed SQL 2008 yet, so
> you may not have even used any of the existing advancements at this point.
> Is that an accurate assessment?
That's partially accurate. I haven't tried sql 2008 as yet but I was not
saying that people in the sqlserver camp don't want advancement. I was just
saying they lean towards compatibility over advancement.
Michael
Michael C
> Actually I do think it works well. Can you give some examples of your
> issue here? I'm very interested, as I've been doing this for a while now
> and only very rarely run into issues like you describe here. And they are
> usually fixed with a hotfix or SP. I would be really interested to know
> why the DBMS does it "in the wrong way" for you "a lot of the time".
I'm just talking about performance with a large number of records. As an
example recently I had a query that ran slowly and I solved it by using an
indexed view. Basically I started off by telling sqlserver what I wanted and
left it up to sqlserver how to do that. But it did it too slowly so I had to
tell it how to do it anyway (I had to tell it to index a view). It seems
that with everything query I write I have to tell sqlserver how to do it in
some way.
> Everything is possible, just throw time and money at it. The question is
> this: is it worth it? If you can convince MS that it is, then they'll go
> for it. If not, then they won't.
I think this is similar to VB6 and/or COM. I remember Dan Appleman said that
he didn't realise how broken COM was until after it was replaced. While it
worked and was very useful it had many faults and we now have something much
better. If MS didn't write .net the world would have still spun so it wasn't
essential.
> In addition to "square blocks of data" (i.e., tabular result sets), how
> about returning scalar values?
That's just a 1x1 square really.
> Or XML? Or a .NET *object*?
Both are a big kludgy don't you think? Imagine returning a dot net
collection into a dot net client with a dot net back end? Sure everything
works now but wouldn't that be soooooo much better? :-)
> Obviously with 2005 (and 2008) all of these are possible today. Maybe I'm
> missing your point here... Perhaps you could describe in more detail what
> else you would like to be able to return from a stored proc?
I would like to be able to return hierarchical data without implementing
kludges. Surely you would not recommend returning a dot net collection or
XML from an sqlserver stored proc if you needed performance? It would make
more sense just to return multiple square blocks of data and marry them back
up in the client. Having to use XML or .net objects to get hierarchical data
is really what I don't like about sqlserver in that pretty much everything
is possible in sqlserver but more often than not there is something kludgy
about it (ever used calculared columns?). It reminds me of VB6 a lot in that
everything was possible but mostly kludgy. I know sqlserver is a much more
serious product than VB6 but it does have the same feel to me. You can do
what you need if you stand on your head and hold your tounge to your left
nostril... :-)
Michael
Erland Sommarskog
> "Erland Sommarskog" <esq...@sommarskog.se> wrote in message
> news:Xns9CCE637FC...@127.0.0.1...
>> Again: a relational database is just so much more than the language
>> elements. You need memory management. You need process management. You
>> need storage management. You need a cost-based optimizer.
>
> Again, yes I know. That is why someone would need to write a linq
> database.
And in the end you would maybe end up with as many "silly" restrictions
as you have in SQL Server. You need to ship at some point.
>> Keep in mind that C# is a procedural language. SQL is declarative. What
>> you
>> write in C# not only instructs the computer what to do, but also how. In
>> SQL you only say what. This means that when you use LINQ, you essentially
>> tell the compiler what SQL to generate.
>
> Sounds like a gray area to me. Certainly linq could be optimised by an
> optimiser in the same way sql is.
Yes, it could, but today it isn't, but LINQ is only something that generates
something else, which brings us to:
> I have provided a code sample (check back through the posts, did you miss
> it?) but as I said I am not going to install sql2008 just to write some
> sample code.
If LINQ is not able to generate the code needed to pass a TVP, it isn't.
My source says that LINQ does not have this capability. You claim it has,
but you don't decline to back up your statement with working code. The
code you provided tells me nada.
(And why not install SQL 2008? That would save you from the limitation
with 8000 bytes in a UDA.)
Erland Sommarskog
> I'm just talking about performance with a large number of records. As an
> example recently I had a query that ran slowly and I solved it by using
> an indexed view. Basically I started off by telling sqlserver what I
> wanted and left it up to sqlserver how to do that. But it did it too
> slowly so I had to tell it how to do it anyway (I had to tell it to
> index a view). It seems that with everything query I write I have to
> tell sqlserver how to do it in some way.
have to help the optimizer quite a bit.
A good example is a running sum. I had to compute one recently, and wrote
this query:
update #insids
set running = (SELECT SUM(nooftrans) from #insids b
where b.insid <= a.insid)
from #insids a
There were 170000 rows in the table. I killed the query after a few minutes,
and then wrote a cursor which completed within a minute.
There is an construct in ANSI-SQL to express a running sum using the
OVER clause, and it is high on my wishlist for SQL Server that Microsoft
implements this clause like other vendors already have. But strictly
speaking, this clause is not needed: I already have expressed my intent
above, but the optimizer does not realise that the query can be executed
in a single pass over the table.
But still SQL is a lot better that you would get if you would with a C#
program. Well, in this particular example the correct plan is trivial, but
for more complex plans it isn't, and this is an area where SQL beats C#.
Erland Sommarskog
> The SQL Server optimizer is the result of 2 decades of R&D, production
> implementations and response to customer feedback. To completely start
> creating a new transactional database engine from scratch based on the
> OO model (as opposed to relational), complete with an
> "industrial-strength" optimizer would require huge investments of time,
> money and resources. It would also require huge investments in
> marketing, trying to convince people they need this new DBMS while not
> annihilating the client base for your SQL DBMS. I'm sure it could be
> done, but is it worth it to anyone? Who knows...
half of the nineties, when was the days I heard about them. I seem to
recall that there were a few areas where they were better than relational
databases, like CAD. But it is quite clear that for one reason or another,
they have not been able to do a major breakthrough.
Michael Coles
> example recently I had a query that ran slowly and I solved it by using an
> indexed view. Basically I started off by telling sqlserver what I wanted
> and left it up to sqlserver how to do that. But it did it too slowly so I
> had to tell it how to do it anyway (I had to tell it to index a view). It
> seems that with everything query I write I have to tell sqlserver how to
> do it in some way.
Unfortunately for you proper indexing is another tool used to express your
intent to the optimizer. If you did not properly index the underlying
tables then you only did half the job to begin with. You have many options
that you can take advantage of to better define your intent and to help the
optimizer achieve better performance, but you don't have to use them. As
you point out above, your performance may suffer but it will complete
eventually.
> I think this is similar to VB6 and/or COM. I remember Dan Appleman said
> that he didn't realise how broken COM was until after it was replaced.
> While it worked and was very useful it had many faults and we now have
> something much better. If MS didn't write .net the world would have still
> spun so it wasn't essential.
Exactly. At some point someone convinced MS that .NET was worth it. Now
you just have to do the same...
>> In addition to "square blocks of data" (i.e., tabular result sets), how
>> about returning scalar values?
>
> That's just a 1x1 square really.
No, not a "1x1 square", a "1x1 square with a data type".
>> Or XML? Or a .NET *object*?
>
> Both are a big kludgy don't you think? Imagine returning a dot net
> collection into a dot net client with a dot net back end? Sure everything
> works now but wouldn't that be soooooo much better? :-)
If you want to return a .NET collection, you can do that currently. I'm not
convinced that an enterprise .NET/LINQ OODBMS will be "soooooo much better",
but that's OK because you don't have to convince me... Convince the folks
who have to throw resources at it to make your dream a reality.
>> Obviously with 2005 (and 2008) all of these are possible today. Maybe
>> I'm missing your point here... Perhaps you could describe in more detail
>> what else you would like to be able to return from a stored proc?
>
> I would like to be able to return hierarchical data without implementing
> kludges. Surely you would not recommend returning a dot net collection or
> XML from an sqlserver stored proc if you needed performance? It would make
> more sense just to return multiple square blocks of data and marry them
> back up in the client. Having to use XML or .net objects to get
> hierarchical data is really what I don't like about sqlserver in that
> pretty much everything is possible in sqlserver but more often than not
> there is something kludgy about it (ever used calculared columns?). It
> reminds me of VB6 a lot in that everything was possible but mostly kludgy.
> I know sqlserver is a much more serious product than VB6 but it does have
> the same feel to me. You can do what you need if you stand on your head
> and hold your tounge to your left nostril... :-)
Have you used FOR XML to return hierarchical data from SQL Server? It's
well-optimized, and I have absolutely no problem returning XML from a stored
proc when performance is an issue. If you really want to do your joins in
the client you definitely have the option to read all of your "square blocks
of data" as you call them back and perform the joins on the client with your
own C# nested loops. You may run into bandwidth limitations when you start
pulling gigabytes of data across the wire and performing custom nested loops
on the client to "marry them up", but on the plus side this could be the
foundation for your very own custom database engine...
So in reality you want a hierarchical DBMS as opposed to an OODBMS or a
relational/SQL DBMS? Unfortunately SQL is optimized for relational data,
not OO or hierarchical data, although there are ways to work around the
limitations. There are definitely hierarchical DBMSs available if that's
where your needs are, but you'll face other limitations there.
At any rate this has been an interesting conversation, but I think we've
eventually gotten to the root of your complaint here -- essentially you
don't want a SQL DBMS, you want a hierarchical DBMS. You already know that
the users here can't really rewrite SQL Server to your specifications, but
you've also been informed that you can file your recommendations at
http://connect.microsoft.com to get them heard by the people who can rewrite
it to your specifications.
So I think I've come to the end of my portion of this thread. You'll have
to sell MS on your vision of rewriting SQL Server as a hierarchical DBMS or
an OODBMS or whatever model you would like them to implement.
Michael Coles
> half of the nineties, when was the days I heard about them. I seem to
> recall that there were a few areas where they were better than relational
> databases, like CAD. But it is quite clear that for one reason or another,
> they have not been able to do a major breakthrough.
Absolutely, and so do hierarchical DBMS's, which would solve his issues with
the limitations on hierarchical data inherent in the relational/SQL model.
I don't know why OODBMS's haven't caught on outside of specialized
applications either -- maybe they're inferior in some respects, could be an
issue of support, or it might just be good old-fashioned marketing? Either
way, I think he needs to convince people who have the necessary resources to
implement his dream DBMS...
Jeffrey Williams
capabilities to utilize SQL you might want to take a look at Intersystems
Cache. It does all of the above and is an Enterprise class system that can
also run on various hardware platforms.
You have the options of accessing data as objects, or running queries using
SQL. Just note that the same applies to this system that applies to any
DBMS - there are things they do well, and things they do not.
"Michael Coles" <ad...@geocodenet.com> wrote in message
news:F34B74AD-D2DC-448B...@microsoft.com...
Jeffrey Williams
I know that everything he is asking for is already available from
Intersystems Cache.
"Michael Coles" <ad...@geocodenet.com> wrote in message
news:567B3528-2C5F-4748...@microsoft.com...
Michael Coles
news:exkzh$5bKHA...@TK2MSFTNGP06.phx.gbl...
> As I replied on another branch - I actually think his dream already
> exists. I know that everything he is asking for is already available from
> Intersystems Cache.
There you go Michael C, Jeffrey has your OODBMS that surfaces procedural
C#/.NET/LINQ as its native language, handles hierarchical data with the
greatest of ease, efficiently returns objects to the client without having
to deal with all those "squares", and automatically optimizes queries
without requiring you to define "indexes" or performing other administrative
tasks to optimize performance.
Erland Sommarskog
> Custom aggregates is a good example. While sqlserver can do it, it is
> kludgy and difficult. You need to recompile and add your assembly back
> into sqlserver. You can't pass parameters into the custom aggregates
> which means you need to write different custom aggregates where normally
> you would pass in a parameter. As an example, I wrote a custom aggregate
> to comma seperate varchar values when grouping. This worked well and was
> use by all the programmers here. But then someone wanted it seperated by
> a space so I had to write a whole new aggregate. Then someone wanted it
> sorted, then sorted descending, then one to remove duplicates, and the
> remove duplicates sorted descding seperate by a semi colon.
Rather than using an UDA, you can use FOR XML PATH for this. Here is
an example:
select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go
It's not really what you call intuitive, but it's very useful.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Michael C
news:exkzh$5bKHA...@TK2MSFTNGP06.phx.gbl...
> exists. I know that everything he is asking for is already available from
> Intersystems Cache.
It looks interesting I will have a look into it. Thanks for the tip. I'm not
sure that it gives me what I want though? I don't think I can write linq or
C# into a stored proc and I can't add a reference to a dot net dll directly
into the database?
Cheers,
Michael
Michael C
> And in the end you would maybe end up with as many "silly" restrictions
> as you have in SQL Server. You need to ship at some point.
We would have restrictions but there is a difference between restrictions
and silly restrictions. Having an 8K limit on dot net code is a silly
restriction. Not having generics in the *first* release of a product is
quite a valid restriction. The second release had generics.
> Yes, it could, but today it isn't, but LINQ is only something that
> generates
> something else, which brings us to:
Of course, but the whole point I'm making is that something should be
written.
> If LINQ is not able to generate the code needed to pass a TVP, it isn't.
> My source says that LINQ does not have this capability. You claim it has,
> but you don't decline to back up your statement with working code. The
> code you provided tells me nada.
That's all there is to the code, it's such a simple problem to solve that
the code is minimal. As I said, if ADO.net can do it then linq can do it.
You stated ado.net can do it so it is possible.
> (And why not install SQL 2008? That would save you from the limitation
> with 8000 bytes in a UDA.)
My boss craps himself when I say I need to install a service pack. Besides,
we have just rolled into month 24 of a 24 month project so it's a bit late
for big changes now. :-)
Michael
Michael C
> Unfortunately for you proper indexing is another tool used to express your
> intent to the optimizer. If you did not properly index the underlying
> tables then you only did half the job to begin with. You have many
> options that you can take advantage of to better define your intent and to
> help the optimizer achieve better performance, but you don't have to use
> them. As you point out above, your performance may suffer but it will
> complete eventually.
The indexing was fine but the table design was probably not ideal. There was
a large amount of "old" data and a small amount of "current" data. To grab
just the current data and then filter on that was slowing it down. By
creating an indexed view I told sqlserver to keep a copy of the current data
only.
> Exactly. At some point someone convinced MS that .NET was worth it. Now
> you just have to do the same...
Seems like a big task. I'd be suprised if they hadn't considered this and
already made a decision one way or the other.
> Have you used FOR XML to return hierarchical data from SQL Server? It's
> well-optimized, and I have absolutely no problem returning XML from a
> stored proc when performance is an issue.
I haven't ued it but it seems like a bit of a VB6 style solution to me.
Anything using XML isn't going to be that efficient. (BTW, seems like I'm
telling sqlserver how to do things again :-)
> If you really want to do your joins in the client you definitely have the
> option to read all of your "square blocks of data" as you call them back
> and perform the joins on the client with your own C# nested loops. You
> may run into bandwidth limitations when you start pulling gigabytes of
> data across the wire and performing custom nested loops on the client to
> "marry them up", but on the plus side this could be the foundation for
> your very own custom database engine...
That's my point, it's another imperfect solution.
> So in reality you want a hierarchical DBMS as opposed to an OODBMS or a
> relational/SQL DBMS? Unfortunately SQL is optimized for relational data,
> not OO or hierarchical data, although there are ways to work around the
> limitations. There are definitely hierarchical DBMSs available if that's
> where your needs are, but you'll face other limitations there.
I don't think I want a hierarchical database, just to be able to return
hierarchical data.
> At any rate this has been an interesting conversation, but I think we've
> eventually gotten to the root of your complaint here -- essentially you
> don't want a SQL DBMS, you want a hierarchical DBMS. You already know
> that the users here can't really rewrite SQL Server to your
> specifications, but you've also been informed that you can file your
> recommendations at http://connect.microsoft.com to get them heard by the
> people who can rewrite it to your specifications.
>
> So I think I've come to the end of my portion of this thread. You'll have
> to sell MS on your vision of rewriting SQL Server as a hierarchical DBMS
> or an OODBMS or whatever model you would like them to implement.
Ok, fair enough. Thanks for the interesting conversation. :-)
Michael
Michael C
> Yes, it's true that the declarative model is not perfect, but we sometimes
> have to help the optimizer quite a bit.
Sometimes? We have to tell sqlserver how to do stuff in almost everything we
do in sqlserver. A beginner will write inefficient tables, views, indexes
and queries because they don't know how to tell sqlserver how to do things.
> But still SQL is a lot better that you would get if you would with a C#
> program.
That's certainly true, although I would suspect it would run this example in
less than 30 seconds (assuming the data was in a text file). You can
actually have indexes in linq, you just need to put your data into a
hashtable or some other kind of lookup.
> Well, in this particular example the correct plan is trivial, but
> for more complex plans it isn't, and this is an area where SQL beats C#.
That's true.
Michael
Michael C
> Rather than using an UDA, you can use FOR XML PATH for this. Here is
> an example:
>
> select CustomerID,
> substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
> -- strip the last ',' from the list
> from
> Customers c cross apply
> (select convert(nvarchar(30), OrderID) + ',' as [text()]
> from Orders o
> where o.CustomerID = c.CustomerID
> order by o.OrderID
> for xml path('')) as Dummy(OrdIdList)
> go
>
> It's not really what you call intuitive, but it's very useful.
Thanks but I had a look at all the various options as there was a website I
found that listed about 10 or so different methods. I found the clr method
to be the simplest and easiest to use. I was after something that was
reusable and I got the bonus of speed with dot net. If someone needs a new
aggregate to concatenate strings then I just add a new one.
Michael
Erland Sommarskog
For SQL 2008, the above solution still has the bonus that you can easily
sort the resulting lists.
But the XML solution is certainly a bit strange. You also need some more
trickery to handle entitization. That is, in the above and "&" in the data
will become & in the output.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
Erland Sommarskog
> We would have restrictions but there is a difference between restrictions
> and silly restrictions. Having an 8K limit on dot net code is a silly
> restriction. Not having generics in the *first* release of a product is
> quite a valid restriction. The second release had generics.
And the second release of SQLCLR had unlimited sizes for UDTs and
UDAs.
And in SQL Server, like it or not, 8000 bytes is a dividing line,
between regular object and large object. Doing something in large
objects is one more feature.
> That's all there is to the code, it's such a simple problem to solve that
> the code is minimal. As I said, if ADO.net can do it then linq can do it.
> You stated ado.net can do it so it is possible.
So why don't you do it then?
Just because ADO .Net can do it, does not mean that LINQ knows that
ADO .Net can do it, or how it does it. Even less so if you bring EF.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
Erland Sommarskog
> "Erland Sommarskog" <esq...@sommarskog.se> wrote in message
> news:Xns9CD0613B9...@127.0.0.1...
>> Yes, it's true that the declarative model is not perfect, but we
>> sometimes have to help the optimizer quite a bit.
>
> Sometimes? We have to tell sqlserver how to do stuff in almost
> everything we do in sqlserver. A beginner will write inefficient tables,
> views, indexes and queries because they don't know how to tell sqlserver
> how to do things.
Database design and physical design, that is indexes requires skills.
SQL Server is not an AI thing that creates indexes on its own.
Once the right indexes are in place, and the database design is good,
you should not need that many hints.
But there is quite a few gotachs to make things more difficult, so
even the knowledgeable can slip.
>> But still SQL is a lot better that you would get if you would with a C#
>> program.
>
> That's certainly true, although I would suspect it would run this
> example in less than 30 seconds (assuming the data was in a text file).
Yes, C# stored procedure that would read the temp table into a dataset
could probably beat the T-SQL solution with quite a margin. C# is after
all better apt to run loops than T-SQL.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
Michael C
> Yes, C# stored procedure that would read the temp table into a dataset
> could probably beat the T-SQL solution with quite a margin. C# is after
> all better apt to run loops than T-SQL.
I guess C# doesn't have to worry about locks, multiple users etc so it's not
really a valid comparison.
Michael
Michael C
> And the second release of SQLCLR had unlimited sizes for UDTs and
> UDAs.
That is true but it's still a pretty silly limitation to have at all. It
took until 2005 (7th release?) were we could have local variables of more
than 8K. It just seems that sqlserver always has a much bigger list of
gotchas than other products and this list of gotchas always makes less
sense.
>> That's all there is to the code, it's such a simple problem to solve that
>> the code is minimal. As I said, if ADO.net can do it then linq can do it.
>> You stated ado.net can do it so it is possible.
>
> So why don't you do it then?
I've explained several times that I can't be bothered installing 2008 just
to write less than 5 lines of code to prove something that would be very
simple to do. I don't mean to be rude but I really think you're clutching at
straws here trying to push this very minor point. Considering I've already
provided a code sample anyway. Basically linq is very flexible and will have
no problem providing the data in the form that ado.net requires.
> Just because ADO .Net can do it, does not mean that LINQ knows that
> ADO .Net can do it, or how it does it. Even less so if you bring EF.
EF is a little different and I have already stated that it's likely it can't
do it.
Michael
Erland Sommarskog
to MSIL), and T-SQL is not.
Actually, I found the other day when testing functions for making comma-
separated lists into table, and if I had a list of numbers, it was better
to make a special UDF that returns integer, and thus to the string-to-int
conversion in C#, and returning a table of strings and convert in T-SQL.
The difference was not big, maybe 20 ms for a total execution time
over 500 ms, but nevertheless.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
Erland Sommarskog
> "Erland Sommarskog" <esq...@sommarskog.se> wrote in message
> news:Xns9CD3F2D05...@127.0.0.1...
>> And the second release of SQLCLR had unlimited sizes for UDTs and
>> UDAs.
>
> That is true but it's still a pretty silly limitation to have at all.
As I said, there is a difference between a limited object and an unlimited
object. SQL Server does not live in a some nice model world which
pretends to be unlimited, but which crashes when memory runs out.
> It took until 2005 (7th release?) were we could have local variables of
> more than 8K.
You could have input parameters of text/ntext/image in previous versions,
but you could not assign them.
And there is a difference. Consider this script:
declare @g varchar(MAX) = ''
declare @x varchar(MAX) = replicate(convert(varchar(MAX), 'K'), 1000000)
declare @i int = 1500
while @i > 0
select @g += @x, @i -= 1
select datalength(@g)
I'm running this on a virtual machine with 516 MB of memory and some
786 MB of pagefile. How do think it ends?
As a clue, it has been running for soon four hours, and tempdb has
expanded from 8 MB to almost 1GB. We don't know yet, but I'm optimistic
that the script will complete successfully.
The corresponding C# program would complete a lot faster - with an
out-of-memory exception.
And this is the important thing: if you have to deal with large object
in a server, you must also be able to spill to disk. But if everything
could spill to disk, there would be a performance penalty for that. So
it does make sense in having a division in small and large objects. And,
obviously, spilling objects to disk is more complex than having them
in memory only.
Then you can call that silly if you like.
> I've explained several times that I can't be bothered installing 2008
> just to write less than 5 lines of code to prove something that would be
> very simple to do. I don't mean to be rude but I really think you're
> clutching at straws here trying to push this very minor point.
> Considering I've already provided a code sample anyway. Basically linq
> is very flexible and will have no problem providing the data in the form
> that ado.net requires.
Again: LINQ would still need to know what parameter types to use when
defining the parameter list. It's not going to sort it out magically.
And I'm sorry, it does not hold to say that something is trivial, and
refusing to prove it.
Michael C
> As I said, there is a difference between a limited object and an unlimited
> object. SQL Server does not live in a some nice model world which
> pretends to be unlimited, but which crashes when memory runs out.
Sounds fine to me.
> And there is a difference. Consider this script:
>
> declare @g varchar(MAX) = ''
> declare @x varchar(MAX) = replicate(convert(varchar(MAX), 'K'), 1000000)
> declare @i int = 1500
> while @i > 0
> select @g += @x, @i -= 1
> select datalength(@g)
>
> I'm running this on a virtual machine with 516 MB of memory and some
> 786 MB of pagefile. How do think it ends?
>
> As a clue, it has been running for soon four hours, and tempdb has
> expanded from 8 MB to almost 1GB. We don't know yet, but I'm optimistic
> that the script will complete successfully.
>
> The corresponding C# program would complete a lot faster - with an
> out-of-memory exception.
Sounds reasonable. In the *extremely* rare case I need to store such huge
strings then I expect to send the results to disk. If this is the reason
sqlserver has these 8k limits then that seams even sillier than I first
thought. A feature than it is very rarely used seams to be limiting the
other 99% of cases.
> And this is the important thing: if you have to deal with large object
> in a server, you must also be able to spill to disk. But if everything
> could spill to disk, there would be a performance penalty for that. So
> it does make sense in having a division in small and large objects. And,
> obviously, spilling objects to disk is more complex than having them
> in memory only.
>
> Then you can call that silly if you like.
I still do. The only difference is they reduced the limit where it crashes
to a 1980s era figure of 8k.
>> I've explained several times that I can't be bothered installing 2008
>> just to write less than 5 lines of code to prove something that would be
>> very simple to do. I don't mean to be rude but I really think you're
>> clutching at straws here trying to push this very minor point.
>> Considering I've already provided a code sample anyway. Basically linq
>> is very flexible and will have no problem providing the data in the form
>> that ado.net requires.
>
> Again: LINQ would still need to know what parameter types to use when
> defining the parameter list. It's not going to sort it out magically.
No, of course not, you have to tell it what to convert it to. This was
exactly what I did in the code sample I provided. You did read the code I
posted? :-)
> And I'm sorry, it does not hold to say that something is trivial, and
> refusing to prove it.
Actually, it's up to you to prove it is not possible. You are the one who
raised this point from second hand information of which you're not really
sure about. (is it Linq or linq to sql or EF that has this supposed
limitation). It is not my responsibility to do the work for you to prove or
disprove something that you raised. As far as I am concerned this is
possible from linq and I will not discuss this any further until you've
actually attempted it and can provide something a little more substantial.
Michael
Erland Sommarskog
> Sounds reasonable. In the *extremely* rare case I need to store such huge
> strings then I expect to send the results to disk. If this is the reason
> sqlserver has these 8k limits then that seams even sillier than I first
> thought. A feature than it is very rarely used seams to be limiting the
> other 99% of cases.
Sure, this example is not very practical. But 1 MB string for an image
is nothing unreasonable. Then imagine 1000 users doing it at the same time.
This is what you fail to realise: SQL Server is a server application, which
should be able to serve thousands of simultaneous users at the same time.
C# as such is not defined to be a server, so it can afford to give users a
simplistic model, and then crash when it hits the ceiling.
> I still do. The only difference is they reduced the limit where it crashes
> to a 1980s era figure of 8k.
It doesn't crash. Yes, you get an execution error (or truncation), but the
entirely application does not stop.
> No, of course not, you have to tell it what to convert it to. This was
> exactly what I did in the code sample I provided. You did read the code I
> posted? :-)
It didn't tell me much. I asked for working code, not for some theories.
> Actually, it's up to you to prove it is not possible. You are the one who
> raised this point from second hand information of which you're not really
> sure about.
Eh, my source is a fellow SQL Server MVP with strong knowledge in .Net. And
I asked the question in our internal MVP forum, so I expect that if his
answer was wrong, someone would have corrected him.
> As far as I am concerned this is possible from linq and I will not
> discuss this any further until you've actually attempted it and can
> provide something a little more substantial.
How would I be able attempt something before you have posted any code?
Keep in mind that I'm an SQL programmer that only occasionally writes C#
and never uses LINQ. But I know how to pass a TVP to SqlClient - and I
can't see how LINQ out of thin air set all parameter properties correctly.
So the burden of proof lies on you.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
Michael C
> How would I be able attempt something before you have posted any code?
> Keep in mind that I'm an SQL programmer that only occasionally writes C#
> and never uses LINQ. But I know how to pass a TVP to SqlClient - and I
> can't see how LINQ out of thin air set all parameter properties correctly.
>
> So the burden of proof lies on you.
Sorry, that is complete rubbish and as I said I am not going to discuss this
point further until you have some proof. What you have is basically about as
sketchy as it gets and you've just gone and admitted you have no proof. Do
your own work Erland, I am not your lacky.
Michael
Michael C
> Eh, my source is a fellow SQL Server MVP with strong knowledge in .Net.
> And
> I asked the question in our internal MVP forum, so I expect that if his
> answer was wrong, someone would have corrected him.
Interesting that it appears you've made quite an effort to prove what is a
*really* minor point and still don't have a answer. Now you're trying to get
the person you're trying to impress this point apon to help you. If your MVP
friend is so knowledgable why can't you get a straight answer out of him?
The funny thing is is that if he really did know C# that well then he'd know
how to do it (it really is trivial).
Michael
Erland Sommarskog
> Interesting that it appears you've made quite an effort to prove what is
> a *really* minor point and still don't have a answer. Now you're trying
> to get the person you're trying to impress this point apon to help you.
> If your MVP friend is so knowledgable why can't you get a straight
> answer out of him? The funny thing is is that if he really did know C#
> that well then he'd know how to do it (it really is trivial).
I got a straight answer: no, it is not possible.
All you have been able to produce is that it works, but you have completely
refused to make any effort to prove it.
And furthermore, while I do not know LINQ, I know how to pass a TVP through
ADO .Net, and I know that there is no way that LINQ is able to do that
without someone coding for it.
As for it being a minor point, you complained that SQL Server had so
many limitations that would not happen i C#. I pointed out this limitation
in LINQ, which in my opinion is a considerable limitation. If now LINQ is
so fantastic, why does it not permit users to utilise the full power of
SQL Server?
Since you have not worked with TVPs and SQL 2008, you could have accepted
my word for it. Instead you insist on maintaining a position despite you
don't have the knowledge.
Michael C
> I got a straight answer: no, it is not possible.
You didn't get a straight answer because you have not been able to answer
my question as to whether it is straight linq or linq to sql. Basically you
don't know the most basic detail of this. Are you not in contact with this
person to ask them again?
> All you have been able to produce is that it works, but you have
> completely
> refused to make any effort to prove it.
No, I have provided sample code. Did you try my sample code and prove it
does not work?
> And furthermore, while I do not know LINQ, I know how to pass a TVP
> through
> ADO .Net, and I know that there is no way that LINQ is able to do that
> without someone coding for it.
So you're saying that it is possible? Of course you need to do some coding
for it.
> As for it being a minor point, you complained that SQL Server had so
> many limitations that would not happen i C#. I pointed out this limitation
> in LINQ, which in my opinion is a considerable limitation. If now LINQ is
> so fantastic, why does it not permit users to utilise the full power of
> SQL Server?
Even though linq does do this, *if* the limitation did exist it is not silly
like many of the limitations in sqlserver. C# does have limitations but they
are not as silly as those in sqlserver. Don't you think an 8k limit is the
most rediculous thing you've every heard of in 2005? C# just doesn't have
limitations that silly. My point was never that C# didn't have limitations,
just that they weren't as silly as those in sqlserver.
> Since you have not worked with TVPs and SQL 2008, you could have accepted
> my word for it. Instead you insist on maintaining a position despite you
> don't have the knowledge.
Because it's fairly clear you don't really know the full details of this and
you've basically admitted it does work with your "without coding for it"
statement. Of course you have to write code for it and of course I'm going
to question you on it.
Michael
Michael Coles
parameters.
--
Thanks
Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------
"Michael C" <mi...@nospam.com> wrote in message
news:%236i4vtG...@TK2MSFTNGP04.phx.gbl...
Michael C
> Michael, neither LINQ to SQL nor EF 1 currently supports table-valued
> parameters.
Thanks Michael, that is exactly what i have been saying all along. I presume
the fact you left out plain linq that means it is possible. And presumably
for LINQ to SQL and EF it would just be a limitation of the code generator
which could be corrected with some very simple code changes.
Michael
Michael Coles
Sorry, you'll have to explain to me what "plain LINQ" is exactly? Is that
the LINQ standard query (sequencing) operators as expressed by the standard?
If so, SQL Server table-valued parameters don't apply to that level of
detail, which is why they're not part of the .NET Standard Query Operators
(http://download.microsoft.com/download/5/8/6/5868081c-68aa-40de-9a45-a3803d8134b8/standard_query_operators.doc)
or the C# 3.0 specification
(http://msdn.microsoft.com/en-us/library/ms364047(VS.80).aspx).
Of course I could be wrong. If we're talking about another LINQ provider
that currently implements SQL Server TVPs, perhaps you can point us in the
direction of that LINQ provider? The only application I'm currently aware
of that uses SQL Server TVPs is SQL Server 2008, but I gave up on trying to
keep track of LINQ providers after LINQ to Twitter.
Michael C
> Sorry, you'll have to explain to me what "plain LINQ" is exactly? Is that
> the LINQ standard query (sequencing) operators as expressed by the
> standard? If so, SQL Server table-valued parameters don't apply to that
> level of detail, which is why they're not part of the .NET Standard Query
> Operators
> (http://download.microsoft.com/download/5/8/6/5868081c-68aa-40de-9a45-a3803d8134b8/standard_query_operators.doc)
> or the C# 3.0 specification
> (http://msdn.microsoft.com/en-us/library/ms364047(VS.80).aspx).
>
> Of course I could be wrong. If we're talking about another LINQ provider
> that currently implements SQL Server TVPs, perhaps you can point us in the
> direction of that LINQ provider? The only application I'm currently aware
> of that uses SQL Server TVPs is SQL Server 2008, but I gave up on trying
> to keep track of LINQ providers after LINQ to Twitter.
I'm just talking about linq to objects. If we're using linq to objects to
format data for a parameter object to call an ADO command object. eg
SqlCommand command = CodeToCreateMyCommand();
command.parameters["MyTableVariable"].Value = ResultsFromMyLinqQuery();
command.Execute();
Basically what I am saying is that linq to objects can be used to work with
table variables.
Michael
Michael Coles
I see you're using an ADO.NET SqlCommand and .Executing it your example
below, but I'm at a loss as to what this has to do with using Table-Valued
*Parameters* with LINQ? You're assigning the results of some function that
appears to return the results of a LINQ query as a parameter to an ADO.NET
SqlCommand. As Erland pointed out, TVPs work with ADO.NET.
--
Thanks
Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------
"Michael C" <mi...@nospam.com> wrote in message
news:O73sotJd...@TK2MSFTNGP06.phx.gbl...
Michael C
>I see you're using an ADO.NET SqlCommand and .Executing it your example
>below, but I'm at a loss as to what this has to do with using Table-Valued
>*Parameters* with LINQ? You're assigning the results of some function that
>appears to return the results of a LINQ query as a parameter to an ADO.NET
>SqlCommand. As Erland pointed out, TVPs work with ADO.NET.
That's my point, any limitation is due to the code generator that generates
linq to sql, it's not a limitation of linq to objects. Whether you use linq
to objects or linq to sql it's going to come down to calling a command
object.
Michael
Michael Coles
I'm not understanding how your example proves your point. Your example
proves (assuming a few things about your code, that is) that ADO.NET can
handle TVPs, which is what Erland has already stated a few times. Is the
point you're trying to make that LINQ to Objects does support SQL Server
Table-Valued Parameters? I'd love to see that code sample sometime!
The "code generator" is not the *only* limitation. You can generate all the
code in the world, incorporating all kinds of features, but it does you no
good if the guts of your provider can't take advantage to make the new
features actually work. This is a provider limitation. The LINQ to SQL
provider, and all LINQ providers that I know of, do not have the capability
to handle SqlDbType.Structured data.
If it were as simple a change as you speculate, LINQ to SQL TVP support
would probably be scheduled for .NET 4.0. In fact, the only change that
appears to be on the radar for LINQ TVPs in .NET 4.0 is that SQL Metal will
simply skip/ignore stored procs that use TVPs instead of aborting
processing.
Michael Coles
thread, do you actually know what a "Table-Valued Parameter" is?
"Michael C" <mi...@nospam.com> wrote in message
news:uEYcpdKd...@TK2MSFTNGP05.phx.gbl...
Erland Sommarskog
> And there is a difference. Consider this script:
>
> declare @g varchar(MAX) = ''
> declare @x varchar(MAX) = replicate(convert(varchar(MAX), 'K'), 1000000)
> declare @i int = 1500
> while @i > 0
> select @g += @x, @i -= 1
> select datalength(@g)
>
> I'm running this on a virtual machine with 516 MB of memory and some
> 786 MB of pagefile. How do think it ends?
>
> As a clue, it has been running for soon four hours, and tempdb has
> expanded from 8 MB to almost 1GB. We don't know yet, but I'm optimistic
> that the script will complete successfully.
was getting worried, because tempdb grew a lot more than I expected.
The final size is just over 3 GB, with 683 MB to spare on the disk.
Erland Sommarskog
>> All you have been able to produce is that it works, but you have
>> completely refused to make any effort to prove it.
>
> No, I have provided sample code. Did you try my sample code and prove it
> does not work?
Permit me to point out that in the programming trade, nothing is proven to
work until code is written and tested.
Later in the thread, you can this sample:
SqlCommand command = CodeToCreateMyCommand();
command.parameters["MyTableVariable"].Value = ResultsFromMyLinqQuery();
command.Execute();
This sample is not likely to work of the simple reason that you fail
to set the parameter type. You also need to make sure that
ResultsFromMyLinqQuery returns either a DataSet, a List<SqlDataRecord>
or an IDataReader. Of course, all these are no-brainers.
But the more important point is that is not what I've been talking about.
As Michael Coles have pointed out, I'm talking about calling a stored
procedure through LINQ (to SQL or EF) and passing a TVP.
> Even though linq does do this, *if* the limitation did exist it is not
> silly like many of the limitations in sqlserver. C# does have
> limitations but they are not as silly as those in sqlserver. Don't you
> think an 8k limit is the most rediculous thing you've every heard of in
> 2005? C# just doesn't have limitations that silly. My point was never
> that C# didn't have limitations, just that they weren't as silly as
> those in sqlserver.
Of course what is silly is in the eye of the beholder. Limitations can
exist of various reasons. One really "silly" restriction in SQL Server
Management Studio is that it does not support connection to SQL 7. I'm
fairly sure this was possible in early betas. So why was it cut? Maybe
they found some pieces that did not work. Or they simply ran out of time
to test it. Since it was not deemed to be high priority, they cut it.
People who still have SQL 7 in their shops would of course disagree.
But limitations can also exist because of technical hurdles. When it
comes to LINQ to SQL, we should keep in mind that VS 2008 shipped before
SQL 2008, so it is not strange that they did not support a future
feature (although, the feature was fully known at at the time). But if
I understand Michael Coles correctly, there are deeper problems than just
add a few lines to the code genereator. Silly or not?
Well, what in my opinion think is silly is to tout LINQ as a the right
way to call SQL Server, when this means that you cannot use all capabilities
in SQL Server. Of course, EF may be good if you need to be portable and
support multiple platforms. In such a scenario you don't have interest
in using proprietary features.
But using LINQ to SQL or EF when you only have the intention against
SQL Server? I would advice against that, because you will lose to much
of the power in SQL Server.
As for the 8k limitations in SQL Server, I have explained a number of
times that SQL Server distinguishes between small objects and big objects,
as distinction which is unknown in C#. C# does not impose any limits,
but the OS will tell you when you have hit the ceiling. This is certianly
permissible for C#, because many C# programs are single-user programs,
and it can permit the OS to handle memory and spilling to disk. However,
this is not permissible for something that always runs in a server
context with multiple users.
The 8K limitations I can think of on the top of my head, SQL 2005 are for
CLR UDTs, user-defined aggregates and the sql_variant data type. In
SQL 2008, it applies only to sql_variant.
Whether it was silly to limit UDTs and UDAs to 8K in SQL 2005 can
be disputed. Certainly for UDAs it's quite a limitation. But keep in
mind that it was the 1.0 of the CLR in SQL Server, and they had to ship
at some point. And certainly it was better to ship with UDAs limited
to 8K, than no UDAs at all?
You thought yourself that it was OK to ship C# 1.0 without generics,
which in an object-oriented perspective, it's a far bigger handicap
than the 8K limitation for UDAs, as this is a core feature for an
object-oriented language. After all, in all the years SQL 2005 have
been available, I've written one user-defined aggregate, and I was able
to work around the 8K limitation without too much pain.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
Michael C
> I'm not understanding how your example proves your point. Your example
> proves (assuming a few things about your code, that is) that ADO.NET can
> handle TVPs, which is what Erland has already stated a few times. Is the
> point you're trying to make that LINQ to Objects does support SQL Server
> Table-Valued Parameters? I'd love to see that code sample sometime!
My point is that Linq can return the appropriate data to pass a TVP.
> The "code generator" is not the *only* limitation. You can generate all
> the code in the world, incorporating all kinds of features, but it does
> you no good if the guts of your provider can't take advantage to make the
> new features actually work. This is a provider limitation. The LINQ to
> SQL provider, and all LINQ providers that I know of, do not have the
> capability to handle SqlDbType.Structured data.
Actually yes this is true. I was mistaken about the details of linq-to-sql.
I think it is a load of rubbish so I have not used it much.
Michael
Michael C
> Come to think of it, and I have to ask this based on your comments in this
> thread, do you actually know what a "Table-Valued Parameter" is?
I'm not even going to dignify that with an answer, oops, I just did ;-)
Michael
Michael Coles
> "Michael Coles" <michaelcoAToptonlineDOTnet> wrote in message
>> I'm not understanding how your example proves your point. Your example
>> proves (assuming a few things about your code, that is) that ADO.NET can
>> handle TVPs, which is what Erland has already stated a few times. Is the
>> point you're trying to make that LINQ to Objects does support SQL Server
>> Table-Valued Parameters? I'd love to see that code sample sometime!
>
> My point is that Linq can return the appropriate data to pass a TVP.
... .NET DataTables, IEnumerable objects and data readers? I would hope
LINQ can return some (if not all) of these types of objects or it wouldn't
be of much use.
>> The "code generator" is not the *only* limitation. You can generate all
>> the code in the world, incorporating all kinds of features, but it does
>> you no good if the guts of your provider can't take advantage to make the
>> new features actually work. This is a provider limitation. The LINQ to
>> SQL provider, and all LINQ providers that I know of, do not have the
>> capability to handle SqlDbType.Structured data.
>
> Actually yes this is true. I was mistaken about the details of
> linq-to-sql. I think it is a load of rubbish so I have not used it much.
It's really for people who want a lightweight .NET OR/M solution for SQL
Server, but I have to admit I haven't found much use for it myself.
Michael C
> ... .NET DataTables, IEnumerable objects and data readers? I would hope
> LINQ can return some (if not all) of these types of objects or it wouldn't
> be of much use.
I did say it could do it easily and it was trvial. :-)
> It's really for people who want a lightweight .NET OR/M solution for SQL
> Server, but I have to admit I haven't found much use for it myself.
I guess I don't see an issue with it except for the way it translates linq
into sql code. I can't imagine why we'd want to go to so much trouble to
have linq translated into sql. Surely it would just make writing optimised
sql more difficult. This is one of the reasons I want a native linq
database, then we'd have a really good reason to use linq :-)
Michael
Michael Coles
>
> I did say it could do it easily and it was trvial. :-)
Unfortunately the ability to return a .NET DataTable in .NET code != SQL
Server TVP Support
> I guess I don't see an issue with it except for the way it translates linq
> into sql code. I can't imagine why we'd want to go to so much trouble to
> have linq translated into sql. Surely it would just make writing optimised
> sql more difficult. This is one of the reasons I want a native linq
> database, then we'd have a really good reason to use linq :-)
At this point I have no definition for a "native linq database". It sounds
like a marketing slogan more than a real enterprise DBMS. So far we've seen
a wish list of DBMS and database language features, but not a foundation for
an enterprise DBMS. BTW, the MS push appears to be to get people using EF
instead of LINQ right now.
Michael C
> Server TVP Support
Depends how you look at it. I only use linq to objects and call the database
via ado.net so to me it means it does work.
> At this point I have no definition for a "native linq database". It
> sounds like a marketing slogan more than a real enterprise DBMS.
That suprised me. I thought it was such an obvious idea that it was just a
matter of time before it would be released. Why write a new sql style
language if we don't have a database that uses it directly?
> So far we've seen a wish list of DBMS and database language features, but
> not a foundation for an enterprise DBMS. BTW, the MS push appears to be
> to get people using EF instead of LINQ right now.
Microsoft tend to market all their new features (of course) but many confuse
this with microsoft recommending this as the best way to do things.
Michael
Michael Coles
> database via ado.net so to me it means it does work.
I can see that possibly working; however, it does not equal SQL Server TVP
support in LINQ. It's simply a workaround for the lack of support, and if
you're dealing in disconnected datasets a potentially costly one. The fact
that you don't even use SQL 2008 means you have no idea whether or not even
this workaround will work.
> That suprised me. I thought it was such an obvious idea that it was just a
> matter of time before it would be released. Why write a new sql style
> language if we don't have a database that uses it directly?
Maybe because no one has defined it. The obvious ideas are often the
trickiest to define, much less implement, because people tend to take a lot
of things for granted. If it really is that obvious, take a shot at giving
it a clear definition.
> Microsoft tend to market all their new features (of course) but many
> confuse this with microsoft recommending this as the best way to do
> things.
Actually LINQ and EF are both living in the OR/M space right now. Some
might consider them competing products that offer similar functionality. If
so the decision has to be made whether resources will be committed to one or
the other, or if resources will be split to develop both. From what I've
read it sounds like the decision was made to dedicate resources towards EF.
--
Thanks
Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------
"Michael C" <mi...@nospam.com> wrote in message
news:uicMUj5d...@TK2MSFTNGP04.phx.gbl...
Michael C
> I can see that possibly working; however, it does not equal SQL Server TVP
> support in LINQ. It's simply a workaround for the lack of support, and if
> you're dealing in disconnected datasets a potentially costly one.
Again it depends on how you look at it. Certainly linq to objects does not
restrict you from using TVPs.
> The fact that you don't even use SQL 2008 means you have no idea whether
> or not even this workaround will work.
As you said, I would be suprised if it did not work.
> Maybe because no one has defined it. The obvious ideas are often the
> trickiest to define, much less implement, because people tend to take a
> lot of things for granted. If it really is that obvious, take a shot at
> giving it a clear definition.
That would be a considerable amount if work. Most of it is clear in my mind
but some is not. To how much of a degree would it be object orientated. I
certainly feel like I might have more luck convincing the US to pull out of
iraq than convincing MS of anything this big :-)
Michael
Michael Coles
> Again it depends on how you look at it. Certainly linq to objects does not
> restrict you from using TVPs.
And while your television probably doesn't provide any food digestion
functionality, it certainly does not restrict you from digesting your food.
Talking backwards you might be able to convince yourself, but it still makes
no sense. LINQ to Objects does not support TVPs because they are a SQL
Server construct. This was why I asked you previously if you actually knew
what a TVP is. You're talking yourself into believing that an ADO.NET
DataSet is the same thing as a TVP. The ADO.NET DataSet can be used to
populate a TVP, but it is *not* a TVP; in much the same way an XML file can
be used to populate a relational database, but an XML file is *not* a
relational database.
Michael C
> functionality, it certainly does not restrict you from digesting your
> food. Talking backwards you might be able to convince yourself, but it
> still makes no sense. LINQ to Objects does not support TVPs because they
> are a SQL Server construct. This was why I asked you previously if you
> actually knew what a TVP is. You're talking yourself into believing that
> an ADO.NET DataSet is the same thing as a TVP. The ADO.NET DataSet can be
> used to populate a TVP, but it is *not* a TVP; in much the same way an XML
> file can be used to populate a relational database, but an XML file is
> *not* a relational database.
Again, it depends on how you look at it. Linq to objects can return the data
needed for a TVP, basically it works with TVPs. Simple really. Your analogy
is flawed as your television in no way has anything to do with digestion
where linq marries up with ado.net and TVPs quite well.
Michael
Michael Coles
> Again, it depends on how you look at it. Linq to objects can return the
> data needed for a TVP, basically it works with TVPs. Simple really. Your
> analogy is flawed as your television in no way has anything to do with
> digestion where linq marries up with ado.net and TVPs quite well.
Since we're really stretching the definition of "support for TVPs", we can
surely stretch the definition of "support for digestion" to include the Food
Network. As you say, it depends on how you look at it, if you choose to
look at it that way. :) The fact that the LINQ providers have no support
for TVPs is not even a question. The fact that you can kludge around the
lack of support by adding a layer of abstraction between SQL Server and LINQ
doesn't change that fact.
We could likewise say that JavaScript has no support for querying SQL Server
directly; however, we can put a Web Service in between JavaScript and SQL
Server to accept requests from JavaScript, connect to SQL Server, execute
the query, and return the results to JavaScript in JSON format. This
doesn't mean JavaScript now suddenly *poof* supports SQL Server querying --
what it means is we have added a layer of abstraction in between JavaScript
and SQL Server that can communicate with both. You've proposed the same
thing with ADO.NET, which doesn't suddenly mean that *wow* LINQ suddenly
supports TVPs.
Michael C
> Since we're really stretching the definition of "support for TVPs", we can
> surely stretch the definition of "support for digestion" to include the
> Food Network. As you say, it depends on how you look at it, if you choose
> to look at it that way. :) The fact that the LINQ providers have no
> support for TVPs is not even a question. The fact that you can kludge
> around the lack of support by adding a layer of abstraction between SQL
> Server and LINQ doesn't change that fact.
>
> We could likewise say that JavaScript has no support for querying SQL
> Server directly; however, we can put a Web Service in between JavaScript
> and SQL Server to accept requests from JavaScript, connect to SQL Server,
> execute the query, and return the results to JavaScript in JSON format.
> This doesn't mean JavaScript now suddenly *poof* supports SQL Server
> querying -- what it means is we have added a layer of abstraction in
> between JavaScript and SQL Server that can communicate with both. You've
> proposed the same thing with ADO.NET, which doesn't suddenly mean that
> *wow* LINQ suddenly supports TVPs.
Looks like we're going to have to agree to disagree on this extremely minor,
drawn out point.
Michael