SQL Server Questions Part IV

Guys,

Here are some more questions on SQL Server. PART - IV. Here are some more questions with answers on SQL Server:

Q. Define ACID property?

ANS:

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.

• Atomicity is an all-or-none proposition.
• Consistency guarantees that a transaction never leaves your database in a half-finished state.
• Isolation keeps transactions separated from each other until they are finished.
• Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

Q. What are the Different Normalization Forms?


Ans:

1st Normal Form: Eliminate Repeating Groups. Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2nd Normal Form: Eliminate Redundant Data. If an attribute depends on only part of a multi-valued key, then remove it to a separate table.

3rd Normal Form: Eliminate Columns Not Dependent On Key.
If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key.

BCNF: Boyce-Codd Normal Form. If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4th Normal Form: Isolate Independent Multiple Relationships. No table may contain two or more 1:n or n:m relationships that are not directly related.

5th Normal Form: Isolate Semantically Related Multiple Relationships. There may be practical constrains on information that justify separating logically related many-to-many relationships.

Q. What are the Different Types of Triggers?


Ans.
There are two types of Triggers.
1) DML Trigger
There are two types of DML Triggers
a.Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.
b. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.
2) DDL Trigger
This type of trigger is fired against Drop Table, Create Table, Alter Table or Login events. DDL Triggers are always After Triggers.

Q. What is a View?


Ans.
A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

Q. What is a Linked Server?


Ans.
Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server databases using T-SQL Statements. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedures sp_addlinkedserver, sp_addlinkedsrvlogin will be used to add new Linked Server.

Q. What is Collation?


Ans.
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence with options for specifying case sensitivity, accent marks, Kana character types, and character width.

Will add more soon.

Happy Learning

Sudhir