SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

What is OLTP?

OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data At the very instant it is received and has a large number of concurrent users.

What is OLAP?

OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

What is the Difference between OLTP and OLAP?

Data Source

OLTP:         Operational data is from original data source of the data
OLAP:        Consolidation data is from various sources.

Process Goal

OLTP:         Snapshot of business processes which do fundamental business tasks
OLAP:        Multi-dimensional views of business activities of planning and decision making

Queries and Process Scripts

OLTP:         Simple quick running queries ran by users.
OLAP:        Complex long running queries by system to update the aggregated data.

Database Design

OLTP:         Normalized small database. Speed will be not an issue because of a small database, and normalization will not degrade performance. This adopts the entity relationship (ER) model and an application-oriented database design.

OLAP:        De-normalized large database. Speed is an issue because of a large database and de-normalizing will improve performance as there will be less tables to scan while performing tasks. This adopts star, snowflake or fact constellation mode of subject-oriented database design.

Back up and System Administration

OLTP: Regular Database backup and system administration can do the job.
OLAP: Reloading the OLTP data is considered as a good backup option.

What are Normalization Forms?

There are different types of normalization forms such as

1NF: 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.

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

3NF: 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.

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

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

ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is ODS?

ODS is the abbreviation of Operational Data Store ‑ a database structure that is a repository for near real-time operational data rather than long-term trend data. The ODS may further become the enterprise-shared operational database, allowing operational systems that are being re-engineered to use the ODS as their operation databases.

What is ER Diagram?

Entity Relationship (ER) Diagrams are a major data modeling tool and will help organize the data in your project into entities and define the relationships between the entities. This process has enabled the analyst to produce a good database structure so that the data can be stored and retrieved in a most efficient manner.

An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. A type of diagram used in data modeling for relational data bases. These diagrams show the structure of each table and the links between tables.

List of all the Interview Questions and Answers Series blogs

Reference: Pinal Dave (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 22 of 31

  1. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31 Journey to SQLAuthority

    • //Here is the script to remove spaces from column names.
      DECLARE @table_name varchar(1000) ,@column_name varchar(1000)

      DECLARE changetablename CURSOR — Declare cursor



      SELECT t.name AS table_name,
      c.name AS column_name
      FROM sys.tables AS t
      INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
      ——————————————————-Change the Column Name

      OPEN changetablename — open the cursor

      FETCH NEXT FROM changetablename

      INTO @table_name,@column_name

      — PRINT @table_name + ‘—-‘ + @column_name — print the name


      declare @newcolumnname varchar(1000),@oldcolumnname varchar(1000)
      set @oldcolumnname=@table_name+’.’+@column_name

      set @newcolumnname=replace(@column_name,’ ‘,’_’)

      exec sp_RENAME @oldcolumnname, @newcolumnname , ‘COLUMN’

      FETCH NEXT FROM changetablename

      INTO @table_name,@column_name

      CLOSE changetablename — close the cursor

      DEALLOCATE changetablename — Deallocate the cursor


  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s