Explore topic-wise InterviewSolutions in .

ROLLBACK retracts any of the CHANGES resulting from the SQL STATEMENTS in the TRANSACTION.

ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

Rule-based and Cost-based.

Rule-based and Cost-based.

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance COST is significant. Your choice should depend on the type of application you are RUNNING. If you have many small transactions as with OLTP, use a smaller block size. With FEWER but LARGER transactions, as with a DSS application, use a larger block size. If you are using a volume MANAGER, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. Your choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a DSS application, use a larger block size. If you are using a volume manager, consider your "operating system block size" to be 8K. This is because volume manager products use 8K blocks (and this is not configurable).

One can CREATE and modify Oracle databases using the Oracle "dbca" (Database CONFIGURATION ASSISTANT) UTILITY. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software. One can also create databases manually using scripts. This option, however, is falling out of fashion, as it is quite involved and error prone. Look at this example for creating and Oracle 9i database:

One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal Installer (oui) normally starts it after installing the database server software. One can also create databases manually using scripts. This option, however, is falling out of fashion, as it is quite involved and error prone. Look at this example for creating and Oracle 9i database:

The OPTIMIZER_MODE initialization PARAMETER STATISTICS in the DATA Dictionary. The OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

The OPTIMIZER_MODE initialization parameter Statistics in the Data Dictionary. The OPTIMIZER_GOAL parameter of the ALTER SESSION command hints in the statement.

The Optimizer CHOOSES Cost_based approach and optimizes with the goal of best throughput if STATISTICS for atleast one of the tables accessed by the SQL STATEMENT exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

The Optimizer chooses Cost_based approach and optimizes with the goal of best throughput if statistics for atleast one of the tables accessed by the SQL statement exist in the data dictionary. Otherwise the OPTIMIZER chooses RULE_based approach.

When it is COMMITTED or Rollbacked.

When it is committed or Rollbacked.

A CLUSTER with an INDEX on the Cluster KEY.

A Cluster with an index on the Cluster Key.

A Control file records the PHYSICAL structure of the database. It contains the following INFORMATION.

• Database NAME
• Names and locations of a database's FILES and redolog files.
• Time STAMP of database creation.

A Control file records the physical structure of the database. It contains the following information.

A data block size is specified for each ORACLE DATABASE when the database is created. A database users and ALLOCATED FREE database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed LATTER.

A data block size is specified for each ORACLE database when the database is created. A database users and allocated free database space in ORACLE datablocks. Block size is specified in INIT.ORA file and can’t be changed latter.

A data FILE can be ASSOCIATED with only one database. Once created a data file can't change size. One or more data files FORM a logical unit of database storage CALLED a tablespace.

A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

A table is the BASIC unit of data storage in an ORACLE DATABASE. The tables of a database hold all of the user accessible data. Table data is STORED in rows and COLUMNS.

A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

A view is a VIRTUAL table. Every view has a Query ATTACHED to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view USES.)

A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

An Extent is a SPECIFIC number of contiguous data blocks, obtained in a SINGLE allocation, and USED to store a specific TYPE of INFORMATION.

An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

An INDEX is an optional STRUCTURE associated with a table to have direct ACCESS to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more COLUMNS of a table.

An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

An INTEGRITY constraint is a DECLARATIVE way to define a BUSINESS RULE for a COLUMN of a table.

An integrity constraint is a declarative way to define a business rule for a column of a table.

Clusters are groups of one or more tables physically STORES together to SHARE COMMON columns and are OFTEN used together.

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

Each INDEX has an Index SEGMENT that STORES all of its DATA.

Each Index has an Index segment that stores all of its data.

The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the PSEUDO columns LEVEL or ROWNUM.

The set of Redo Log files YSDATE,UID,USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

There are TWO TYPES of SYNONYMS PRIVATE and PUBLIC.

There are two types of Synonyms Private and Public.

UPDATE and DELETE Restrict - A REFERENTIAL integrity RULE that DISALLOWS the update or deletion of referenced data.
DELETE Cascade - When a referenced row is deleted all associated dependent ROWS are deleted.

UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data.
DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.

VIEWS do not CONTAIN or STORE DATA.

Views do not contain or store data.

When an instance of an ORACLE database is STARTED, its control file is used to identify the database and redo log files that MUST be opened for database operation to PROCEED. It is ALSO used in database RECOVERY.

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Yes.

Yes.

Yes.

Yes.

Yes.

Yes.

A full BACKUP is an operating system backup of all DATA files, on-line redo log files and control FILE that constitute ORACLE database and the parameter.

A full backup is an operating system backup of all data files, on-line redo log files and control file that constitute ORACLE database and the parameter.

A Partial Backup is any OPERATING system backup short of a FULL backup, taken while the DATABASE is open or SHUT down.

A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

REPLICATION is the process of copying/moving DATA between DATABASES on the same or different servers. SQL Server supports the following TYPES of replication scenarios:

•  Snapshot replication
•  TRANSACTIONAL replication (with immediate updating subscribers, with queued updating subscribers)
•  Merge replication

Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. QUERY optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

1. If there is significant change in the key values in the index
2. If a LARGE amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3. Database is upgraded from a previous version

Look up SQL SERVER books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats.

Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Some situations under which you should update statistics:

Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats.

SQL SERVER can be started from COMMAND line, using the SQLSERVR.EXE. This EXE has some very IMPORTANT parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in MINIMAL confuguration mode.

SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode.

INDEXES in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. CLUSTERED indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or PRESENCE of clustered index on the table.

If you create an index on each column of a table, it improves the query PERFORMANCE, as the query optimizer can choose from all the existing indexes to COME up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.

Constraints enable the RDBMS enforce the integrity of the database automatically, WITHOUT needing you to CREATE triggers, RULE or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the TRANSACTION log, which makes it slow. TRUNCATE TABLE also deletes all the ROWS in a table, but it won't log the deletion of each row, instead it LOGS the deallocation of the data pages of the table, which makes it FASTER. Of course, TRUNCATE TABLE can be rolled BACK.

DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.

LOCK ESCALATION is the process of converting a lot of low level locks (like ROW locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL SERVER escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.

Active/Active :

• It is the bassically USE for the DEFAULT nodes, Here first node will be default and second node will be named instance. Both node will be active.
• Its MOVE group from cluster administration is possible for both side.
• System performance will GO down, if both resources are in one node.

Active/Passive :

• Its also basically use for nodes But in this CASE Only one Active node with default instance. No system performance degradation will be there for this case even if we switchover to the other node. Both have same configuration.

Active/Active :

Active/Passive :

Non-clustered INDEX. Important thing to NOTE: By default a clustered index GETS created on the PRIMARY key, UNLESS specified otherwise.

Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.

An ISOLATION level determines the degree of isolation of DATA between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. SEE SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
CREATE INDEX myIndex ON MYTABLE(myColumn)

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
CREATE INDEX myIndex ON myTable(myColumn)

A transaction is a LOGICAL unit of work in which, all the STEPS must be PERFORMED or NONE. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the PROPERTIES of a transaction.

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

A CANDIDATE key is one that can identify each ROW of a table UNIQUELY. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are CALLED alternate keys.
A key formed by COMBINING at least two or more columns is called composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.

Bit DATATYPE is used to store BOOLEAN information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype COULD hold either a 1 or 0 and there was no SUPPORT for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.

USER defined datatypes let you extend the base SQL SERVER datatypes by providing a descriptive name, and format to the database. TAKE for example, in your database, there is a column called Flight_Num which appears in many TABLES. In all these tables it should be varchar(8). In this CASE you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.

As the name indicates, DENORMALIZATION is the reverse process of normalization. It's the CONTROLLED introduction of REDUNDANCY in to the database design. It helps improve the query PERFORMANCE as the NUMBER of joins could be reduced.

As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

Failover Clustering, DATABASE MIRRORING, LOG Shipping and Replication are the High- Availability FEATURES available in SQL SERVER.

Failover Clustering, Database Mirroring, Log Shipping and Replication are the High- Availability features available in SQL Server.

There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here :

•  Database Partitioning
•  Dynamic Management Views
•  SYSTEM CATALOG Views
•  Resource Database
•  Database SNAPSHOTS
•  SQL Server Integration SERVICES
•  Support for Analysis Services on a a Failover Cluster.
•  Profiler being able to trace the MDX queries of the Analysis Server.
•  Peer-toPeer REPLICATION
•  Database Mirroring

There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here :

•  In a clustered index, the LEAF level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged ACCORDINGLY based on the clustered index key. There can only be one Clustered index on a table.
•  In a Non-Clustered index, the leaf level pages does not contain data pages instread it contains pointers to the data pages. There can multiple non-clustered indexes on a SINGLE table.

“Clustered and Non-Clustered Indexes”. There are other types of Indexes such as Unique, XML, SPATIAL and Filtered Indexes. 

“Clustered and Non-Clustered Indexes”. There are other types of Indexes such as Unique, XML, Spatial and Filtered Indexes. 

The GLOBAL VARIABLE version has the BUILD and version information for the SQL Server and the service packs.

The global variable version has the build and version information for the SQL Server and the service packs.

The RAID STANDS for Redundant Array of Independent Disks. With its own RAID controllers, the RAID implements a fail-safe STORAGE mechanism with its own backup mechanisms. There are different configurations of the RAID that all give US the ACID properties of storage along with other such facilities. This kind of storage will make the SQL Server database to be failsafe and stable. This can SOMETIMES mean that the backup mechanisms and other such reliability measures can be taken off from the SQL Server level of operations.

The RAID stands for Redundant Array of Independent Disks. With its own RAID controllers, the RAID implements a fail-safe storage mechanism with its own backup mechanisms. There are different configurations of the RAID that all give us the ACID properties of storage along with other such facilities. This kind of storage will make the SQL Server database to be failsafe and stable. This can sometimes mean that the backup mechanisms and other such reliability measures can be taken off from the SQL Server level of operations.

The scrollable CURSORS are the ones that can get the ENTIRE SET of rows as single entity, within which all the rows present can be accessed in any order without the open/close of cursor DONE for every row access. The scrollable cursors are created with the keyword SCROLL added to the CREATE Cursor statements. The scrollable cursors are useful for the access of information from different rows but not for the delete/insert of new rows.

The scrollable cursors are the ones that can get the entire set of rows as single entity, within which all the rows present can be accessed in any order without the open/close of cursor done for every row access. The scrollable cursors are created with the keyword SCROLL added to the CREATE Cursor statements. The scrollable cursors are useful for the access of information from different rows but not for the delete/insert of new rows.