Oracle conversion reference to SQL (transfer)

xiaoxiao2021-03-06 117

SQL language support

This section outlines the same points and different points between Transact-SQL and PL / SQL language syntax, and give a conversion policy.

To migrate Oracle DML statements and PL / SQL programs to SQL Server, follow these steps:

Verifying the syntax of all SELECT, INSERT, UPDATE, and DELETE statements. Take any necessary modifications.

Change all external connections to SQL-92 standard external coupling grammar.

Use the corresponding SQL Server function to replace the Oracle function.

Check all comparison operators.

Use the " " string arithmetic operator instead of "||" string co-transport operator.

Use the Transact-SQL program instead of the PL / SQL program.

Change all PL / SQL cursors to non-visual SELECT statements or TRANSACT-SQL cursors.

Use the Transact-SQL process instead of the PL / SQL process, functions, and packages.

Convert the PL / SQL trigger to the Transact-SQL trigger.

Use the Set ShowPlan statement to optimize query performance.

SELECT statement

The SELECT statement syntax used by Oracle and Microsoft SQL Server is similar.

Oracle

Microsoft SQL Server

SELECT [/ * optimizer_hints * /] [ALL | DISTINCT] select_list [FROM {table_name | view_name | select_statement}] [WHERE clause] [GROUP BY group_by_expression] [HAVING search_condition] [START WITH U CONNECT BY] [{

Union

| Union All | InterSect | minus} SELECT U] [ORDER BY CLAUSE] [for Update]

SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY [ALL] group_by_expression [, Un] [WITH {CUBE | ROLLUP}] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC]] In addition: UNION Operator Compute ClauseFor Browse ClauseOption Clause

SQL Server does not support Oracle-specific overhead optimizing programs, it must be deleted. It is recommended to use the Optimizer based on the overhead of SQL Server. For more information, see "SQL Statement Optimization" behind this chapter.

SQL Server does not support the Start WITHUCONNECT BY clause of Oracle. In SQL Server, you can create a stored procedure that completes the same task replaces it.

SQL Server does not support Oracle's INTERSECT and MINUS collection operators. The same results can be implemented using the SQL Server EXISTS and NOT EXISTS clauses.

In the example below, use the INTERSECT operator to find the code and name of all the courses registered by the student. Note that the Exists operator is instead of the INTERSECT operator. The returned data is the same.

Oracle

Microsoft SQL Server

SELECT CCODE, CNAMEFROM DEPT_ADMIN.CLASSINTERSECTSELECT C.CCODE, C.CNAMEFROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS CWHERE C.CCODE = G.CCODESELECT CCODE, CNAMEFROM DEPT_ADMIN.CLASS CWHERE EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE GWHERE C. Ccode = g.ccode)

In this case, use the Minus operator to find courses without any student registration.

Oracle

Microsoft SQL Server

Select ccode, cnamefrom dept_admin.classminusselect c.ccode, c.cnameFrom student_admin.grade g, dept_admin.class cwhere c.ccode = g.ccode

Select ccode, cnamefrom dept_admin.classcwhere not exists (SELECT 'x' from student_admin.grade gwhere c.ccode = g.ccode)

INSERT statement

INSERT statement syntax used by Oracle and Microsoft SQL Server is similar.

Oracle

Microsoft SQL Server

INSERT INTO {Table_name | View_name | SELECT_STATEMENT} [(Column_List)] {VALUES_LIST | SELECT_STATEMENT}

INSERT [INTO] {table_name [[AS] table_alias] WITH ( [Un]) | view_name [[AS] table_alias] | rowset_function_limited} {[(column_list)] {VALUES ({DEFAULT | NULL | expression} [, UN]) | Derived_Table | Execute_Statement}} | DEFAULT VALUES

Transact-SQL language supports insertion of tables and views, but does not support INSERT operations for the SELECT statement. If the Oracle application code performs insertion operations for the SELECT statement, you must modify it.

Oracle

Microsoft SQL Server

