Friday, June 26, 2009

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.

No comments:

Locations of visitors to this page