From: Erland Sommarskog on
Michael C (mike(a)nospam.com) writes:
> 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, esquel(a)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
From: klem s on
Thank you all for your help
From: Michael C on
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CBE89529BEFAYazorman(a)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


From: Erland Sommarskog on
Michael C (mike(a)nospam.com) writes:
> 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, esquel(a)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

From: Michael C on
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CC0F0D571492Yazorman(a)127.0.0.1...
> 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


 |  Next  |  Last
Pages: 1 2 3 4 5 6 7 8 9 10 11
Prev: Sql Server on VMWare
Next: AdventureWorks database