patternsqlCritical
As a DBA, how would I go about transitioning from Oracle to SQL Server?
Viewed 0 times
oracletransitioningsqlwouldabouthowserverdbafrom
Problem
I'm an Oracle DBA that also has Sybase experience.
What are the major architectural and conceptual differences between the two RDBMS platforms?
An answer similar to the SQL Server->Oracle question here would be of most use.
What are the major architectural and conceptual differences between the two RDBMS platforms?
An answer similar to the SQL Server->Oracle question here would be of most use.
Solution
I've swapped between working on Oracle and SQL Server over the past few years, and wrote a blurb on going the other way here. There are a number of idiomatic and architectural differences, and various bits of terminology get used differently by the vendor and developer/DBA communities surrounding each product.
Physical architecture
SQL Server organises various things a bit differently to Oracle and has one or two key concepts that have no direct analogues in Oracle.
-
A 'Database' is a separate item in SQL Server, with its own user permissions, schemas/name spaces and storage. If you're familiar with Sybase, they work much the same as databases in Sybase, due to the common origins of the product.
-
Filegroups are roughly equivalent to table spaces, although they are local to a database.
-
A schema is a distinct concept from a database user in SQL Server, although users can have a default schema.
-
MVCC works somewhat differently in SQL Server. It's a relatively recent feature, maintaining different copies of a row until the locks on the old version are released. SQL Server has no direct equivalent to a rollback segment. It is not active by default on SQL Server databases.
-
Tempdb is much more heavily used in SQL Server. The system uses it for temporary tables and intermediate join results. More on tempdb later.
-
Table partitioning is somewhat clumsier than Oracle. You need to set up a partition function that creates a partition key from whatever you are supplying it, then a partition scheme over that partition function. The partition scheme behaves a bit like a filegroup in that you then create the table on the partition scheme.
Swapping partitions in and out requires you to set up a constraint on an empty table in the right structure. The constraint guarantees that the partition key values are appropriate to the partition you intend to swap into it.
-
Materialised views are called indexed views in SQL Server. The
-
SQL Server does not support autonomous transactions, although it does support two-phase commit through XA or OLEDB transaction protocols.
-
Clustered indexes are slightly different from index-ordered tables in Oracle, as they don't require all of the columns in the table to participate in the clustered index. They're much more widely used in SQL Server architecture than IOTs are in Oracle.
-
SQL Server does support covering indexes, but doesn't have join indexes. Bitmap indexes are not supported, although it does have an index intersection/star transform operator that can calculate intersections without hitting the fact table.
-
Sequences are a relatively recent addition to SQL Server. Traditionally autoincrementing keys are done through identity columns. You can load values into an identity column through
Programming
Idiomatic T-SQL has some differences to idiomatic PL/SQL. It works differently enough that some of the paradigmatic differences merit explaining in more depth.
-
T-SQL has no concept of a package. All stored procedures and functions in a database live in a common namespace, although schemas can be used to break this up, and the namespace is local to a database.
-
Get a feel for how to use temporary tables, and
Idiomatic T-SQL will use temp tables in the sort of roles that you would see cursor variables in PL/SQL, but will make much more use of set operations. Temp tables can, however make for fairly obtuse code, so use with care.
-
The system data dictionary was much more obtuse than Oracle's one on older versions, but it got much better with SQL Server 2005. Although the tools supplied by Microsoft have quite a lot of introspection stuff built in in the SSMS explorer, it's still worth knowing your way around the data dictionary. It doesn't differentiate between
-
SSMS has a query plan viewer built in.
-
Identifiers in T-SQL code can be quoted with [], and can contain all sorts of rubbish if quoted. However, if we catch you calling a column 'Direct/Transfer', we will rip your intestines out.
-
SQL Server does have window functions (since 2005 IIRC), so you can do ordering, running sums and suchlike within groups now.
-
T-SQL has no direct equivalent to
-
If you need to write code tha
Physical architecture
SQL Server organises various things a bit differently to Oracle and has one or two key concepts that have no direct analogues in Oracle.
-
A 'Database' is a separate item in SQL Server, with its own user permissions, schemas/name spaces and storage. If you're familiar with Sybase, they work much the same as databases in Sybase, due to the common origins of the product.
-
Filegroups are roughly equivalent to table spaces, although they are local to a database.
-
A schema is a distinct concept from a database user in SQL Server, although users can have a default schema.
-
MVCC works somewhat differently in SQL Server. It's a relatively recent feature, maintaining different copies of a row until the locks on the old version are released. SQL Server has no direct equivalent to a rollback segment. It is not active by default on SQL Server databases.
-
Tempdb is much more heavily used in SQL Server. The system uses it for temporary tables and intermediate join results. More on tempdb later.
-
Table partitioning is somewhat clumsier than Oracle. You need to set up a partition function that creates a partition key from whatever you are supplying it, then a partition scheme over that partition function. The partition scheme behaves a bit like a filegroup in that you then create the table on the partition scheme.
Swapping partitions in and out requires you to set up a constraint on an empty table in the right structure. The constraint guarantees that the partition key values are appropriate to the partition you intend to swap into it.
-
Materialised views are called indexed views in SQL Server. The
GROUP BY clause does have a CUBE operator, and the documentation alludes to a query rewrite feature. However, this functionality is not well documented and may not be terribly mature. YMMV.-
SQL Server does not support autonomous transactions, although it does support two-phase commit through XA or OLEDB transaction protocols.
-
Clustered indexes are slightly different from index-ordered tables in Oracle, as they don't require all of the columns in the table to participate in the clustered index. They're much more widely used in SQL Server architecture than IOTs are in Oracle.
-
SQL Server does support covering indexes, but doesn't have join indexes. Bitmap indexes are not supported, although it does have an index intersection/star transform operator that can calculate intersections without hitting the fact table.
-
Sequences are a relatively recent addition to SQL Server. Traditionally autoincrementing keys are done through identity columns. You can load values into an identity column through
set identity_insert on.Programming
Idiomatic T-SQL has some differences to idiomatic PL/SQL. It works differently enough that some of the paradigmatic differences merit explaining in more depth.
-
T-SQL has no concept of a package. All stored procedures and functions in a database live in a common namespace, although schemas can be used to break this up, and the namespace is local to a database.
-
Get a feel for how to use temporary tables, and
SELECT INTO. It's pretty rare to encounter T-SQL code that actually needs a cursor; temporary tables allow operations to be broken up into steps that can be done with set operations. SELECT INTO in tempdb is minimally logged, and it is minimally logged in certain recovery modes on user databases as well, so it is just as fast as query operator that persists an intermediate join result.Idiomatic T-SQL will use temp tables in the sort of roles that you would see cursor variables in PL/SQL, but will make much more use of set operations. Temp tables can, however make for fairly obtuse code, so use with care.
-
The system data dictionary was much more obtuse than Oracle's one on older versions, but it got much better with SQL Server 2005. Although the tools supplied by Microsoft have quite a lot of introspection stuff built in in the SSMS explorer, it's still worth knowing your way around the data dictionary. It doesn't differentiate between
ALL, USER and DBA views of the DB objects, though.-
SSMS has a query plan viewer built in.
-
Identifiers in T-SQL code can be quoted with [], and can contain all sorts of rubbish if quoted. However, if we catch you calling a column 'Direct/Transfer', we will rip your intestines out.
-
SQL Server does have window functions (since 2005 IIRC), so you can do ordering, running sums and suchlike within groups now.
-
T-SQL has no direct equivalent to
CONNECT BY, although recursion can be done through recursive CTEs.-
If you need to write code tha
Context
StackExchange Database Administrators Q#16884, answer score: 52
Revisions (0)
No revisions yet.