MS-SQL Server

Hi Friends



Today I will be discussing some more Q&A on SQL Server.

Q. What is De-normalization?

Ans.For optimizing the performance of a database by adding redundant data or by grouping data is called de-normalization. It is sometimes necessary because current DBMS implement the relational model poorly.

In some cases, de-normalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.

A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

Q. What are the properties of the Relational tables?

Ans. Relational tables have six properties

Values Are Atomic

Columns in a relational table are not repeating group or arrays. Such tables are referred to as being in the "first normal form" (1NF). The atomic value property of relational tables is important because it is one of the cornerstones of the relational model. The key benefit of the one value property is that it simplifies data manipulation logic.

Column Values Are of the Same Kind

All values in a column come from the same set of values or domain. For example, a Product_Price column contains only specific to product price. It never contains other information such as comments, status flags, or even weekly salary.
It simplifies data access because developers and users can be certain of the type of data contained in a given column. It also simplifies data validation. Since all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL) of the database software.

Each Row is Unique

Each unique row ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of which uniquely identify each row in the table. Such columns are called primary keys.This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the primary key value.

The Sequence of Columns is Insignificant

Ordering of the columns in the relational table has no meaning. Columns can be retrieved in any order and in various sequences. The benefit of this property
is that it enables many users to share the same table without concern of how the table is organized. It also permits the physical structure of the database to change without affecting the relational tables.

The Sequence of Rows is Insignificant

This property is analogous the one above but applies to rows instead of columns. The main benefit is that the rows of a relational table can be retrieved in different order and sequences. Adding information to a relational table is simplified and does not affect existing queries.

Each Column Has a Unique Name

Because the sequence of columns is insignificant, columns must be referenced by name and not by position. In general, a column name need not be unique within an entire database but only within the table to which it belongs.

Q.What is lock escalation?

Ans. 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.

Q. What is the difference between clustered and a non-clustered index?

Ans. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

Q. What’s the difference between a primary key and a unique key?

Ans. Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

I will covering some more important Q&A in my coming interactions.

Happy Learning!!...

Cheers

Padhaku Einstein

Contact me at einstein.padhaku@gmail.com