INSERT INTO (SELECT SSN, CCODE, GRADE "VALUES ('111111111', '1111', NULL)

INSERT INTO GRADE (SSN, CCODE, GRADE) VALUES ('111111111', '1111', null)

The Transact-SQL VALUES_LIST parameter provides SQL-92 standard keyword Default, but Oracle does not support. This keyword is specified, the default value of the column is used when performing insert operation. Insert NULL if the default value of the specified column does not exist. If the column does not allow NULL, an error message is returned. If the column data type is defined as TimeStamp, insert the next ordered value.

The identifier column cannot use the default keyword. To generate the next serial number, the column with the Identity property cannot be listed in Column_List or VALUES_CLASE. You don't need to use the default keyword to get the default value of the column. As in Oracle, if the column is not referenced in column_list, and it has a default value, the default value is stored in the column. This is the most compatible method for migration. A useful Transact_SQL option (Execute Procedure_Name) is performed, performs a process and outputs its results to the target table or view. Oracle does not allow this.

UPDATE statement

Because Transact SQL supports most syntax used by the Oracle Update command, it only requires very few modifications.

Oracle

Microsoft SQL Server

Update {Table_name | View_name | SELECT_STATEMENT} set [Column_name (s) = {constant_view | express | select_statement | colorn_list | variable_list] {where_statement}

UPDATE {table_name [[AS] table_alias] WITH ( [Un]) view_name [[AS] table_alias] | rowset_function_limited} SET {column_name = {expression | DEFAULT | NULL} | @variable = expression | @variable = column = Expression} [, un] {{{[from {} [, un]] [where ]} | [where current of {{[global] cursor_name}]} [option ( [, Un])]

The Transact-SQL UPDATE statement does not support update operations for the SELECT statement. If the Oracle application code is updated to the SELECT statement, you can convert the SELECT statement into a view and then use the view name in the SQL Server Update statement. See the example in the "Insert Statement" in the previous "INSERT statement".

The Oracle Update command can only use program variables in a PL / SQL block. To use variables, Transact-SQL language does not need to use blocks.

Oracle

Microsoft SQL Server

DECLAREVAR1 Number (10, 2); Beginvar1: = 2500; Update Student_Admin.studentset Tuition_total = Var1; End;

DECLARE @ var1 numeric (10, 2) SELECT @ var1 = 2500UPDATE STUTENT_ADMIN.STUDENTSET TUition_total = @ var1

In SQL Server, the Default keyword can be used to set a column to its default value. But you can't use the Oracle Update command to set a column as the default value.

Transact-SQL and Oracle SQL support use subqueries in the UPDATE statement. However, the Transact-SQL FROM clause can be used to create a join UPDATE. This feature makes Update syntax readability, and in some cases improve performance. Oracle

Microsoft SQL Server

Update student_admin.student sset tuition_total = 1500where ssn in (select ssn from grade gwhere g.ssn = s.ssnand g.ccode = '1234')

Subquery: UPDATE STUDENT_ADMIN.STUDENT SSET TUITION_TOTAL = 1500WHERE SSN IN (SELECT SSN FROM GRADE GWHERE G.SSN = S.SSNAND G.CCODE = '1234') FROM clause: UPDATE STUDENT_ADMIN.STUDENT SSET TUITION_TOTAL = 1500FROM GRADE GWHERE S.SSN = G.ssnand g.ccode = '1234'

DELETE statement

In most cases, you don't need to modify the DELETE statement. If you want to perform a delete action to the SELECT statement in Oracle, you must modify the SQL Server syntax because Transact-SQL does not support this feature.

Transact-SQL supports using subqueries in the WHERE clause, and using join in the FROM clause. The latter can produce a more efficient statement. See the example in the "Update Statement" in front.

Oracle

Microsoft SQL Server

Delete [from] {TABLE_NAME | View_name | Select_Statement} [Where Clause]

DELETE [FROM] {table_name [[AS] table_alias] WITH ( [Un]) | view_name [[AS] table_alias] | rowset_function_limited} [FROM {} [, Un]] [WHERE { | {[Current of {{[Global] Cursor_name} `]}] [option ( [, un])

TRUNCATE TABLE statement

TRUNCATE TABLE statement syntax used by Oracle and Microsoft SQL Server is similar. TRUNCATE TABLE is used to remove all rows from the table and cannot roll back. Table structure and all its indexes continue to exist. The delete trigger is not executed. If the table is referenced by a Foreign Key constraint, it cannot be truncated.

Oracle

Microsoft SQL Server

Truncate Table Table_name [{Drop | Reuse} Storage]

Truncate Table Table_name

In SQL Server, this statement can only be performed by the owner of the table. In Oracle, this command can be executed if the owner of the table or with the Delete Table system permission.

Oracle Truncate Table Commands can have a selection release of the storage space occupied by the table. The SQL Server TRUNCATE TABLE statement always retracts the space occupied by the table data and its related indexes. Processing of data in identifier columns and time stamp columns

Oracle sequences are database objects that are not directly related to any given table or column. The relationship between the columns and sequences is implemented in the application, ie, the sequence value is assigned to the column by programming. Therefore, any rules are not implemented when Oracle uses sequences. However, in the Microsoft SQL Server ID column, the value cannot be updated and the default keyword cannot be used.

By default, data cannot be inserted directly into the identifier column. The identifier column automatically generates a unique serial number for each new line inserted in the table. You can override this default setting using the following SET statement:

Set Identity_Insert Table_name on

Set Identuty_Insert to ON, the user can insert any value to the new row's identifier column. To prevent an entry with repetition numbers, you must create a unique index for the column. The purpose of this statement is to allow users to recreate a value to the unintentional deletion. The @@ identity function can be used to get the previous identity value.

The TRUNCATE TABLE statement resets the identifier column to its starting seed value. If you do not want to reset the column identification value, do not use the TRUNCATE TABLE statement, and use the DELETE statement that does not take the WHERE clause. It must be assessed to the impact of Oracle migration, because Oracle Sequence is not reset after the Truncate table command.

When processing the TimeSTAMP column, you can only perform insertion and deletion. If you want to update a TimeStAMP column, you will receive the following error message:

MSG 272, Level 16, State 1 CAN't Update A TimeStamp Column.

Lock request

Oracle uses the for Update clause to lock the rows specified in the select command. You don't need to use peer clauses in Microsoft SQL Server because it is the default behavior.

Listant and compute clause

The SQL Server Compute clause is used to generate a running function (SUM, AVG, MIN, MAX, and COUNT), which appears as an additional line in the query results. It allows you to view detailed and summary information lines of a set of results. The summary value of the subgroup can be calculated, and a plurality of synthesis functions of the same group can be calculated.

The Oracle Select command syntax does not support the compute clause. However, the SQL Server Compute clause is similar to the COMPUTE command in the Oracle SQL * Plus query tool.

Join clause

Microsoft SQL Server 7.0 allows up to 256 tables in a coupling subsequent, including temporary tables and permanent tables. In Oracle, there is no connection limit.

When using an external connection in Oracle, the external coupling operator ( ) is usually placed next to the coupling sublining (foreign key). ( ) Identify columns with fewer unique values. The situation is usually the case, unless the foreign key allows null value, in this case, ( ) is placed on the parent key or UNIQUE constraint. ( ) Cannot be placed on both sides of the equal sign (=).

In SQL Server, * = and = * external join operators can be used. * Used to identify columns with more unique values. If the child (foreign key) does not allow null values, * is placed on the side of the LMARY key or UNIQUE constraint. * The location is completely opposite Oracle. * Can not be placed on both sides of the equal sign (=). * = And = * is considered an old-fashioned operator. SQL Server also supports the SQL-92 standard connection operator listed below. This syntax is recommended. SQL-92 standard syntax features are more powerful and less than * operators.

Join operation

Description

Cross Join

This is the cross product of two tables. It is the same as the rows returned by the WHERE clauses in the old coupled. In Oracle, this type is connected to the Cartesian connection.

Inner

This connection is specified, all internal rows are returned. Discard any mismatched rows. This is the same as the standard Oracle table.

LEFT [OUTER]

This type of join specified, all the external lines of all left tables are returned, even if the matching column is not found, the same. This is similar to the operation of Oracle external connection ( ).

Right [Outer]

This type of join specified, all the external lines of all the tops should return, even if the matching column is not found, it is also true. This is similar to the operation of Oracle external connection ( ).

Full [Outer]

If a line in the two tables does not meet the selection criteria, specify this line to the result set, and the output of the other table is set to NULL. This is the same as the Oracle external coupling operator on the "=" number (COL1 ( ) = col2 ( )) is the same, but the latter is not allowed in Oracle.

The following code example returns a list of curriculums registered by all students. External join definitions between students (Student) and grade tables, transcript allows all students to appear above, even those students who do not register any courses. External join also add to the curriculum to return to the course name. If the external join does not add the course table, it will not return students who do not register any courses because their course code (ccode) is empty.

Oracle

Microsoft SQL Server

Select S.ssn As SSN, FNAME, LNAMEFROM Student_admin.student S, Dept_admin.class C, Student_admin.grade gwhere s.ssn = g.ssn ( ) and g.ccode = c.ccode ( )

Select S.ssn As SSN, FNAME, LNAMEFROM Student_admin.grade Gright Outer JoinStudent_admin.student

G.ssn = s.ssnleft Outer JoIndept_admin.class con g.ccode = c.ccode

Use the SELECT statement as a table name

Both Microsoft SQL Server and Oracle support to use the SELECT statement as the source of the table as the source of the table. SQL Server requires an alias; for Oracle, the use of alias is optional.

Oracle

Microsoft SQL Server

Select SSN, LNAME, FNAME, TUition_PAID, SUM_PAIDFROM Student_admin.student, (SELECT SUM (Tuition_PAID) SUM_PAID from Student_admin.student

SELECT SSN, LNAME, FNAME, TUition_PAID, SUM_PAIDFROM Student_admin.student, (Select Sum (Tuition_PAID) SUM_PAID From Student_admin.student Sum_Student Read and Modify BLOB

Microsoft SQL Server uses the Text and Image columns to implement binary large objects (BLOB). Oracle implements blob using long and long RAW columns. In Oracle, the select command can query the value in the long and long RAW columns.

In SQL Server, you can read data in the Text and Image columns using standard Transact-SQL statements or dedicated ReadText statements. The READTEXT statement allows the partial segment of the Text or Image column to be read. Oracle does not provide a peer-to-peer statement that processes long and long Raw.

The readText statement uses text_point, which can be obtained with the TextPtr function. The TextPtr function returns a pointer, pointing to the text or image column in the specified row, or if it returns more than a row, it points to the final line of the query returned. Because the TextPtr function returns a 16-byte binary string, it is best to declare a local variable to store the text pointer and then use the variable by Readtext.

The READTEXT statement specifies the number of bytes to return. The value in the @@ textsize function is the limit of the characters or bytes to return. If it is less than the size specified by the readText, the readtext statement is specified.

You can use a Set statement with a TextSize parameter to specify the size of the text data returned by the SELECT statement (number of bytes). If you specify TextSize to 0, the size is reset to the default value (4 KB). Set the TextSIZE parameter to affect the @@ TextSIZE function. When the SQL_MAX_LENGTH statement option changes, the SQL Server ODBC driver automatically sets the TextSIZE parameter.

In Oracle, the Update and Insert commands are used to change the values in the long and long Raw columns. In SQL Server, you can use standard Update and Insert statements, you can also use UpdateText and WriteText statements. UpdateText and WriteText are allowed to have no logged options, and UpdateText allows the Text or Image column to be partially updated.

The UPDATEXT statement can be used to replace existing data, delete existing data or insert new data. The newly inserted data can be constant, table name, column name, or text pointer.

The WRITETEXT statement fully covers any existing data in the columns they affected. Use WRITETEXT to replace text data; use UpdateText to modify text data. The UpdateText statement is more flexible because it only changes a part of the text or image value, not all of the changes.

For more information, see SQL Server Books Online.

The table in this section gives the relationship between Oracle and SQL Server scalar values and total functions. Although the name looks the same, it is important to note that the number and type of function parameters are different, this is very important. In addition, in this list, no functions provided by Microsoft SQL Server are given, because this chapter is limited to the story, how to easily implement the migration from existing Oracle applications. Oracle does not support functions: DegRees, circumferential rate (PI), and random number (RAND). Digital / mathematical function

Below is a digital / mathematical function supported by Oracle and its Microsoft SQL Server peer function.

function

Oracle

Microsoft SQL Server

Absolute value

ABS

Anti-rest

ACOS

Dispelling

Asin

N, anyway

Atan

M / N

Atan2

ATN2

> = Minimum integer

CEIL

Ceiling

Cosine

COS

Hyperbolic

Cosh

Cot

Index value

EXP

<= Maximum integer

Floor

Natural logarithm

Log

Logarithm

Log (n)

Temporary

Logarithm at 10

Log (10)

Log10

Modulus (remainder)

MOD

Use modulo (%) Operator

power

Power

random number

Temporary

Rand

included

Round

Number symbol

Sign

Sinusoidal

Sin

Double sine

Sinh

Temporary

Square root

SQRT

Tan

Hyperbolic

Tanh

Temporary

Truncation

Trunc

Temporary

Maximum number in the list

Greatest

Temporary

Minimum number in the list

Least

Temporary

Convert to numbers if NULL

NVL

ISNULL

Character function

Below is the character function supported by Oracle and its Microsoft SQL Server peer function.

function

Oracle

Microsoft SQL Server

Convert characters to ASCII

ASCII

String series

Concat

(Expression expression)

Convert ASCII into characters

CHR

Charr

Returns the start character (from left) in the string

Instr

Charindex

Convert characters to lowercase

Lower

Convert characters to uppercase

Upper

Fill characters on the left side of the string

LPAD

Temporary

Delete front guide

Ltrim

Delete tail space

RTRIM

Start point of the pattern in string

Instr

Patindex

Multiple repeat strings

RPAD

Replicate

String voice representation

Soundex

Repeat space string

RPAD

Space

Character data from digital data conversion

TO_CHAR

Str

Substring

Substr

Substring

Character replacement

Replace

Stuff

The first letter of each word in the string

Initcap

Temporary

String conversion

Translate

Temporary

String length

Length

DateLength or Len

Maximum string in the list

Greatest

Temporary

Minimum string in the list

Least

Temporary

If you are null, the string is converted.

NVL

ISNULL

Date function

Below is the date function supported by Oracle and its Microsoft SQL Server peer function.

function

Oracle

Microsoft SQL Server

Date plus

(Date Column /- value) or add_monthsdateadd

Interval during the day

(Date Column /-) or Months_Between

Datediff

Current date and time

Sysdate

Getdate ()

The last day of the month

Last_day

Temporary

Time zone conversion

New_time

Temporary

The first working day after this date

NEXT_DAY

Temporary

Date string representation

TO_CHAR

Datename

Date integer representation

TO_NUMBER (TO_CHAR))

DatePart

Date rounding

Round

Convert

Date cut

Trunc

Convert

String conversion to date

TO_DATE

Convert

If null, conversion date

NVL

ISNULL

Conversion function

Below is the conversion function supported by Oracle and its Microsoft SQL Server peer function.

function

Oracle

Microsoft SQL Server

Numbers to characters

TO_CHAR

Convert

Character to numbers

TO_NUMBER

Convert

Date to characters

TO_CHAR

Convert

Character to date

TO_DATE

Convert

Hexadecimal to binary

HEX_TO_RAW

Convert

Binary to hex

RAW_TO_HEX

Convert

Other row-level functions

Below are other row-level functions supported by Oracle and their Microsoft SQL Server peer function.

function

Oracle

Microsoft SQL Server

Return the first non-empty expression

Decode

Coalesce

Current sequence value

CURRVAL

Temporary

Next sequence value

NextVal

Temporary

If the expression 1 = expression 2, return empty

Decode

Nullif

User login ID number

UID

SUSER_ID

User login name

User

SUSER_NAME

User's database ID number

UID

User_id

User's database name

User

User_name

Current user

Current_user

User environment (audit record)

Userenv

Temporary

CONNECT BY clause level

Level

Temporary

Total function

Here is the total function of Oracle support and its Microsoft SQL Server peer function.

function

Oracle

Microsoft SQL Server

average value

AVG

count

Count

Maximum

Max

Minimum

MIN

standard deviation

STDDEV

STDEV or stdevp

Summary

Sum

variance

Variance

VAR or VARP

Condition test

Oracle Decode statements and Microsoft SQL Server Case expressions perform condition tests. When the value in Test_Value meets any of the following expressions, the associated value is returned. Returns Default_Value if not in line. If DEFAULT_VALUE is not specified, Decode and CASE return NULL if you do not specify any expression. The following table gives examples of syntax and a conversion DECODE command.

Oracle

Microsoft SQL Server

Decode (Test_Value, Expression1, Value1 [[[, Expression2, Value2] [u]] [, default_value]) Create View Student_admin.student_gpa (SSN, GPA) AS SELECT SSN, ROUND (AVG (Decode (Grade, 'A', 4 , 'A ', 4.3, 'A-', 3.7, 'B', 3, 'B ', 3.3, 'B-', 2.7, 'C', 2, 'C ', 2.3, 'C-', 1.7, 'D', 1, 'D ', 1.3, 'D-', 0.7,0)), 2) FROM STUDENT_ADMIN.GRADEGROUP BY SSNCASE input_expressionWHEN when_expression THEN result_expression [[WHEN when_expression THEN result_expression] [...]] [ELSE else_result_expression] ENDCREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND (AVG (CASE gradeWHEN 'A' THEN 4WHEN 'A ' THEN 4.3WHEN 'A-' THEN 3.7WHEN 'B' THEN 3WHEN 'B ' THEN 3.3WHEN 'B-' THEN 2.7WHEN 'C' THEN 'C-' THEN 'THEN' D 'THEN' DHEN 'D ' TEN 1.3When 'D-' Ten 0.7ELSE 0nd, 2 ) From student_admin.gradegroup by SSN

The CASE expression can support Boolean test using the SELECT statement, which is not allowed by the decode command. For more information on CASE expressions, see SQL Server Books Online.

Convert value into different data types

Microsoft SQL Server Convert and CAST functions are multi-purpose conversion functions. They provide similar features that convert a data type expression to another data type and support a variety of special data formats:

CAST (Expression As Data_Type)

Convert (Data Type [(Length)], Expression [, Style])

CAST is a SQL-92 standard function. These functions perform the same operation as Oracle to_CHAR, TO_NUMBER, TO_DATE, HEXTORAW, and RawToHex functions.

The data type refers to any system data type that the expression is converted. You cannot use user-defined data types. The Length parameter is optional, which is used with CHAR, VARCHAR, BINARY, and VARBINARY data types. The maximum length of allowable is 8000.

Conversion

Oracle

Microsoft SQL Server

Character to numbers

TO_NUMBER ('10 ')

Convert (Numeric, '10')

Numbers to characters

TO_CHAR (10)

Convert (Char, 10)

Character to date

TO_DATE ('04-Jul-97 ') TO_DATE ('04-Jul-1997', 'DD-MON-YYYY') TO_DATE ('July 4, 1997', 'Month DD, YYYY') Convert (DateTime, '04 -JUL-97 ') Convert (DateTime, '04-Jul-1997') Convert (DateTime, 'July 4, 1997')

Date to characters

TO_CHAR (SYSDATE) TO_CHAR (SYSDATE, 'DD MON YYYY') TO_CHAR (Sysdate, 'MM / DD / YYYY')

Convert (char, getdate ()) Convert (Char, Getdate (), 106) Convert (char, getdate (), 101)

Hexadecimal to binary

HEXTORAW ('1f')

Convert (binary, '1f')

Binary to hex

Rawtohex (binary_column)

Convert (char, binary_column)

Note how the string is converted to a date. In Oracle, the default date format model is "DD-MON-YY". If you use any other format, you must provide the corresponding date format model. The CONVERT function automatically converts the standard date format without a formatting model.

When the date is converted into a string, the Convert function outputs the default output to "DD MON YYYY HH: MM: SS: MMM (24h)". A digital type encoding is used to set the format of the other date format model output. See SQL Server Books Online for more information on Convert functions.

The following table gives the default output of the Microsoft SQL Server date.

Do not bring a century

Century

standard

Output

0 or 100 (*)

default

MON DD YYYY HH: MIAM (or PM)

101

United States

MM / DD / YY

102

ANSI

Yy.mm.dd

103

United Kingdom / France

DD / mm / yy

104

Germany

Dd.mm.yy

105

Italy

DD-MM-YY

106

DD MON YY

Seduce

107

MON DD, YY

108

HH: mm: ss

9 or 109 (*)

Default millisecond

MON DD YYYY HH: MI: SS: MMM (AM or PM)

110

United States

MM-DD-YY

111

Japan

YY / MM / DD

112

ISO

YYMMDD

13 or 113 (*)

Europe default

DD MON YYYY HH: MM: SS: MMM (24h)

114

HH: MI: SS: MMM (24h)

User-defined functions

The Oracle PL / SQL function can be used in the Oracle SQL statement. In Microsoft SQL Server, this feature is usually implemented in other ways.

In the following example, the Oracle user-defined function get_sum_major is used to obtain the sum of the tuition fees paid by the professional (MAJOR). In SQL Server, you can use the query as a table to replace this function.

Oracle

Microsoft SQL Server

SELECT SSN, FNAME, LNAME, - TUition_Paid, Tuition_PAID / GET_SUM_MAJOR (MAJOR) AS Percent_majorfrom student_admin.student

SELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID / SUM_MAJOR AS PERCENT_MAJORFROM STUDENT_ADMIN.STUDENT, (SELECT MAJOR, SUM (TUITION_PAID) SUM_MAJORFROM STUDENT_ADMIN.STUDENTGROUP BY MAJOR) SUM_STUDENTWHERE STUDENT.MAJOR = SUM_STUDENT.MAJORCREATE OR REPLACE FUNCTION GET_SUM_MAJOR (INMAJOR VARCHAR2) RETURN Numberas SUM_PAID NUMBER; Beginselect Sum (Tuition_PAID) INTO SUM_PAIDFROM Student_admin.studentwhere Major = INMAJOR; RETURN (SUM_PAID); End Get_Sum_major;

No Create function syntax; use the CREATE Procedure syntax.

Oracle and Microsoft SQL Server compare operators are almost the same.

Operator

Oracle

Microsoft SQL Server

equal

(=)

more than the

(>)

Be less than

(<)

greater than or equal to

(> =)

less than or equal to

(<=)

not equal to

(! =, <>, ^ =)

Not greater than, not less than

Temporary

!>,! <

In any member of the collection

Not in any member of the collection

NOT IN

Nothing in the collection

Any, Some

Reference all values in the collection

! = All, <> all, all, <= all,> = all,! = Some, <> some, some, <= some,> = Some

Similar to modes

Not similar to

Not Like

The value between X and Y

BetWeen X and Y

No value between the two _

NOT BETWEEN

Value

Exists

Value does not exist

NOT EXISTS

Value {不不} null

Is Null, IS Not NULL

the same. You can also use = null,! = Null for backward compatibility (not recommended).

Pattern matching

SQL Server Like keyword provides some Oracle not supported, useful wildcard search options. In addition to supporting two RDBMS universal% and _ wildcards, SQL Server also supports [] and [^] characters.

[] Characters are used to search for a single character in a given range. For example, if you search for characters from A to F in a single-character location, you can be specified by Like '[A-F]' or LIKE '[Abcdef]'. This table gives these additional wildcards. Oracle

Microsoft SQL Server

Select * from student_admin.studentwhere Lname Like 'a%' or lname like 'b%' or lname like 'c%'

Select * from student_admin.studentwhere Lname Like '[ABC]%'

[^] Collection of wildcards is used to specify characters that are not within a given range. For example, if you accept any characters other than A to F, use Like '[^ a - f]' or Like '[^ Abcdef]'.

For more information on the LIKE keyword, see SQL Server Books Online.

NULL usage comparison

Although Microsoft SQL Server has traditionally supports SQL-92 standards and other non-standard NULL behaviors, it also supports the usage of NULL in Oracle.

To perform a distributed query, SET ANSI_NULLS should be set to ON.

When the SQL Server ODBC driver is connected to the OLE DB provider of SQL Server, set ANSI_NULLS is automatically set to ON. This setting can be set in the OLE DB connection attribute set in the application before the ODBC data source, the ODBC connection property setting, or before connecting SQL Server. Connections from the DB-Library application, SET ANSI_NULLS defaults to OFF.

Set ANSI_NULLS is enabled when SET ANSI_DEFAILTS is ON.

For more information on using null, see SQL Server Books Online.

String series

Oracle will operate two pipe symbols (||) as a string co-transport operator, while SQL Server uses a plus sign ( ). This difference only needs to make small modifications to the application code.

Oracle

Microsoft SQL Server

SELECT FNAME || '' || Lname As Namefrom Student_admin.student

SELECT FNAME '' LNAME As NameFrom Student_admin.student

Control the streaming language controls the execution of the SQL statement, the statement block, and the stored procedure. PL / SQL and Transact SQL provide many identical structures, but there are some differences in grammar.

Keyword

The following is a keyword supported by each RDBMS.

Statement

Oracle PL / SQL

Microsoft SQL ServerTransact-SQL

Declaration variable

Declare

Statement block

Begin ... End;

Begin ... End

Conditional treatment

Ifuthen, Elsifuthen, Elsendif;

IFU [Beginuend] Else [Beginuend] else if CASE EXPRESSION

Unconditional exit

Return

Unconditional exits to the statement followed by the current block

Exit

Break

Restart a while loop

Temporary

Continue

Waiting for the specified interval

Temporary lack (dbms_lock.sleep)

Waitfor

Cyclic control

While Loopuend Loop; Labelugoto Label; Forum Loop; Loopuend

Loop

;

While Beginu Endlabelugoto Label

comments

/ * U * /, -

Print output

RDBMS_OUTPUT.PUT_LINE

Program error

RAISE_APPLICATION_ERROR

Raiserror

execute program

EXECUTE

Statement terminator

Section (;)

Temporary

Declaration variable

Transact-SQL and PL / SQL variables are created using the Declare keyword. The Transact-SQL variable is identified by @, and like the PL / SQL variable, the variable is initialized as null value when the first creation is created.

Oracle

Microsoft SQL Server

Declarevssn Char (9); vfname varchar2 (12); VLNAME VARCHAR2 (20); VBIRTH_DATE DATE; VLOAN_AMOUNT NUMBER (12, 2);

Declare @ vssn char (9), @ vfname varchar2 (12), @ Vlname varcha2 (20), @ vbpter_date datetime, @ VLOAN_AMOUNT NUMERIC (12, 2)

Transact-SQL does not support% type and% rowType variable data type definition. In the DECLARE command, the Transact-SQL variable cannot be initialized. Oracle NOT NULL and Constant keywords cannot be used in the Microsoft SQL Server data type definition.

Like Oracle Long and long RAW data types, Text and Image data types cannot be used for variable declarations. In addition, the records and table definitions of the PL / SQL type are not supported.

Variable assignment

Oracle and Microsoft SQL Server provide the following methods to assign a local variable.

Oracle

Microsoft SQL Server

Assignment operator (: =)

Set @local_variable = value

The Select ... INTO syntax used to select the column value from a row.

SELECT @local_variable = expression [fromu] is used to assign a value to a literal value, involving a value of other local variables or column values in a row.

Fetchuinto syntax

Here are some syntax examples.

Oracle

Microsoft SQL Server

Declare vssn char (9); VFNAME VARCHAR2 (12); VLNAME VARCHAR2 (20); Beginvssn: = '123448887'; SELECT FNAME, LNAME INTO VFNAME, VLNAME from Students Where SSN = Vssn; END;

Declare @vssn char (9), @ vfname varchar (12), @ VLNAME VARCHAR (20) set @vssn = '12355887'select @ vfname = fname, @ VLNAME = Lname from students where ssn = @vssn

Statement block

Oracle PL / SQL and Microsoft SQL Server Transact-SQL supports using Beginuend terms to specify blocks. Transact-SQL does not require a statement in the DECLARE statement. In Microsoft SQL Server, if the IF statement and the While loop do more than one statement, you need to use the Beginuend statement block. Oracle

Microsoft SQL Server

DECLAREDECLARE VARIABLES ... BEGIN - THIS IS REQUIRED SYNTAXPROGRAM_STATEMENTS ... IF ... THENSTATEMENT1; STATEMENT2; STATEMENTN; END IF; WHILE ... LOOPSTATEMENT1; STATEMENT2; STATEMENTN; END LOOP; END; - THIS IS REQUIRED SYNTAX

DECLAREDECLARE VARIABLES ... BEGIN - this is optional syntaxprogram_statements ... if ... Begin Statement1Statement2Statementnend While ... BeginStatement1Statement2StateNendnend end - this is required Syntax

Conditional treatment

The Microsoft SQL Server Transact-SQL condition statement contains the IF and ELSE statements instead of the ELSIF statement in Oracle PL / SQL. You can nest multiple if statements to achieve the same effect. For a large number of conditions, the CASE expression is easier to read.

Oracle

Microsoft SQL Server

DECLAREVDEGREE_PROGRAM CHAR (1); VDEGREE_PROGRAM_NAME VARCHAR2 (20); BEGINVDEGREE_PROGRAM: = 'U'; IF VDEGREE_PROGRAM = 'U' THENVDEGREE_PROGRAM_NAME: = 'Undergraduate'; ELSIF VDEGREE_PROGRAM = 'M' THEN VDEGREE_PROGRAM_NAME: = 'Masters'; ELSIF VDEGREE_PROGRAM =' P 'Ten vdegree_program_name: =' phd '; else vdegree_program_name: =' unknown '; end if; end;

DECLARE @ VDEGREE_PROGRAM CHAR (1), @ VDEGREE_PROGRAM_NAME VARCHAR (20) SELECT @VDEGREE_PROGRAM = 'U'SELECT @VDEGREE_PROGRAM_NAME = CASE @VDEGREE_PROGRAMWHEN' U 'THEN' Undergraduate'WHEN 'M' THEN 'Masters'WHEN' P 'THEN' PhD '.Else' unknown'end

Duplicate statement execution (loop)

Oracle PL / SQL provides unconditional loop and for loop. Transact-SQL provides the WHILE loop and goto statement to achieve the purpose of the loop.

While boolean_expression {sql_state | statement_block} [BREAK] [Continue] Test a Boolean expression for the repeated execution of one or more statements. As long as the given expression value is true, the statement will be repeated. If you want to perform multiple statements, they must be placed in a beginuend block.

Oracle

Microsoft SQL Server

DecLareCounter: = 0WHILE (Counter <5) loopCounter: = Counter 1; End

Loop

;

Declare @ counter NumericSelect @ counter = 1WHILE (@counter <5) Beginselect @counter = @counter 1nd

You can use the Break and Continue keywords, the execution of the internal control statement of the loop. BREAK keyword leads to unconditional exits from the While loop, the Continue keyword makes the while runs over the following statement and restarts the loop. Break keywords and Oracle PL / SQL EXIT keywords. Oracle does not have Continue's peer keywords.

GOTO statement

Oracle and Microsoft SQL Server have a goto statement, but the syntax is different. When you encounter a goto statement, Transact-SQL batch execution will jump to the label. The statement between the goto statement and the label is not executed.

Oracle

Microsoft SQL Server

Goto label; << label name here >>

Goto label

PRINT statement

The TRANSACT-SQL PRINT statement is the same as the operation performed by the PL / SQL RDBMS_OUTPUT.PUT_LINE process. It is used to print the user specified by the user.

The message limit of the Print statement is 8,000 characters. Variables defined using a char or VARCHAR data type can be embedded in the print statement. If any other data type is used, you must use the CONVERT or CAST function. Local variables, global variables, and text can be printed. The text can be enclosed in single quotes and double quotes.

Return from the stored procedure

Microsoft SQL Server and Oracle have RETURN statements. Using the RETURN statement, the program can exit from the query or process unconditional. Return is an immediate statement that can be performed and can be used at any time to exit from the process, batch or statement. None of the statements behind Return.

Oracle

Microsoft SQL Server

Return Expression:

Return [integer_expression]

Program error

The Transact-SQL raiserror statement returns a user-defined error message and sets a system flag to record an error has occurred. It is similar to the function of the PL / SQL RAISE_APPLICATION_ERROR exception error handler.

The RAISError statement allows customers to retrieve an entry from the SysMessages table or dynamically create a message using user-defined severity and status information. After defining, this message is returned to the customer as the server error message.

Raiserror ({msg_id | msg_str}, severity, state [, argument1 [, argument2]]) [WITH OPTION]

When converting the PL / SQL program, you may not need to use the RAISError statement. In the following code example, the PL / SQL program uses the RAISE_APPLICATION_ERROR exception error handler, and the Transact-SQL program is not used. Join the RAISE_APPLICATION_ERROR exception error handler, avoid the PL / SQL program to return the unhandled exception error message. Conversely, it always returns Oracle error message when an unexpected problem occurs. When the Transact-SQL program fails, it always returns detailed error message to the client program. Therefore, unless a special error is required, the RAISERROR statement is not always required.

Oracle

Microsoft SQL Server

CREATE OR REPLACE FUNCTIONDEPT_ADMIN.DELETE_DEPT (VDEPT IN VARCHAR2) RETURN NUMBER ASBEGINDELETE FROM DEPT_ADMIN.DEPTWHERE DEPT = VDEPT; RETURN (SQL% ROWCOUNT); EXCEPTIONWHEN OTHER THENRAISE_APPLICATION_ERROR (-20001, SQLERRM); END DELETE_DEPT; /

Create ProcedureDEPT_ADMIN.DELETE_DEPT@vdept varchar (4) asdelete from dept_db.dbo.deptwhere dept = @vdeptreturn @@ RowCountGO

Realization of cursors

Oracle always requires the cursor and the SELECT statement to be used without the number of rows from the database. In Microsoft SQL Server, the SELECT statement is not included in the cursor to return the line as the default result set, return to the customer. This is an effective way to return data to a client.

SQL Server provides two interfaces to the hosted function. When a cursor is used when the TRANSACT-SQL batch or stored procedure, the SQL statement can be used to open the cursor and the cursor and the cursor and the location update and delete. When using a cursor from DB-Library, ODBC or OLE DB, the SQL Server client library transparently calls the built-in server function to process the cursor more effectively.

When importing the PL / SQL process from Oracle, first determine if the cursor needs to perform the same functions as in Transact-SQL. If the cursor returns a group of rows to the client, use the non-visible SELECT statement in Transact-SQL to return a default result set. If you load a row of data using a cursor to the local process variable, you must use the cursor in Transact-SQL.

The following table gives the syntax of the cursor.

operating

Oracle

Microsoft SQL Server

Disclaimer

Cursor Cursor_name [(Cursor_Parameter (s))] is select_statement;

Open the cursor

Open cursor_name [(Cursor_Parameter (s))];

Open cursor_name

Extract from the cursor

Fetch Cursor_name INTO VARIABLE (S) Fetch [[Next | PRIOR | First | Last | Absolute {n | @nvar} | @nvar} | @n | @nvar}] from] cursor_name [inTo @variable (s)]

Update extract line

Update Table_nameset Statement (s) uwhere current of cursor_name;

Update Table_nameset Statement (s) uwhere current of cursor_name

Delete extract

Delete from table_name where current of cursor_name;

Delete from table_name where current of cursor_name

Close a cursor

Close Cursor_name;

Close Cursor_name

Delete a cursor data structure

Temporary

DEAALLOCATE CURSOR_NAME

Although the Transact-SQL Declare Cursor statement does not support the use of cursor parameters, it has supported local variables. When the cursor is turned on, the value of these local variables can be used in the cursor. Microsoft SQL Server provides many additional features in its Declare Cursor statements.

The INSENSITIVE option is used to define a cursor that creates a temporary copy of the data to use. All requests for the cursor are appended from this temporary table. Therefore, the modification of the basic table is not reflected in the data returned to the cursor. This type of cursor access is not modified.

The application can request a cursor type and then execute a Transact-SQL statement that is not supported by the requested type server cursor. SQL Server returns an error, pointing out that the cursor type has been changed; or if a set of elements is given, SQL Server will implicit the cursor. For a complete feature list that implicit SQL Server 7.0 implicitly converts the cursor from one type to another, see SQL Server Books Online.

In addition to forward extraction, the scroll option allows backward, absolute and relative extraction. Scrolling cursors use the key set cursor model, in which any user's submitted deletion and update of the base table will be reflected in the subsequent extraction. Just only when the INSENSITIVE option is not used to declare the cursor.

If the READ ONLY option is selected, any row in the cursor is disabled. This option will overwrite the default function of the cursor update.

Update [of column_list] is used to define the updatable columns in the cursor. If [of column_list] is given, only the columns listed are allowed to modify. If no list is given, all columns can be updated unless the cursor is defined as read only.

Note that the name of the SQL Server cursor is the connection itself, it is important. This is different from the name of the local variable. On the same user connection, the second cursor as the existing cursor name is unable to declare before the first cursor is released.

Unlike PL / SQL, Transact-SQL does not support the pass parameters to the cursor when a cursor is turned on. When the Transact-SQL cursor is turned on, the identity and order of the result set set is fixed. For updates and deletions of other users have submitted, they are reflected in all cursors extracted in all INSENSITIVE options. For the Insensitive cursor, a temporary table will also be generated.

The Oracle cursor can only move forward - cannot scroll backward or relative. SQL Server can scroll forward and backwards using the extraction options shown in the table below. These extraction options can only be used only when the cursor uses the Scroll option to declare. Scroll options

Description

If it is the extraction of the cursor, return the first line of the result set, if not, move the cursor in the result. Next is the main method used in the results of the results. Next is the default cursor extraction.

PRIOR

In the result set, return to the previous line.

First

Move the cursor to the first line of the result set and return to the first line.

Last

Move the cursor to the last line of the result set and return to the last line.

Absolute N

Returns the Nth line in the result set. If N is a negative value, the returned row is the Nth line from the last row of the result set.

Relative N

Returns the Nth row after the row. If n is a negative value, the returned row is the Nth line of the relative position from the cursor.

The Transact-SQL Fetch statement does not require an INTO clause. If you do not specify a returned variable, the line will be used as a single line result set, automatically returns to the customer. However, if the process must provide a line to the customer, use a non-free SELECT statement, more effective.

After each Fetch, the @@ fetch_status function is updated. It is similar to the Cursor_Name% Found and Cursor_name% NOTFOUND variables used in PL / SQL. After each successful extraction, the @@ fetch_status function value is set to 0. If the extraction is to read the end of the cursor, the value is returned to -1. If the cursor is turned on, the requested row has been removed from the table, then the @@ fetch_status function returns -2. Typically, the return value-2 appears only in a cursor declared by the Scroll option. After each extraction, the variable must be checked to ensure the effectiveness of the data.

SQL Server does not support Oracle's cursor for loop syntax.

In PL / SQL and TRANSACT-SQL, the CURRENT OF clause syntax and functions for updates and deletions are the same. Positioning Update or Delete is used to update and delete the current row within the specified cursor.

Transact-SQL Close Cursor statements Close the cursor, but the data structure can still be used to reopen the cursor. The PL / SQL Close Cursor statement is turned off and releases all data structures.

Transact-SQL needs to use the DEAALLOCATE CURSOR statement to delete the cursor data structure. The DEAALLOCATE CURSOR statement is different from the Close Cursor that the closing cursor can be reopened. DEAALLOCATE CURSOR statement releases all data structures related to cursors and deletes the definition of a cursor.

The following example is given, a cursor statement, such as PL / SQL and TRANSACT-SQL.

Oracle

Microsoft SQL Server

Declarevssn Char (9); vfname varchar (12); VLNAME VARCHAR (20);

Declare @ vssn char (9), @ vfname varchar (12), @ VLNAME VARCHAR (20)

CURSOR CUR1 ISSELECT SSN, FNAME, LNAMEFROM STUDENT ORDER BY LNAME; BEGINOPEN CUR1; FETCH CUR1 INTO VSSN, VFNAME, VLNAME; WHILE (CUR1% FOUND) LOOPFETCH CUR1 INTO VSSN, VFNAME, VLNAME; END LOOP; CLOSE CUR1; END; DECLARE curl CURSOR FORSELECT SSN, FNAME, LNAMEFROM STUDENT ORDER BY SSNOPEN CUR1FETCH NEXT FROM CUR1INTO @VSSN, @VFNAME, @VLNAMEWHILE (@@ FETCH_STATUS <> -1) BEGINFETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAMEENDCLOSE CUR1DEALLOCATE CUR1

Optimize SQL statements

This section provides information on some SQL Server tools for optimizing Transact-SQL statements. For more information on optimizing the SQL Server database, see "Performance Optimization" in front of this volume.

You can use the SQL Server query analyzer's graphic display schedule to learn more about the optimizer processing statement.

This graphics tool can capture continuous records of server activity in real time. SQL Server Event Profiles monitors multiple different server events and event categories, filtering these events using user-defined criteria, and outputs tracking records to screens, files, or other SQL Server.

SQL Server Event Profiler is available:

Monitor the performance of SQL Server.

Debug TRANSACT-SQL statements and stored procedures.

Determine the slow query.

Solve problems in SQL Server. By capturing all events that cause a particular problem, then reproduce these events on the test system, to repeat and separate the problem, to achieve the purpose of solving the problem.

In the project development phase, through a single step, each line, test the SQL statement and stored procedure, confirm whether the code is executed in accordance with the expected results.

Capture events on the production system and reproduce those events captured on the test system, thus reconstructing events in the production environment for testing or debugging. By reproducing the captured events in other systems, users can continue to use the production system without affecting normal operation.

The SQL Server event probe provides a graphical user interface to a set of extended stored procedures. These extended stored procedures can also be used directly. Therefore, you can create your own application, which uses the SQL Server Event Profiler extension stored procedure to monitor SQL Server.

The SET statement can set the SQL Sever query processing option for the work session, trigger, or stored procedure period.

The SET FORCEPLAN ON statement enforces the optimizer to process the join in the order in the FROM clause, and the Ordered prompts used in the Oracle optimization program.

SET Showplan_all and set showplan_text statement returns to the execution plan information of the query or statement, but does not perform the query or statement. To run the query or statement, set the corresponding display plan statement to OFF. Then, the query or statement will be executed. The showPLAN option is similar to the results provided by the Oracle Explain Plan tool.

Using the SET STATISTICES PROFILE ON, each executed query returns the standard result set, then returns the additional result set (the event probe to give the query execution). Other options include Set Statistics Io and Set Statistiecs Time.

Transact-SQL statement processes include two steps, namely compilation and execution. NOEXEC options compiles each query, but does not execute. When NOEXEC is set to ON, the subsequent statement (including other SET statements) is not performed until noExec is set to OFF. SET SHOWPLAN ONSET NOEXEC ONgoSELECT * FROM DEPT_ADMIN.DEPT, STUDENT_ADMIN.STUDENTWHERE MAJOR = DEPTgoSTEP 1The type of query is SETONSTEP 1The type of query is SETONSTEP 1The type of query is SELECTFROM TABLEDEPT_ADMIN.DEPTNested iterationTable ScanFROM TABLESTUDENT_ADMIN.STUDENTNested iterationTable Scan

Oracle requires a prompt to adjust the operation and performance of the overhead-based optimization program. Microsoft SQL Server's Optimizer-based Optimizer does not need to use a prompt to assist its query evaluation process. But in some cases, it is necessary to use them.

INDEX = {index_name | index_id} prompts the index name or ID used by the table. Index_ID is 0, it will force a table scan, and when INDEX_ID is 1, the aggregation index (if present) is forced. This is similar to the index prompt used in Oracle.

If the column order and the ORDER BY clause match, the SQL Server Fastfirstrow prompt will indicate that the optimizer uses a non-aggregated index. This prompt is similar to the Oracle First_ROWS prompt.

Define database objects

Oracle Database objects (tables, views, and indexes) can easily migrate to Microsoft SQL Server, because each RDBMS strictly follows SQL-92 standards, which is a standard for object definitions. Converting Oracle SQL tables, index, and view definitions to SQL Server tables, index, and view definitions, only require relatively simple syntax changes. The following table focuses on some differences between Oracle and Microsoft SQL Server database objects.

category

Microsoft SQL Server

Oracle

Column number

1024

254

Row

8060 bytes, plus 16 bytes points to each Text or Image column

No limit (but only one long or long RAW is allowed)

Maximum number of lines

no limit

BLOB Type Storage

The 16-byte pointer stored with the row. Data is stored on other data pages.

One long or long RAW each table. Must be at the end. The data is stored on the same block as the row.

Gathering table index

One

Each table (the table of index tissue)

Non-aggregated table index

249

no limit

The maximum number of columns indexed in a single index

The maximum length of the column value in the index

900 bytes

1/2 block

Table naming rules

Table_name........................................................................................................................

Table_name.] TABLE_NAME

View naming rules

Table_name........................................................................................................................

Table_name.] TABLE_NAME

Index naming rules

Table_name........................................................................................................................

Table_name.] TABLE_NAME

Assume that you start from the Oracle SQL script or program used to create a database object. Just copy this script or program and make the following modifications. Each change is discussed in other parts of this section. This example takes the script sample program script oracable.sql and sstable.sql. Make sure the database object identifier meets the Microsoft SQL Server Naming Rules. You may only need to change the index name.

Modify the data storage parameters so that it is used for SQL Server. If you use RAID, you do not need to store parameters.

Modify the Oracle Constraint definition to use it for SQL. If necessary, create a trigger to support foreign key DELETE CASCADE statements. If the table spans several databases, the trigger is used to force the foreign key relationship.

Modify the CREATE INDEX statement to use the aggregated index.

Use Data Conversion Services to create a new CREATE TABLE statement. Check this statement, pay attention to how the Oracle data type is corresponding to the SQL Server data type.

Delete all CREATE SEQUENCE statements. In the CREATE TABLE or ALTER TABLE statement, use the identifier column, the use of the alternative sequence.

Modify the CREATE VIEW statement if necessary.

Delete any reference to the synonym.

Evaluate the use of the Microsoft SQL Server temporary table, and its use in the application.

Change all of the CREATE TABLEUAS SELECT command command command to SQL Server Selectuinto statement.

Evaluate the potential use of user-defined rules, data types, and default values.

The following chart compares how Oracle and Microsoft SQL Server handle object identifiers. In most cases, the object name is not required when migrating to SQL Server.

Oracle

Microsoft SQL Server

1-30 characters long. Database Name: Up to 8 characters. Database Link Name: Up to 128 characters.

1-128 Unicode characters long. Temporary Table Name: Up to 116 characters.

The identifier name must begin with letters, including letters, numeric characters, or _,, and # characters.

The identifier name can be started with alphanumeric characters or _, and can almost contain any characters. If the identifier begins with a space, and contains characters other than _, @, # or $, you must use [] (separator) to enclose the identifier name. If the object start characters are: @ it is a partial variable. # It is a local temporary object. ## It is a global temporary object.

The table space name must be unique.

The database name must be unique.

In the user account (architecture), the identifier name must be unique.

In the database user account, the identifier name must be unique.

In the table or view, the column name must be unique.

In the user architecture, the index name must be unique.

In the database table name, the index name must be unique.

When accessing the table in the Oracle user account, only it is selected according to its unqualified name. When accessing the table in other Oracle architecture, add the schema name and an English period before the table name (.). Oracle synonyms provide other positional transparency.

When the Microsoft SQL Server reference table, another set of naming rules is used. Because the SQL Server login account can create a table in multiple databases, you can use the following rules to access tables and views: [[Database_name.] Owner_name.] Table_Name

Access the table in the following

Oracle

Microsoft SQL Server User Account

Select * from student

SELECT * from user_db.student_admin.student

Other architecture

Select * from student_admin.student

SELECT * from other_db.student_admin.student

The following is a guiding principle named Microsoft SQL Server tables and views:

Using the database name and the username are optional. When only the name is referenced (for example, Student), SQL Server finds the table in the current user account of the current database. If not found, it will find an object that keeps the same name owns the username DBO in the database. In the user account of the database, the table name must be unique.

A SQL Server login account has the same name in multiple databases. For example, the Enduser1 account has the following database objects: user_db.enduser1.student and other_db.enduser1.student. The qualifier is the database username, not the SQL Server login name because they do not necessarily.

At the same time, other users in these databases can have the same name:

User_db.dbo.student

User_db.dept_admin.student

User_db.student_admin.student

Other_db.dbo.student

Therefore, it is recommended to use the owner name as part of the database object reference. If the application has multiple databases, it is recommended to use the database name as part of the reference. If the query across multiple servers, the server name is also added to the reference.

Each SQL Server connection has a current database context, which is set to use the USE statement when logging in. For example, assume the following scene:

A user, use the Enduser1 account, log in to the USER_DB database. The user requests the Student table. SQL Server looks for the Enduser1.student table. If you find the table, SQL Server executes the requested database operation on user_db.enduser1.student. If you do not find the table in the Enduser1 database account, SQL Server looks for user_db.dbo.student in the DBO account of this database. If the table still doesn't find, SQL Server returns an error message, indicating that the table does not exist.

If another user, such as DEPT_ADMIN, has this table, the table name must be added to the name of the database user (DEPT_ADMIN.STUDENT). Otherwise, the database name defaults to the database current in the context.

If the referenced table is in another database, the database name must be used as part of the reference. For example, in an OtherDB database, you want to use other_db.enduser1.student when accessing the Student table owned by the EndUser1.

The name of the database and table can be separated by two English aphones, omit the owner name of the object. For example, if the application references Student_Db..student, SQL Server is inquiry:

Student_db.current_user.student

Student_db.dbo.student

If the user uses only one database at a time, the database name is omitted in the object's reference, so that it is simple to use the application in other databases. All object references implicitly access the currently used database. This is useful if you have a test database and a production database on the same server. Because Oracle and SQL Server support SQL-92 primary rules that identify the RDBMS object, the Create Table syntax is similar.

Oracle

Microsoft SQL Server

CREATE TABLE [. Schema] table_name ({col_name column_properties [default_expression] [constraint [constraint [... constraint]]] | [[,] constraint]} [[,] {next_col_name | next_constraint} ...]) [Oracle Specific Data Storage Parameters]

CREATE TABLE [. Server] [database.] [Owner.] Table_name ({col_name column_properties [constraint [constraint [... constraint]]] | [[,] constraint]} [[,] {next_col_name | next_constraint} .. [On filegroup_name]

Oracle Database Object Name is not case sensitive. In Microsoft SQL Server, the selected installation option is deserved, the database object name can be case sensitive.

When SQL Server is installed, the default sort order is a dictionary order and is not case sensitive. (You can use the SQL Server installer to set different configurations.) Because the Oracle object name is always unique, so the database object migrates to SQL Server, and there should be no problem. It is recommended that all tables and column names in Oracle and SQL Server use capitalization to avoid problems when users are installed on SQL Server case.

With Microsoft SQL Server, using RAID usually simplifies the storage of database objects. Like the table of Oracle Index, SQL Server aggregation index is integrated into the structure of the table.

Oracle

Microsoft SQL Server

CREATE TABLE DEPT_ADMIN.DEPT (DEPT VARCHAR2 (4) NOT NULL, DNAME VARCHAR2 (30) NOT NULL, CONSTRAINT DEPT_DEPT_PKPRIMARY KEY (DEPT) USING INDEX TABLESPACE USER_DATAPCTFREE 0 STORAGE (INITIAL 10K NEXT 10KMINEXTENTS 1 MAXEXTENTS UNLIMITED), CONSTRAINT DEPT_DNAME_UNIQUEUNIQUE (DNAME) USING INDEX TABLESPACE USER_DATAPCTFREE 0 STORAGE (INITIAL 10K NEXT 10KMINEXTENTS 1 MAXEXTENTS UNLIMITED)) PCTFREE 10 PCTUSED 40TABLESPACE USER_DATASTORAGE (INITIAL 10K NEXT 10KMINEXTENTS 1 MAXEXTENTS UNLIMITEDFREELISTS 1)

CREATE TABLE USER_DB.DEPT_ADMIN.DEPT (DEPT VARCHAR (4) NOT NULL, DNAME VARCHAR (30) NOT NULL, CONSTRAINT DEPT_DEPT_PKPRIMARY KEY CLUSTERED (DEPT), CONSTRAINT DEPT_DNAME_UNIQUEUNIQUE NONCLUSTERED (DNAME)) in Oracle, any valid command SELECT Create a table. Microsoft SQL Server provides the same features, but the grammar is different.

Oracle

Microsoft SQL Server

Create Table StudentBackup As SELECT * FROM STUDENT

Select * Into StudentBackup from student

SELECTUINTO will not take effect unless the database to be applied is set to TURE, otherwise. (The database owner can use the SQL Server Enterprise Manager or Transact-SQL SP_DBOPTION system stored procedure to set this option.) Check the status of the database using the sp_helpdb system stored procedure. If SELECT INTO / BULKCOPY is not set to True, you can still use the SELECT statement to copy the data into a temporary table.

SELECT * INTO #student_backup from user_db.student_admin.student

When you create a new table using the SELECT..INTO statement, the reference integrity definition is not transferred to a new table.

You must set the Select INTO / BULKCOPY option to TRUE, which may make the migration process complicated. If you must use the SELECT statement to copy the data to the table, create a table first, then use the INSERT INTOUSELECT statement to load the table. Oracle and SQL Server syntax is consistent and does not need to set any database options.

In Microsoft SQL Server, syntax for creating views is similar to Oracle.

Oracle

Microsoft SQL Server

Create [or report] View [Schema.] View_name [(Column_name [, Column_name] ...)] AS SELECT_STATEMENT [with check option [constraintname]] [with read only]

Create View [oowner.] View_name [(Column_name [, Column_name] ...)] [with encryption] as select_statement [with check option]

SQL Server's view requires that the table exists and the view owner has the right to access the table specified in the SELECT statement (similar to the Oracle Force option).

By default, the data modification statement on the view is not checked, determine if the affected row is in the scope of the view. To check all modifications, use with check option. The main difference between WITH CHECK OPTION is that Oracle defines it as a constraint, while SQL Server is not. Other aspects, both are the same.

When the view is defined, Oracle provides the with read only option. The SQL Server application can also get the same result by granting the SELECT permission. Both SQL Server and Oracle views support the use of mathematical expressions, functions, and constant expressions. Some SQL Server specific differences are:

If the data modification statement only affects a base table, you can allow the data modification statement (INSERT or UPDATE) on multiple views. In a statement, the data modification statement cannot be used for more than one table.

The Text or Image column in the view cannot use readtext or writetext.

Can't use ORDER BY, Compute, For Browse or Compute By clause.

The INTO keyword cannot be used in the view.

When a view is defined by an external connection, the results given by SQL Server and Oracle may be different when querying a qualified condition on a column in the connection in the connection. In most cases, the Oracle view can easily transfer to the SQL Server view.

Oracle

Microsoft SQL Server

Create View Student_admin.student_gpa (SSN, GPA) AS SELECT SSN, ROUND (AVG (Decode (Grade, 'A', 4, 'A ', 4.3, 'A-', 3.7, 'B', 3, 'B ' 3.3, 'b-', 2.7, 'c', 2, 'c ', 2.3, 'c-', 1.7, 'd', 1, 'd ', 1.3, 'd-', 0.7, 0) ), 2) from student_admin.gradegroup by SSN

Create View Student_admin.student_gpa (SSN, GPA) AS SELECT SSN, ROUND (AVG (Case Gradewhen 'a' Then 4when 'A ' TEN 4.3WHEN 'A-' TEN 3.7WHEN 'B' THEN 3when 'B ' Ten 3.3when ' B- 'THEN 2.7WHEN' C 'Twain' C- 'TEN 1.7WHEN' D 'THEN' DHEN 'D ' THEN 1.3when 'D-' Then 0.7ELSE 0nd, 2) from student_admin. Gradegroup by SSN

Microsoft SQL Server provides aggregation and non-aggregated index structures. These index consists of pages, and the page is constituted by a branch structure called "B tree" (similar to the Oracle B tree index structure). The start page (root level) specifies the range of the value of the table. Each range of the root-level page points to another page (decision node), which contains a narrower meter range. Seipope, these decision nodes can point to other decision nodes to further reduce the search range. The final level of the branch structure is called a leaf-level.

Aggregate index

In Oracle, the aggregated index is implemented as a table with an index tissue. The aggregation index is physically combined with the table. Tables and indexes share the same storage area. The aggregation index is physically rejected according to the order of the index, and constitutes the middle decision node. The indexed leaf-level page contains actual table data. This structure only allows each table to aggregate indexes. Once a Primary Key or UNIQUE constraint is applied on the table, Microsoft SQL Server automatically creates a gathering index for the table. Gathering indexes: primary key

Not updated columns

Returns a query of a value of a value using an operator such as BETWEEN,>,> =,

Select * from student where grad_datebetween '

1/1/97

'And '12 / 31/97'

Returns the query of the big result set:

Select * from student where lname = 'smith'

Columns used in the order of order operations (ORDER BY, GROUP BY).

For example, on the Student table, a non-aggregated index on the primary key SSN may be very helpful, and the aggregation index can be created on the LNAME, FNAME (last name, name), as this is a common method of packeting students.

Distribute a table in a table to prevent "hotspots". Hotspots are usually caused by plurality of users to insert a table. This application scenario is usually processed by a row-level lock.

In SQL Server, delete and recreate the aggregation index is a common technique for the restructuring table. Using this method, it can be easily guaranteed, and there is a continuous page on disk, and some free space can be easily reconstructed on the table. This is similar to the export, deletion, and import tables in Oracle.

The SQL Server aggregation index and Oracle gathers are not available. Oracle aggregation is a physical combination of two or more tables that share the same data block and use common columns as aggregation keys. In SQL Server, there is no structure similar to the Oracle aggregation.

In principle, the aggregation index is defined on the table to improve SQL Server performance and spatial management. If you don't understand the query or update mode of the given table, you can create a gathering index on the primary key.

The following table is given, taken from the sample application source code. Note the use of SQL Server aggregation indexes.

Oracle

Microsoft SQL Server

CREATE TABLE STUDENT_ADMIN.GRADE (SSN CHAR (9) NOT NULL, CCODE VARCHAR2 (4) NOT NULL, GRADE VARCHAR2 (2) NULL, CONSTRAINT GRADE_SSN_CCODE_PKPRIMARY KEY (SSN, CCODE) CONSTRAINT GRADE_SSN_FKFOREIGN KEY (SSN) REFERENCESSTUDENT_ADMIN.STUDENT (SSN), ConsTRAINT grade_ccode_fkforeign key (ccode) ReferenceSdept_admin.class (ccode))

CREATE TABLE STUDENT_ADMIN.GRADE (SSN CHAR (9) NOT NULL, CCODE VARCHAR (4) NOT NULL, GRADE VARCHAR (2) NULL, CONSTRAINTGRADE_SSN_CCODE_PKPRIMARY KEY CLUSTERED (SSN, CCODE), CONSTRAINT GRADE_SSN_FKFOREIGN KEY (SSN) REFERENCESSTUDENT_ADMIN.STUDENT (SSN) Constraint grade_ccode_fkforeign key (ccode) ReferenceSdept_admin.class (ccode)) Non-aggregated index

In the non-aggregated index, index data and table data are physically separated, and the rows in the table do not store in the order of the index. You can migrate the Oracle index definition to the Microsoft SQL Server non-aggregated index definition (as shown in the following example). However, for performance considerations, you may want to select a given table, and create it as a gathering index.

Oracle

Microsoft SQL Server

Create indexstudent_admin.student_major_idxon student_admin.student (Major) TABLESPACE USER_DATAPCTFREE 0Storage (Initial 10k Next 10kmineltnce) 1 maxExtentsunlimited)

Create nonclustered indexstudent_major_idxon user_db.student_admin.student (Major)

Indexed grammar and naming

In Oracle, the index name is unique in the user account. In Microsoft SQL Server, the index name must be unique in the table name, but it is not necessarily unique in the user account or database. Therefore, when creating or deleting an index in SQL Server, the name and index name of the table must be specified. In addition, the SQL Server Drop Index statement can delete multiple indexs at the same time.

Oracle

Microsoft SQL Server

CREATE [UNIQUE] INDEX [schema] .index_nameON [schema.] Table_name (column_name [, column_name] ...) [INITRANS n] [MAXTRANS n] [TABLESPACE tablespace_name] [STORAGE storage_parameters] [PCTFREE n] [NOSORT] DROP INDEX ABC;

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON table (column [, Un]) [WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING] [[,] STATISTICS_NORECOMPUTE ]] [On filegroup] Drop Index User_Db.Student.demo_idx, user_db.grade.deemo_idx

Index data storage parameters

The FillFactor option in Microsoft SQL Server and the PCTFree variable in Oracle are basically the same. As the table increases, the index page will be split to accommodate new data. Indexes must reorganize to accommodate new data values. Fill factor percentage is only used when the index is created, and it will not be maintained later.

When the index is originally created, the FillFactor option (value from 0 to 100) controls how many spaces on the index page. If not specified, use the default fill factor 0 - this will completely fill the ribbon page and hold at least one entry space on each decision node page (two for non-unique aggregation indexes). Using a lower fill factor value, it will initially reduce the splitting of the index page and increase the number of B tree index structures. Using a higher fill factor value, the index page space can be used more efficiently, and the access index data requires less disk I / O, and reduces the number of B tree index structures.

The PAD_INDEX option specifies that the filling factor is applied to the index decision node page and the data page.

Although in Oracle, the PCTFree parameter must be adjusted to optimize performance, but in the CREATE INDEX statement, you do not need to join the FillFactor option. Fill factors can be used to optimize performance. The filling factor is useful when using existing data to create a new index on the table and can accurately estimate the data after the data is used.

If the PCTFREE of the Oracle index has already been set to 0, a fill factor having a value of 100 is considered. It is used to do not insert and update tables (read-only tables). When the fill factor is set to 100, SQL Server creates 100% filled index per page.

Ignore repeated keywords

For Oracle and Microsoft SQL Server, users cannot insert repetition values to columns of the unique index. If this is done, you will generate a message. However, using SQL Server, the developer can choose the INSERT or UPDATE statement to react to the error.

If IGNORE_DUP_KEY is specified in the CREATE INDEX statement, the INSERT or UPDATE statement that generates a duplicate key is executed, and SQL Server will issue a warning message and ignore (not inserted) the repetition line. If you do not specify IGNORE_DUP_KEY to the index, SQL Server will make an error message and roll back to the entire INSERT statement. For more information on these options, see SQL Server Books Online.

Oracle applications may need to create a table with only a short time. The application must ensure that all tables created for this purpose can be deleted at some point. If the application does not do this, the table space will soon become chaotic and difficult to manage.

Microsoft SQL Server provides a temporary table database object, which is created for this purpose. These tables are always created in the TEMPDB database. The table name determines how long it is reserved in the Tempdb database.

Name of the table

Description

#table_name

This partial temporary table is only exists during the creation of its user sessions or procedures. The table is automatically deleted when the user is logged out or created. These tables cannot be shared between multiple users. Other database users cannot access this class. The permissions of such a table cannot be granted or revoked.

## Table_name

This global temporary table is usually existed during the creation of its user sessions or procedures. This table can be shared between multiple users. When the last user session interrupt is interrupted, it is automatically deleted. All other database users can access this table. The permissions of such a table cannot be granted or revoked.

You can define an index for a temporary table. A view can only be created in TEMPDB and does not have a table with a # or ## prefix. The following example is given, how to create a temporary table and its related index. When the user exits, the table and index are automatically deleted.

SELECT SUM (ISNULL (TUITION_PAID, 0)) SUM_PAID, MAJOR INTO #SUM_STUDENTFROM USER_DB.STUDENT_ADMIN.STUDENT GROUP BY MAJORCREATE UNIQUE INDEX SUM STUDENT IDX ON #SUM STUDENT (MAJOR), you may find that using temporary tables brings a lot of advantages, There is a reason to modify the program code for this purpose.

Microsoft SQL Server has some more robust data more than Oracle. There are multiple conversion methods between Oracle and SQL Server data types. It is recommended to automatically create a new CREATE TABLE statement using the DTS wizard. This statement can be modified if necessary.

Oracle

Microsoft SQL Server

Charr

It is recommended to use char. Because the column of the char type uses a fixed storage length, it is faster than the varchar column when access is.

VARCHAR2 and Long

VARCHAR or Text. (If the length of the data value in the Oracle column is 8000 bytes or less, use varchar; otherwise, you must use text.)

RAW and long RAW

Varbinary or Image. (If the length of the data value in the oracle column is 8000 bytes or less, use varbinary; otherwise, you must use image.)

Number

If the integer is between 1 and 255, Tinyint is used. If integers are between -32768 and 32767, use Smallint. If integers are between -2, 147, 483, 648, and 2, 147, 483, 647, INT is used. If you need a floating point type, use numeric (with accuracy and decimal). Note: Do not use float or real because it may be rounded (Oracle Number and SQL Server NuMeric). If it is not sure, use numeric; it is close to the Oracle Number data type.

Date

Datetime.

RowID

Use the Identity column type.

CURRVAL, NEXTVAL

Use the Identity column type and @@ identity, ident_seed (), and ident_incr () functions.

Sysdate

Getdate ().

User

User.

Use Unicode data

The Unicode specification defines a unified coding scheme to almost all characters widely used around the world. All computers use this Unicode specification to uniformly convert bit mode in Unicode data into characters. This ensures that on all computers, the same bit mode is always converted to the same character. Data can be transferred from one database or computer to another without having to worry that the receiving system cannot convert the bit mode correctly into a character.

For each character using one byte encoded data type, a problem is that this data type can only represent 256 different characters. This requires multiple encoding specifications (or code pages) for different alphabets. It does not handle systems such as Japanese Kanji or Korean Hangul alphabet.

Microsoft SQL Server uses the definition in the code page installed with SQL Server to convert bit mode in the char, varchar, and text columns into characters. The customer computer uses the code page interpretation bit mode installed with the operating system. There are many different code pages. Some characters are in some code pages, but are not in other code pages. Some characters are defined in some code pages, using another bit mode in other code pages. When creating an international system of various languages must be processed, it is very difficult to select the code page that meets multiple national language requirements for all computers. It is also difficult to guarantee that all computers can interact correctly when interacting with systems using different code pages. In the Unicode specification, each character uses 2 bytes encoding to solve this problem. There are enough different modes (65, 536) in the two bytes, allowing a single specification to cover most of the common business languages. Because all Unicode systems represent all characters using the same bit mode, when the characters migrate from a system to another, the problem of character conversion errors will occur.

In SQL Server, nchar, nvarchar, and ntext data types support Unicode data. For more information on the SQL Server data type, see SQL Server Books Online.

User-defined data types.

User-defined data types can be created for the Model database or a single user database. If the user-defined data type is defined for Model, all new user databases created can be used. User-defined data types are defined by the SP_ADDTYPE system stored procedure. For more information, see SQL Server Books Online.

You can use user-defined data types in Create Table and ALTER TABLE statements and bind them with defaults and rules. During the table creation, if the user-defined data type is used, it is clearly defined as emptiness, it is precedenced to air when the data type is created.

This example is given, how to create a user-defined data type. The parameter is a user type name, data type, and air.

sp_addtype gender_type, 'varchar (1)', 'Not Null'go

At first glance, this feature solves the problem of the Oracle table creation script to SQL Server migration. For example, it is convenient to add Oracle Date data types:

sp_addtype date, datetime

But there is no use of data types that require size variable, such as Oracle Data Type Number. The returned error message indicates that the length must also be specified.

SP_ADDTYPE VARCHAR2, VARCHARGOMSG 15091, Level 16, State 1you Must Specify A Length with this Physical Type.

Microsoft TimeStamp column

The TimeStAMP column allows the browse mode to update and make the cursor update operation more efficient. TimeSTAMP is a data type that automatically updates each time the row containing the TimeStAMP column is inserted or updated.

The value in TimeSTAMP is not stored as actual date or time, but is stored in binary (8) or varbinary (8), which represents the event sequence of the line in the table. A table can only have a TimeStAMP column.

For more information, see SQL Server Books Online.

The permissions of the Microsoft SQL Server object can be granted to other database users, database groups, and public roles, or they can be rejected or revoked. Unlike Oracle, SQL Server does not allow object owners to grant ALTER TABLE and CREATE INDEX permissions to objects. These permissions must only belong to the object owner. The GRANT statement creates an entry in the secure system that allows users in the current database to process data in the current database or perform a specific Transact-SQL statement. In Oracle and SQL Server, the syntax of the GRANT statement is the same.

The Deny statement creates an entry in the security system, rejects the permissions of the security account in the current database, and prohibits the security account inheritance in group or role member. Oracle has no Deny statement. The REVOKE statement is revoked prior to granting permissions for one user in the current database or is rejected.

Oracle

Microsoft SQL Server

GRANT {ALL [PRIVILEGES] [column_list] | permission_list [column_list]} ON {table_name [(column_list)] | view_name [(column_list)] | stored_procedure_name} TO {PUBLIC | name_list} [WITH GRANT OPTION]

Grant {All [privileges] | permission [, un]} {[(column [, un]) {Table | View} | ON {Table | View} [(Column [, UN])] | ON {stored_procedure | Extended_procedure}}} to security_account [, un] [with gran option] [as {group | role}] revoke [grant option for] {all [privileges] | permission [, un]} {[(column [, un])] ON {Table | View} | {Table | View} [(Column [, Un])] | {stored_procedure | Extended_Procedure}} {to | from} security_account [, un] [cascade] [as {group | role}] Deny {All [Privileges] | Permission [, UN]} {[(column [, un]) {Table | View} | ON {Table | View} [(Column [, Un])] | ON {stored_procedure | Extended_procedure}} to security_account [, un] [cascade]

For more information on object level permissions, see SQL Server Books Online.

In Oracle, References permissions can only grant a user. SQL Server allows the References permission to grant database users and database groups. In Oracle and SQL Server, INSERT, UPDATE, DELETE, and SELECT permissions are the same.

Implement data integrity and business rules

The implementation of data integrity ensures the quality of the data in the database. In the planning plan, there are two important steps, that is, identify the valid values of the column, and how to implement data integrity in the column. Data integrity can be divided into four categories and implemented in different ways.

Integrity type

Implementation

Entity integrity

Primary Key Constrained UNIQUE Constrained Identity Property

Range integrity

Default Definition Foreign Key Constraint Check Constrained

Quote integrity

Range Default Definition Foreign Key Constrained CHECK Constraint for Empty User Defined Integrity

Stored procedures and table-level constraints in Create Table.

The physical integrity defines a row as a separate entity of a specific table. The entity integrity is inteacpended by indexing, UNIQUE constraints, Primary Key constraints, or Identity properties, to implement the integrity of the identifier column or primary key.

Stamped naming

Should always explicitly naming constraints. If not, Oracle and Microsoft SQL Server use different naming rules implicitly constraints. These naming differences will bring unnecessary trouble to the migration process. When deleting or disable constraints, it will cause inconsistency, because the name must be used, the constraint can be deleted. For Oracle and SQL Server, the syntax of explicit naming constraints is the same.

ConsTRAINT CONSTRAINT_NAME

Primary key and only column

SQL-92 standard requirements, all values in the primary key should be unique, and the column is not allowed to have an empty value. Once the Primary Key or UNIQUE constraint, Oracle and Microsoft SQL Server implement uniqueness by automatically created a unique index. In addition, the primary key column is automatically defined as NOT NULL. Each table only allows a primary key.

For primary keys, create a SQL Server aggregation index by default, although non-aggregated indexes can also be requested. The Oracle index on the primary key can be deleted by deleting or disabling the constraint, while the SQL Server index can only be deleted by deleting the constraint.

In both RDBMS, the unique constraint can be used to define the standby key. On either, multiple UNIQUE constraints can be defined. The Unique Constrained column can be empty. In SQL Server, unless otherwise specified, a non-aggregated index is created by default.

When migrating an application, pay attention to the complete unique key (single or multiple column indexes), SQL Server only allows a row to contain NULL values, while Oracle allows any number of rows to contain NULL values.

Oracle

Microsoft SQL Server

Create Table Dept_admin.dept (

DEPT

VARCHAR2

(4) NOT NULL, DNAME VARCHAR2 (30) NOT NULL, CONSTRAINT DEPT_DEPT_PKPRIMARY KEY (DEPT) USING INDEX TABLESPACEUSER_DATAPCTFREE 0 STORAGE (INITIAL 10K NEXT 10KMINEXTENTS 1 MAXEXTENTS UNLIMITED), CONSTRAINT DEPT_DNAME_UNIQUEUNIQUE (DNAME) USING INDEX TABLESPACE USER_DATAPCTFREE 0 STORAGE (INITIAL 10K NEXT 10kminextents 1 maxExtentsunlimited))

Increase and delete constraints

Disable constraints can improve database performance and simplify data replication processes. For example, when the remote site is rebuilt or copies table data, it is not necessary to repeat the constraint check, because the data is initially inserted into the table, the data integrity has been checked. You can write an Oracle application, disable or enable constraints (except Primary Key and UNIQUE). In Microsoft SQL Server, use the ALTER TABLE statement with the Check and WITH OnCheck option, or the above process can also be implemented. This illustration is given, the contrast of this process.

In SQL Server, you can use the NOCHECK clause and the all keyword to delay all table constraints.

If the Oracle application is to disable or delete the Primary Key or UNIQUE constraint using the Cascade option, you may need to override some code because the Cascade option disables or deletes the parent constraint and its associated any sub-integrity constraints.

Below is an example of this syntax:

Drop connection_DEPT_PK CASCADE

The SQL Server application must be modified to remove the sub-constraint first and then delete the parent constraint. For example, to delete the Primary Key constraint on the DEPT table, you must delete the foreign key on the column student.major and Class.Dept. Below is an example of this syntax:

ALTER TABLE STUDENTDROP Constraint Student_major_fkalter Table ClassDrop ConsTraint Class_DEPT_FKALTER TABLE DEPTDROP CONSTRAINT DEPT_DEPT_PK

Alter Table syntax (for adding and deleting constraints) is almost the same as Oracle and SQL Server.

Generate continuous values

If the Oracle application uses Sequence, you can easily modify it to use the Microsoft SQL Server Identity property.

category

Microsoft SQL Server Identity

grammar

CREATE TABLE new_employees (Empid int IDENTITY (1,1), Employee_Name varchar (60), CONSTRAINT Emp_PK PRIMARY KEY (Empid)) If the interval is increased 5: CREATE TABLE new_employees (Empid int IDENTITY (1,5), Employee_Name varchar (60 ), Constraint EMP_PK PRIMARY Key (Empid))

Identifier columns in each table

One

Allow null value

Can you use the default constraint, value

can not be used

Implement uniqueness

Yes

After the INSERT, SELECT INTO, or the large-capacity copy statement is completed, query the current largest identification number

@@ identity (function)

Returns the seed value specified during the identifier column creation

Ident_seed ('Table_name')

Returns the increment value specified during the identifier column creation

Ident_incr ('Table_name')

SELECT syntax

When the column with the Identity property is referenced, in SELECT, INSERT, UPDATE, and DELETE statements, the name of the keyword IdentityCol can be used.

Although the Identity property is automatically completed in a table, different tables (each table has its own identifier columns) attribute values may be the same. This is because the Identity property is only guaranteed to be unique in the table using it. If the application must generate a identifier column, it is unique in the entire database, or even each database on each networking computer, using the ROWGUIDCOL property, the UNIQUEIDENTIFIER data type, and the NewID function. SQL Server uses globally unique identifier columns to merge replication, ensuring that the rows are uniquely identified in multiple copies of the table. For more information on creating and modifying identifier columns, see SQL Server Books Online.

For a given column, the range integrity implements a valid entry. Range integrity is implemented by limiting the type of possible values (via data type), format (via Check constraint) or range (via REFERENCE and CHECK constraint).

Default and Check Constraint

Oracle uses the default value as column properties, and Microsoft SQL Server uses the default value as constraints. SQL Server Default constraints can contain constants, without parameter built-in functions (NILADCI functions) or NULL.

To facilitate migrating Oracle Default column properties, you should define default constraints in the SQL Server column, without having to use constraint names. For each Default constraint, SQL Server generates a unique name.

In Oracle and SQL Server, define the syntax of the Check constraints is the same. The search criteria must be evaluated to a Boolean expression and cannot include subqueries. The column CHECK constraint can only reference the constrained columns, and the table-level CHECK constraint can only reference the columns in the constraint sheet. Multiple Check constraints can be defined for a table. In the CREATE TABLE statement, the SQL Server syntax specifies that only a column CHECK constraint is allowed on a column, and there are multiple conditions.

The best way to test the modified CREATE TABLE statement is to use the SQL Server query analyzer in SQL Server and analyze the syntax. The result pane gives all errors. For more information on constraint syntax, see SQL Server Books Online.

Oracle

Microsoft SQL Server

Create Table Student_admin.student (SSN Char (9) Not Null, Fname Varchar2 (12) Null, Lname Varchar2 (20) Not Null, Gnder Char (1) Not NullConstRAINTSTUDENT_GENDER_CKCHECK (Gnder In ('m', 'f'), MAJOR VARCHAR2 (4) DEFAULT 'Undc' NOT NULL, BIRTH_DATE DATE NULL, TUITION_PAID NUMBER (12,2) NULL, TUITION_TOTAL NUMBER (12,2) NULL, START_DATE DATE NULL, GRAD_DATE DATE NULL, LOAN_AMOUNT NUMBER (12,2) NULL , DEGREE_PROGRAM CHAR (1) DEFAULT 'U' NOT NULLCONSTRAINTSTUDENT_DEGREE_CK CHECK (DEGREE_PROGRAM IN ( 'U', 'M', 'P', 'D')), ... CREATE TABLE USER_DB.STUDENT_ADMIN.STUDENT (SSN CHAR (9 NOT NULL, FNAME VARCHAR (12) NULL, LNAME VARCHAR (20) Not Null, Gnder Char (1) Not NullConst Student_gender_ckcheck (Gnder In ('M,' F ')), Major Varchar (4) Default' undc ' NOT NULL, BIRTH_DATE DATETIME NULL, TUITION_PAID NUMERIC (12,2) NULL, TUITION_TOTAL NUMERIC (12,2) NULL, START_DATE DATETIME NULL, GRAD_DATE DATETIME NULL, LOAN_AMOUNT NUMERIC (12,2) NULL, DEGREE_PROGRAM CHAR (1) DEFAULT 'U 'Not nullconstraint student_degree_ck check (Degree_Program in ('U', 'm', 'p', 'd')), ...

Description of User-defined rules and defaults: For backward compatible considerations, Microsoft SQL Server rules and default values are retained, but for new applications, it is recommended to use Check and Default constraints. For more information, see SQL Server Books Online.

Void

Microsoft SQL Server and Oracle create column constraints to implement. Oracle Libra Default NULL unless NOT NULL is specified in the CREATE TABLE or ALTER TABLE statement. In Microsoft SQL Server, the database and session settings can override the data type used in the column definition as empty.

All SQL scripts (whether Oracle or SQL Server) should explicit NULL and NOT NULL for each column. To understand how this strategy is implemented, see ORATABLE.SQL and SSTABLE.SQL Example Table Create a script. If there is no explicit definition, the column is empty to follow the following rules.

NULL settings

Description

Use user-defined data type definition columns

SQL Server uses this data type to be specified for empty. Using the sp_help system stored procedure to get the default empty. Use the data type definition column provided by the system

If there is only one option provided by the system, it is preferred. Now, BIT data type can only be defined as NOT NULL. If any session setting is ON (using SET open), then if ANSI_NULL_DFLT_ON is ON, it assigns NULL. If the ANSI_NULL_DFLT_OFF is ON, it assigns NOT NULL. If any database settings are configured (modified with the sp_dboption system stored), then if the ANSI NULL Default is true, the value is assigned NULL. If the ANSI NULL DEFAULT is False, it assigns NOT NULL.

NULL / NOT NULL is not defined

If there is no explicitly defined (no ASNI_NULL_DFLT options), the session has no change, and the database is set as the default value (ANSI NULL Default is false), then the SQL Server assigns NOT NULL.

The following table provides a syntax comparison for defining reference integrity constraints.

constraint

Oracle

Microsoft SQL Server

PRIMARY Key

[Constraint constraint_name] Primary Key (col_name [, col_name2 [..., col_name16]]) [use index storage_parameters]

[Constraint constraint_name] primary key [clustered | nonclustered] (col_name [, col_name2 [..., col_name16]]) [on segment_name] [not for replication]

Unique

[Constraint constraint_name] unique (col_name [, col_name2 [..., col_name16]]) [use index storage_parameters]

[Constraint constraint_name] unique [clustered | nonclustered] (col_name [, col_name2 [..., col_name16]]) [on segment_name] [not for replication]

Foreign Key

[Constraint constraint_name] [FOREIGN KEY (Col_name [, col_name16])] References [oowner.] Ref_table [(Ref_col [, ref_col2 [..., ref_col16]]] [on delete cascade]

[Constraint constraint_name] [FOREIGN Key (col_name [, col_name16])] References [oowner.] Ref_table [(ref_col [, ref_col2 [..., ref_col16]]) [NOT for replication]

DEFAULT

Column properties, but not constrained Default (constant_expression)

[Constraint constraint_name] default {constant_expression | niladic-function | null} [for col_name] [not for replication] Check

[Constraint constraint_name] Check (Expression)

[Constraint constraint_name] Check [not for replication] (expression)

NOT for Replication clauses are used to suspend column, Foreign Key and Check constraints during replication.

Foreign key

In each RDBMS, the rules of the defined foreign key are similar. The number of columns specified in the outer key clause and the data type of each column must match the References clause. The non-empty value input in this column must exist in the tables and columns defined in the References clause, and in the columns of the reference table, there must be a primary key or unique constraint.

Microsoft SQL Server Constraint provides the ability to reference the table in the same database. To achieve a reference integrity across the database, a table-based trigger is required.

Oracle and SQL Server supports self-reference tables, reference (foreign bonds) to one or more columns of the same table in this table. For example, the Prereq column in the Class table can reference the CCODE columns in the CLASS table to ensure that valid course code (as a prerequisite).

Oracle uses the Cascade delete clause to implement cascading deletions and updates, while SQL Server uses a table trigger to provide the same functionality. For more information, see "SQL Language Support" later in this chapter.

User-defined integrity allows definitions that do not belong to any other integrity category business rules.

Stored procedure

The Microsoft SQL Server stored procedure uses the CREATE Procedure statement, accepts and returns the parameters provided by the user. In addition to the temporary stored procedure, other stored procedures are created in the current database. The following table gives its Oracle and SQL Server syntax.

Oracle

Microsoft SQL Server

Create or replace procedure [(argument [in | out] dattype [, argument [in | out] datatype] {is | as} block

CREATE PROC [EDURE] procedure_name [; number] [{@ parameter data_type} [VARYING] [= default] [OUTPUT]] [, Un] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR REPLICATION] ASsql_statement [Un ]

In SQL Server, the temporary process is created in the TEMPDB database, for local temporary processes, plus a number (#procedure_name) before procedure_name, plus two numbers (## procedure_name) before the global temporary process (## procedure_name).

The local temporary process can only be used by the user created. Permissions to run local temporary processes cannot grant other users. After the user session is over, the local temporary process is automatically deleted.

All SQL Server users can use global temporary processes. If a global temporary process is created, all users can access it, and the permissions cannot be explicitly revoked. After the last user session of the process, the global temporary process is deleted.

The SQL Server stored procedure can nearest 32. The nested stage is incremented when the calling process begins, and the calling process is decremented. The following example is given, how to use the Transact-SQL stored procedure to replace the Oracle PL / SQL packaging function. Transact-SQL version is much simpler, because SQL Server can return a result set directly from the SELECT statement in the stored procedure without using a cursor.

Oracle

Microsoft SQL Server

CREATE OR REPLACE PACKAGE STUDENT_ADMIN.P1 AS ROWCOUNT NUMBER: = 0; CURSOR C1 RETURN STUDENT% ROWTYPE; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER; END P1; / CREATE OR REPLACE PACKAGE BODY STUDENT_ADMIN.P1 AS CURSOR C1 RETURN STUDENT% ROWTYPE ISSELECT * FROM STUDENT_ADMIN.STUDENT WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADEWHERE GRADE.SSN = STUDENT.SSN) ORDER BY SSN; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER ISWORKREC STUDENT% ROWTYPE; BEGINIF NOT C1% ISOPEN THEN Open C1; ROWCOUNT: = 0; Endif; Fetch C1 INTO WORKREC; IF (C1% Notfound) Thenclose C1; RowCount: = 0; elseWorkVar: = Workrec.fname || '|| Workrec.lname ||', Social Security Number '|| Workrec.ssn ||' Is Not Enrolled in Any Classes! '; RowCount: = ROWCOUNT 1; Endif; Return (Rowcount);

CREATE PROCEDURESTUDENT_ADMIN.SHOW_RELUCTANT_STUDENTSAS SELECT FNAME '' LNAME ', social security number' SSN 'is not enrolled in any classes!' FROM STUDENT_ADMIN.STUDENT SWHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE GWHERE G.SSN = S. SSN) Order by ssnreturn @@ rowcountgo

Exceptionwhen Othershenif C1% Isopen Ten Close C1; ROWCOUNT: = 0; Endif; Raise_Application_ERROR (-20001, SQlerRM); End Show_reluctant_students; END P1; /

SQL Server does not support structures similar to ORACLE packages or functions, nor does it support the create or replace option for creating stored procedures.

Delayed stored procedure

Microsoft SQL Server provides Waitfor, which allows developers to specify time, time intervals, or events executed by trigger statements, stored procedures, or transactions. For Transact-SQL, it is equivalent to Oracle's dbms_lock.sleep. Waitfor {delay 'Time' | Time 'Time'}

Here

DELAY

Inform Microsoft SQL Server Waiting until the specified time is over, up to 24 hours.

'Time'

Waiting time. Time can be specified in a format that can be accepted by DateTime data or specified as a partial variable. You cannot specify a date; therefore, the data portion of the DateTime value is not allowed.

Time

Notify SQL Server Wait until the specified time.

E.g:

BeginwaitFor Time '22: 20'Execute Update_all_statsend

Specify the parameters of the stored procedure

To specify the parameters of the stored procedure, use the following syntax:

Oracle

Microsoft SQL Server

Varname DattypedPedefault ;

{@Parameter Data_Type} [varying] [= default] [OUTPUT]

trigger

Oracle and Microsoft SQL Server have triggers, but their implementations are somewhat different.

Description

Oracle

Microsoft SQL Server

The number of triggers for each table

no limit

The trigger is executed before INSERT, UPDATE, and DELETE

Yes

The trigger is executed after INSERT, UPDATE, and DELETE

Yes

Statement level trigger

Yes

Row-level trigger

Yes

Check the constraint before execution

Yes, unless the trigger is disabled.

Yes. In addition, this is an option for the data conversion service.

Reference Update or the old or previous value in the DELETE trigger.

: OLD

Deleted.column

Reference INSERT trigger

: New

INSERTED.COLUMN

Disable trigger

Alter Trigger

Options in the data conversion service

DELETED and INSERTED are the logic (concept) table created by SQL Server as the trigger statement. They are similar to the form structure of the definition trigger and save the old value or new value of the user operations that may change. These tables track the row-level changes in Transact-SQL. These tables provide the same function as the Oracle row trigger. The row is simultaneously added to the trigger table, inserted, and deleted tables when executing Insert, Update, or DELETE statements in SQL Server.

Inserted and deleted tables are identical to the trigger table. They have the same column name and data type. For example, if a trigger is placed on a grade table, the Inserted and Deleted tables have such a configuration.

Grade

Insert

Deleted

SSN Char (9) Ccode Varchar (4) grade varchar (2)

The trigger can check the INSERTED and DELETED tables to determine what kind of operation should be taken. The INSERTED table is used with the INSERT and UPDATE statements. The deleted table is used with the Delete and Update statements.

The UPDATE statement uses the INSERTED and DELETED table, because SQL Server always deletes the old row and inserts a new row when the Update operation is performed. Therefore, executing the UPDATE operation, the row in the Inserted table is always identical. The following example uses the INSERTED and DELETED table to replace the PL / SQL row trigger. A fully external connection is used to query all rows in each table.

Oracle

Microsoft SQL Server

CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADESAFTERINSERT OR UPDATE OR DELETEON STUDENT_ADMIN.GRADEFOR EACH ROWBEGININSERT INTO GRADE_HISTORY (TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADE, NEW_SSN, NEW_CCODE, NEW_GRADE) VALUES (USER, SYSDATE,: OLD.SSN,: OLD.CCODE,: Old.grade,: new.ssn,: new.ccode,: new.grade, end;

CREATE TRIGGER STUDENT_ADMIN.TRACK_GRADESON STUDENT_ADMIN.GRADEFOR INSERT, UPDATE, DELETEASINSERT INTO GRADE_HISTORY (TABLE_USER, ACTION_DATE, OLD_SSN, OLD_CCODE, OLD_GRADENEW_SSN, NEW_CCODE, NEW_GRADE) SELECT USER, GETDATE (), OLD.SSN, OLD.CCODE, OLD.GRADE, NEW .Ssn, new.ccode, new.gradefrom inserted new full = = old.ssn

You can only create triggers in the current database, but you can reference objects outside the current database. If the owner name is used to qualify the trigger, the same method is used to qualify the name of the table.

The trigger can nest 32. If the trigger changes a table on which another trigger there is, the second trigger is started and the third trigger is called, and so on. If a trigger in this chain generates a dead loop, it will exceed the nested stage, and the trigger is canceled. In addition, if the update trigger on the table results in an update to another column, the update trigger is only enabled once.

Microsoft SQL Server Declaration Reference Integrity (DRI) does not provide reference integrity between databases. Use triggers if reference integrity between databases is required.

The Transact-SQL trigger does not allow the following statements:

Create statement (Database, Table, INDEX, Procedure, Default, Rule, Trigger, Schema, and View)

DROP statement (Trigger, INDEX, TABLE, Procedure, Database, View, Default, Rule)

ALTER statement (Database, Table, View, Procedure, Trigger)

Truncate Table

Grant, Revoke, Deny

Update statistics

Reconfigure

Update statistics

Restore Database, Restore log

Load log, Database

DISK statement

Select Into (because it creates a table)

For more information on the trigger, see SQL Server Books Online. Transaction, locking and concurrency

This section explains, in Oracle and Microsoft SQL Server, the transaction is executed and reveals the difference in locking procedures and concurrency issues in the two databases.

In Oracle, the transaction is automatically started when inserting, updating, or removing operations. To save all changes to the database, the application must perform a commit command. If you do not perform a commit, all changes are rolled back or automatically.

By default, Microsoft SQL Server automatically performs a CommT statement after each insertion, update, or delete operation. Because the data is automatically stored, no changes can be rolled back. You can use a hidden or dominant transaction mode to change this default behavior.

The hidden transaction mode allows SQL Server to operate as Oracle, which can be launched by the SET IMPLICIT_TRANSACTION ON statement. If this option is ON, and there is no pending transaction, each SQL statement will automatically launch a transaction. If you have an open transaction, you can't start a new transaction. To make all changes to take effect and release all locks, the user must explicitly submit the open transaction using the commit transaction statement.

The dominant transaction is a set of SQL statements that enclose the following transaction points:

Begin Transaction [Transaction_name]

Commit transaction [transaction_name]

Rollback Transaction [Transaction_Name | SavePoint_name]

In the following examples, the ENGLISH is changed to the Literature system. Note the usage of Begin Transaction and Commit Transaction statements.

Oracle

Microsoft SQL Server

INSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ( 'LIT', 'Literature') / UPDATE DEPT_ADMIN.CLASSSET MAJOR = 'LIT'WHERE MAJOR =' ENG '/ UPDATE STUDENT_ADMIN.STUDENTSET MAJOR =' LIT'WHERE MAJOR = ' ENG '/ Delete from DePt_admin.deptwhere Dept =' ENG '/ COMMIT /

BEGIN TRANSACTIONINSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ( 'LIT', 'Literature') UPDATE DEPT_ADMIN.CLASSSET DEPT = 'LIT'WHERE DEPT =' ENG'UPDATE STUDENT_ADMIN.STUDENTSET MAJOR = 'LIT'WHERE MAJOR =' ENG 'Delete from dept_admin.deptwhere dept =' eng'commit TransactionGO

All dominant transactions must be included in the Begin Transaction ... commit transaction statement. Save Transaction and Oracle SavePoint statements The effect is the same, that is, set a save point for transaction to allow sections to roll. It can be nested between the transactions. If so, the outermost nested is created and submitted to the column of tracking. When you encounter a nested transaction, the @@ Transaction function is incremented. Typically, this obvious transaction nested occurs when the stored procedure or trigger is called with each other. Although the transactions can be nested, they have no impact on the behavior of the Rollback Transaction statement.

In a stored procedure or trigger, the number of beac transaction statements must be consistent with the number of commit transaction statements. An error message is generated when the stored procedure or trigger is executed when the non-pair of Begin Transaction is executed. If you include a Begin Transaction and a CommT Transaction statement, the syntax allows the stored procedures and triggers from the transaction.

As long as it is possible, big transactions should be divided into smaller transactions. Make sure that in a batch of jobs, each transaction is explicitly defined. To minimize possible concurrency conflicts, the transaction should not span multiple batch jobs, nor should you wait for user input. Combine multiple Transact-SQL statements to a long-running transaction, which will negatively affect recovery time and cause concurrency issues.

When using ODBC programming, you can use the SQLSETCONNECTOPTION function, select a hidden or dominant transaction mode. The ODBC program selects this or that mode, depending on the AutoCommit connection option. If AutoCommit is set to ON (default), it is in a dominant mode. If AutoCommit is set to OFF, it is in a hidden mode.

If you use the SQL Server query analyzer or other query tool to perform a script, you can include the explicit Begin Transaction statement shown in the script, or start the script with the SET IMPLICIT _TRANSACTION ON statement. Begin Transaction methods are more flexible, but implicit methods are better than Oracle compatibility.

Oracle and Microsoft SQL Server lock and quarantine strategies are different. When you migrate your application from Oracle to SQL Server, you must consider these differences to ensure scalability of the application.

For all SQL statements of all read data, Oracle explicitly or implicitly uses multiple versatile consistency models. In this model, by default, before reading the data line, the data reader is neither locked and does not wait for other locks released. When the data to be requested by the reader has been changed by other writers but not submitted, Oracle rebesses a row snapshot using its rollback segment to recreate old data.

The data writer in Oracle requests to lock the data updated, deleted, or inserted. These locks have been kept until the end of the transaction, prevent other users from overwriting uncommitted changes.

Microsoft SQL Server has a multi-granular lock function that allows a transaction to lock various types of resources. To minimize the lock overhead, SQL Server automatically locks resources at the corresponding level of the task. Locking on smaller particle size (eg, line), improves concurrency, but the overhead is larger, because if you want to lock multiple lines, you must maintain more locking. Lock on a larger particle size (eg, table), from the perspective of concurrency is high, because the locks of the entire table limit access to any part of the table, but it has a small overhead because only There is a need to maintain less lock. SQL Server can lock the following resources (arranged in order of granularity). Resource

Description

RID

Row identifier. Used to lock a single line table separately.

Key

The row of keys and indexes are locked. The range of protection of a serial transaction is used.

Page

8 kb data page or index page.

Extent

Group of adjacent eight data pages or index pages.

TABLE

Out of the table, including all data and indexes.

database.

SQL Server uses a different mode to lock resources, which determines how concurrent transactions access resources.

Lock mode

Description

Sharing (S)

An operation (read-only operation) for not change or update data, such as a SELECT statement.

Update (U)

Used for updatable resources. Prevent a normal form of deadlock, which occurs when multiple sessions are read, locked and then possible to update the resource.

Row it (X)

For data modification operations, such as Update, INSERT, or DELETE. Make sure you cannot do multiple updates to the same resource at the same time.

Intent

Used to establish a lock hierarchy.

Architecture

It is used when performing operations depending on the operation of the table architecture. There are two types of architectural locks: architectural stability (SCH-S) and architectural modification (SCH-M).

In any RDBMS, it is important to quickly release the lock to provide maximum concurrency. You can shorten your transactions as possible to ensure fast release lock. If possible, transactions should not cross multiple round-trip operations of the server, and should not include the user "think" time. If you use a cursor, you also need to write an application to quickly extract the data because the unated data scan may occupy the shared lock on the server, which hinders the update to update. For more information, see "Using ODBC" later in this chapter.

Both Microsoft SQL Server and Oracle allow developers to request non-default locking and isolation behavior. In Oracle, the most common mechanism is the for Update clause on the select command, the SET Transaction Read Only command, and the explicit Lock Table command.

Because Oracle and SQL Server's lock and isolation mechanism are completely different, it is difficult to directly correct these lock options directly between the two. It is important to better understand this process, understand the options of SQL Server to change their default locking behavior.

In SQL Server, changing the most commonly used mechanism for default lock mode is the SET Transaction ISOLATION LEVEL statement, and the lock prompt supported in the SELECT and UPDATE statements. Set Transaction Isolation LEVEL statement Sets the transaction isolation level during the user session. This is the default behavior of the session unless the lock prompt is specified in the table level in the FROM clause of a SQL statement. Transaction isolation This setting:

SET Transaction Isolation Level {Read Committed | REPEATABLE READ | Serializable}

Read committed

The default isolation level of SQL Server. When using this option, the application cannot read data that has not been submitted by other transactions. However, in this mode, the shared lock is released as long as the data is read from the page. If the application re-reads the same data range in the same transaction, it will see another user changes. Serializable

This option is set, and the transaction is isolated from each other. If you don't want to see other users in the query, you should set the transaction isolation level to serializble. SQL Server keeps all shared lock until the end is over. In the SELECT statement, use the HoldLock prompt after the table name, you can get this effect on a thinner level. With these two options, while ensuring strict consistency, it also reduces concurrency, so it is only used if necessary.

Read uncommitted

Set this option, SQL Server readers will be unimpeded, this is the same as Oracle. This option implements dirty or isolation level 0 to lock, which means that it does not send a sharing lock and does not support the lock. When this option is set, you can read uncommitted or "dirty" data; before the end of the transaction, the value in the data can be changed, and the rows in the data set may appear or disappear. This option has the same effect as the ONLOCK all tables in all SELECT statements in the transaction. This is the least limited in the four isolation levels. This isolation level can only be used after thoroughly analyzing how it affects the accuracy of the results in the application.

SQL Server supports Oracle Read Only features in two ways:

If some of the transactions in the application need to read behavior repeatedly, you may need to use the SQL Server supplied SELIALIZABLE level.

If all database access is read-only, you can set the SQL Server database option to Read All to improve performance.

Oracle's SelectuFor Update statement is primarily used when the application needs to perform positioning updates or delete cursors with WHERE CURRENT OF syntax. In this case, you can selectively delete the for Update clause because the Microsoft SQL Server cursor can be updated by default.

By default, the SQL Server cursor does not retain the lock of the extracted row. SQL Server uses an optimized concurrency policy to prevent updates from covering each other. If the user wants to update or delete a row, the line has been changed since reading the cursor, then SQL Server issues an error message. The application can capture the error message, retry the update or delete accordingly. To replace this behavior, developers can use scroll_locks in the cursor statement.

Under normal circumstances, there are very few conflicts between updates, and optimistic concurrency strategies support high concurrency. If the application does need to make sure the row cannot be changed after the extracted, use the UPDLOCK prompt in the SELECT statement. This prompt does not obstruct other readers read, but it prevents other potential writers from getting the update lock on the data. When using ODBC, you can use SQLSETSTMTOPTION (U, SQL_CONCURRENCY) = SQL_CONCUR_LOCK to achieve this effect. However, these two options reduce concurrency.

Microsoft SQL Server can lock the entire table using the Selectable_name (Tablock) statement. It is the same as the ORACLE LOCK TABLEUIN Share Mode statement. This lock allows others to read tables, but they prohibit them from updating. By default, the lock has been kept until the end of the statement. If you have also added keyword holdlock (selectutable_name (Tablock Holdlock), the table lock has been kept until the end of the transaction. You can use the selectutable_name (Tablockx) statement to set the row to lock on the SQL Server table. This statement requests the discharge of the table to lock. It is used to prevent others from reading or updating tables and keeping it until the end of the command or the end. It is similar to the functionality of the Oracle Lock Tableuin Exclusive Mode statement.

In the explicit lock request, SQL Server does not provide the NOWAIT option.

The Microsoft SQL Server automatically generates page level lock when the query is requested from the table. However, if the query request occupies a large proportion in the table, SQL Server upgrades the lock from the page level to the table level. This process is called "Lock Upgrade".

Locking upgrade makes table scans and more efficient to large result sets, because it reduces lock overhead. SQL statements without WHERE clauses typically cause lockup upgrades.

During the read operation, the shared table lock is used when the shared page lock is upgraded to a table lock. Shared table lock is applied to:

When using the Holdlock or Set Transaction ISOLATION Level Serializable statement.

The optimizer is selected when a table scan is selected.

The cumulative number of shared locks in the table exceeds the lock upgrade limit.

Lock upgrade limit defaults to each table 200, but can also customize the minimum and maximum limits according to table size. The shared table lock is also used when creating a non-aggregated index. For more information on locking upgrade limits, see SQL Server Books Online.

During the write operation, a UPDATE lock is upgraded to a table lock, then the TabLockx is applied. Row it table locks to:

The update or delete operation does not have an index.

The number of tables with rows it is smothering the page exceeds the lock upgrade limit.

Create a gathering index.

If Oracle cannot upgrade the row lock, there may be problems in some queries containing the for Update clause. For example, assume that the Student table has 100,000 row data, and an Oracle user performs the following statements:

Select * from student for Update

This statement forces Oracle RDBMS to lock the Student table once; this may take a long time. It does not upgrade the request to lock the entire table.

In SQL Server, the same query is:

Select * from stay (UPDLOCK)

When the query is running, the page level lock is upgraded to the table-level lock, and it queries more efficient and the speed is much faster.

Dead lock

When a process locks the page or table required by another process, the latter is locked when the front of the frontier needs. The deadlock is also called "deadlock". SQL Server automatically detects and processes deadlocks. If a deadlock is found, the server will terminate the user process in "deadlock".

After each data is modified, the program code should detect the message number 1205, which is used to indicate a deadlock. If this message number is returned, a deadlock has occurred and the transaction is rolled back. In this case, the application must restart the transaction.

Use some simple skills to avoid dead locks:

Each part of the application uses the same order access table. Use the aggregation index on each table to implement explicit line sorting.

Keep your transaction short.

For more information, see Microsoft Knowledge Base: Detecting and Avoiding Deadlocks in Microsoft SQL Server (Microsoft SQL Server Detection and Avoiding).

In Oracle, to perform a remote transaction, you must be able to use the database link to access the remote database node. In SQL Server, you must access a "remote server". The remote server is a server running SQL Server on the network, and the user can access it using the local server. When a server is set to a remote server, the user can use the system procedure and stored procedure on it without explicitly logging.

The remote server is set to pair. The two servers must be configured so that each other as a remote server. You must use the sp_addlinkedServer system stored procedure or SQL Server Enterprise Manager to add the name of each server to its partner name.

After setting the Remote server, use the sp_addremotelogin system stored procedure or SQL Server Enterprise Manager to set the remote login ID for users who must access the remote server. After this step is completed, you must grant this permission to perform the stored procedure.

Then, use the Execute statement to run the procedure on the remote server. The following example executes the validate_student stored procedure on the remote server Studsvr1, and stores the return status indicating success or failure in @ RetValue1:

Declare @ RetValue1 INTEXECUTE @RetValue = studsvr1.student_db.student_admin.validate_student '111111111'

For more information, see SQL Server Books Online.

If you change the table on two or more network database nodes, Oracle will automatically start a distributed transaction. SQL Server distributed transactions use two phases submitted services that are included in the Microsoft Distributed Transaction Coordinator (MS DTC) in SQL Server.

By default, SQL Server must be used to participate in distributed transactions. You can use one of the following methods to start SQL Server to participate in the MS DTC transaction:

Use the Begin Distribute Transaction statement. This statement starts a new MS DTC transaction.

Use the client application that directly calls the DTC transaction interface.

In this case, note the distributed update of the local table Grade and remote table Class (using the class_name process):

BEGIN DISTRIBUTED TRANSACTIONUPDATE STUDENT_ADMIN.GRADESET GRADE = 'B ' WHERE SSN = '111111111' AND CCODE = '1234'DECLARE @ retvalue1 intEXECUTE @ retvalue1 = CLASS_SVR1.dept_db.dept_admin.class_name' 1234 ',' Basketweaving'COMMIT TRANSACTIONGO

If the application cannot complete this transaction, the application will use the Rollback Transaction statement to cancel it. If the application fails or participating resource manager fails, the MS DTC will cancel this transaction. MS DTC does not support distributed save points or Save Transaction statements. If a MS DTC transaction terminates or rolls back, the entire transaction is rolled back to the beginning of a distributed transaction, no matter how many save points are available. Oracle and MS DTC two-stage submission mechanism is similar to operation. In the first phase of the SQL Server two-stage submission, the Transaction Manager requests each participated resource manager to prepare for submission. If any resource manager cannot be prepared, the Transaction Manager terminates the decision to each person involved in the transaction.

If all resource managers can be successfully prepared, the Transaction Manager Broadcast is determined. This is the second phase of the submission process. When the resource manager is prepared, it does not know that the transaction will be submitted or terminated. The MS DTC has an ordered log that can be permanently saved or terminated. If the resource manager or transaction manager fails, there are questions that can be reprocessing when they reconnect.

转载请注明原文地址:https://www.9cbs.com/read-103216.html

9cbs

New Post(0)