From: Michael C on
"Michael Coles" <admin(a)geocodenet.com> wrote in message news:%23hvu%
> 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.

>
> --
> Thanks
>
> Michael Coles
> SQL Server MVP
> Author, "Expert SQL Server 2008 Encryption"
> (http://www.apress.com/book/view/1430224649)
> ----------------
>


From: Michael Coles on
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.

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" <mike(a)nospam.com> wrote in message
news:eSJSvHZbKHA.5656(a)TK2MSFTNGP04.phx.gbl...
> "Michael Coles" <admin(a)geocodenet.com> wrote in message news:%23hvu%
>> 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.
>
>>
>> --
>> Thanks
>>
>> Michael Coles
>> SQL Server MVP
>> Author, "Expert SQL Server 2008 Encryption"
>> (http://www.apress.com/book/view/1430224649)
>> ----------------
>>
>
>

From: Erland Sommarskog on
Michael C (mike(a)nospam.com) writes:
>> 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, 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: Michael C on
"Michael Coles" <admin(a)geocodenet.com> wrote in message
news:Okx4EqZbKHA.5976(a)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 :-)))

> 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


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

> 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


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