Oracle porting to SQLServer

We are planning on porting out app from oracle db to
sqlserver. In many cases we have used oracle extension to
ANSI SQL and in general what to find out what and how does
SQLServer 2000 support following feature that are
available in Oracle:
1. Oracle Sequences.
2. Materialized Views
3. Global temp tables
4. Oracle extensions to ANSI SQL like START with CONNECT
BY to do hierarchical queries, decode, etc
Thanks1. Oracle Sequences - IDENTITY columns. Or manually
creating a table with the table name, current value and
next value column and applying some logic surrounding it.
2. Materialized Views - Same concept exist in SQL Server
a.k.a Indexed Views
3. Global temp tables - Its been there in SQL Server for a
long time. ## tables.
4. Oracle extensions to ANSI SQL like START with CONNECT
BY to do hierarchical queries, decode, etc - Do some
research on CASE statement.
Rich.
>--Original Message--
>We are planning on porting out app from oracle db to
>sqlserver. In many cases we have used oracle extension to
>ANSI SQL and in general what to find out what and how
does
>SQLServer 2000 support following feature that are
>available in Oracle:
>1. Oracle Sequences.
>2. Materialized Views
>3. Global temp tables
>4. Oracle extensions to ANSI SQL like START with CONNECT
>BY to do hierarchical queries, decode, etc
>Thanks
>.
>|||"Rich B" <anonymous@.discussions.microsoft.com> wrote in message
news:052501c39a87$9d6dd830$a301280a@.phx.gbl...
> 1. Oracle Sequences - IDENTITY columns. Or manually
> creating a table with the table name, current value and
> next value column and applying some logic surrounding it.
Better to create a sequence table with an identity column to mimic the
behavior of Oracle sequences.
> 2. Materialized Views - Same concept exist in SQL Server
> a.k.a Indexed Views
> 3. Global temp tables - Its been there in SQL Server for a
> long time. ## tables.
Global temp tables in Oracle are something different.
Global temp tables in Oracle are persistent schema objects, and they look
exactly like ordinary tables, except that the data stored in them is private
to the session that is using them.
Oracle did it like this because each Oracle instance has only one database
and hence a single global catalog, and so Oracle can't really support lots
of users creating temp tables on the fly. Sql Server temp tables are
similar to Oracle temp tables insofar as the data is private to each
session, but unlike Oracle you must create the temp table each time before
it can be used.
David|||Actually one to create persistent temp tables in SQL
server is create a script that runs at the start of the
SQL server instance that will crate the global temporary
tables. As the temporary tables can be distinguished as
local and global by the way they are created # and ##,
using ## tables the table can be shared between sessions
and the data in that table can be inserted and purged
based on the session id or something like that.
>--Original Message--
>"Rich B" <anonymous@.discussions.microsoft.com> wrote in
message
>news:052501c39a87$9d6dd830$a301280a@.phx.gbl...
>> 1. Oracle Sequences - IDENTITY columns. Or manually
>> creating a table with the table name, current value and
>> next value column and applying some logic surrounding
it.
>Better to create a sequence table with an identity column
to mimic the
>behavior of Oracle sequences.
>> 2. Materialized Views - Same concept exist in SQL Server
>> a.k.a Indexed Views
>> 3. Global temp tables - Its been there in SQL Server
for a
>> long time. ## tables.
>Global temp tables in Oracle are something different.
>Global temp tables in Oracle are persistent schema
objects, and they look
>exactly like ordinary tables, except that the data stored
in them is private
>to the session that is using them.
>Oracle did it like this because each Oracle instance has
only one database
>and hence a single global catalog, and so Oracle can't
really support lots
>of users creating temp tables on the fly. Sql Server
temp tables are
>similar to Oracle temp tables insofar as the data is
private to each
>session, but unlike Oracle you must create the temp table
each time before
>it can be used.
>
>David
>
>.
>