Transplanting Oracle Database to Microsoft SQL Server 7.0 Summary: This article is written for developers who want to convert their Oracle applications to Microsoft SQL Server applications. This article describes a tool, process, and technique required for successful conversion. At the same time, it emphasizes the basic design elements of establishing high performance, highly parallel SQL Server applications. The readers of this article should have:
The solid foundation of the Oracle relational data management system (RDBMS). Ordinary database management knowledge. Familiar with Oracle SQL and PL / SQL languages. Work experience of C / C programming language. Member qualifications for setting server rules in the sysadmin group assumes that you are familiar with Oracle Rdbms terms, concepts, and tools. If you want to know more about oracle rdbms and its structure, please refer to Oracle 7 Server Concepts Manual. For use Oracle scripts and examples, you still assume that you are familiar with Oracle Server Manager and Oracle SQL * Plus tools. To get more detailed information, please see the Oracle documentation. table of Contents
Development and Application Platform Overview The Organized Form Structure and Terminology of this article Install and configure Microsoft SQL Server Define Database Object Enhanced Data Integrity and Business Rule Transactions, Locking and Parallel Dead Lock SQL Language Support Cursor Adjustment SQL Statement Using ODBC Development and Management Database Copy your data and application database Delivery development and application platform In order to make it clear, the development and application platform referred to this article assumes Microsoft Visual Studio Version 6.0, Microsoft Windows NT Version 4 (Service Pack 4), SQL Server 7.0, Oracle 7.3. Oracle 7.3 uses Visigenic Software ODBC (Version 2.00.0300) driver, SQL Server 7.0 is driven using Microsoft Corporation ODBC (version 3.70). Microsoft SQL Server 7.0 includes OLE DB drivers for Oracle, but the driver does not have a wide range of discussions in this chapter. Overview The transplant of the application seems very complicated. There are too many structural differences between different relational data management systems. The words and terms used to describe the Oracle structure are usually completely different from the meaning in Microsoft SQL Server. In addition, Oracle and SQL Server have made many self-extensions for SQL-92 standards. From an application developer's point of view, Oracle and SQL Server are managed in a similar approach. There is a major internal difference between Oracle and SQL Server, but if managed, these differences can be minimized. The most important migration problem facing SQL language extension developers is to implement SQL-92 language standards and language extensions provided by each relational data management system. Some developers only use standard SQL syntax, like to maintain the universality of their program code as much as possible. Typically, this method limits the program code on the entry-level of the SQL-92 standard, and this level is implemented by many database products, including Oracle and SQL Server. This method will produce some unnecessary program code complexity and will also have a big impact on the performance of the program. For example, Oracle's Decode function is a non-standard SQL extension. Microsoft SQL Server's CASE expression is a SQL-92 extension that exceeds the login level and is not implemented in all other database products. Oracle's Decode and SQL Server Case are optional, you can implement them with these two functions, which requires more data from the relational data management system. Also, the program extension to the SQL language will also cause difficulties. Oracle's PL / SQL and SQL Server Transact-SQL language is similar to the function, but is different in syntax. There is no clear symmetry in the middle of the two databases and programs. Therefore, you may decide whether you do not use the stored programs such as the programs and triggers. This is unfortunate because they provide anything else to achieve the advantages of performance and security. The use of private development interface has brought new problems. Program conversion with Oracle OCI (Oracle Call Interface) usually requires many resources. Developing an application that may use multiple relational data management systems, it is best to consider using an Open Database Connectivity (ODBC) interface. ODBCODBC is designed to work with multiple database management systems.
ODBC provides a consistent application programming interface (API) that works with different databases using a driver for the database. The consistent application programming interface means that the program is used to establish a connection, execute command, and obtain the result of the result, whether the program is or the SQL Server dialog. ODBC also defines a standardized call level interface and uses standard export orders for SQL functions that do the same tasks in different databases but different syntaxes. The ODBC driver can automatically convert this ODBC syntax into Oracle or SQL Server locale, this process does not need to make any revisions on the program code. In some cases, the best way is to write a program and let the ODBC perform conversion processing at runtime. ODBC is not an universal, complete and high-performance solution that can be fully independent, complete and high performance for any database. Different databases and third-party dealers provide support for different levels of ODBC. Some drivers simply implements the core API function, which maps the top or other interface libraries. Some other drives, such as Microsoft SQL Server drives, providing complete level 2 support in a local, high-performance drive. If a program only uses the core ODBC API, it is likely to discard the features and performance of certain databases. In addition, not all local SQL extensions can be described in the ODBC outlet order (such as the Case expression of Oracle Decode and SQL Server). In addition, writing SQL statements to utilize database optimizer is a natural thing. However, in Oracle's technology and methods for extending database performance in Microsoft SQL Server 7.0 is not necessarily the best. The ODBC interface does not translate the techniques used in a system into another system. ODBC does not affect an application to use database exclusive features and adjustments to improve performance, but applications require certain database exclusive code parts. ODBC makes it easy to keep program structures and most program code between multiple databases. OLE DBOLE DB is the next generation of data access technology. Microsoft SQL Server 7.0 utilizes OLE DB included in its own components. In this way, application developers can consider using OLE DB to develop new SQL Server 7.0 development. Microsoft also provides OLE DB supporting Oracle 7.3 in SQL Server 7.0. OLE DB is a strategic system-level programming interface for Microsoft to manage data across the organization. OLE DB is an open design established in ODBC characteristics. ODBC is a design to access related databases, while OLE DB is designed to access relevant or unrelated information sources, such as ISAM / VSAM and hierarchical databases, email, and file system storage, text, images, and Geographic data and custom business objects. OLE DB has a set of COM interfaces to compress different database management services while also allowing software components to implement these services. The OLE DB component contains data providers (holding and revealing data), data consumers (using data), and service components (processing, and transfer data, such as query processors and cursor engines). The OLE DB interface design is to help implement the smoothing of components, so that OLE DB component providers can quickly provide high quality OLE DB components to the market. In addition, OLE DB also includes a bridge that connects to ODBC, so that you can continue to support the large number of ODBC-related database drivers that you can get today. This article provides an overview of each part of this article with an overview of Oracle7.3 and Microsoft SQL Server 7.0 in order to help you achieve the conversion of Oracle to SQL Server. It also includes conversion considerations, advantages of SQL Server 7.0, and multiple instances.
Structure and Terminology As the beginning of successful transplant, you should master the basic structures and terms used by Microsoft SQL Server 7.0. Many examples in this section are intercepted from the Oracle and SQL Server applications contained in this article. Database definitions In Oracle, the database refers to the entire Oracle RDBMS environment and includes the following components. Oracle Database Processing Process and Data Cache (instance). Contains a centralized system directory of System table space. Other tablespaces defined by DBA (optional). Two or more redo logs. Archive REDO log (optional) Various other files (control files, init.ora, etc.).
A Microsoft SQL Server database provides logic distinguishing between data, applications, and security mechanisms, more like a table spaces. As Oracle supports multiple tablespaces, SQL Server also supports multiple databases. Table space is also used to provide physical placement of data, SQL Server provides the same functionality through file groups (fileGroups). Microsoft SQL Server will install the following databases default.
The Model database is a template for all newly built user databases. The tempted table space in the Tempdb database is very similar to the temporary table space for temporary work storage and sort operation. Different, when the user quits login, Microsoft SQL Server automatically deletes the temporary table space created. The MSDB database supports the SQL Server agent and its predetermined work, alerts, and replication information. Northwind and Pubs Database are an instance database for training. If you want to get more information about the default database, please see the SQL Server online book. Database System Directory Each Oracle database is running on a centralized system directory (or data dictionary), which exists in the System tablespace. Each Microsoft SQL Server 7.0 database maintains a system directory, which contains the following information: Database objects (tables, indexes, storage programs, views, triggers, etc.). Constraints. Users and licenses. User defines data types. Replication definition. The file used by the database. SQL Server saves a centralized system directory in the Master database, which contains some information about the system directory and each database:
The database name and the initial file location of each database. SQL Server login account. system information. Database configuration value. Remote and / or connected servers. Current activity information. System stored procedure. Like the System table space in Oracle, SQL Server's Master database must also access any other database. Similarly, it is important to prevent failure by backing up the Master database for any major changes. Database administrators should also be able to make mirrors for files that make up the Master database. Physical and Logical Storage Structures Oracle RDBMS consists of table space, while tablespace consists of data files. The tablespace data file is formatted into internal block units. The size of the block is set by DBA when it is created in Oracle, can be varied within 512 to 8192 bytes. When an object is created in the Oracle table space, the user uses a unit called the length (initial eXtent), next eXtent, minimum length (min extents), and max extents) To indicate the space size of the object. The size of an Oracle length can vary, but to include a chain consisting of at least five consecutive blocks. Microsoft SQL Server uses the file group to control the physical placement of the table and index in the database level. File The group is a logical container of one or more files, and the data in a file group fills all the files belonging to the file group. If there is no clear definition and use file group, the database object will be placed in a default file group. This file group is implied in the creation of the database. The file group allows you to do the following: distribute large tables in multiple files to increase I / O throughput. Store index in different files Instead of being placed in their respective tables, increase I / O throughput and implement disk parallel operation. Temx TEXT, NTEXT, and Image Column are stored in different files in a table. Place the database object A specific disk ingot. Back up and restore individual tables and tables in a file group. SQL Server format files into units called page (Pages). The size is fixed to 8192 bytes (ie 8K). Page is fixed to the format of the 8 Continuous page size. When you create a table or an index, SQL Server automatically assigns a page, which is stored in a smaller table and index. This method is more effective. Striping data (Strip - In mass storage system (MSS), the part of the data cassette tape accessed by the given head position) Oracle type segment Most Microsoft SQL Server installations don't need. Instead, SQL Server can use hardware-based RAID or Windows NT software RAID to complete data distribution or tag. Hardware-based RAID or Windows NT software RAID can Set up a tag device consisting of multiple hard drives, which look like a logical drive. If the database file is created on this tag device, the disk subsystem assumes that the distributed is performed by multiple disks. I / O load. It is recommended that administrators use RAID to distribute data on multiple physical disks. The RAID recommended configuration for SQL Server is RAID 1 (mirror icon) or RAID 5 (with an additional drive as redundant) equipment).
RAID 10 (mirror icon with parity) is also recommended, but it is more expensive than the first two. Marking equipment is very good in scattered I / O on a dispersion database file. If you can't use RAID, the file group is an attractive choice, which provides some of the same benefits available by RAID. In addition, file groups may be a good choice for those that may span multiple physical RAID arrays, which can be distributed on multiple RAID arrays by a controlled manner. Transaction log files must be optimized to adapt to continuous I / O and must protect the file to prevent single point failure. Therefore, it is recommended to use RAID1 (mirror icon) to do business logs. The size of the drive should be at least in the size of the online recovery log, and the inverse segment table space added. Create one or more log files to spend the space defined on the logical drive. Unlike the data stored in the file group, the transaction log entries are always written in sequential instead of score. For more information on RAID, please see SQL Server online books, your Windows NT server documentation, and Microsoft Windows NT Resources Guide. Transaction Logs and Automatic Recovery Oracle Rdbms Performs Auto Repair at each startup. It checks if the directory of the table spatial file is the same as the directory in the online recovery log file. If not the same, Oracle uses the online recovery log file to overwrite the tablespace file (Roll Forward, the front roll), then remove all the unfinished transactions found in the rear roll (Roll Back, rear roll). If Oracle cannot get information required from the online recovery log, Oracle helps the archive recovery log file. Microsoft SQL Server 7.0 also performs automatic recovery by checking each database in the system each time startup. It first checks the Master database and then starts the thread to override all the databases in the system. For each SQL Server database, the automatic repair mechanism will check the transaction log. If you contain any unfinished transactions in the transaction log, the transaction rolls. Then automatically repair the mechanism to check the transaction log to identify unfinished transactions that have not been written to the database. If it is found, the transaction is executed, the front roll. The SQL Server transaction log contains the total functionality of both Oracle and Oracle online recovery logs. Each database has its own transaction log, which records any changes occurred in the database, and the log is shared by all users of the database. When a transaction begins and a data modification occurs, a begin transaction event (like the Modification event) is recorded in the log. Use this event during automatic recovery to determine the starting point of the transaction. Each time you receive a data modification event, the change is recorded in the transaction log, prioritize in its database. For more information, please refer to the "Transaction, Locking and Parallel" section behind this chapter. SQL Server has an automatic checkpoint mechanism that ensures that the completed transaction rule is written to the transaction log file from the SQL Server disk cache. From the previous checkpoint of the database, any modified cache page will be written to a checkpoint. Write the checkpoint to these cache pages (Dirty Pages, Dirty Pages) to ensure that all completed transactions are written to disk. This process shortens the time of repairing the system when the failure (for example, energy loss, power outage) is shortened.
This setting can be modified with SQL Server Enterprise Manager and use Transact-SQL to modify (SP_Configure System Storage Programs). Backup and Recovery Data Microsoft SQL Server provides several options for backup data: Full database backup To perform full database backup, use your backup Database statement or Backup Wizard. Differential Backup After a complete database backup, the Backup Database With Differential statement or "Backup Wizard" is regularly used to back up the changed data and index page. Transaction Log Backup The transaction log in Microsoft SQL Server has a standalone database. The database is only filled only after backup or delete. The default setting in SQL Server 7.0 is the automatic growth of the transaction log until it runs out all available space or to achieve the largest space it set. When the transaction log is full, it generates an error and blocks any data modifications until the log is backed up or deleted. Other databases will not be affected. You can use the Backup Log statement or "Backup Wizard" for transaction log backup. Files or file groups SQL Server can back up files or file groups. For more information, please see SQL Server online books. Backups can be performed when the database is being used, so that those that must be constantly running will be backed up. The backup process and data structure of SQL Server 7.0 have greatly improved, which allows the backup to reach the maximum data transfer rate in the case where the transaction throughput is less. Oracle and SQL Server require a special log file format. In SQL Server, these files called backup devices are created with SQL Server Enterprise Manager, Transact-SQL sp_addumpDevice store or equivalent SQL-DMO commands. Although the backup can be performed by manual operation, it is recommended that you use SQL Server Enterprise Manager and / or "Database Maintenance Plan Wizard" to perform regular backups, or backed up based on database activity. Applying transaction log backups and / or differential backups, a database can be stored on a specific point of a fully backup database (device) on time. Database Use the information contained in the backup to recover data. You can recover with SQL Server Enterprise Manager, Transact-SQL (Restore Database), or SQL-DMO. Just like you can turn off the Oracle Archimon to skip backups, in Microsoft SQL Server, members in the DB_OWner group can force the transaction log to erase the directory when the checkpoint occurs. You can use the SQL Server Enterprise Manager (Log on the Checkpoint), Transact-SQL (SP_DBOPTION stored procedure) or SQL-DMO to complete. Network Oracle SQL * NET supports network connections for Oracle databases and its clients. They communicate via transparent network layer data stream protocol and allow users to run many different network protocols without having to write any special code. SQL * NET is not included in the core Oracle database software product. In Microsoft SQL Server, the NET library (network library) supports the client and server connections, which communicate through the list data stream protocol.
This allows clients that can be simultaneously and running the named Pipes, TCP / IP sockets, or other interactive processing mechanisms (Internet Process Communication, IPC). The SQL Server CD-ROM contains all client NET libraries and does not need to purchase these products. The SQL Server Net library option can be modified after installation. The client network tool configures the default NET library and server connection information for clients running Windows NT, Windows 95, or Windows 98. All ODBC clients also use the same NET library and server connection information unless explicitly indicated in the ODBC data source installation or in the ODBC connection string. For more information on the NET library, please see the SQL Server online manual. Database Security and Roles (Database Security and Roles) In order to make your Oracle application to Microsoft SQL Server 7.0, you need to understand how SQL Server implements the security and roles of the database. The login account login account allows a user to access SQL Server data or management options. The login account allows the user to just log in to SQL Server and display those databases that can be accessed by guests. (Guest account is not default, you must create) SQL Server provides two types of login security. Windows NT Verification Mode (also known as integrated) and SQL Server authentication mode (also known as standard). SQL Server 7.0 also supports the combination of standards and integrated security, called mixing. Windows NT Verification Mode uses Windows NT built-in security mechanism when verifying login connections, and relying on users' Windows NT security trust. The user does not need to enter the login ID and password for SQL Server - its login information is directly connected from the network connection. When a connection occurs, an entry is written to the Syslogins table and verifies between Windows NT and SQL Server. This way is called trusted connection, and its working principle is the same as the trusted relationship between two Windows NT servers. This feature is similar to the Identified External Option option for user accounts in Oracle. SQL Server Verification Mode requires a user to enter the login ID and password when the user requests access to SQL Server. This way is called untrustworthy. This feature is similar to the Identified by Password option for user accounts in Oracle. Using standard security mode, log in to only the ability of users to access the SQL Server database engine, and do not allow users to access user databases. For more information on security mechanisms, please see the SQL Server online manual. Groups, Roles, And Permissions Microsoft SQL Server and Oracle are licensed to enhance database security. SQL Server uses a grade license to limit the ability to create a new database object. (Like Oracle) SQL Server also provides an object-level license. Like Oracle, the object-level ownership is the founder assigned to the object and cannot be transferred. The license must be given to other users before accessing the object. SYSADMIN Fixed Server Role, DB_OWNER fixed database role, or members of the DB_SecurityAdmin fixed database role can also give other users licenses for a user object. SQL Server Stage and Object Level Licensing can give database user accounts directly. However, a simpler method is to give the database role administrator license. The SQL Server role is used to give or revoke the privilege of the database user group (very similar to the Oracle role).
Roles is a database object with a special database. There are some fixed server roles each time, these characters are working on the database. An example of a fixed server role is sysadmin. The Windows NT group can also be logged in as SQL Server, just like database users. Licensing can give a Windows NT group or a Windows NT user. A database can have any number of roles or Windows NT groups. The default public role can always be found on any database, which cannot be cleared. The function of the public role is very similar to the public account in Oracle. Each database user is a member of the public role. In the public role, a database user can also be a member of any number of roles. Windows NT users or groups can also be members of any number of roles, and they are also members of the public role. Database User and Guest Accounts (Database Users and The Guest Accent) In Microsoft SQL Server, a user login account must be authorized to use a database and its object. The login account can be accessed by one of the following methods: The login account can be set to a database user. The login account can be used in the database using a guest (GUEST) account. A WINDOWS NT group login can be mapped to a database role. As a single Windows NT account for the group, you can connect to the database. DB_OWNER or DB_ACCESSADMIN role or member of the sysadmin fixed server role can create a database user account role. An account can contain some parameters: SQL Server login ID, database username (optional), and a role name (optional). The database username is not necessarily the same as the user login ID. If a database username is not provided, the user's login ID and database username are the same. If a role name is not provided, the database user is only a member of the Public role. After creating a database user, the user can assign any role as needed. A member of the DB_OWNER or DB_ACCESSADMIN role can also create a guest account. The guest account allows any valid SQL Server login account to access a database, and even if there is a database user account. By default, the guest account inherits privilege assigned to the public role; however, these privileges can be modified to privileges that are more than or less Public accounts. A Windows NT user or a group account can be given the right to access the database, just like the SQL Server login can do. If a member of a Windows NT group is connected to the database, the user receives a license assigned to this group. If the user is a member of more than one Windows NT group, he will receive a collection of all of these groups of privileges. The Sysadmin Role (The Sysadmin Role) Microsoft SQL Server Sysadmin Fixed server role has access to members in the Oracle DBA group. In SQL Server 7.0, if SQL Server is installed on a Windows NT computer, the account logged in with the SA SQL Server verification mode is default for the role member, which is a member in the local administrator group. Members in a sysadmin role can add or delete Windows NT users and groups, and SQL Server login account. Typical members of the role have the following responsibilities: Install SQL Server. Configure the server and client. Create a database. * Set up login authority and user license. * Import data or export the SQL Server database. * Backup and restore the database. * Execute and maintain replication. Arrange unmanned operations. * Monitor and debug the performance of SQL Server.
* Diagnostic system problem. * These projects can delegate other security roles and users. In SQL Server 7.0, there is no limit to members of members in the sysadmin fixed server role. Therefore, the member in this role can access any database through a special SQL Server instance, all objects (including data). Like an Oracle DBA, there are some commands and system programs to be only members in the sysadmin role. DB_OWNER Role Although the Microsoft SQL Server database is similar to Oracle table space, they are managed differently. Each SQL Server database is a self-sustain management domain. Each database indicates the database owner (DBO). The user is always a member of the DB_OWNER fixed database role. Other users can also be a member of the DB_OWNER role. All members in this role can manage management tasks related to his database. (Unlike in Oracle, DBA manages management tasks for all databases). These management tasks include: Managing database access. Modify the database settings (read-only, single users, etc.). Backup and restore the database directory. Grant and cancel the database license. Create and delete database objects. Members in the DB_OWNER role can do anything on their database. Most of the rights assigned to this role are given to some fixed database roles, or can also give database users. Exercising DB_OWNER privileges on a database does not need to give the sysadmin server range privilege. Installing and configuring Microsoft SQL Server After understanding the basic structural differences between Oracle and SQL Server, you can start the first step in the transplant process. SQL Server Query Analyzer will run the following script: Use Windows NT software-based RAID or hardware-based RAID fifth level to create a logical drive that puts down your data. Estimation of space can be performed by calculating the file space size occupied by the Oracle system, temporary file, and application table space. Use Windows NT software-based RAID or hardware-based RAID first level to place a second logical drive to release the transaction log. The size of the driver should be consistent with the total size of the rear roll space. Use SQL Server Enterprise Manager to create a database with an Oracle application table space name. (The database name used by the sample application is called user_db) indicates that the file location makes them match the disk position created by the data and transaction in the first step and the second step. If you use multiple Oracle tablespaces, you don't need it. You don't need to create multiple SQL Server databases, RAID will automatically assign you. Create a SQL Server login account:
Use master exec sp_addlogin student_admin, student_admin exec sp_addlogin dept_admin, dept_admin exec sp_addlogin enduser1, Enduser1 Go Add a role to the database:
Use user_db exec sp_addrole data_admin Exec sp_addrole user_logon GO Grants a license:
Grant Create Table, Create Trigger, Create View, Create Procedure to Data_admin Go adds a login account for the database user account:
EXEC SP_ADDUSER ENDUSER1, ENDUSER1, USER_LOGON EXEC SP_ADDUSER DEPT_ADMIN, DEPT_ADMIN, DATA_ADMIN EXEC SP_ADDUSER Student_admin, Student_admin, Data_admin Go Defines Database Objects
Oracle Database objects (tables, views, and indexes) can be easily ported to Microsoft SQL Server because the two databases basically follow SQL-92 standards, which recognizes object definitions. Conversion of Oracle SQL's table, index, and view to SQL Server's table, index, and view definitions only need to do a relatively simple syntax change. The following table indicates some differences between the database objects between Oracle and Microsoft SQL Server.
Category Microsoft SQL Serveroracle Columns 1024254 Row Size 8060 Byte Store a 16-Byte pointer. Data is stored in other data pages. A long or long RAW each table. Must be at the end of the line. Data is stored in the same block of the row. Splicate table index each table One (index-Organized Tables) unclused table index each table 249 unlimited maximum index column number 1616 index in a single index 900 Bytes? Block table name Table_name [schema.] Table_name view name.] Owner.] Table_name [scheve.] Table_name index name .] ooner.] Table_name [schema.] Table_name
Suppose you start from an Oracle script or program, this script or program is used to create your database object. Copy your script or program and make the following modifications. These modifications will be discussed elsewhere in this section. This example is taken from the sample application script ortable.sql and sstable.sql:
Make sure the database object identity follows the Microsoft SQL Server naming method. You may only need to modify the name of the index. Modify the data storage parameters to make it working under SQL Server. If you use RAID, you don't need any storage parameters. Modify the Oracle Constraint Definition to work in SQL Server. If you need it, create a trigger to support the external key DELETE CASCADE statement. If you are using the database, use the trigger to enhance the relationship between the external key. Modify the CREATE INDEX statement to utilize a cluster index. Use data conversion services to create a new CREATE TABLE statement. Review this statement, note how the Oracle data type is mapped to the SQL Server data type. Clear all CREATE SEQUENCE statements. Use the same column to replace the order in the CREATE TABLE or ALTER TABLE statement. Modify the CREATE VIEW statement if needed. Clear all references to the same word. Evaluate the use of the Microsoft SQL Server Temporary table and its use in your application. Transform all Oracle's CREATE TABLE ... AS SELECT commands to SQL Server's SELECT ... INTO statement. Assess potential use of user-defined rules, data types, and default use.
Data object identifier
The following table compares how Oracle and Microsoft SQL Server handle object identifiers. In many cases, when porting to SQL Server, you don't need to change the name of the object.
OracleMicrosoft SQL 1-30 character length. Database Name: Up to 8 characters long. Database connection name: up to 128 characters long. 1-128 Unicode character length. Temporary Table Name: Up to 116 characters. The name of the identifier must be used: letters, characters that contain text numbers, or characters _, $, and # beginning identifier names: alphanumeric characters, or _ beginning, actually start with any character. If the identifier is not the character, or contains characters not _, @,, or $, you must use the [] (delimiter) enclosure identifier name if an object begins with the following characters: @ indicates that the object It is a local variable. #, The object is a local temporary object. ## The object is a global temporary object table space name must be unique. The database name must unique identifier must be unique within the user account (plan, Schema). The identifier must be unique within the table and view range within the database user account. The column name must be unique within the table and view range. The index name must be unique within the user account (Schema). Index name must be unique in the database table name
This table can be selected by the unrestricted table name when accessing the table exists in your user account. Accessing tables in other Oracle plans need to add the name of the program as a prefix to the table name, and the two are separated from the point number (.). Oracle synonym can provide a higher positional transparency.
When the table is involved, Microsoft SQL Server uses a different method. Because a SQL Server login account can create a table with the same name in multiple databases, use the following method to access the table and view: [[Database Name] Owner name] Table name]
...... with a table OracleMicrosoft SQL Server access your user account SELECT * FROM STUDENTSELECT * FROM USER_DB.STUDENT_ADMIN.STUDENT other mode (schema) SELECT * FROM STUDENT_ADMIN.STUDENTSELECT * FROM OTHER_DB.STUDENT_ADMIN.STUDENT
This is some guidelines naming for Microsoft SQL Server Tables and views:
Using database names and usernames are optional. If a table is referenced by the name (for example, student), SQL Server searches the table in the current database. If you are not found, you are looking for objects with the same name with the same name by DBO's reserved user name in the database. The table name must be unique under the same user account in the same database. The same SQL Server login account can have a table with 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 restrictions added here are the database username instead of the SQL Server login name because both do not have to be the same.
At the same time, other users of 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 that you contain the name of the owner when referenced by the database object. If the application has multiple databases, it is recommended that you will also include the database name again in the reference. If the query spans multiple servers, you have to include the server name.
Each connection of SQL Server has a current database context, which is set to use the USE statement when logging in. For example, suppose you have the following scenario: a user, use the EndUser1 account, log in to the USER_DB database. The user requests the Student table. SQL Server queries the Enduser1.student table. If found, SQL Server is on the user_db.enduser1.student table to do required database operations. If you do not find the table under the Enduser1 database account, SQL Server searches for user_db.dbo.student for the DBO account. If you still can't find the table, SQL Server returns an error message and points to the table does not exist. If another user, such as DEPT_ADMIN, the table must be used as a prefix (dept_admin.student) with the database username. In addition, the database name defaults to the database name in the current context. If the referenced table is in another database, the database name must be part of the reference. For example, to access table Student owned by Enduser1 in an OtherDB database, you need to use other_db.enduser1.student to reference.
Two points can be added between the database and the table name to omit the owner name of the object. For example, if the application references Student_Db..student, SQL Server does search for the following:
Student_db.current_user.student student_db.dbo.student
If the application uses only one database at a time, the database name is omitted when doing object references, so that the application can be convenient for other databases. All object references are implicitly accessing the current database. This is useful for you to maintain a test database on the same server and a product database.
Create a table
Because Oracle and SQL Server supports SQL-92-level (entry-level) about identifying the RDBMS object, the syntax of CREATE TABLE is similar.
OracleMicrosoft SQL 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] [{Col_name Column_Properties [Constraint [constraint]]]]]]]]]]]]]]]]]]]] | [[,] constraint]} [[,] {NEXT_COL_NAME | Next_CONSTRAINT} ...]) [on filegroup_name]
Oracle Database Object Name is not case-sensitive. In Microsoft SQL Server, the name of the database object can be sensitive, which is set to see the installation.
When SQL Server is first set, the default sort order is a dictionary order, which is case sensitive. (You can use SQL ServerSetup to do different settings) because the name of the Oracle object is always unique, you will not encounter any trouble when transplanting the database object to SQL Server. It is recommended that you write all the tables and columns in Oracle and SQL Server to avoid problems when there is a user who has case-sensitive SQL Server installed. Table and index storage parameters
For Microsoft SQL Server, use RAID to simplify the placement of the database object. A SQL Server's cluster index is integrated in the structure of the table, just like an Oracle index tab.
OracleMicrosoft SQL CREATE TABLE DEPT_ADMIN.DEPT (DEPTVARCHAR2 (4) NOT NULL, DNAMEVARCHAR2 (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 10PCTUSED 40TABLESPACE USER_DATASTORAGE (INITIAL 10K NEXT 10KMINEXTENTS 1 MAXEXTENTS UNLIMITEDFREELISTS 1) CREATE TABLE USER_DB.DEPT_ADMIN.DEPT (DEPTVARCHAR (4) NOT NULL, DNAMEVARCHAR (30) NOT NULL , Constraint dept_dept_pkprimary key clustered (dept), constraint dept_dname_uniqueunique nonclustered (DNAME))
Create a table with SELECT statement
Using Oracle, a table can be created with any valid select command. Microsoft SQL Server provides the same feature, but the syntax is different.
OracleMicrosoft SQL CREATE TABLE StudentBackup As Select * from studentselect * Into studentbackup from student
To select ... INTO can work, you must set the option Select INTO / BULKCOPY of the database using the program to TRUE. (The database owner can set this option with SQL Server Enterprise Manager or Transact-SQL sp_dboption system store program). Use the sp_helpdb system stored procedures to check the status of the database. If SELECT INTO / BULKCOPY is not set to True, you can still copy to the temporary table with the SELECT statement:
SELECT * INTO #student_backup from user_db.student_admin.student
When using the Select .. INTO statement to create a new table, the integrity definition of the reference will not be converted to a new table.
The requirements that set Select Into / Bulkcopy to True may make the transplant process complex. If you have to copy data with the SELECT statement to the table, create a table first, then use the INSERT INTO ... SELECT statement to load the table. For Oracle and SQL Server, the syntax is the same, nor does it need to set any database options. view
The syntax for creating a view in Microsoft SQL Server is the same as ORACLE.
OracleMicrosoft SQL CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [. Schema] view_name [(column_name [, column_name] ...)] AS select_statement [WITH CHECK OPTION [CONSTRAINT name]] [WITH READ ONLY] CREATE VIEW [owner .] View_name [(Column_name [, Column_name] ...)] [with encryption] as select_statement [with check option]
The SQL Server view requires the table to exist, and the owner of the view must have permission to access the database indicated in the SELECT statement (similar to the Force option in Oracle).
By default, the data modification statement on the view is not checked to determine if the affected row is within the scope of the view. To check all modifications, use the use of check option. The main difference between WITH CHECK OPTION is that Oracle is defined as a constraint, while SQL Server is not. In addition, the functionality of both is the same.
When the view is defined, Oracle provides a with read only option. The SQL Server application can reach the same result with a method of providing Select permissions to the view user.
Both SQL Server and Oracle views support derived columns, using mathematical expressions, functions, and constant expressions. Some special differences between SQL Server are:
If data modification only affects a basic table, the data modification statement (INSERT or UPDATE) can exist on multiple views. Data modified statements in a single statement cannot be used in more than one table. ReadText or WRITETEXT cannot be used in the columns in the view. 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 with an external connection, and the query defines the internal table of the external joint point, the results of SQL Server and ORACLE will vary. In most cases, the Oracle view is easily converted to the SQL Server view.
OracleMicrosoft SQL 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 SSNCREATE 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 'TEN 3.3when' b- 'THEN 2.7WHEN' C 'THEN' C- 'THEN' THEN 'DHEN' C- 'THEN' DHEN 'D' THEN 1WHEN 'D ' THEN 1.3WHEN 'D-' Then 0.7ELSE 0nd, 2) from student_admin.gradegroup by SSN Index
Microsoft SQL Server provides a split clustered and unclused index structure. These indexes are constructed from pages from a tree structure called B-Tree (similar to the B-Tree index structure in Oracle). The start page ("root" strategy illustrates the range of values in the table. Each range in the "Root" page points to other pages (judging nodes), which contains a smaller range of values in the table. In this class, the node can point to other judgment nodes, which narrows the range of search. The last level of the tree structure is called "leaf" level.
Cluster index
A cluster index is implemented in the form of an indexed tissue in Oracle. A clustered index is a physically contained index in a table. Tables and index sharing the same block storage space. The cluster index is established in the indexed sequence physically rearrangement data line, and establishes an intermediate judgment node. The "Leaf" page of the index contains real table data. This structure allows each table with only one cluster index. Microsoft SQL Server is automatically created a clustered index for tables, regardless of whether the table sets the Primary Key or the UNIQUE constraint. Sub-clustered indexes are useful:
Primary Keys cannot be updated columns. Returns a query of a range of values, using operators such as Between,>,> =, <,, and <= such, for example:
Select * from student where grad_date
Between '1/1/97' and '12 / 31/97 '
Returns a big result collection query:
Select * from student where lname = 'smith'
Columns used to be sorted (ORDER BY, GROUP BY)
For example, on the Student table, it is useful to include an unclused index on the primary key of the SSN, and the clustering index can be created on the LNAME, FNAME (Last Name, First Name, because this is a commonly used Divide students' methods. The update behavior on the distribution table can prevent "hotspots". The hotspot is usually caused by a plurality of users fill in a table with a rising button. Such scenarios often lead to the lock of the line.
Deleting and rebuilding a clustered index is a technique of a very common reorganization table in SQL Server. This is a simple way to ensure that the data page is continuous on the disk and some free space in the reconstruction table. This is very similar to the export, deletion, and importing a table in Oracle.
A SQL Server split index is simply different from the cluster of Oracle. A Oracle cluster. A Oracle cluster is a physical collection of two or more tables that share the same data block, using a common column as a cluster key. SQL Server has no structure similar to the Oracle cluster.
As a general principle, define a clustered index will increase the performance of SQL Server and strengthen space management. If you don't know the query and update mode for a given table, you can create a clustered index on the primary key.
The following table exceeds the source code for the sample application. Note the use of the SQL Server "cluster" index.
OracleMicrosoft SQL CREATE TABLE STUDENT_ADMIN.GRADE (SSNCHAR (9) NOT NULL, CCODEVARCHAR2 (4) NOT NULL, GRADEVARCHAR2 (2) NULL, CONSTRAINT GRADE_SSN_CCODE_PK PRIMARY 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 (SSNCHAR (9) NOT NULL, CCODEVARCHAR (4) NOT NULL, GRADEVARCHAR (2) NULL, CONSTRAINT GRADE_SSN_CCODE_PK PRIMARY KEY CLUSTERED (SSN, CCODE), ConsTRAINT GRADE_SSN_FK Foreign KEY (SSN) References Student_admin.student (SSN), ConsTRAINT grade_code_fk foreign key (ccode) References de PEPT_ADMIN.CLASS (CCODE))
Unexclusted index
In an unclused index, index data and table data are physically separated, and the rows in the table are not stored in the index in order. You can transplant the Oracle index definition to Microsoft SQL Server unclused index definition (just like shown in the table below). However, considering performance, you may want to select one of the indexes to create it into a clustered index.
OracleMicrosoft SQL CREATE INDEXSTUDENT_ADMIN.STUDENT_ MAJOR_IDXON STUDENT_ADMIN.STUDENT (MAJOR) TABLESPACE USER_DATAPCTFREE 0STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED) CREATE NONCLUSTERED INDEXSTUDENT_MAJOR_IDXON USER_DB.STUDENT_ ADMIN.STUDENT (MAJOR) Index Syntax and Naming
In Oracle, an indexed name is unique in one user account. In Microsoft SQL Server, a index must be unique in a table name, but it is not necessary to unique in the username and database name. Therefore, when you create or delete an index in SQL Server, you must explain the table name and index name. In addition, SQL Server's DROP INDEX statement can delete multiple indexes at a time.
OracleMicrosoft SQL CREATE [UNIQUE] INDEX [schema] .index_name ON [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 [, ... n]) [WITH [PAD_INDEX] [[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY] [[,] DROP_EXISTING ] [[,] Statistics_norecompute]] [on filegroup] Drop index user_db.student.demo_idx, user_db.grade.demo_idx
Index data storage parameters
The FillFactor option in the Microsoft SQL Server feature option is similar to the PCTFree variable in Oracle in many ways. When the size of the table is increased, the index page changes corresponding to the new data. Index must be re-combined to accommodate new data. Only when the index is created, the fill parameter is used, and it is not maintained after this.
The FillFactor option (0 ~ 100) controls how many spaces should be left when creating an index. If you do not indicate the parameters, use the default parameters, which is 0, indicating that the "leaf" page that will fully fills the index, and leaves the space at least one entry (if there are two entries, it means yes if there are two entries. An unique "cluster" chemical index).
A lower fill factor will reduce the division of the index page, but the number of layers of the B-Tree structure will increase. Higher fill factors can use index page space more efficiently, only less disk I / O to access index data, and will reduce the number of layers of the B-Tree structure.
The PAD_INDEX option indicates that the fill factor will also be applied to the judgment node page, just like the data page to be used.
Although it may be necessary to adjust the PCTFree parameters in Oracle to optimize performance. But rarely use the FillFactor parameter in the Create Index statement. The fill factor is provided for performance optimization. But it is only useful when it is created in a table to create an index for existing data and is only useful when you can accurately predict data in the future. If you set the PCTFree parameter in Oracle to 0, you can consider setting it to 100. This is useful when data inputs and modifications (read-only tables) do not occur in the table. If the fill factor is set to 100, the server will create such an index, and each page of it is completely filled.
Ignore repeated keywords
Whether in Oracle or in Microsoft SQL Server, users cannot enter duplicate values in a column of one or some unique indexes. This will produce an error message. However, SQL Server allows developers to choose the Insert or Update statement how to handle this error.
If IGNORE_DUP_KEY is used in the CREATE INDEX statement, and an Insert or Update statement that creates a repeated keyword is executed, SQL Server will give a warning message and ignore the repeated line. If IGNORE_DUP_KEY is not used, SQL Server will give an error message and then roll the entire INSERT statement. Please refer to the SQL Server online manual if you need more information about this option.
Use temporary table
An Oracle application may have to create a temporary table. The application must ensure that all tables created for this purpose at some time. If the application does not do this, the table space will soon become confusing and difficult to manage.
Microsoft SQL Server provides a temporary table database object, which is created for the purpose mentioned above. Such tables are always created in the TEMPDB database. The name of the table determines how long it has to exist in the TEMPDB database.
Table Name Description #table_name This local temporary table exists only within the lifetime of the user session or creates its process. This table is automatically deleted after the user exits login or creates its process. The table cannot be shared between multiple users. Other database users cannot access the table. The license cannot be given or withdrawn on this table. ## Table_name This table also typically exists in a user session or creates its life period. But the table can be shared by multiple users. After the last referenced a user session, the table is automatically deleted. Users of all other databases can access the table. The license cannot be given or withdrawn on this table.
You can define an index for a temporary table. However, you can only create a view on the table created in Tempdb, and the name of these tables is not added to the # or ## prefix. The following example shows the creation of a temporary table and the corresponding index. When the user exits, the table and index are automatically deleted.
SUM_PAID, Major INTO #SUM_STUDENT from User_Db.Student_Admin.student Group by Major Create Unique Index Sum Student IDX ON #SUM Student (Major)
Use a temporary table in your program code, you can find it.
type of data
Compared with Oracle, Microsoft SQL Server is more powerful in the selection of database types. There are many possible conversion methods between Oracle and SQL Server data types. We recommend that you use the DTS wizard to move to create a new CREATE TABLE statement. When you need it, you can also modify it.
OracleMicrosoft SQL Char recommends using char. Char types of columns have a slightly fast access speed because a CHAR column uses a fixed storage length. VARCHAR2 and Longvarchar or Text. (If the length of the data value in your Oracle column is less than or equal to 8000 Bytes, use varchar; otherwise, you must use text.) RAW and long Rawvarbinary or Image (if you are in your Oracle column). The length of the data value is less than or equal to 8000 bytes, using varbinary; otherwise, you must use image.) Number If the integer is between 1 and 255, use Tinyint. If the integer is between -32768 to 32767, use Smallint. If an integer is Inter-2, 147, 483, 648 to 2, 147, 483, 647, INT. If you need a floating-point number, use Numeric (accurate and can be caller). Note: Do not use float or real because there may be truncation (Oracle Number and SQL Server Numeric It will be truncated). If you are not sure, use numeric; it is very similar to the Oracle Number data type. DateDateTime. RowId Using the Identity column type CURRVAL, NextVAL uses the Identity column type, and @@ identity, Ident_seed (), and Ident_inCr () functions. SysdategetDate (). Useruser. Using Unicode Data
The Unicode specification defines a coding scheme that uses a single encoding method for all character encodings used worldwide services. All computers can use a single Unicode encoding to convert bit mode in Unicode data into characters. This program ensures that on all computers, the same bit mode is converted to the same character. Data can be transferred from one database or a computer to another without considering whether the acceptance system can convert the position mode correctly into a character.
There is a problem with one byte to represent characters, that is, this data type can only represent 256 characters. This creates multiple coding specifications (or called code pages) for different languages. This is also impossible to deal with Japanese or Korean language.
Microsoft SQL Server converts the bitmap of the characters installed in SQL Server to CHAR, VARCHAR, or TEXT type. The client uses the code page installed by the operating system to explain the bit mode of the character. There are now many different code pages. Some characters are only available on some code pages, and there is no in other code pages. Some characters are defined on some code pages as a bit mode, and there is another bit mode on another code page. If you want to build an international system that must handle various languages, it becomes very difficult for those who meet the computers of language requirements or multiple countries. Similarly, it is also very difficult to ensure that each computer can implement character conversion when using a system that uses a different code page.
The UNICODE specification solves this problem using a double-byte coding scheme. With double byte encoding, there is enough space to overwrite the most widely used business languages. Because all Unicode systems use the same bit mode to represent all characters, when transferring from one system to another, there is no problem that the character conversion is incorrect.
In SQL Server, NChat, NVARCHAR, and NTEXT data types support Unicode data. If you need more information about the SQL Server data type, please see the SQL Server online manual.
User defines data type
You can create a user-defined data type for the Model database or a single user database. If the user-defined data type is defined for the Model, the data type can be used after all newly created user databases. The user-defined data type is defined by the SP_ADDTYPE system store program. If you need more information, please see the SQL Server online manual. You can use the user to define data types in Create Table and ALTER TABLE statements and bind default methods and rules for it. If you define NULLABILITY when you use a user-defined data type, it is higher than the NULLLELITY priority defined at the time of data definition.
The following example shows how to create a user-defined data type. The parameters are user type names, data types, and NULLABILITY.
Sp_addtype gender_type, 'varchar (1)', 'not null' Go
This capability is useful for solving problems related to the creation of scripts to the Oracle table to SQL Server. For example, it is very simple to add a Date data type of an Oracle.
sp_addtype date, datetime
This feature cannot be used on those data types that need to be changed, such as Oracle Data Type Number. If this is done, the system will return an error message telling you that you need to indicate the length of the data.
SP_ADDTYPE VARCHAR2, VARCHAR GO MSG 15091, Level 16, State 1 You Must Specify A Length with this Physical Type.
Microsoft TimeStamp column
The TimeStAMP column makes Browse mode modification and cursor modification operations more efficient. TimeSTAMP is such a data type, which is automatically modified when there is an input or modification of the run or modified operation.
The value in the TimeStAMP column is not stored in the actual date and time, but is stored as binary (8) or varbinary (8), which indicates the frequency of the event that occurs in the table. A table can only have a TimeStAMP column.
If you want to learn more, please see the SQL Server online manual.
Object-level license
Microsoft SQL Server object privileges can be granted to any other database user, database group, and public role, reject grant, and undo. SQL Server does not allow the owner of the object to grant other users, groups, or public roles ALTER TABLE and CREATE INDEX privileges, this is different from Oracle. These privileges must be retained by the object owner.
The GRANT statement creates an entry license for a secure system that allows a user in the current database to operate data in the current database, or perform a specific Transact-SQL statement. The syntax of the GRANT statement is the same in Oracle and SQL Server.
Deny statements create an entry in the security system to reject a license for a security account in the current database, and prohibit the security account to inherit the license from the group or role member belonging to the account. There is no Deny statement in Oracle. The REVOKE statement clearing previously granted a license for a user in the current database or rejects its license.
OracleMicrosoft SQL 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 [, ... n]} {Table | View} | ON {Table | View} [(Column [, ... n])] | on {stored_procedure | Extended_Procedure }}}}}} To security_account [, ... n] [with grant option] [as {group | role}] revoke [grant option for] {all [private option for] {all [privileges] | permission [, ... n]} {[(column [, ... n] )] ON {Table | View} | {Table | View} [(Column [, ... n])] | {stored_procedure |} {to | from} security_account [, ... n] [cascade] [as {group | role}] deny {all [privileges] | permission [, ... n]} {Table | View} | ON {Table | View} [(Column [, ... n] )] | ON {stored_procedure | Extended_Procedure}} to security_account [, ... n] [cascade] If you need to know the object-level license For more information, please see the SQL Server online manual.
In Oracle, the References privilege can only grant users. SQL Server allows the privilege to grant the privilege to database users and database groups. INSERT, UPDATE, DELETE, and SELECT privileges are processed in the same way in Oracle and SQL Server.
Strengthen data integrity and business rules
Strengthen data integrity to ensure the quality of data in the database. Two two steps of planning questions are the validity of the column value and how to strengthen the integrity of data in columns. Data integrity can be divided into four categories, which are strengthened in different ways.
How the type of force integrity Entity integrityPRIMARY KEY constraintUNIQUE constraintIDENTITY property Domain integrityDomain DEFAULT definitionFOREIGN KEY constraintCHECK constraintNullability Referential integrityDomain DEFAULT definitionFOREIGN KEY constraintCHECK constraintNullability User-defined integrityAll column- and table-level constraints in CREATE TABLEStored proceduresTriggers
Entity integrity (Entity Integrity)
Entity integrity defines one line in a particular table as a unique entity. Entity integrity passes the index, UNIQUE constraint, the primary key constraint, or the Identity feature, enhance the integrity of the labeling or the main keyword, named
You can always be explicitly named your constraints. If you don't do this, Oracle and Microsoft SQL Server will use different naming practices to implicitly named. Different in naming will bring unnecessary trouble for your transplant. There will be problems when deleting constraints or makes constraints, because constraints must be deleted by name. The syntax of explicit naming constraints is the same in Oracle and SQL Server.
ConsTRAINT CONSTRAINT_NAME
Primary key and only column
SQL-92 standard requires all values in the primary keyword and the column does not allow null values. Oracle and Microsoft SQL Server are enforced uniqueness by automatically created a unique index, whether it is defined by the Primary Key or UNIQUE constraint.
Although an unclused index can be created, the SQL Server default is the main keyword to create a clustered index. Oracle's index on the primary keyword can be cleared by deleting constraints or makes a constraint failure, and the index of SQL Server can only be implemented by deleting constraints.
In any of RDBMS, other keywords can define a unique constraint. Multiple unique constraints can be defined in any table. The UNIQUE constraint can be empty. In SQL Server, unless otherwise stated, create an unusalized index by default.
When transplanting your app, it is important to note that SQL Server only allows full unique keywords (single or multiple column indexes) to have a null value, and Oracle allows full keywords. It is a null value.
OracleMicrosoft SQL CREATE TABLE DEPT_ADMIN.DEPT (DEPT VARCHAR2 (4) NOT NULL, DNAME VARCHAR2 (30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY (DEPT) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED), CONSTRAINT DEPT_DNAME_UNIQUE UNIQUE (DNAME) USING INDEX TABLESPACE USER_DATA PCTFREE 0 STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED)) CREATE TABLE USER_DB.DEPT_ADMIN.DEPT (DEPTVARCHAR (4) NOT NULL, DNAMEVARCHAR (30) NOT NULL, CONSTRAINT DEPT_DEPT_PK PRIMARY KEY CLUSTERED (DEPT), Constraint Dept_DName_Unique Unique Nonclustered (DNAME))
Increase and clear constraints
The constraint failure can improve the database performance and make the data replication process more smooth. For example, when you rebuild or copy data in a remote site, you don't have to repeat your constraints, because the integrity of the data is checked when it originally enters the database. You can prepare an Oracle application to enable or fail to constrain (except Primary Key and Unique). You can use Check and With Nocheck to reach the same purpose in Microsoft SQL Server ALTER TABLE statements.
The following illustration shows the comparison of the process.
In SQL Server, you use all keywords on the Nocheck clause to postpone all table constraints. If your Oracle application uses the Cascade option to fail or delete the Primary Key or UNIQUE constraint, you may need to override some code because the Cascade option fails or deletes the integrity constraints of the parent class and subclass.
This is an example of grammar:
Drop connection_DEPT_PK CASCADE
The SQL Server application must modify to first delete subclasses, and then delete the constraints of the parent class. For example, in order to delete the Primary Key constraint on the DEPT table, the external keywords related to the list of Student.major and Class.Dept must be deleted. This is an example of grammar:
ALTER TABLE STUDENT DROP CONSTRAINT TABLE CLASD DEPT_DEPT_PK ALTRAINT CONST_DEPT_FK ALTER TABLE DEPT DROP CONSTRAINT DEPT_DEPT_PK
The grammar of Alter Table and delete constraints is the same in Oracle and SQL Server.
Generate continuous digital values
If your Oracle application uses Sequences, this option can be easily changed to utilize the Identity features of Microsoft SQL Server.
Category Microsoft SQL Server syntax CREATE TABLE new_employees (Empid int IDENTITY (1,1), Employee_Name varchar (60), CONSTRAINT Emp_PK PRIMARY KEY (Empid)) If increment interval is 5: CREATE TABLE new_employees (Empid int IDENTITY (1,5) Employee_name varchar (60), consTRAINT EMP_PK PRIMARY Key (EMPID)) Each table has the identity column that allows null value to use default constraints, the value cannot be used. The only one is in the INSERT, SELECT INTO or BULK COPY statement The maximum current identification number @@ identity (function) returns the specified seed value Ident_seed ('table_name') when the identity column is created, and the added value of the added value specified when the identity column is created. When references the columns with the Identity property in the DELETE statement, you can use the Identoty keyword on the column name.
Although Identity features automate the number of tags in a table, different tables, if each one has its own identity column, can produce the same value. This is because the Identity feature can only be guaranteed to be guaranteed on the table using it. If an application must generate a unique identity column throughout the database, or around each of the network, you can use the ROWGUIDCOL feature, the UNIQUEIDENTIFIER data type, and the NewID function. SQL Server uses globally independent identity columns to copy, make sure the row is unique in all the copies of all tables.
Please refer to the SQL Server online manual if you need more information about creating and modifying identity columns.
Domain integrity
Domain integrity constraints to give a valid entry for a given column. Domain integrity is implemented by limiting type (via data type), format (via Check constraint), or the range of possible values (via REFERENCE and CHECK constraints).
DEFAULT and CHECK Constraint Oracle treat the default as a column property, while Microsoft SQL Server treats the default as a constraint. The Default constraint of SQL Server can contain integer values, built-in universal functions (NILADIC functions), or NULL.
It is easy to transplant the default column properties of Oracle, you should define the default constraints of the column-level non-constraint name in SQL Server. SQL Server generates a unique name for each Default constraint.
The syntax used to define the CHECK constraint is the same in Oracle and SQL Server. Search conditions should be represented by Boolean expressions and cannot include subquers. The column level constraint can only be used on the confusing column, and the table level constraint can only be used on columns in the constrained table. Multiple Check constraints can be defined for a table. The SQL Server syntax allows only one column level Check constraint to be created in a CREATE TABLE statement, and the constraint can have multiple conditions.
The best way to test your modified CREATE TABLE statement is to use SQL Server Query Analyzer in SQL Server, and only analyze syntax. The output will point out any errors. Please refer to the SQL Server online manual if you need more information about binding syntax.
OracleMicrosoft SQL CREATE TABLE STUDENT_ADMIN.STUDENT (SSN CHAR (9) NOT NULL, FNAME VARCHAR2 (12) NULL, LNAME VARCHAR2 (20) NOT NULL, GENDER CHAR (1) NOT NULL CONSTRAINT STUDENT_GENDER_CK CHECK (GENDER 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 NULL Constraint Student_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 Null Constraint Student_gender_ck Check ('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 NULL CONSTRAINT Student_degree_ck Check (Degree_Program In ('u', 'm', 'P', 'D')), ... About User Defined Rules and Default Note: About Microsoft SQL Server rules and default syntax are considering backward compatibility, but it is recommended to use the Check constraint and Default constraints in new development. If you need more information, please see the SQL Server online manual.
Nullability
Microsoft SQL Server and Oracle create column constraints to force NULLABILITY. In Oracle's CREATE TABLE and ALTER TABLE statements, the provision is NULL, not NOT NULL. The settings of the Microsoft SQL Server, the database, and sessions can be across the NULLABILITY of the data type used in the column definition.
All of your SQL scripts (whether Oracle or SQL Server) must show the null and not null definitions of each column. To understand how this policy is implemented, please refer to Oracle.SQL and SSTABLE.SQL These two examples of the table creation script. If there is no clear definition, the columns of Nullability follow the following rules. Null SettingsDescription columns are null value specified using SQL Server using a user-defined data type when creating data types. Using the sp_help system stored procedures to get the default nullue column for data types is defined by a system type that is defined by a system type if the system is provided with only one option. Currently, BIT data type can only be defined as NOT NULL. If any session is set to ON (with SET open), then if ANSI_NULL_DFLT_ON is ON, it is specified as NULL. If ANSI_NULL_DFLT_OFF is ON, specify as not null. If any database settings are modified (modified with sp_dboption system stored) Then: If the ANSI NULL Default is true, specify as null. If the ANSI NULL DEFAULT is false, specify that NOT NULLNULL / NOT NULL is not defined when there is no clear definition (there is no settings in the ANSI_NULL_DFLT option), the session will Modified, and the database is set to the default (ANSI NULL DEFAULT is false), then SQL Server specifies it to NOT NULL.
Quote integrity
The following table provides a syntax comparison for defining the REFERENTIAL integrity constraint.
Constraints OracleMicrosoft SQL Server PRIMARY KEY [CONSTRAINT constraint_name] PRIMARY KEY (col_name [, col_name2 [..., col_name16]]) [USING 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]]) [USING 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_name2 [..., col_name16]])] REFERENCES [owner .]] d c. (r d) [allign key (col_name [, col_name16])] [..., col_name16]]]. ] Ref_Table [(Ref_col [, REF_COL2 [..., REF_COL16]]] [NOT for Replication] DefaultColumn Property, Not a constraintdefault (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 clause is used The column level, Foreign Key, and Check constraints during the replication process.
External key
Defining the grammar of external keywords is similar in various RDBMs. The number of columns indicated in the external keyword and the data type of each column must match the References clause. A non-empty value input to the column must define a table and column in the References clause, and the columns of the table must have a primary key or UNIQUE constraint.
Microsoft SQL Server constraints provide the ability to reference tables in the same database. To achieve the application integrity of the database range, you can use a table-based trigger.
Both Oracle and SQL Server support self-reference tables, which have references to the same list or several columns of the same table. For example, the Prereq column in the Class table can reference the CCODE column in the CLASS table to ensure that a valid course number is entered as a clause prerequisite.
Implementing a laminated deletion and modification in Oracle is to use the Cascade delete clause, while the SQL Server uses a table trigger to achieve the same functionality. If you need more information, please refer to the "SQL Language Support" section later in this chapter. User-defined integrity
User-defined integrity allows you to define a specific business rule that does not belong to other integrity.
Stored procedure
The Microsoft SQL Server store is accepted or returned to the parameters provided by the CREATE Procedure statement. In addition to the temporary storage program, the storage program is created in the current database. The table below shows the syntax of Oracle and SQL Server.
OracleMicrosoft SQL CREATE OR REPLACE PROCEDURE [. User] procedure [(argument [IN | OUT] datatype [, argument [IN | OUT] datatype] {IS | AS} blockCREATE PROC [EDURE] procedure_name [; number] [{@parameter data_type } [Varying] [= default] [OUTPUT]] [, ... n] [with {recompile | encryption | Recompile, Encryption}] [for replication] as sql_statement [... n]
In SQL Server, temporary storage programs are created in the TEMPDB database by adding digital tags before procedure_name. Add a digital tag (#procedure_name) Represents a local temporary storage program, plus two digital tags (## procedure_name) indicates a global temporary program.
A local temporary program can only be used to create it. Permit to perform a local temporary program cannot grant other users. The local temporary program is automatically deleted at the end of the user session.
A global temporary program can be used by all SQL Server users. If a global temporary program is created, all users can access it and cannot explicitly withdraw license. The global temporary program is automatically deleted at the end of the last user session.
The SQL Server store program can have up to 32-level nested. The number of nested layers increases when the program is called, and the runtime is reduced when the program is called.
The following example illustrates how to use a Transact-SQL store program to replace an Oracle's PL / SQL package function. Transact-SQL's version is simpler, because the ability of SQL Server's return results is set directly in a storage program, and no cursors are required.
OracleMicrosoft SQL 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 IS SELECT * FROM STUDENT_ADMIN.STUDENT WHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE WHERE GRADE.SSN = STUDENT.SSN) ORDER BY SSN; FUNCTION SHOW_RELUCTANT_STUDENTS (WORKVAR OUT VARCHAR2) RETURN NUMBER IS WORKREC STUDENT% ROWTYPE; BEGIN IF NOT C1% Isopen Then Open C1; RowCount: = 0; Endif; Fetch C1 INTO WORKREC; IF (C1% Notfound) Then Close C1; RowCount: = 0; Else Workvar: = Workrec.fname || '' || WorkRec .Lname || ', Social security Number' || Workrec.ssn || 'Is Not Enrolled in Any Classes!'; RowCount: = RowCount 1; Endif; Return (RowCoun T); 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 G WHERE G.! SSN = S.SSN) ORDER BY SSNRETURN @@ ROWCOUNTGO EXCEPTIONWHEN OTHERS THENIF C1% ISOPEN THEN CLOSE C1; ROWCOUNT: = 0; ENDIF; RAISE_APPLICATION_ERROR (-20001, SQLERRM); END SHOW_RELUCTANT_STUDENTS; END P1; / SQL Server does not support Oracle The package or function is similar, nor does it support the Create or Replace option when creating a store program.
Delayed stored procedure
Microsoft SQL Server provides Waitfor, allowing developers to give a time, time period, or event to trigger a statement block, store program, or transaction. This is the equivalent of Transact-SQL for DBMS_LOCK_SLEP in Oracle. Waitfor {delay 'Time' | Time 'Time'}
Indicates Microsoft SQL Server Waiting until a given time has been executed later, up to 24 hours.
it's here
DELAY
Indicates Microsoft SQL Server Waiting until a given number of time has been executed in the past, up to 24 hours.
'Time'
Time to wait, the time can be the format of any acceptable DateTime data type, or can be given as a local variable. However, the date part of the DateTime value cannot be specified.
Time
Indicates SQL Server waiting to the specified time
E.g:
Begin Waitfor Time '22: 20 'Execute Update_all_stats end
Specify the parameters in the stored program
To specify a parameter in a stored program, you can use the syntax given below.
OracleMicrosoft SQL VarName DattypedPedefault
Trigger
Oracle and Microsoft SQL Server have triggers, but they differ in the execution.
Description OracleMicrosoft SQL Server Each table can have no limit unlimited in INSERT, UPDATE, DELETE, perform triggers before INSERT, UPDATE, DELETE, is the statement-level trigger has a row-level trigger Checking the constraint before execution is unless the trigger is canceled. In addition, this is an option in DTS (DATA TRANSFORMATION SERVICES) Submitted in an UPDATE or DELETE trigger: OldDeleted.column Submit a new value in the Insert trigger: newInserted.column cancel trigger ALTER TRIGGERDTS Options
Deleded and Inserted are the conceptual tables created by SQL Server as the trigger. The table is similar to the table that is defined on the structure on the structural trigger and saves the values in the old or new rows that may be changed by the user. The table will track changes in the line level of Transact-SQL. These tables provide the same functionality as the row level trigger in Oracle. When an insert, update, or delete statement is executed in SQL Server, the row is added to the trigger table and is also added to the INSERTED and DELETED table simultaneously.
INSERTED and DELETED doors are the same as the trigger table. They have the same column name and data type. For example, if you place a trigger in a Grade table, INSERTED and DELETED have such a structure.
Gradeinserteddeleted SSN Char (9) ccode varchar (4) grade varchar (2) SSN Char (9) ccode varchar (4) grade varchar (2) SSN Char (9) ccode varchar (4) grade varchar (2)
The INSERTED and DELETED tables can be checked by trigger to determine what trigger action to do. The Inserted table is used with INSERT and UPDATE statements. The deleted table is used with the Delete and the Update statement.
The UPDATE statement uses the INSERTED and DELETED table because SQL Server always deletes the old row when performing Update operations and fills in new rows. Therefore, when executing UPDATE, the rows in the Inserted table are always a copy of the line in the deleted table. The following example uses the INSERTED and the DELEDED table instead of the row level trigger in the PL / SQL. A complete external joint point is used to query all rows in any table.
OracleMicrosoft 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_GRADE New_ssn, new_ccode, new_grade, select (), old.ssn, old.ccode, old.grade, new.ssn, new.ccode, new.gradefrom inserted new full! Join deleted OLD on new.ssn = Old. SSN
You can only create a trigger in the current database, you can also reference objects outside the current database. If you use the owner name to modify the trigger, then use the same method to modify the table name.
The trigger can nearest 32. If a trigger changes a table, and the table has another trigger, the second trigger is active, and the third trigger can be called, so push. If any trigger on the chain causes a dead loop, the nested level overflows, the trigger being canceled. In addition, if an update trigger on one line in a table is simultaneously updated at the same time, the update trigger will perform only once.
Microsoft SQL Server's publication reference integrity (DRI) does not provide reference integrity definitions across databases. A trigger can be used if the integrity across the database is required.
The following statement is not allowed in the Transact-SQL trigger.
CREATE statements (DATABASE, TABLE, INDEX, PROCEDURE, DEFAULT, RULE, TRIGGER, SCHEMA, and VIEW) DROP statement (TRIGGER, INDEX, TABLE, PROCEDURE, DATABASE, VIEW, DEFAULT, RULE) ALTER statements (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) If you need more information about triggers, see SQL Server online manual.
Transaction, locking and parallelism
This section explains how the Oracle and Microsoft SQL Server transactions are executed, and provide the difference between locking processes and parallel issues in all database types.
Transaction
In Oracle, start transactions when performing insert, update, or deleting operations. An application must give a commit command to save all changes to the database. If you do not execute your commit, all modifications will be rehever or automatically turned into unfinished.
By default, Microsoft SQL Server automatically executes a commit statement every time you insert, update, or delete actions. Because the data is saved automatically, you can't roll any changes. You can change this default or explicit transaction mode.
Implicit transaction mode allows SQL Server to operate like Oracle, which is activated with a set implicit_transactions on statement. If this option is ON and there is currently no prominent transaction, each SQL statement automatically starts a transaction. If there is an open transaction, there will be no new transaction begins. Opened transactions must be promised by the user with the Commit Transaction statement to enable all changes to take effect and release all locks.
A clear transaction is a set of SQL statements surrounded by the following transaction separator:
Begin Transaction [Transaction_name] Commit Transaction [Transaction_name] Rollback Transaction [Transaction_name | SavePoint_name]
In the following example, the English Department is changed to the literature department. Note the usage of Begin Transaction and Commit Transaction statements.
OracleMicrosoft SQL 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 expressed transactions must be closed with begin transaction ... commit transaction statements. The function of the Save Transaction statement is the same as the SavePoint command in Oracle, set a save point in the transaction, so that the roll back is possible.
The transaction can be nested. If this happens, the outermost layer is created and submits a transaction, and the inside is the tracking nested layer. When you encounter a nested transaction, the @@ TRANCOUNT function increases. Typically, this obvious transaction nested occurs in a store program or a trigger that is called to each other. Although the transaction can nested, the impact on the behavior of the Rollback Transaction statement is small.
In the stored procedures and triggers, the number of beac transaction statements must match the number of commit transaction statements. The stored procedures and triggers that contain mismatched Begin Transaction and COMMIT TRANSACTION statements will generate an error message when running. The syntax allows a stored procedure and trigger that contains the Begin Transaction and the Commit Transaction statement to be called in a transaction.
As long as the situation is permit, a large transaction should be divided into several smaller transactions. Make sure that each transaction has a complete definition in a separate Batch. In order to minimize possible parallel conflicts, the transaction should neither cross multiple batches, and should not wait for user input. The multiple transactions will bring a negative impact on the recovery time in a transaction for longer running time, and will also cause parallel issues.
When using ODBC programming, you can select explicitly or implicit transaction mode by using the SQLSetConnectOption function. Which mode should I choose to take the AUTOCOMMIT connection option? If AutoCommit is ON (default), you are in explicit mode. If AutoCommit is OFF, it is implicit mode.
If you use scripts through SQL Server Query Analyzer or other query tools, you can explicitly include a Begin Transaction statement mentioned above, or you can use the Set Implicit_Transactions ON statement to start the script. Begin Transaction's method is more flexible, and implicit methods are more suitable for Oracle. Lock and transaction isolated
Oracle and Microsoft SQL Server have a very different lock and isolated strategy. When you translate the Oracle application to SQL Server applications, you must take into account these differences to ensure scalability of your application.
Oracle implicitly or explicitly uses a multi-version consistency model for all SQL statements of all read data. In this model, the data reader is before reading the data line, and the default is neither a lock and does not wait for other lock unsales. When the reader needs a change that has changed but other writers has not submitted, Oracle rebesses a snapshot of a data line by using the rear segment to recreate the old data.
The data writer in Oracle requests lock when updating, deleting, or inserting data. The lock will continue to maintain the end of the transaction, and the user covers the modification that has not been submitted.
Microsoft SQL Server uses multiple granular locks that allow transactions to lock different types of resources. In order to minimize the locking overhead, SQL Server is automatically locked in the hierarchy associated with the task. Lock with smaller intervals, such as rows, enhances parallel, but the management overhead is large, because if there are many rows of locks, multiple locks must be maintained. Lock with larger intervals, such as a table, is expensive in parallel, because the locking of the entire table limits access to any part of the table, but the management overhead is relatively small, because as long as a few locks are maintained . SQL Server can lock these resources (arranged in order of interval size).
Resource description RID line identifier. Separate locking of a single-line table. Key key; one row in the index locks. Used to protect key in a serialized transaction. Page8-KB data page or index page. Extut neighboring eight data sheets or groups of index pages. Table The entire table, including all data and indexes. DB database.
SQL Server uses different lock mode to lock resources, which mode uses which mode determines how current transaction access to resources.
Lock mode Description Shared (s) is used for operations (read-only operations) that do not modify or update data, such as a SELECT statement. Update (u) is used for resources that can be updated. A common form of deadlock occurs when multiple sessions are read, locked, and then potentially updated resources. Exclusive (x) is used for data modification operations such as Update, Insert, or Delete. Make sure that multiple modifications to the same resource will not occur at the same time. INTENT is used to establish a lock level. SCHEMA is used when performing an operation of the schedule. There are two types of patterns lock: Schema Stability (SCH-S) and Schema Modification (SCH-M).
It is important for any RDBMS to quickly release lock to provide maximum parallelism. You can secure the quick release lock by keeping a transaction as short as possible. If possible, the transaction should not span multiple round-trip to the server, nor should it include the user "thinking". If you use a cursor, you should also make your app quickly extract data, because the scan of unattracked data will occupy the sharing lock on the server, so it will hinder the update. For more information, please see the "Using the ODBC" section later in this chapter.
Change the default lock behavior
Microsoft SQL Server and Oracle allow developers to use non-default locking and isolation behavior. In Oracle, the most common mechanism is the for Update clause of the select command, the set transaction read only command, and the explicit Lock Table command. Because the lock and isolation strategy are so different, it is difficult to map lock selection between Oracle and SQL Server. To better understand this process, it is important to understand the choice of default locking behavior provided by SQL Server.
In SQL Server, modify the most common mechanism for default lock behavior is the SET Transaction ISOLATION LEVEL statement and lock hints supported in the SELECT and UPDATE statements. Set Transaction ISOLATION LEVEL statement Sets a transaction isolation level for a user session. This will become the default behavior of the session unless indicated in the FROM clause of a SQL statement. Transaction is set to set:
SET Transaction Isolation Level {Read Committed | REPEATABLE READ | Serializable}
Read committed
Default SQL Server Isolate Level. If you use this option, your application will not read the data that other transactions have not been submitted. In this mode, once the data is read from the page, it is still necessary to release the sharing lock. If the application re-reads the same data area in the same transaction, you will see the modifications made by other users.
Serializable
If this choice is set, the transaction will be isolated from other transactions. If you don't want to see the changes made by other users in the query, you can set the isolation level of the transaction for serializable. SQL Server will occupy all shared lock until the end of the transaction. You can make the same effect on a smaller level by using Holdlock using Holdlock after the table name in the SELECT statement.
Read uncommitted
If set to this choice, SQL Server readers will not be blocked, just like in Oracle. This selection achieves a locking of the sewage reading or is a lock level of 0, which means that no sharing lock is used and does not use any exclusive lock. When this option is selected, it is possible to read data that is not submitted or dirty; data may change before the end of the transaction, and the rows in the data set may also disappear. This option is the same in all tables in all SELECT statements in all SELECT statements in the same transaction. This is one of the four isolated levels of restrictions. This choice can only be used in the premise that it will use this option in the premise that it will affect the accuracy of your application results.
SQL Server has two ways to implement the READ ONLY function in Oracle:
If transactions in an application requires repeatable behavior, you may need to use the Serializable Isolate level provided by SQL Server. If all database access is read-only, you can set the SQL Server database option to improve performance for read only.
SELECT ... for Update
When an application uses WHERE CURRENT OF syntax to implement location updates or delete on a cursor, first use the Select ... for Update statement in Oracle. In this case, the for Update clause can be removed, because the default behavior of the Microsoft SQL Server cursor is "updatable".
By default, the SQL Server cursor does not occupy the lock in the extracted line. SQL Server uses an optigistic parallel policy (Optimistic Concurrence Strategy) to prevent overworships between updates. SQL Server will give an error message if a user tries to update or delete a read cursor. The application can capture this message and re-perform appropriate updates or delete. To change this behavior, developers can use scroll_locks in a cursor statement. Usually, optimistic parallel policies support high parallelism, so-called usual case means that there are few conflicts between updates. If your application does need to ensure that one line will not be modified after being extracted, you can use Updlock in the SELECT statement. This implies not hinders other readers, but it prohibits other potential writers to get update locks for this data. When using ODBC, you can achieve the same purpose by using SQLSetStmtoption (..., SQL_Concurrency) = SQL_CONCUR_LOCK. However, any other options will be reduced in parallelism.
Table level lock
Microsoft SQL Server can lock the entire table with a Select ... Table_name (TabLock) statement. This is the same as Oracle's Lock Table ... in Share Mode statement. This lock allows others to read a table but prohibit them from modifying the table. By default, the lock will remain until the end of the statement. If you add a Holdlock keyword (Select ... Table_Name (Tablock Holdlock), the lock will remain until the end of the transaction.
You can set an exclusive lock in a SQL Server table with the select ... Table_name (Tablockx) statement. This statement requests exclusive locks on the table. This lock prohibits other people from reading and modifying the table and will remain until the command or the end of the transaction. This is the same as the functionality of the Table ... in Exclusive Mode statement in Oracle.
SQL Server does not provide the NOWAIT option for explicitly lockup requests.
Lock upgrade
When a query is requested to request a row, Microsoft SQL Server generates a page level lock. However, if the query request table, SQL Server will upgrade the lock from the page level to the table level. This process is called a lock upgrade.
Locking increases the scanning and operation of the table that produces larger result sets more effective because it reduces lock management overhead. The SQL statement lacking the WHERE clause is generally caused to increase.
In the read operation, a shared table lock is applied if a shared page level is locked to a table lock. In the following cases, the shared table level lock:
Use the Holdlock or SET Transaction Isolation Level Serializable statement. The optimizer selects a scan of a table. The number of shared locks accumulated in the table exceeds the limit of the lock upgrade.
The default lock upgrade of the table is 200 pages, but this limit can be customized to a percentage of the table size with minimal and maximum range. For more information on locking upgrade limits, please see the SQL Server online manual.
In a write operation, when a UPDATE lock is upgraded to a table lock, apply an exclusive table lock (TabLockx). Excising the list locks in the following cases:
Update or delete the operation without indexing. There are exclusive locks in the table exceed the upper limit of the lock upgrade. Created a clustered index.
Oracle cannot upgrade the line level lock, which will cause some query problems that contain for Update clauses. For example, suppose the Student table has 100,000 row data, and an Oracle user gives the following statement: Select * from student for Update
This statement enforces Oracle RDBMS to lock a row of the Student table in turn; this will take a while. It will never ask for an upgrade to lock to the entire table.
The same query in SQL Server is:
Select * from stay (UPDLOCK)
When this query is running, the page level lock is upgraded to a table level lock, and the latter is more effective and obviously fast.
Dead lock
When a process lockeds the page or table required by another process, and the second process locks the first page you need. At this time, a deadlock will occur. The deadlock is also called to die. SQL Server automatically detects and solves the deadlock. If a deadlock is found, the server will terminate the completed user process.
After each data modification, your program code needs to check 1205 messages, this message indicates a deadlock. If this message is returned, you will explain that a deadlock has occurred and the transaction has rolled back. In this case, your application must restart this transaction.
Death locks can generally be avoided by some simple technologies:
Access tables in the same order in your part of your application. Use a clustered index on each table to force an explicit line order. Make the transaction short.
For more information, see Microsoft Knowledge Base Articles "Detecting and Avoiding Deadlocks in Microsoft SQL Server"
Remote transaction
Perform a remote transaction in Oracle, you must access the remote database node through a database connection. In SQL Server, you must access a remote server. The remote server is a server running SQL Server, and users can access the server with their local server. When a server is set to a remote server, the user can use the system procedure and stored procedure without the need to log in to the above.
The remote server is set to pair. You must configure two servers to make them as remote servers with each other. The names of each server must be stored on the partner server with the sp_addlinkedServer system stored procedure or SQL Server Enterprise Manager.
After setting the remote server, you can use the sp_addremotelogin system stored procedure or SQL Server Enterprise Manager to set up remote login accounts for users who must access the remote server. After this step is completed, you must also give them the permissions of the stored procedure.
Then use the Execute statement to execute the process on the remote server. This example executes the validate_student stored procedure on the remote server Studsvr1, and stores the successful or failed return condition in @ RetValue1:
DECUTE @ RetValue1 Int Execute @retValue = studsvr1.student_db.student_admin.validate_student '111111111'
For more information, please see the SQL Server online manual.
Distributed affairs
If you modify the tables on two or more database nodes, Oracle automatically initializes a distributed transaction. SQL Server distributed transactions use two-step submission services included in Microsoft Distribute Coordinator, MS DTCs in SQL Server.
By default, SQL Server must be notified to participate in distribution transactions. SQL Server is involved in one MS DTC transaction to store any of the following methods: Begin Distributed Transaction statement. This statement starts a new MS DTC transaction. A client application calls DTC transaction interface directly.
In the following example, pay attention to the distributed update of the local table grade and remote table Class (using a class_name process):
BEGIN DISTRIBUTED TRANSACTION UPDATE STUDENT_ADMIN.GRADE SET GRADE = 'B ' WHERE SSN = '111111111' AND CCODE = '1234' DECLARE @ retvalue1 int EXECUTE @ retvalue1 = CLASS_SVR1.dept_db.dept_admin.class_name '1234', 'Basketweaving' COMMIT TRANSACTION GO
If the program cannot complete the transaction, the transaction is terminated through the Rollback Transaction statement. If the program fails or participating resource manager fails, the MS DTC terminates the transaction. MS DTC does not support distributed storage points or Save Transaction statements. If a MS DTC transaction fails or then roll, the entire transaction returns to the starting point of the distributed transaction without ignoring any storage points.
Two-step submission process
Oracle and MS DTC two-step submission mechanisms are similar in operation. The first step submitted in SQL Server two steps, the Transaction Manager requests every participating resource manager ready to be submitted. If any resource manager is not ready, Transaction Manager broadcasts an exception interrupt to all members related to the transaction.
If all resource managers can successfully prepare, transaction managers broadcast a submission decision. This is the second step in the submission process. When a resource manager is ready, the transaction is submitted or failed, this is not allowed. The MS DTC maintains a continuous log, so its submission or interrupt decision is lasting. If an resource manager or transaction manager fails, it can coordinate it when they are reconnected.
SQL language support
This section briefly introduces similarity and differences in Transact-SQL and PL / SQL language syntax, and gives a conversion policy.
SELECT and data operation declaration
When you put the Oracle DML statement and the PL / SQL program to SQL Server, follow these steps:
Check if all SELECT, INSERT, UPDATE, and DELETE statements are valid. Do any needs. Replace all external nodes to SQL-92 external node syntax with the appropriate SQL Server function instead of the Oracle function Check all comparison operators with " " instead of "||" string series operator. Use the Transact-SQL program instead of the PL / SQL program to change all the PL / SQL cursors to the non-visible SELECT statement or the Transact-SQL cursor. Use the Transact-SQL process instead of the PL / SQL process, functions, and package. Convert the PL / SQL trigger to the Transact-SQL trigger. Use the Set Showpla statement to debug your query to get high performance.
SELECT STATEMENTS statement
The syntax of the SELECT statement used by Oracle and Microsoft SQL Server is similar.
OracleMicrosoft SQL 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 ... CONNECT BY] [{UNION | UNION ALL | INTERSECT | MINUS} SELECT ...] [ORDER BY clause] [FOR UPDATE] SELECT select_list [INTO new_table_] FROM table_source [WHERE search_condition] [GROUP BY [ALL] group_by_expression [, ... n] [WITH {CUBE | ROLLUP }] [Having Search_Condition] [ASC | DESC]] IN Addition: UNION Operator ComputesSQL Server does not support Oracle-based overhead optimizer hints, these implications must be removed. It is recommended to use SQL Server's overhead optimizer. For more information, see the "Debug SQL Statement" section later in this chapter.
SQL Server does not support Oracle's Start With ... Connect By clause. In SQL Server, you can use the stored procedure to perform the same task instead.
SQL Server does not support Oracle's INTERSECT and MINUS collection. The EXISTS and NOTSTS clauses of SQL Server can complete the same task.
The following example uses the INTERSECT operator to find the course code and course name for all students. Note how the EXISTS operator replaces the Intersect operator. The data returned by both is the same.
OracleMicrosoft SQL 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)
The following example uses the Minus operator to find out the class without students.
OracleMicrosoft SQL SELECT CCODE, CNAMEFROM DEPT_ADMIN.CLASSMINUSSELECT C.CCODE, C.CNAMEFROM STUDENT_ADMIN.GRADE G, DEPT_ADMIN.CLASS CWHERE C.CCODE = G.CCODESELECT CCODE, CNAMEFROM DEPT_ADMIN.CLASSCWHERE NOT EXISTS (SELECT 'X' FROM STUDENT_ADMIN.GRADE GWHERE C.ccode = g.ccode)
INSERT statement
The syntax of the Insert statement for Oracle and Microsoft SQL Server is similar.
OracleMicrosoft SQL INSERT INTO {table_name | view_name | select_statement} [(column_list)] {values_list | select_statement} INSERT [INTO] {table_name [[AS] table_alias] WITH (
OracleMicrosoft SQL Insert Into (Select SSN, CCODE, GRADE "VALUES ('11111111', '1111', NULL) INTO GRADE (SSN, CCODE, GRADE) VALUES ('11111111",' 1111 ', NULL)
Transact-SQL's VALUES_LIST parameters provide the keyword default of SQL-92 standards, but this is not supported in Oracle. When the insertion operation is performed, this keyword gives the default value for the column to use. Insert a NULL if a given column does not have a default. If the column does not allow NULL, an error message is returned. If the column is defined as a time slice data type, insert the next continuous value.
Keyword Default cannot be used for identity columns. To generate the next continuous value, the column with the Identity property must not be included in Column_List or VALUES_CLASE. You don't have to use the default keyword to get a list of defaults. In Oracle, if the column is not referenced by a column_list and it has a default, the default value will be placed in the column. This is the most compatible method for performing transplantation.
A useful Transact-SQL option (Execute Procedure_name) is an execution process and outputs its output value to a target table or view with a pipe. Oracle does not allow you to do this.
UPDATE statement
Because Transact-SQL supports the most syntax of Oracle's UPDATE, only small modifications are required.
OracleMicrosoft SQL UPDATE {table_name | view_name | select_statement} SET [column_name (s) = {constant_value | expression | select_statement | column_list | variable_list} {where_statement} UPDATE {table_name [[AS] table_alias] WITH (
Oracle's Update command can only use program variables in a PL / SQL block. The Transact-SQL language does not require blocks when using variables.
OracleMicrosoft SQL DECLAREVAR1 NUMBER (10,2); BEGIN VAR1: = 2500; UPDATE STUDENT_ADMIN.STUDENT SET TUITION_TOTAL = VAR1; END; DECLARE @ VAR1 NUMERIC (10,2) SELECT @ VAR1 = 2500UPDATE STUDENT_ADMIN.STUDENTSET TUITION_TOTAL = @ VAR1
In SQL Server, keyword default can be used to set a column to its default. You can't use Oracle's update command to set a default value for it.
Both Transact-SQL and Oracle SQL support using subqueries in a UPDATE statement. However, Transact-SQL's From clauses can be used to create a node-based UPDATE. This capability is your Update syntax more readable and improves performance in a sense.
OracleMicrosoft SQL 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 = 1500 from grade g where s.ssn = g.ssn and g.ccode = '1234'Delete statement
In most cases, you don't need to modify the DELETE statement. But if you execute the delete operation of the SELECT statement in Oracle, you must modify it because this feature is not supported in Transact-SQL.
Transact-SQL supports the use of sub-queries in the WHERE clause, the FROM clause is the same. The latter can generate more efficient statements. Please see the example in the "Update Statement" in front.
OracleMicrosoft SQL DELETE [FROM] {table_name | view_name | select_statement} [WHERE clause] DELETE [FROM] {table_name [[AS] table_alias] WITH (
TRUNCATE TABLE statement
The syntax of the TRUNCATE TABLE statement in Oracle and Microsoft SQL Server is similar. The TRUNCATE TABLE statement is used to clear all the rows in a table, and this operation cannot be rolled back. The structure and index of the table still exist. The Delete trigger will not be executed. If the table is referenced by a Foreign Key, the table cannot be cut off.
OracleMicrosoft SQL Truncate Table Table_name [{Drop | Reuse] Truncate Table Table_name
In SQL Server, this statement can only be given by the owner of the table. In Oracle, this statement can only be used when you are ownership of the table or if you have a DELETE TABLE system privilege.
Oracle's TRUNCATE TABLE command can freely release the storage space occupied by the rows in the table. The Truncate Table of SQL Server always reclaims the space occupied by the data in the table and the space associated with the index.
Operating data in Identity and TimeSTAMP columns
Oracle sequence is a database object that is not directly related to any given table or column. The relationship between a column and a sequence is implemented in the application, and the method is to assign a sequence value to a column. Therefore, when working with the same sequence, Oracle does not enhance any rules. However, in the identity column of Microsoft SQL Server, the value cannot be updated and cannot use the default keyword. By default, data cannot be inserted directly into a identity column. Identify each line of the newly inserted table to automatically generate a unique, orderful number. This default setting can be overwritten with the following set statement.
Set Identity_Insert Table_name on
When Identity_Insert is set to ON, the user can insert any value in the identification column of the new row. To prevent input repeated values, a unique index must be created on this column. The purpose of this statement is to allow users to recreate an accidental deleted value for a row. @@ identity can be used to get the last identification value.
The Truncate Table statement retests a identity column to its original SEED value. If you don't want to reset the identity value for a column, you can use the delete clause without WHERE clauses to replace the Truncate Table statement. You must estimate what impact this will give you Oracle transplants, because Oracle Sequences will not reset with the Truncate Table command.
For the TimeStamp column, you can only perform insert or delete operations. If you try to update a time information column, you will receive such an error message.
MSG 272, Level 16, State 1 CAN't Update A TimeStamp Column.
Lock the requested row
Oracle uses for Update clause to lock the row specified in the select command. In Microsoft SQL Server, you don't need to use its equivalent clause because it is a default.
Listance and calculating clause
SQL Server's Compute clause is used to generate a running function (SUM, AVG, Min, Max, and Count), which seems to be an additional line of query results. This allows you to see a set of results and summary. You can calculate a summary value for a subgroups, and you can calculate more consolidated functions for the same set.
Oracle's SELECT command syntax does not support compute clauses. However, the COMPUTE clause of SQL Server is the same as the COMPUTE command that can be found in the Oracle's SQL * Plus query tool.
Connection subsee (Join clauses)
Microsoft SQL Server 7.0 allows for up to 256 tables, including temporary and permanent tables in a connection clause. In Oracle, there is no connection limit.
When using external connections in Oracle, the external connection operator ( ) is typically placed behind the foreign key. ( ) Rely, relying on a few unique values to identify the column. This is always like this unless the subscriber is allowed. If the sub-column allows null values, ( ) is placed on the parent key (Primary Key or UNIQUE). You can't put ( ) on both sides of the equal sign (=).
With SQL Server, you can use (* =) and (= *) external connection operators. (*) Used to identify a column with a lot of unique values. If the sub-column does not allow null values, (*) is placed on the side of the equal number. In Oracle, the placement of (*) is exactly the opposite. You can't put (*) on both sides of the equivalent. (* =) And (= *) are considered to be a conventional connection operator. SQL Server also supports the SQL-92 standard connection operators listed below. It is recommended that you use this grammar. SQL-92 Standard Grammatics ratio (*) operator is more powerful, limited to smaller.
JOIN Operation Description Cross Join This is the cross product of two tables. If you do not specify a WHERE clause in an old-style connection, return the same line. In Oracle, this type of connection is called a Cartesian connection. Inner This connection specifies to return all internal rows. Any unmatched row will be discarded. This connection is the same as the same standard Oracle table connection. LEFT [OUTER] This type of connection specifies all the external rows of the right table, even if no matching line is found. The operator is the same as Oracle external connections ( ). Right [Outer] This type of connection specifies all the external rows of the left table, even if no matching line is found. The operator is the same as Oracle external connections ( ). Full [OUTER] If the line does not match the selection criterion from any table, specify that the row is included to the result set, and its output column that matches other tables is set to NULL. This is the same as both the Oracle external connection operator in "=" (COL1 ( ) = COL2 ( )), and in Oracle, this is not allowed.
The following example returns a list of courses to be on all students. External connections defined between student tables and grades allowed all students, even those who don't participate in any course. There is also an external connection on the curriculum, which returns the course name. If there is no external connection on the course table, you will not return those who don't participate in any course, because their course code (CCDOE) is null.
OracleMicrosoft 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 Son g.ssn = s.ssnleft outerial joindept_admin.class con g.ccode = c.ccode
Use the SELECT statement to make a table name
Both Microsoft SQL Server and Oracle support with the SELECT statement as a table source when performing queries. SQL Server requires an alias (alias); whether you use an alias in Oracle is optional.
OracleMicrosoft SQL 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 BLOBS
Microsoft SQL Server implements binary large objects, blobs with text and Image. Oracle implements Blobs with long and long Raw columns. In Oracle, the select command can query the value of 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 you to read part of the text and image columns. Oracle does not provide equivalent statements for long and long RAW.
The readtext statement takes advantage of a Text_Pointer, which can be obtained with the TextPtr function. The TextPtr function returns a pointer to the text or Image in a particular row. If the query is more than one line returns, return to the TEXT or Image of Image in the last row. Since TEXTPTR returns a 16-byte binary string, it is best to declare an internal variable to keep this text pointer, then use this variable in ReadText.
The READTEXT statement shows that there are several bytes to return. The value in the @@ textsize function limits the number of returned characters or bytes, if the value is less than the value of the readtext declaration, use the latter instead.
The SET statement can be used to illustrate the size of the text data returned by a SELECT statement in bytes in bytes. If you set a TextSize that is 0, the size is reset to the default (4 KB). Setting TextSize has an impact on the @@ TextSize function. When the SQL_MAX_LENGTH statement option changes, SQL Server ODBC automatically sets the TextSIZE parameter.
In Oracle, use Update and Insert commands to change the value in the long and long Raw columns. In SQL Server, you can use standard Update and Insert statements, or you can use UpdateText and WriteText statements. UpdateText and WriteText are allowed to allow a NonLogged option, and UpdateText also allows sections of the text or graphics column to be updated.
UpdateText can be used instead of existing data, delete existing data, or insert new data. The newly inserted data can be a constant value, a table name, a column name, or a text pointer.
The WRITETEXT statement completely overrides any existing data in the columns it affected. Use WRITETEXT to replace text data, use UpdateText to modify text data. Because the UpdateText statement changes only one part of a text or graphical value, the UpdateText statement is more flexible.
For more information, see the SQL Server online manual.
function
The table in this section shows the relationship between the Scalar-Valued and Total Functions of Oracle and SQL Server. Although the name is the same, it is important to note the difference between the number and type of the function parameters. Those functions that only provide by Microsoft SQL Server are not mentioned in this list, because this chapter is limited to make the existing Oracle application make it easier. For example, these functions are not supported by Oracle: DegRees, PI (PI), and Random (RAND) digital / mathematical functions
Below is the digital / mathematical function supported by Oracle and their Microsoft SQL Server equivalent functions.
Absolute value function OracleMicrosoft SQL Server ABSABS Arc cosineACOSACOS Arc sineASINASIN Arc tangent of nATANATAN Arc tangent of n and mATAN2ATN2 Smallest integer> = valueCEILCEILING CosineCOSCOS Hyperbolic cosineCOSHCOT Exponential valueEXPEXP Largest integer <= value FLOORFLOOR Natural logarithmLNLOG Logarithm, any baseLOG (N) N / A Logarithm , base 10LOG (10) LOG10 Modulus (remainder) MODUSE MODULO (%) OPERATOR PowerPOWERPOWER Random numberN / ARAND RoundROUNDROUND Sign of numberSIGNSIGN SineSINSIN Hyperbolic sineSINHN / A Square rootSQRTSQRT TangentTANTAN Hyperbolic tangentTANHN / A TruncateTRUNCN / A Largest number in listGREATESTN / A Smallest number in ListleAstn / A Convert Number if Nullnvlisnull
Character function
Below is the character functions supported by Oracle and their Microsoft SQL Server equivalents.
Function OracleMicrosoft SQL Server converts characters to ASCIIASCIASCII string Connect (Expression Expression) Converting ASCII to character chrchar Returns the start character in the string (left) InstrCharindex Transfer characters to lowercase LowerLower to convert characters to uppercase Upperupper fill characters The left LPADN / A Clear Start Blank LTRIMLTRIM Clear the Starting Mode in the Number of RTRIMRTRIM Strings (Pattern) InstrPatindex Multiple Repeat Strings RPADReplicate String Strings SoundexSoundEx Repeat Space String RPADSpace Switch from Digital Data Character Data TO_CHARSTR Scer Substrsubstring Replace Character ReplaceStuff Translation Initcapn / A Translation String Translation InitCapn / A Translation String Translation InitCapn / A Translation String TranslateN / A String LengthlengthLength Al LEN Listing The smallest string in the list of largest strings in the list Leastn / a Convert String NVLisnull if null
Date function
Below is the date function supported by Oracle and their Microsoft SQL Server equivalent functions.
Date addition function OracleMicrosoft SQL Server (date column /- value) orADD_MONTHSDATEADD difference (date column /- value) orMONTHS_BETWEENDATEDIFF current date and time SYSDATEGETDATE two dates () zone conversion NEW_TIMEN / A during the last day of the month LAST_DAYN / A The first Sunday next question after the date of the Date String to_ChardateName Represents Date TEMBER TO_NUMBER (TO_CHAR)) DatePart Date RoundConvert date Truncconvert String Translation To Date To_DateConvert If null Date NVLisnull Conversion Function
Below is the conversion function supported by Oracle and their Microsoft SQL Server equivalent functions.
Function OracleMicrosoft SQL Server Digital Convert to Character TO_CHARCONVERT Characters Convert to Number TO_NUMBERCONVERT Date Convert to Character TO_CHARCONVERT Characters Convert to 2 En-Binary HEX_TO_RAWCONVERT 2 Enter to 16 Enter Raw_to_HEXCONVERT
Other line-level functions
Below is Oracle's other row level functions and their Microsoft SQL Server equivalent functions.
Function OracleMicrosoft SQL Server returns the first non-NULL expression DECODECOALESCE the current sequence value CURRVALN / A sequence value NEXTVALN / A If exp1 = exp2, return nullDECODENULLIF user login account ID number UIDSUSER_ID user login name USERSUSER_NAME user database user database ID number UIDUSER_ID Useruser_name Current User Current_Usercurrent_user User Environment (Audit Trail) Leveln / A in the CONNECT BY clause Leveln / A
Total function
Below are the total functions supported by Oracle and their Microsoft SQL Server equivalents.
Function OracleMicrosoft SQL Server AverageAvgavg CountCountCount Maximum DeviationsTDDevstdev or Stdevp SummationSumsum VarianceVarianceVar or Varp
Condition test
The Case expression of Oracle's Decode statement and Microsoft SQL Server performs condition tests. When the value in Test_Value and any expression of the following match, the associated value is returned. If you do not find any matching values, return DEFAULT_VALUE. If DEFAULT_VALUE is not specified, Decode and Case return a NULL when there is no match. The table below shows the syntax of the statement, and an example of the conversion DECODE command is given.
OracleMicrosoft SQL DECODE (test_value, expression1, value1 [[, expression2, value2] [...]] [, default_value]) CREATE VIEW STUDENT_ADMIN.STUDENT_GPA (SSN, GPA) AS SELECT SSN, ROUND (AVG (DECODE (grade, 'A' , 4, 'a ', 4.3, 'A-', 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] END CREATE 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 ' TEN 3.3when 'b-' THEN 'C ' THEN 'DHEN' C- 'THEN 1.7WHEN' D 'THEN' DHEN 'D ' TEN 1.3when 'D-' Then 0.7ELSE 0nd ), 2) from student_admin.gradeGroup By SSNCase expressions can support Boolean tests with a SELECT statement, which is not allowed by the decode command. For more information on CASE expressions, see the SQL Server online manual.
Convert value into different data types
The CONVERT and CAST functions for Microsoft SQL Server are multi-objective conversion functions. They provide similar functions that convert an expression of a data type 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 function of SQL-92 standard. These functions perform the same features as Oracle's To_CHAR, TO_NUMBER, TO_DATE, HEXTORAW, and RAWTTEXT functions.
The data type referred to herein is a system data type that will be converted to be converted. You cannot use user-defined data types. The length parameters are optional, which is used for char, varchar, binary, and varbinary data types. The maximum allowed length is 8000.
Convert OracleMicrosoft SQL Server characters to digital TO_NUMBER ('10') Convert (Numeric, '10') Number to Character 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 Character 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) 16 Enter 2-en-en-HexToraw (Binary, '1F') 2 into 16 Rawtohex (binary_column) Convert (char, binary_column) Please note how the string is converted to date. In Oracle, the default date format model is "DD-MON-YY". If you use any other format, you must provide a suitable date format model. The CONVERT function automatically converts the standard date format and does not require any format model.
When the date is converted to the string, the default output of the Convert function is "DD MON YYYY HH: MM: SS: MMM (24H)". Format the output with a digital style code so that it can output a date format model for other types. For more information on the CONVERT function, see the SQL Server online manual.
The following table shows the default output of the Microsoft SQL Server date.
Without CenturyWith CenturyStandardOutput -0 or 100 (*) Defaultmon dd yyyy hh: miAM (or PM) 1101USAmm / dd / yy 2102ANSIyy.mm.dd 3103British / Frenchdd / mm / yy 4104Germandd.mm.yy 5105Italiandd-mm-yy 6106-dd MON YY 7107-MON DD, YY 8108-HH: mm: ss -9 or 109 (*) Default MilliseCondsmon DD YYYY HH: MI: SS: MMM (AM OR PM) 10110USAMMM-DD-YY 11111JAPANYY / MM / DD 12112InYMMDD - 13 or 113 (*) Europe Default DD MON YYYY HH: MM: SS: MMM (24H) 14114-HH: MI: SS: MMM (24h)
User definition function
The Oracle PL / SQL function can be used in the Oracle SQL statement. The same functions can be achieved in Microsoft SQL Server.
Instruments you can use in the SQL Server instead.
OracleMicrosoft SQL Server SELECT SSN, FNAME, LNAME,) TUITION_PAID, TUITION_PAID / GET_SUM_ MAJOR (MAJOR) AS PERCENT_MAJORFROM STUDENT_ADMIN.STUDENTSELECT SSN, FNAME, LNAME, TUITION_PAID, TUITION_PAID / SUM_MAJOR AS PERCENT_MAJORFROM STUDENT_ADMIN.STUDENT, (SELECT MAJOR, SUM (TUITION_PAID) SUM_MAJOR FROM STUDENT_ADMIN.STUDENT GROUP BY MAJOR) SUM_STUDENTWHERE STUDENT.MAJOR = SUM_STUDENT.MAJOR CREATE 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 is required; use create procedure syntax. Compare operator
The comparison operator of Oracle and Microsoft SQL Server is almost the same.
ORCLEMICROSOFT SQL Server is equal to (=) (=) greater than (>) (>) less than or equal to (> =) (> =) less than or equal to (<=) (<=) is not equal to (! =, <>, ^ =) (! =, <>, ^ =) Is not greater than, not less than N / a!>,!
Pattern matching
SQL Server's LIKE keyword provides a useful wildcard search feature, which is not supported in Oracle. SQL Server also supports ([]) and ([^]) wildcards except for all RDBMS supported. ([]) Characters are used to query all individual characters in a range. For example, if you need to query the data containing a character from A to F, you can write: "Like '[ABCDEF]'". The validity of these additional wildcards is given in the table below.
OracleMicrosoft SQL SELECT * AROME LNAME LIKE 'A% LNAME LNAME LIKE' or LNAME LIKE 'C%' Select * from student_admin.studentwhere Lname Like '[ABC]%'
[^] Wildcard is used to mark those characters that are not within a specific range. For example, if all characters other than A to F are acceptable, you can write: Like '[^ a - f]' or Like '[^ Abcdef]'.
For more information about the LIKE keyword, see the SQL Server online manual.
Use null in comparison
Although Microsoft SQL Server traditionally supports SQL-92 standards and some non-standard NULL behaviors, it also supports NULL in Oracle.
To support distributed queries, SET ANSI_NULLS must be set to ON.
When connecting, SQL Server's SQL Server ODBC drivers and OLE DB providers are automatically set to ON. This setting can be configured in an ODBC data source, an ODBC connection property, or an OLE DB connection attribute set in the application before connecting to SQL Server. When connected to the DB-Library application, set ANSI_NULLS is default to OFF.
When SET ANSI_DEFAULTS is ON, SET ANSI_NULLS is allowed.
For more information about NULL usage, see the SQL Server online manual.
String connection
Oracle uses two pipe symbols (||) as a string connection operator, SQL Server uses a plus sign ( ). This difference requires you to make small modifications in your app.
OracleMicrosoft SQL SELECT FNAME || '' || Lname As NameFrom Student_admin.studentSelect Fname ' Lname As Namefrom Student_admin.student
CONTOL-OF-FLOW
Stream control language controls the SQL statement to execute flow, statement blocks, and stored procedures. PL / SQL and Transact-SQL provide most identical structures, but there are still some syntax differences.
Keyword
This is a keyword supported by two RDBMS.
Statement Oracle PL / SQLMICROFT SQL ServerTransAct-SQL Declared Variable DeclaredEclare Sentence Block Services ... End; Begin ... END Condition Processing IF ... THEN, ELSIF ... Then, ElseEndif; if ... [begin ... end] else
Transact-SQL and PL / SQL variables are created with Declare keywords. TRANSACT-SQL variables are used in @ tags, just like PL / SQL, initialization with null value when creating.
OracleMicrosoft SQL 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 VARCHAR2 ( 20), @ vbpter_date datetime, @ VLOAN_AMOUNT NUMERIC (12, 2)
Transact-SQL does not support% type and% rowType variable data type definition. A Transact-SQL variable cannot be initialized in the declare command. Oracle's NOT NULL and CONSTANT keywords cannot be used in the Microsoft SQL Server data type definition.
Like Oracle's long and long RAW data types. Text and graphical data types cannot be used to define variables. In addition, Transact-SQL does not support the definition of records and tables for PL / SQL style.
Assign value for variables
Oracle and Microsoft SQL Server provide the following methods to assign a local variable.
OracleMicrosoft SQL Assignment operator (: =) SET @local_variable = value SELECT ... INTO syntax for selecting column values from a single rowSELECT @local_variable = expression [FROM ...] for assigning a literal value, an expression involving other local variables, or a Column Value from A Single Row Fetch ... INTO SYNTAXFETCH ... INTO SYNTAX
There are some syntax examples here.
OracleMicrosoft SQL 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
Both Oracle PL / SQL and Microsoft SQL Server Transact-SQL support to mark the phrase block with begin ... END terms. Transact-SQL does not need to use a statement after the DECLARE statement. If there is more statements in the IF statement and the While loop in Microsoft SQL Server, you need to use the Begin ... END statement block.
OracleMicrosoft SQL DECLAREDECLARE VARIABLES ... BEGIN - THIS IS REQUIRED SYNTAXPROGRAM_STATEMENTS ... IF ... THENSTATEMENT1; STATEMENT2; STATEMENTN; END IF; WHILE ... LOOPSTATEMENT1; STATEMENT2; STATEMENTN; END LOOP; END; - THIS IS REQUIRED SYNTAXDECLAREDECLARE VARIABLES ... BEGIN - THIS IS OPTIONAL SYNTAXPROGRAM_STATEMENTS ... IF ... BEGIN STATEMENT1STATEMENT2STATEMENTNEND WHILE ... BEGINSTATEMENT1STATEMENT2STATEMENTNEND END - THIS IS REQUIRED SYNTAX
Conditional treatment
The conditional statements of the Microsoft SQL Server Transact-SQL include IF and ELSE, but do not include the elseiF statement in Oracle PL / SQL. You can use a nest multi-IF statement to reach the same effect. For a wide range of conditions, it is easier and readable with a Case expression.
OracleMicrosoft SQL 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' THEN 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_Program When 'u' TEN 'TEN' MASTERS 'WHEN' P '' TEN 'PHD'. ELSE 'Unknown'END Repeat statements (loop)
Oracle PL / SQL provides unconditional loop and for loop. Transact-SQL provides a While loop and goto statement.
While Boolean_Expression {SQL_Statement | Statement_Block} [Break] [Continue]
The While loop needs to test a Boolean expression to determine the repeated execution of one or more statements. As long as the given expression results are true, this (some) statements have been repeated. If there are multiple statements that need to be executed, these statements must be placed in a BEGIN ... END block.
OracleMicrosoft SQL DECLARECOUNTER NUMBER; BEGINCOUNTER: = 0WHILE (COUNTER <5) LOOPCOUNTER: = COUNTER 1; END LOOP; END; DECLARE @ COUNTER NUMERICSELECT @ COUNTER = 1WHILE (@COUNTER <5) BEGIN SELECT @COUNTER = @COUNTER 1END
The execution of the statement can be controlled by Break and Continue keywords within the cycles. The Break keyword makes the WHILE cycle unconditionally, and the Continue keyword restarts the statement after the WHILE runs. The Break keyword is equivalent to the EXIT key in Oracle PL / SQL. And keywords in Oracle and Continue equivalents
GOTO statement
Oracle and Microsoft SQL Server have a goto statement, but the syntax is different. The goto statement runs Transact-SQL to run at the specified label, and any statement between the specified label after the goto statement will not be executed.
Oraclemicrosoft SQL Goto Label; << Label Name Here >> Goto labelprint statement
The Print statement of Transact-SQL performs the same operation as the RDBMS_Output.put_line process with the PL / SQL. This statement is used to print the information given by the user.
The message cap of printing with a Print statement is 8,000 characters. Variables defined as char or varchar data types can be embedded in the print statement. If the variables of other data types are used, the CONVERT or CAST function must be used. Local variables, global variables can be printed. You can close the text with single quotes or dual quotes.
Return from the stored procedure
All Microsoft SQL Server and Oracle have Return statements. Return makes your program from the query or unconditional jump out of the process. Return is immediate, complete, and can be used to jump out of any part of the process, batch or speech block. The statement behind Reutr will not be executed.
OracleMicrosoft SQL Return Expression: return [integer_expression]
Raising Program Errors
Transact-SQL's raiserror returns a user-defined error message and sets a system flag to record an error. This feature is similar to the functionality of the PL / SQL Raise_Application_ERROR exception processor.
The RAISError statement allows customers to re-acquire an entry of the SysMessages table, or create a message with the severity and status information specified by the user. After being defined, the message is sent back to the client as a system error message.
Raiserror ({msg_id | msg_str}, severity, state [, argument1 [, argument2]]) [WITH OPTION]
When converting your PL / SQL program, you may not use the RaisError statement. In the sample code below. The PL / SQL program uses the Raise_Application_ERROR exception processor, but the Transact-SQL program is useless. Include the RAISE_APPLICATION_ERROR exception processor is to prevent PL / SQL from returning an unclear unprocessed exception error message. Instead, when an unforeseen problem occurs, the abnormal processor always returns Oracle error message.
When a TRANSACT-SQL fails, it always returns a detailed error message to the client. Therefore, unless certain specific error processing is required, it is generally not required to be the raiserror statement.
OracleMicrosoft SQL 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
Realize a cursor
Oracle always requires a cursor when using the SELECT statement, regardless of how much rows are requested from the database. In Microsoft SQL Server, the SELECT statement does not attach the cursor on the row of customers as the default result collection. This is an effective way to return data to the client application.
SQL Server provides two interfaces for the cursor function. When using a cursor during the Transact-SQL batch or stored procedure, the SQL statement can be used to declare, open, and extract from the cursor, just like positioning updates and deletion. When using cursors from DB-Library, ODBC, or OLEDB programs, SQL Server explicitly calls built-in server functions to process cursors more efficient. When entering a PL / SQL process from Oracle, first determine if a cursor is needed in Transact-SQL to implement the same function. If the cursor only returns a row to the client, use the non-target SELECT statement to return to the default result collection. If the cursor is used to get a data from the row to the local process variable, you must use a cursor in Transact-SQL.
grammar
The following table shows the syntax using the cursor.
Operation OracleMicrosoft SQL Server declare a cursor CURSOR cursor_name [(cursor_parameter (s))] IS select_statement; DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [ TYPE_WARNING] FOR select_statement [FOR UPDATE [oF column_name [, ... n]]] open a cursor oPEN cursor_name [(cursor_parameter (s))]; oPEN cursor_name extraction (Fetching) from the cursor FETCH cursor_name INTO variable (s) FETCH [[ Next | prior | first | Last | Absolute {n | @nvar} | {n | @nvar}] from] CURSOR_NAME [INTO @variable (s)] update extract Update Table_Nameset Statement (s) ... WHERE CURRENT OF CURSOR_NAME; UPDATE table_nameSET statement (s) ... WHERE CURRENT OF cursor_name deleted rows fetched dELETE FROM table_name WHERE CURRENT OF cursor_name; dELETE FROM table_name WHERE CURRENT OF cursor_name close the cursor CLOSE cursor_name; CLOSE cursor_name cursor data structures cleared N / ADEALLOCATE cursor_name
Declare a cursor
Although the Transact-SQL Declare Cursor statement does not support the use of cursor parameters, it does support local variables. When the cursor is opened, it uses the value of these local variables. Microsoft SQL Server provides a number of additional features in its Declare Cursor.
The INSENSITIVE option is used to define a temporary copy to create data to be used by the cursor. All requests for the cursor are replied by this temporary table. Therefore, modifications to the original table do not reflect those data returned by FETCH to the cursor. This type of cursor access is not modified.
The application can request a cursor type and perform a Transact-SQL statement that is not supported by the requested server cursor type. SQL Server returns an error, indicating that the cursor type is changed, or a set of parameters, implicit conversion cursors. To get a complete list of the Trigger SQL Server 7.0 implicitly implicit cursors from one type to another type of parameters, see the SQL Server online manual.
The Scroll option allows for later, backward, absolute, and relative data extraction in addition to the forward extraction. A scrolling cursor uses a cursor model of a key collection, in which the deletion and update of any user submitted to the table will affect the subsequent data. The above features only work only when the cursor is declared with the InSensitive option declaration. If the read only option is selected, the update of the rows in the cursor is disabled. This option allows you to update your cursor.
Update [of column_list] The statement is used to define an updated column in the cursor. If [of column_list] is provided, it is just those columns that are listed can be modified. If no column is specified. All columns can be updated unless the cursor is defined as read only.
It is important to note that the name of the SQL Server cursor is to connect to themselves. This is different from the name range of local variables. You cannot declare a cursor of the same name as the existing cursor on the same user, unless the first cursor is released.
Open a cursor
Transact-SQL does not support passing parameters to an open cursor, which is different from PL / SQL. When a TRANSACT-SQL cursor is opened, the members and the order of the result set will be fixed. Updates and deletes of other users' cursors of the original table will be reflected on the data extraction of all cursors defined by the INSENSITIVE option. For an Insensitive cursor, a temporary table will be generated.
Extract data
The Oracle cursor can only move the dates backward or relatively rolling capabilities. The SQL Server cursor can be scrolled forward or backwards, and how to scroll, it is determined by the data extraction options given by the following table. These options can only be used only if the cursor is declared with the Scroll option.
Rolling Option Description Next If this is the first time for the cursor, the first line of the result set is returned; otherwise, the cursor is moving the cursor to the next line in the result. Next is the basic method of moving in the result collection. Next is the default cursor extraction (FETCH). PRIOR returns the front line of the result collection. First move the cursor to the first line of the result collection, and return the first line. Last moves the cursor to the last line of the result collection, and return to the last line. Absolute n Returns the Nth line of the result collection. If n is a negative number, then the row, RELATIVE N, returns the Nth line of the currently mentioned row, if n is a negative number, returns a countdown nth line from the relative position of the cursor.
Transact-SQL's FETCH statement does not require an INTO clause. If you do not specify a returned variable, the line automatically returns to the customer as a single line result collection. However, if your process must give the customer, a SELECT statement that does not have a cursor is more effective.
Before each fetch, the @@ fetch_status function is updated. This is similar to using Cursor_name% Found and Cursor_name% notfound variables in PL / SQL. The @@ fetch_status function is set to 0 after each successful data extraction. If the data extraction attempts to read a data at the end of the cursor, the value of -1 is returned. If the requested row is deleted from the table after the cursor is open, the @@ fetch_status function returns a value of -2. Only the cursor is defined with the scroll option, the -2 value will be returned. This variable must be checked after each data extraction to ensure data validity.
SQL Server does not support Oracle's cursor for loop syntax.
Current of clause
The update and deletion of Current OF classes syntax and functions are the same in PL / SQL and Transact-SQL. In a given cursor, the positioned Update and Delete are performed on the current line.
Close a cursor
TRANSACT-SQL's Close Cursor statement off the cursor, but reserves the data structure for reopen. PL / SQL's Close Cursor statement is turned off and all data structures are released. Transact-SQL needs to use a DEALLOCATE CURSOR statement to clear the cursor data structure. The DEAALLOCATE CURSOR statement is different from the Close Cursor, the latter reserves the data structure for reopen. DEAALLOCATE CURSOR releases all the data structures related to the cursor and clear the definition of the cursor.
Cursor example
The following example shows a cursor statement in PL / SQL and Transact-SQL equivalents.
OracleMicrosoft SQL 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; BEGIN OPEN CUR1; FETCH CUR1 INTO VSSN, VFNAME, VLNAME; WHILE (CUR1% FOUND) LOOP FETCH CUR1 INTO VSSN, VFNAME, VLNAME; END LOOP; CLOSE CUR1; END; DECLARE curl CURSOR FOR SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSNOPEN CUR1FETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAMEWHILE (@@ FETCH_STATUS <> -1) BEGIN FETCH NEXT FROM CUR1 INTO @VSSN, @VFNAME, @VLNAME ENDCLOSE CUR1DEALLOCATE CUR1
Debug SQL statement
This section provides information about SQL Server tools, and you can use these tools to debug the Transact-SQL statement. For more information on debugging the SQL Server database, see "Performance Adjustment Section" in front of this volume.
SQL Server Query Analyzer (SQL Server Query Analyzer)
You can use the SQL Server query analyzer's graphical features to learn more about how the optimizer handles your statement.
SQL Server PROFILER
This graphics tool real-time consecutive record of server activity. SQL Server Profier monitors many different server events and event categories, filtering these events with the criteria specified by the user, and outputs a track, one file, or another SQL Server displayed on the screen.
SQL Server PROFILER can be used:
Monitor the performance of SQL Server. Debug TRANSACT-SQL statements and stored procedures. Identify execution of a very slow query. By capturing all events that lead to a special problem, find the problem in SQL Server, and then replay this series of events on a test system to reproduce and isolate the issue. Test the SQL statement and stored procedures in the development phase of the project to ensure that the code is expected. Capture the event on the product system, and playback of the captured event on the test system to reproduce the things that occur in the product environment for the purpose of testing or debugging. A playback captured event on a stand-alone system allows users to continue using the product system without causing conflicts.
SQL Server Profiler provides a graphical user interface for a series of extended stored procedures. You can also use these extended storage interfaces directly. Therefore, you can create your own application using the SQL Server Profiler extension stored procedures to monitor SQL Server. SET statement
Set statements You can set SQL Server query processing options in your work session, running triggers or stored procedures.
The SET FORCEPLAN ON statement enforcement optimizer processes the connection in the order of the FROM clause, similar to the Ordered prompt used to Oracle Optimizer.
SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements return only information on the query or statement execution scenario without executing the query or statement itself. To perform a query or statement. You need to set the statement of the appropriate display scheme to OFF. Then you can perform a query or statement. The result of the showPLAN statement is similar to Oracle's Explain PLAN tool.
If the set statistics profile is ON, each executed query adds an additional result set in addition to the normal result set, which shows a snapshot executed by a query. Other options include Set Statistics Io and Set Statistics Time.
Transact-SQL statement processing includes two phases, compilation and execution. The NOEXEC option recompores each query but does not perform the query. After the NOEXEC is set to ON, the later statement will no longer be executed, until the NoExec is set to OFF.
SET SHOWPLAN ON SET NOEXEC ON go SELECT * FROM DEPT_ADMIN.DEPT, STUDENT_ADMIN.STUDENT WHERE MAJOR = DEPT go STEP 1 The type of query is SETON STEP 1 The type of query is SETON STEP 1 The type of query is SELECT FROM TABLE DEPT_ADMIN. DEPT NESTED ITERATION TABLE SCAN from Table Student_admin.student Nested Iteration Table Scan
Query optimization
Oracle needs to use a prompt to affect its operational and performance of its overhead-based optimer. Microsoft SQL Server's overhead-based optimizer does not need to use prompts to help them query the evaluation process. However, they can still be used in some cases.
INDEX = {index_name | index_id} Specifies the index name or ID you want to use to the table. If the index_id is 0, forced the table to scan, and the index_id of 1 is forced to use a clustered index, if any. This is similar to the index prompt used in Oracle.
SQL Server's FastFirstRow indicates that an optimizer uses an unclused index if it matches the order of the Order By clause. This prompt is similar to the first_rows prompts in Oralce
Use ODBC
This section provides information on how Oracle and SQL Server uses ODBC and information about development and transplant using ODBC applications.
Recommended conversion strategy
If you want to convert your application from Oracle to SQL Server, it is recommended to use the following procedure:
If your application uses Oracle Pro * C or Oracle Call Interface, OCI, consider converting it to ODBC. Understand SQL Server default result collection and cursor options, then select the most effective extraction strategy for your application. Fetchching Strategy. Remap the Oracle ODBC SQL data type to the appropriate SQL Server ODBC SQL data type. Use the ODBC Extended SQL extension to create the SQL statement of the class. Decide if the SQL Server application needs manual submission mode. Test your application performance and make the necessary modifications to the program.
