SQL



21. What is ACID property in database?  

ACID property is used to ensure that the data transactions are processed reliably in a database system.

A single logical operation of a data is called transaction.

ACID is an acronym for Atomicity, Consistency, Isolation, Durability.

Atomicity: it requires that each transaction is all or nothing. It means if one part of the transaction fails, the entire transaction fails and the database state is left unchanged.

Consistency: the consistency property ensure that the data must meet all validation rules. In simple words you can say that your transaction never leaves your database without completing its state.

Isolation: this property ensure that the concurrent property of execution should not be met. The main goal of providing isolation is concurrency control.

Durability: durability simply means that once a transaction has been committed, it will remain so, come what may even power loss, crashes or errors.



22. What is normalization?  

In RDBMS, the process of organizing data to minimize redundancy is called normalization. In normalization database is divided in two or more tables and a relationship is defined among the tables.



23. What is De-Normalization?  

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.



24. What is the difference between function and stored procedure?  

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.




25. What is collation sensitivity? Explain different types.  

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


26. What is standby server?  

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.



27. What are the authentication modes in SQL Server? How can it be changed?  

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.



28. What is SQL Server Profiler?  

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.


29. What is SQL Server agent?  

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.



30. What is scheduled jobs or scheduled tasks?  

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.



31. What is DBCC command and why is it used?  

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.



32. What command is used to rename the database?  

sp_renamedb 'oldname', 'newname';


33. Can SQL Server be linked with other Servers like Oracle?  

Yes, it can be linked to any Sever. It has OLE-DB provider from Microsoft which allow linking.


34. What is the difference between abstract and interface?  

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.



35. What is the difference between application object and session object?  

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.



36. Is there any difference between primary key and unique with NOT NULL condition?  

There is no difference between primary key and unique key with not null.


37. What is the difference between value type and reference type?  

Value type and reference type may be similar in terms of declaration syntax and usage but their semantics are distinct

Value type are stored on stack while reference type are stored on heap.

Value type store real data while reference type store reference to the data.

Accessing is faster in value type on comparison to reference type.

Value type can contain null value while reference type can't contain null value.

Value types are derived from System.ValueType while Reference type is derived from System.Object.



38. What is the Boxing and Unboxing concept in .net?  

Boxing:Implicit conversion of a value type (integer, character etc.) to a reference type (object) is called boxing. In boxing process a value type(which generally stores on stack) is being allocated on the heap rather than the stack.

Unboxing:explicit conversion of that same reference type (which is created by boxing process) back to a value type is known as unboxing. In unboxing process boxed value type is unboxed from the heap and allocated on the stack.



39. What is the difference between GET and POST methods?  

GET and POST methods are form submission method. Both are used to send the data from client side to server side. These are some differences between GET and POST method -

In GET method caching is possible while it is not possible in POST method.

Only ASCII character data types are allowed in GET method while in POST method there is no restriction, it allows binary data also.

In GET method length of the string is restricted while in POST method length of the string is not restricted.



40. What is Log Shipping?  

Log shipping is the process of automating the backup of a database and transaction log file on a primary database server, and then restoring them onto a standby server.


The primary purpose of log shipping is to increase database availability just like replication.



.Net Interview Question

PHP Interview Question

Java Interview Question

AngularJS Interview Questions