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.
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.
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.
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.
Collation sensitivity is used to define the rules for sorting and comparing the strings of character data.
Different types of collation sensitivity:
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.
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.
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 -
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.
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.
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.
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.
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.
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.
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.
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.
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.