Databases vs. Instances

As a long-time Oracle developer and administrator, one of the most interesting differences that I've found as a newbie SQL Server developer and administrator is in the simple basic term, database. 

In simple terms, a database is a set of database files in permanent storage that share a data dictionary, logs and control files.  An instance is a set of running processes that maintain a database and its control files and logs.  While there are in-memory databases where the database is in the server's memory, rather than maintained in permanent storage while its associated instance is running, eventually even this kind of database does get written to permanent storage.

Until Oracle 12c with its multi-tenant option, a database and an instance in Oracle were pretty close to the same thing.  With Oracle Real Application Clusters (RAC), you might have several instances on separate servers accessing a single database, but you would not have several databases under the same instance.  It would not be unusual to run several Oracle databases on the same server, but each database would be maintained by its own instance (or instances in the case of RAC).  Instances might share a network listener for client connections, but the client would indicate the instance to which it wanted to connect, and therefore which database.

But in SQL Server, and a few other relational databases, for example, PostgreSQL, a single instance can run several databases.  There are also some required databases managed by an instance, such as the database that contains the shared data dictionary.  Each database can have its own schemas, users, tables and other database objects.  None of these need to be unique except within a database.  A full unique name for a database object would be databaseName.schemaName.objectName.

This came in handy recently when I needed to create a new test database identical to our existing development database with three separate schemas.  My Oracle-trained brain initially was thinking that I needed a new SQL Server instance, since I didn't have an extra server or database license.  This is possible, but not very common, and it requires the original SQL Server installation media.  But I was reminded that I could just create a new database in the existing instance.  It could have the same schemas and database objects, but a different database name.  And it was just as easy to configure the test version of the application to connect to a different database in the same instance as to a database with the same name in a different instance.

I had two choices available for creating my test database.  It is very easy to use SQL Server Management Studio to back up my development database, and then restore it into a new test database.  But that would also copy the development data instead of creating the tables empty.  Instead, this proved to be a good opportunity to test my DDL scripts.  I have one script for each object to be created, and a master script to call each in the correct order.  This also executes scripts that load data into reference and lookup tables - remember that I consider data for these tables to be part of the source code for the database.

So, chalk this one up as an advantage for SQL Server.  Multi-tenant Option in Oracle 12c (and its successor, Oracle 18) has similar advantages - and may even be a little better if you need to move tenant databases around.  But it is an extra cost option, while multiple databases is a standard part of SQL Server.

More on my Oracle to SQL Server Transition

I'm continuing to adjust to this new world of Microsoft SQL Server.  Remembering my great experiences in the Oracle user community, I've joined PASS, which is the international user group for SQL Server users.  One thing PASS offers is "SQL Saturday" which are one day (yes, on Saturday) training events held by local groups all over the world.  There are a few SQL Saturdays being held somewhere almost every weekend.  On March 24, I drove the 100 miles from my home to Glen Allen, VA (northwest of Richmond) for the Richmond SQL Saturday.  It was great, I got to meet a few people, and learned at least two things that will be useful in the work I'm doing right now.

About Table Storage and Indexes

Like Oracle, it is very important to have a Primary Key constraint on all of your tables in SQL Server.  By default, SQL Server wants to make this a clustered index.  "What is a clustered index?" my Oracle expert friends will ask.  In a clustered index, the data from the row is stored with the index entry.  That means that a table may only have one clustered index.  Oracle experts will recognize this as essentially the same as an Index-Organized Table (IOT).  IOTs aren't used that much in Oracle, but maybe they should be.  If you often query a table by its primary key, or often do UPDATE or DELETE by primary key, you might get a good performance boost by making it an IOT.

At SQL Saturday, Richmond, I went to a great presentation about indexing, "Indexing for Performance" by Jeffrey Garbus.  Mr. Garbus described very well the access path used for various WHERE conditions, with and without indexes, and differences between using a clustered and a non-clustered index.  A non-clustered index is a B-tree index in SQL Server, just like a normal index in Oracle and is used in a similar way.  In both databases, the optimizer decides whether to use an index or to do a full table scan according to similar criteria, and in fact, may decide not to use an index in situations such as the likelihood that more than a certain percentage of the rows will be returned by the query.

One very important difference between an IOT in Oracle and a table with a clustered index in SQL Server is that an IOT is always indexed with its primary key. While the primary key gets an unique clustered index by default in SQL Server, it is possible to specify that the primary key should get an unique non-clustered index.  This leaves it open to create a clustered index on another key, and that index need not be unique.  Since a clustered index makes the database store the rest of the data with the index, only one clustered index may be created on a table.  Mr. Garbus explained why, for certain tables, it might be better to cluster on a parent key.  For instance, in an Order-Entry application, line items for an order are rarely accessed any other way than by the parent order_id.  Therefore, it is often a good idea to cluster the order_line_item table on this foreign key, rather than its primary key (order_line_item_id).

This reminded me of another Oracle feature that I believe may be under-used.  While IOTs can only use a table's primary key, you can actually store master table data like the "order" table physically next to its detail rows in "order_line_item".  Oracle has clustering too, but the syntax and implementation is different.  Check out the CREATE CLUSTER command and its use in the Database Concepts Guide.

From Oracle Expert to SQL Server Newbie - First Impressions

Oops, I've done it again - I left you readers hanging when I published the first part of two on generating code for your lookup/reference/code tables.  I promise to get back to it.

But in the meantime:
I have a new job.  Yeah, it was pretty sudden - finished my old job at the end of the year, and jumped directly into the new one at the beginning of the new year.  And the biggest shock is that this old Oracle DBA and Database Architect is going to be designing a database to be implemented in Microsoft SQL Server.  No, I didn't lie about my credentials, but I've worked with this boss before, and he said, "Oh, you'll do fine.  You're a smart guy.  And SQL is SQL, right? RDBMS is RDBMS, right?"

So, here I am, the newbie, posing as an expert.  I've been learning all I can about SQL Server, and trying things out, watching tutorials (Microsoft has some really good video lessons, designed especially for the Oracle DBA transitioning.)  Here are some first impressions:

• SQL is SQL.  Yes, there are differences, but the basics of the SELECT, INSERT, UPDATE, DELETE, and most of the DDL commands too, are pretty close.
• T-SQL is NOT PL/SQL.  Think of Transact-SQL as more like a SQL*Plus script with some flow control statements like IF/ELSE.  Some of it is nice, like no difference between a substitution variable and a language variable.  I think (not sure) that a T-SQL variable does act as a bind variable where PL/SQL variables would, but you can do things that PL/SQL would have to put inside an EXECUTE IMMEDIATE.  Cool.
• I miss PL/SQL packages.
• All triggers are statement level triggers.  This means that you write triggers very differently. Hard to get used to.  That doesn't mean you can't do row-level stuff - instead of getting a pseudo-record with the contents of the pre-DML row (the :OLD record) and one with the contents of the post-DML row (the :NEW record) you get pseudo-tables containing the old and new rows.  I don't know for sure, but I think this means that you don't deal with mutating table problems.
• The real kicker is that most triggers are AFTER statement triggers. If you want to do BEFORE stuff, you write an INSTEAD OF trigger.  In Oracle, INSTEAD OF is just for views.  Just like Oracle, the INSTEAD OF trigger needs to do the DML on the underlying table(s).
• There are two SQL Developer-like GUI tools - the SQL Server Management Studio, and SQL Server Data Tools for Visual Studio.  These are nice, but I wish they had a good SQL formatting tool.
• SQL Developer CAN talk to a SQL Server database, which is nice. But it doesn't really understand T-SQL, so it can have problems with it.  I did use its formatting tool on some SELECTs, however, and it worked pretty well.  Also, if you export a table as INSERTs - it writes them as Oracle INSERTs - doesn't work for dates because SQL Server doesn't have TO_DATE.
• Oracle has nothing like the SQL Server Integration Server - this is nice but TOTALLY new to me.
• SQL Server Reporting Server and its report building tool are MUCH better than SQL Reports from Oracle.  But then, I have always hated Oracle's reporting tool.