FAQ : What is WITH ROLLBACK ROLLBACK IMMEDIATE | AFTER integer [SECONDS] | NO_WAIT in ALTER DATABASE in SQL Server

When you ALTER a database state what to do with the current transaction(s) (If any) on the database are defined by these statements.

WITH ROLLBACK IMMEDIATE

WITH ROLLBACK IMMEDIATE tell the DB engine to rollback all the transaction immediately and make the DB into the given state in the Alter Statement. Ie
In my database, there is a transaction running for last one hour and it is not yet committed and now I want to make this database into SINGLE_USER. The following line of Alter statement will do that for me
-- Set the database to single user
Alter Database MyDatabase set SINGLE_USER WITH ROLLBACK IMMEDIATE

The above statement will rollback the current transaction immediately and bring the DB to Single_USER

WITH ROLLBACK AFTER integer [SECONDS]

This command tells DB engine to wait for the specified seconds before rolling back all the transaction. If the transaction still not committed in the given seconds then rollback.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK AFTER 30

The above statement will bring the database to SINGLE_USER after 30 sec. If any transaction is there after 30 sec it will rollback.


WITH NO_WAIT
This is bit different from the above two statements. This tells the DB engine to stop or terminate ALTER Statement rather than terminating or rollback the transaction. Ie. If any active transaction is there on the database, the ALTER Statement will fail.

ALTER DATABASE MyDatabase SET SINGLE_USER WITH NO_WAIT

FAQ : What is Database in SQL Server and Oracle?

Database in SQL Server.
A Microsoft SQL Server database is a collection of entities that stores and manipulate data. There are two types of Databases in SQL Server, System Databases and User Databases. There are four system databases which is mandatory in any instance. An instance can have max 32767 user databases which is not practical in real time scenario. But instances with 100s of DBs may be common. A database in SQL Server is made up of a collection of tables. These tables contain data and other objects, such as views, indexes, stored procedures, user-defined functions, and triggers that are defined to support activities performed with the data. The data stored in a database is typically related to a particular subject or process, such as inventory information for a manufacturing warehouse.


Database in Oracle
In Oracle the database is physical structure that consists of files stored on disks . Oracle’s database structures include tablespaces, control files, redo log files, archived logs, block change tracking files, Flashback logs, and recovery backup (RMAN) files etc.

One point to note here is, in general , the database definition may looks like similar in both SQL Server and Oracle but the way it implemented is little different. Oracle generally, will have single database and different schema for each User (read department) . And Oracle provides a feature to backup /restore only single schema /user. But in SQL Server though there is schema, but this backup/restore feature is not available by default (yes there is a tweaking but not recommended). So in SQL Server we may create separate database for each Department/user. In that case keeping FK relation across database is not possible.

FAQ : What is an instance in SQL Server and Oracle

SQL Server Instance :
In SQL Server you can have multiple instance running on same physical machine. It means, you will have multiple database engine (windows services) running on the same machine. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Each instance will have its own memory area and other resources. There are two types on instances in SQL Server, Default instance and Named Instance. You will have only one Default instance on a machine but may have more than one (depends upon the edition of SQL Server what you have, it can be anything upto 50) Named Instance on a machine.

Why we need multiple instance of SQL Server?
The reason can be technical or non-technical (political). Ie. If you want to have more resources allocated for a particular department(read database(s)) than other department(s) you may go for different instances per department (Exception : In SQL Server2008 onwards there is resource governor feature which can control resource allocation). Another technical reason would be if one of your applications is highly tempdb intensive then you may go for different instances because each instance will have its own tempdb.

Political reason is, generally SA privilege may not be sharable between departments and each department would like to have its own SA, then you may go for different instances.

Oracle Instance.
Instance in Oracle is not similar to Instance in SQL Server. Generally, people uses Instances and Databases in Oracle as synonyms which is incorrect. One need to understand the distinction to understand the Oracle Architecture. To explain Instance in Oracle you need to understand what is Database in oracle too. In short, Databases are PHYSICAL and Instances are LOGICAL. Instance consists of memory structures and process in the server. Oracle has an area of shared memory called System Global Area (SGA) and each process have its own private memory area called Program Global Area (PGA). Instance to Database is ONE TO ONE. Ie An Instance can be part of only one Database but database can have multiple Instances (I never see some thing like this in my short stint as Oracle developer).

Why you would go for multiple instance in Oracle?
I would like to have the answer for this question from viewer. I do have some idea, but not convinced as the way SQL Server reasoning for Multiple instance on same machine.