Appendix List of Differences between SQL Server and Oracle

(Part of a series of SQL Server 2012 training course notes. Previous | Contents)

  1. SQL Server tablespaces are called databases, and lie half way between an oracle tablespace and an oracle instance.
  2. Five system databases exist in each instance: master (=system); tempdb (=temp); distribution (for distributed transactions); model (the template for all new database/tablespaces); and msdb (automatic scheduled jobs information). Three sample databases are also created on install, but can be safely deleted: pubs; northwind; user1.
  3. SQL Server databases comprise one or more datafiles, as in oracle, but also have one or more online redo log files. For DBAs, that is a significant difference between SQL Server and oracle; the idea of the temporary tablespace, system tablespace, and each schema tablespace having their own set of online redo logs takes some getting used to.
  4. SQL Server has logins and users. Logins are specific for a SQL Server instance. Users are specific to a database. A login will have one user for every database it has access to. Logins have systemic privileges (create database, etc.), Users have DDL and DML privileges. Standard practice is to name users the as their parent login – this is done by default.
  5. Users with execute permission on a procedure / select permission on a view will fail to use this if there is broken ownership with referenced objects, unless they are explicitly given access to the referenced table.
  6. Revoke (neutral) and Deny (strong) are both available in place of Oracle Revoke. The difference is whether or not they can be overridden by role or user privileges. Deny appears as a red cross, Grant as a green tick, and Revoke does not appear in Enterprise Manager privilege box. [p109]
  7. Objects can have their schema changed in SQL Server without being rebuilt, using sp_changeobjectowner object, owner. But SQL has to be rewritten to specify the right user, especially with broken ownership chains, so probably this is more trouble than it is worth.
  8. SQL Syntax is slightly different. SQL Server books online has full syntax with a search capability. However DDL and DBA operations should be done by the Enterprise Manager GUI, which has wizards available. SQL Server Enterprise Manager is vastly superior to the equivalent Oracle GUIs. A few operations cannot be done through Enterprise Manager: Column permissions have to be modified through SQL; Filegroups have to be created through SQL.
  9. In DDL, whitespace and special characters must be covered with []. E.g.: REVOKE ALL ON [order details] FROM PUBLIC; DENY CREATE DATABASE TO Eva, [Corporate\ErikB], Ivan.
  10. SQL Server has its equivalent of v$ and dba_ views. It also has a collection of system stored procedures which return v$ information or actually perform system DDL.
  11. SQL Server has application roles in addition to standard roles. Application roles are password identified while standard roles are always enabled for a user. When an application role is enabled, no other privileges are apparent, with the exception of public privileges. Application roles would typically be enabled and disabled through a VB front end script. Syntax is: exec sp_setapprole ‘approle_name’, ‘password’
  12. Databases have a primary datafile (*.mdf), possibly some secondary datafiles (*.ndf); and one or more on line redo log files (*.ldf).
  13. SQL Server has a fixed block size of 8k, and a fixed extent size of 64k. This may not mean an end to defragmentation requests, since there is still a reorganise utility. The reorganise utility can be scheduled to run after backup, and does hot reorgs. Small tables can, apparently, share an extent.
  14. Rows cannot span blocks, so the maximum row size is 8k. This means chaining does not happen in SQL Server, but problems will occur if a row physically cannot fit into 8k.
  15. Databases can be dbo use only (in development phase) (=restricted session); and can be in single user mode (when doing restores, etc.).
  16. Truncate log on checkpoint is equivalent to noarchivelog mode in Oracle. Truncating the log on backup is equivalent to archivelog mode in Oracle.
  17. On line redo logs are not archived, except on backup. So if you forget to configure transaction log backups, they will grow to much larger sizes than Oracle Online redo logs.
  18. There are no rollback segments in SQL Server. Rollback information is obtained from the online redo logs. This improves performance, but stops read consistent views working as in Oracle.
  19. SQL Server is read inconsistent. This will improve performance for some SQL jobs, but produce inconsistent results.
  20. SQL Server creates snapshot disk files, rather than snapshot log tables.
  21. SQL Server has transactional replication, which replicates data by monitoring redo logs. block changes for replication are specifically marked in the redo logs. This is like snapshot replication, but with constant data copying. So it provides the same functionality as distributed database triggers, but with fewer potential problems.
  22. A distribution database stores distribution history, and in transactional replication, also keeps the information culled from the redo logs for propagation.
  23. SQL Server does not have parallel Server, although this may be possible via NT operating system.
  24. SQL Server does not have sequences. Instead columns are given the identity property. For sequences, columns are given the identity property in SQL Server.  This automatically places the nextval into that column on insert, without the need for a custom inert trigger as in oracle.  When sequence values need to be maintained over multiple tables, SQL Server also has oracle style sequences – but only in SQL Server 2012 and above.  (Thanks to Graham Oakley for spotting this in comments).
  25. By default, SQL Server autocommits instantly, with rollback unavailable.
  26. With begin transaction…commit/rollback transaction statements, get commit, rollback, endpoint functionality. However, because SQL Server does not have rollback segments, modified rows in a transaction are locked. Users cannot even select from the entire table in most cases.

Syntactical Differences

  • Page = Block
  • Identity property = sequence
  • Indexed view = materialized view

Replication:

  • Publisher = master site
  • Distributor = replication process
  • Subscriber = copy site
  • Publication = snapshot group
  • Article = snapshot
  • Push Subscription is initiated on publisher
  • Pull Subscription is initiated on subscriber
  • Merge Replication = updateable snapshots

(Part of a series of SQL Server 2012 training course notes. Previous | Contents)

    • Leave a Reply

    Your email address will not be published. Required fields are marked *