January 7, 2011 at 11:55 am
Hi,
Is the SQL Server 2008 Schema same as Oracle Schema? If SQL Server 2008 schema is different from SQL 2008 schema, then in what way it exacltly differnt? Could you please provide me the comparision between them?
Thank you
January 7, 2011 at 12:18 pm
If you are talking about comparing a table in SQL Server to it's assumed equivient table by the same name on oracle, two important things are the way an IDENTITY works in SQL, compared to how Oracle does it (SEQUENCE and a trigger);
Data types are different; some types i don't know if they have a functional equivilent or not;
i guess you have to see it at least with an example, so here's a pretty complex table, with both the SQL DDL and a functional equivient of the table for Oracle...this is a best guess on the oracle equivilent, it's not meant to be a definitive equiv definition
CREATE TABLE [dbo].[WHATEVER] (
[WHATEVERID] INT IDENTITY(3,5) NOT NULL,
[DESCRIP] VARCHAR(30) NULL,
[MYVARBINARY] VARBINARY(1) NULL,
[MYBINARY] BINARY NULL
CONSTRAINT [DF__WHATEVER__MYBINA__15F1AEFB] DEFAULT ((42)),
[MYIMAGE] IMAGE NULL,
[MYVARCHAR] VARCHAR(1) NULL,
[MYMAXVARCHAR] VARCHAR(max) NULL,
[MYCHAR] CHAR(1) NULL
CONSTRAINT [DF__WHATEVER__MYCHAR__16E5D334] DEFAULT ('Y'),
[MYNVARCHAR] NVARCHAR(2) NULL,
[MYNCHAR] NCHAR(2) NULL,
[MYTEXT] TEXT NULL,
[MYNTEXT] NTEXT NULL,
[MYUNIQUEIDENTIFIER] UNIQUEIDENTIFIER NULL,
[MYROWVERSION] TIMESTAMP NOT NULL,
[MYBIT] BIT NULL,
[MYTINYINT] TINYINT NULL,
[MYSMALLINT] SMALLINT NULL,
[MYINT] INT NULL,
[MYBIGINT] BIGINT NULL,
[MYSMALLMONEY] SMALLMONEY NULL,
[MYMONEY] MONEY NULL,
[MYNUMERIC] NUMERIC(18,0) NULL,
[MYDECIMAL] DECIMAL(18,0) NOT NULL
CONSTRAINT [DF__WHATEVER__MYDECI__17D9F76D] DEFAULT ((0)),
[MYREAL] REAL(24) NULL,
[MYFLOAT] FLOAT NULL,
[MYSMALLDATETIME] SMALLDATETIME NULL,
[MYDATETIME] DATETIME NULL)
CREATE TABLE MFHDS.WHATEVER (
WHATEVERID NUMBER(10) NOT NULL,
DESCRIP VARCHAR2(30) NULL,
--MYVARBINARY VARBINARY NULL,
MYBINARY BLOB NULL,
MYIMAGE BLOB NULL,
MYVARCHAR VARCHAR2(1) NULL,
MYMAXVARCHAR VARCHAR2 (2000) NULL,
MYCHAR CHAR (1) DEFAULT ('Y'),
MYNVARCHAR NVARCHAR2(2) NULL,
MYNCHAR NCHAR (2) NULL,
MYTEXT CLOB NULL,
MYNTEXT CLOB NULL,
--MYUNIQUEIDENTIFIER UNIQUEIDENTIFIER NULL,
MYROWVERSION TIMESTAMP NOT NULL,
MYBIT NUMBER(10) NULL,
MYTINYINT NUMBER(10) NULL,
MYSMALLINT NUMBER(10) NULL,
MYINT NUMBER(10) NULL,
MYBIGINT NUMBER(20) NULL,
MYSMALLMONEY NUMBER(19,4) NULL,
MYMONEY NUMBER(19,4) NULL,
MYNUMERIC NUMBER (18,0) NULL,
MYDECIMAL NUMBER (18,0) DEFAULT (0),
MYREAL REAL NULL,
MYFLOAT FLOAT NULL,
MYSMALLDATETIME DATE NULL,
MYDATETIME DATE NULL);
--MFHDS.WHATEVER_SEQUENCE
CREATE SEQUENCE MFHDS.WHATEVER_SEQUENCE
START WITH 1
NOMAXVALUE
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
--MFHDS.WHATEVER_IDN
--
CREATE OR REPLACE TRIGGER MFHDS.WHATEVER_IDN BEFORE INSERT ON MFHDS.WHATEVER FOR EACH ROW
WHEN (
NEW.WHATEVERID IS NULL
)
DECLARE DUMMY NUMBER;
BEGIN
SELECT WHATEVER_SEQUENCE.NEXTVAL INTO DUMMY FROM DUAL;
:NEW.WHATEVERID:= DUMMY;
END;
/
Lowell
January 7, 2011 at 2:56 pm
Thank you. I'm looking only the SCHEMA difference.
Here is what I wanted to do:
We have a Production database Mydb on SQL Server instance1 which refreshed (backup & restore) to other SQL Server instance2. Users query that database in instance2. Now I want to create another database Mydb2 on instance2 and create schemas and let give the users to import (select) the data from Mydb1 (from instance2) to their schemas in Mydb2.
Is that possible?
Thanks
January 7, 2011 at 3:14 pm
What's called a database in Oracle is an instance in SQL Server.
What's called a schema in Oracle is a database in SQL Server, and SQL Server may contain multiple databases.
SQL Server can contain multiple schemas, and permissions can be granted to a user or role at the schema level, if needed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply