SQL Server Basics

What is normalization? what are different types of normalization?

It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as normalization. Benefits of normalizing your database will include : Avoiding repetitive entries Reducing required storage space Preventing the need to restructure existing tables to accommodate new data Increased speed and flexibility of queries, sorts, and summaries. Following are the three normal forms : First Normal Form For a table to be in first normal form, data must be broken up into the smallest units possible. In addition to breaking data up into the smallest meaningful values, tables in first normal form should not contain repetitions groups of fields. Second Normal Form The second normal form states that each field in a multiple field primary key table must be directly related to the entire primary key. Or in other words, each non-key field should be a fact about all the fields in the primary key. Third Normal Form A non-key field should not depend on other Non-key field.

What is denormalization?

Denormalization is the process of putting one fact in numerous places (its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design - to enhance performance or if we are doing data warehousing and data mining. The sacrifice to performance is that you increase redundancy in database.

What is a candidate key?

A table may have more than one combination of columns that could uniquely identify the rows in a table; each combination is a candidate key. During database design you can pick up one of the candidate keys to be the primary key.

What are the different types of joins? What is the difference between them?

Inner Join  Inner join shows matches only when they exist in both tables. Example is given below SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID Left Outer Join  Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Example is given below SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID Right Outer Join  Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Example is given below SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

What are indexes? what is the difference between clustered and nonclustered indexes?

Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quickly. There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way in which 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.The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

How can you increase SQL performance?

Following are tips which will increase your SQL performance : Every index increases the time takes to perform INSERTS, UPDATES, and DELETES, so the number of indexes should not be too much. Try to use maximum 4-5 indexes on one table, not more. If you have read-only table, then the number of indexes may be increased. Keep your indexes as narrow as possible. This reduces the size of the index and reduces the number of reads required to read the index. Try to create indexes on columns that have integer values rather than character values. If you create a composite (multi-column) index, the orders of the columns in the key are very important. Try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key. If you want to join several tables, try to create surrogate integer keys for this purpose and create indexes on their columns. Create surrogate integer primary key (identity for example) if your table will not have many insert operations. Clustered indexes are more preferable than nonclustered, if you need to select by a range of values or you need to sort results set with GROUP BY or ORDER BY. If your application will be performing the same query over and over on the same table, consider creating a covering index on the table. You can use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes. This trace will show which tables are being scanned by queries instead of using an index.

What is the use of OLAP?

OLAP is useful because it provides fast and interactive access to aggregated data and the ability to drill down to detail.

What is a measure in OLAP?

Measures are the key performance indicator that you want to evaluate. To determine which of the numbers in the data might be measures. A rule of thumb is: If a number makes sense when it is aggregated, then it is a measure.

What are dimensions in OLAP?

Dimensions are the categories of data analysis. For example, in a revenue report by month by sales region, the two dimensions needed are time and sales region. Typical dimensions include product, time, and region.

What are levels in dimensions?

Dimensions are arranged in hierarchical levels, with unique positions within each level. For example, a time dimension may have four levels, such as Year, Quarter, Month, and Day. Or the dimension might have only three levels, for example, Year, Week, and Day.The values within the levels are called members. For example, the years 2002 and 2003 are members of the level year in the Time dimension