quick question on SQL server and object support

[Menon]

Hi folks
I am not at all an SQL server guy (more of an Oracle developer.)
I wanted to know if you guys could tell me if SQL Server supports
objects in the database. If so, could you kindly point me to a
doc or a very quick overview of main object features?

Many thanx!

[Simon Hayes]

"Menon" <rm*******@gmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Hi folks
I am not at all an SQL server guy (more of an Oracle developer.)
I wanted to know if you guys could tell me if SQL Server supports
objects in the database. If so, could you kindly point me to a
doc or a very quick overview of main object features?

Many thanx!

That depends on what you mean by 'support' and 'object'. If you mean storing
large binaries, then yes - you can use the image data type to store up to
2GB of binary data in a row. If you mean instantiating an object written in
some particular language from TSQL then you can use the sp_OA% procedures to
manipulate COM objects, although the objects themselves are not in the
database, they're external.

If you mean anything more advanced than that, then you'll probably have to
wait for MSSQL 2005, which includes the .NET CLR, so you can write code
(stored procedures) in C# or VB in addition to TSQL, and use .NET assemblies
to define your own data types, aggregate functions and so on.

Simon

[Menon]

Thanx Simon.
By objects, I meant ability to create objects in your procedural
code (written in T-SQL.)

I can tell you what Oracle provides. In PL/SQL (Oracle's procedural
language), you can create objects (similar to objects in your
language.) You can even store these objects in what
are called object tables. Is this capability
available in SQL Server. Is there public documentation on this
available ?

[Erland Sommarskog]

Menon (rm*******@gmail.com) writes:

I can tell you what Oracle provides. In PL/SQL (Oracle's procedural
language), you can create objects (similar to objects in your
language.) You can even store these objects in what
are called object tables. Is this capability
available in SQL Server. Is there public documentation on this
available ?

In SQL 2000, no.

In SQL 2005, currently in beta, yes. If you are an MSDN Unviersal
subscriber, you can download the full SQL 2005 from MSDN Subscriber Downloads. Everyone can download the beta of the SQL Express edition
from http://msdn.microsoft.com/SQL/.
I'm not really sure how much of the SQL 2005 Books Online that is
publicly available. In any case, this white paper could be a start:
http://msdn.microsoft.com/sql/2005/2...lrguidance.asp
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Menon]

Thanx Erland,
That answers my question - I will also check out the links you pointed
to.

Best Regards
Menon

[Menon]

Folks
Can someone please give a really quick syntax of creating and using
objects
(Say, in T-SQL in 2005)
An example to do this In Oracle (using sql*plus - a command
line utility to execute SQL and PL/SQL (the stored procedure language
of Oracle ) is shown below:

-- first we create the type that represents the object interface:
scott@ORA10G> create or replace type employee as object
2 (
3 name varchar2(50),
4 phone varchar2(20),
5 emp_id number,
6 constructor function employee return self as result,
7 constructor function employee( name in varchar2,
8 phone in varchar2, emp_id in number )
9 return self as result,
10 member function get_name return varchar2,
11 member procedure set_name( p_name in varchar2 ),
12 -- other getters and setters
13 static procedure demo_static_proc
14 )
15 not final;
16 /

Type created.

-- next we create the type body that implements any methods declared
-- in the type interface we created above:

scott@ORA10G> create or replace type body employee
2 as
3 constructor function employee return self as result
4 is
5 begin
6 self.name := null;
7 self.phone := null;
8 self.emp_id := null;
9 return;
10 end;
11
12 constructor function employee( name in varchar2,
13 phone in varchar2, emp_id in number )
14 return self as result
15 is
16 begin
17 self.name := name;
18 self.phone := phone;
19 self.emp_id := emp_id;
20 return;
21 end;
22
23 member function get_name return varchar2
24 is
25 begin
26 return name;
27 end;
28
29 member procedure set_name( p_name in varchar2 )
30 is
31 begin
32 name := p_name;
33 end;
34 -- define other getters and setters here (deleted for brevity)
35 static procedure demo_static_proc
36 is
37 begin
38 dbms_output.put_line ( 'This is a simple Oracle object type
that encapsulates a employee.');
39 end;
40 end;
41 /

Type body created.

Following is an example showing initialization of a variable of type
employee
in PL/SQL. Note that we can store employee object directly in tables
as well (not shown)

scott@ORA10G> declare
2 l_employee employee;
3 begin
4 l_employee := employee( 'Joe', '555-666-3345', 1 );
5 dbms_output.put_line( l_employee.name );
6 end;
7 /
Joe

PL/SQL procedure successfully completed.

If some one can show how to do the above (or another euivalent)
in T-SQL in SQL 2005, it would be great.

Many thanx!

[Erland Sommarskog]

Menon (rm*******@gmail.com) writes:

Can someone please give a really quick syntax of creating and using
objects
(Say, in T-SQL in 2005)

Unfortnately, I don't have any samples to share. There is a sample CLR
type that comes with the SQL 2005 beta, but I don't know of the license
terms permit me to repost the code to public newsgroups, and I am not
taking chances.

Anyway, this is very different from Oracle. Before you start type T-SQL,
you will have write the implementation for your type in a .Net language:
C#, Visual Basic or C++.

Once that is done, you enter the assembly into SQL Server with CREATE
ASSEMBLY and then finally you create the type with CREATE TYPE.

Once you have created the type, you can refer to components of the type
with dot notation:

SELECT @cmplxnumber.x = 1

If you have defined methods on the type you can refer to these with
dot notation as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Menon]

Hi Erland.
I guess I was mainly interested in knowing how different these
two features are - and I can see that they are completely different.
Even the SQL to access the code seems to be quite different (From your
one example.)

Many thanx!

Erland Sommarskog wrote:

Unfortnately, I don't have any samples to share. There is a sample CLR type that comes with the SQL 2005 beta, but I don't know of the license terms permit me to repost the code to public newsgroups, and I am not
taking chances.

Anyway, this is very different from Oracle. Before you start type T-SQL, you will have write the implementation for your type in a .Net language: C#, Visual Basic or C++.

Once that is done, you enter the assembly into SQL Server with CREATE
ASSEMBLY and then finally you create the type with CREATE TYPE.

Once you have created the type, you can refer to components of the type with dot notation:

SELECT @cmplxnumber.x = 1

If you have defined methods on the type you can refer to these with
dot notation as well.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Menon (rm*******@gmail.com) writes:

Even the SQL to access the code seems to be quite different (From your
one example.)

Huh? You had

self.name := null;

and I had

SELECT @cmplx.x = 1

That is the same as far accessing the components goes. Then it goes
without saying that there are tons of differences between MS SQL Server
and Oracle, and some are reflected in these samples. The @ in my
example signifies that this is a variable and not a column.

If you really want to make any useful comparison specific features, you
first need to learn some basics about T-SQL, before going into the gory
details.

I don't know PL/SQL or Oracle in general, but judging from your example,
it's quite different from T-SQL. As a programming language it also
appears to be a lot cleaner. T-SQL has some quite syntax details in
some places.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp