Oracle Database Interview Questions & Answers Set-18

«« Previous
Next »»

1. What is De-Normalization?
Ans: It is a process of attempting to optimize the performance of a database by adding redundant data. Actually redundancy is introduced intentionally in a table to improve performance and it is called de-normalization.


2. What is the difference between function and stored procedure?
Ans: 

  • Function must return a value while stored procedure can return zero or n value.
  • Functions can have only input parameter while procedures can have input/ output parameters.
  • Functions take one mandatory input parameter while stored procedures may take 0 to n input parameter.
  • Exceptions can be handled by try-catch block in stored procedure while you can't use try-catch in functions.

3. What is collation sensitivity? Explain different types.
Ans: Collation sensitivity is used to define the rules for sorting and comparing the strings of character data.

Different types of collation sensitivity:
  • Case sensitivity
  • Accent sensitivity
  • Kana sensitivity
  • Width sensitivity

4. What is standby server?

Ans: Standby server is the type of server which is brought online when primary server goes offline and the application needs continuous availability of the server.

There are three types of standby servers:

Hot standby: Hot standby method is a method of redundancy in which the primary and secondary backup systems run simultaneously so the data also present in secondary server in a real time and this way both system contains identical information.

Warm standby: Warm standby is a method of redundancy in which the secondary system runs in the background of the primary system . data is mirrored in the secondary server at regular interval, so in this method sometimes both servers don't contain the exact same data.

Cold standby: Cold standby is the method of redundancy in which the secondary server is only called when the primary server fails. Cold standby systems are used in cases where data is changed infrequently or for nor critical applications.


5. In which TCP/IP port does SQL Server run? Can it be changed?
Ans: SQL Server runs on port 1433. Yes, it can be changed from the network utility TCP/IP properties.


6. What are the authentication modes in SQL Server? How can it be changed?
Ans: SQL Server supports two authentication modes:

Window authentication mode and mixed mode.
Window authentication mode: This authentication mode is used to connect through a Microsoft NT 4.0 or window 2000 user account.
Mixed mode: It is used to connect with the instance of SQL Server using window authentication or SQL Server authentication.


7. What is SQL Server Profiler?
Ans: Microsoft SQL Server profiler is a graphical user interface that allows system administrators to monitor events of database engine.
You can do the following things with a SQL Server Profiler -
You can create a trace.
You can watch the trace results when the trace runs.
You can store the trace results in a table.
If it is necessary, you can start, stop, pause and modify the trace results.


8. What is SQL Server agent?
Ans: SQL Server agent is a component of Microsoft SQL Server. It is a background tool of Microsoft SQL Server so it runs continuously in the background as a window service. SQL Server agent allows the database administrator to handles automated tasks and schedules jobs. It runs a window service so can start automatically when the system boots or you can start it manually.


9. What is scheduled jobs or scheduled tasks?
Ans: Scheduled tasks let you manage the tasks in an automated manner that runs on regular or predictable cycles. You can schedule administrative tasks and also determine the order in which tasks will run.


10. What is DBCC command and why is it used?
Ans: DBCC stands for database consistency checker. This command is used to check the consistency of the database. For example:

DBCC CHECKDB:
It makes sure that table in the database and the indexes are correctly linked.

DBCC CHECKALLOC:
It checks all pages in the database and make sure that all are correctly allocated.

DBCC CHECKFILEGROUP:
It checks all table file group for any damage.


11. What command is used to rename the database?
Ans: sp_renamedb 'oldname', 'newname';


12. Can SQL Server be linked with other Servers like Oracle?
Ans: Yes, it can be linked to any Sever. It has OLE-DB provider from Microsoft which allow linking.


13. What is the difference between abstract and interface?
Ans: Abstract class:
It provides a set of rules to implement next class. Rules are provided through abstract methods.
Abstract method does not contain any definition.
When a class contains all functions without body, it is called as Fully Abstract Class.

Interface:
If a class contains all abstract methods then that class is called Interface. Interface support like multiple inheritance.


14. What is the difference between application object and session object?
Ans: The session object is used to maintain the session of each user. If a user enters into an application, he gets session id and when he leaves application then the session id is deleted. If he enters again in to the application he gets a different session id but for application object once ad id is generated it maintains whole application.


15. Is there any difference between primary key and unique with NOT NULL condition?
Ans: There is no difference between primary key and unique key with not null.

«« Previous
Next »»