SQL Server equivalents to Oracle-isms.(newbie)

SQL Server equivalents to Oracle-isms.(newbie)

Post by David Blac » Fri, 16 Apr 1999 04:00:00



Sorry if these are really basic questions but I am trying to do some
basic MS-SQL server things and don't quite know where to begin.....

If there is a website with the MS-SQL server structure, that would be
helpful too.

Oracle-isms that I cant find in SQL server...
...what are the SQL Server equivalents....
-----------------------------------------------------
1. System Tables and default synonyms....
        OBJECT, ALL_TABLES, etc.

2. A simple SQL client like SQLplus.

3. DESC in SQLplus.

4. Sequences (I think I've seen a 'serial' datatype for columns in SQL
server.)

Any direct answers would be helpful.  Any resource that helps an Oracle
user learn SQL server would be helpful.

Thanks

David Black
Sr.Application Developer
Nortel Networks

 
 
 

SQL Server equivalents to Oracle-isms.(newbie)

Post by Dan Guzma » Sat, 17 Apr 1999 04:00:00


Quote:>1. System Tables and default synonyms....
>        OBJECT, ALL_TABLES, etc.

EXEC sp_tables or ad-hoc queries against sysobjects

Quote:>2. A simple SQL client like SQLplus.

ISQL/W (GUI) or ISQL (command line)

Quote:>3. DESC in SQLplus.

EXEC sp_helptable

Quote:>4. Sequences (I think I've seen a 'serial' datatype for columns in SQL
>server.)

IDENTITY column

You may want to check out KB article "Migrating Oracle Applications to MS
SQL Server".  Even it you don't intend to migrate, it lists may of the
differences.

Hope this helps.


>Sorry if these are really basic questions but I am trying to do some
>basic MS-SQL server things and don't quite know where to begin.....

>If there is a website with the MS-SQL server structure, that would be
>helpful too.

>Oracle-isms that I cant find in SQL server...
>...what are the SQL Server equivalents....
>-----------------------------------------------------
>1. System Tables and default synonyms....
>        OBJECT, ALL_TABLES, etc.

>2. A simple SQL client like SQLplus.

>3. DESC in SQLplus.

>4. Sequences (I think I've seen a 'serial' datatype for columns in SQL
>server.)

>Any direct answers would be helpful.  Any resource that helps an Oracle
>user learn SQL server would be helpful.

>Thanks

>David Black
>Sr.Application Developer
>Nortel Networks



 
 
 

SQL Server equivalents to Oracle-isms.(newbie)

Post by Carolyn V. McKechni » Sat, 17 Apr 1999 04:00:00


1.  master database - the sys... tables, and in each database the sys...
tables
2.  isqlw or isql or the question mark button on Enterprise Manager
(query window)
3.  sp_help <name of table>
3.  No separate storage that you can access - it's a column with the

latest values in a bitmap for each table defined with the IDENTITY
property.

The master database contains system catalogs about the entire
installation.  Then each database contains its own set of system
catalogs for the contents of its own database.  A database in SQL Server
is close to the concept of a schema in Oracle.

There are a set of system stored procedures that help.  sp_help in any
database shows the contents and owners of the objects in that database.
sp_help <object_name> give details info on that object.  sp_helpsql
<command> give syntax/usage for the command or system stored procedure.
sp_helptext <procedure name> gives the actual text of the stored
procedure.

Master database contains all LOGINS in syslogins.  Then each database
contains a sysusers tables which lists the users and their corresponding
LOGIN in the master database.  sp_addlogin <LOGINID>, <PASSWORD>,
<DEFAULT DATABASE> creates a new login.  sp_adduser <USERNAME>,
<LOGINID>, <GROUP> within a database creates a user in that database.
sp_addgroup <GROUPNAME> within a database creates that group.

System functions that are useful:  object_name(id), db_name(),
getdate(), convert() - check out all the date functions too.

To monitor current activity:  sp_who, sp_who2, sp_lock, sp_lock2.

These are just the basics.  Hope it helps.  There is a system catalog
map that ships with the product.  Also you can look up everything in the
books on line which I find very helpful and still use after 7 years in
the field.  Books on line lists all system catalogs and the purpose of
each at the column level.  www.microsoft.com also has the Knowledge Base
on MSDN Online - lots of articles, bug reports, whitepapers.

Carolyn McKechnie


> Sorry if these are really basic questions but I am trying to do some
> basic MS-SQL server things and don't quite know where to begin.....

> If there is a website with the MS-SQL server structure, that would be
> helpful too.

> Oracle-isms that I cant find in SQL server...
> ...what are the SQL Server equivalents....
> -----------------------------------------------------
> 1. System Tables and default synonyms....
>         OBJECT, ALL_TABLES, etc.

> 2. A simple SQL client like SQLplus.

> 3. DESC in SQLplus.

> 4. Sequences (I think I've seen a 'serial' datatype for columns in SQL
> server.)

> Any direct answers would be helpful.  Any resource that helps an Oracle
> user learn SQL server would be helpful.

> Thanks

> David Black
> Sr.Application Developer
> Nortel Networks


 
 
 

1. Oracle equivalent to SQL-Server SQL

Hi,

As Oracle does not support INNER JOIN can some-one tell me the equivalent of
the
following:

SELECT DISTINCT
ACTORS.EQUITY_ID,ACTORS.FIRST_NAME,ACTORS.SURNAME,ACTORSVEHICLES.CHASSIS_NUM
,ACTORSVEHICLES.MAKE,
ACTORSVEHICLES.MODEL,ACTORSSALARY.PREVIOUS_SALARY FROM
(ACTORS INNER JOIN ACTORSVEHICLES ON ACTORS.EQUITY_ID =
ACTORSVEHICLES.EQUITY_ID)
INNER JOIN ACTORSSALARY ON ACTORSVEHICLES.EQUITY_ID = ACTORSSALARY.EQUITY_ID
WHERE ACTORS.EQUITY_ID LIKE '100%'
AND ACTORSVEHICLES.COLOR NOT LIKE 'S%' ORDER BY ACTORS.EQUITY_ID

Thanks in advance

2. Native Database Drivers

3. SQL Server equivalent of Oracle "DBLink"

4. Editing tables in SQL 6.5

5. What is Oracle RowID equivalent on SQL Server

6. 17544-NY-NEW YORK-Management-3GL-4GL-C-C++-Java-PowerBuilder-Visual Basic-SQL-OR

7. SQL Server equivalent of Oracle SEQUENCE object and nextval function

8. Auto - refresh at DB controls

9. equivalent of Oracle TRUNC function in Sql Server ??

10. Oracle package variable equivalent in SQL Server

11. what is the equivalent of Oracle Sequence in SQL server

12. Oracle select command equivalent in Sql server help

13. Oracle package to SQL Server 7 equivalent