What is an Identity?

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBAs leave these at 1. A GUID column also generates unique keys. Updated based on the comment of Aaron Bertrand. (Blog)

What is DataWarehousing?

Subject-oriented, which means that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;

Time-variant, which means that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;

Non-volatile, which means that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.

Integrated, which means that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

What languages BI uses to achieve the goal?

BI uses following languages for achieve the Goal.

MDX – Multidimensional Expressions:

This language is used for retrieving data from SSAS cubes. It looks very similar to T-SQL, but it is very different in the areas of conceptualization and implementation.

DMX – Data Mining Extensions:

This is again used for SSAS, but rather than cubes it is used for data mining structures. This language is more complicated than MDX. Microsoft has provided many wizards in its BI tools, which further reduced number of experts for learning this language, which deals with data mining structures.

XMLA – XML for Analysis:

This is mainly used for SSAS administrative tasks. It is quite commonly used in administration tasks such as backup or restore database, copy and move database, or for learning Meta data information. Again, MS BI tools provide a lot of wizards for the same.(Read More Here)

What is Standby Servers? Explain Types of Standby Servers.

Standby Server is a type of server that can be brought online in a situation when Primary Server goes offline and application needs continuous (high) availability of the server. There is always a need to set up a mechanism where data and objects from primary server are moved to secondary (standby) server. This mechanism usually involves the process of moving backup from the primary server to the secondary server using T-SQL scripts. Often, database wizards are used to set up this process.

Different types of standby servers are given as follows:

1) Hot Standby:

Hot Standby can be achieved in the SQL Server using SQL Server 2005 Enterprise Edition and the later enterprise versions. SQL Server 2005 has introduced Mirroring of database that can be configured for automatic failover in a disaster situation. In the case of synchronous mirroring, the database is replicated to both the servers simultaneously. This is a little expensive but provides the best high availability. In this case, both primary and standby servers have same data all the time.

2) Warm Standby:

In Warm Standby, automatic failover is not configured. This is usually set up using Log Shipping or asynchronous mirroring. Sometimes warm standby is lagging by a few minutes or seconds, which results into loss of few latest updates when the primary server fails and secondary server needs to come online. Sometimes a warm standby server that is lagging by a few transactions is brought back to the current state by applying the recent transaction log.

3) Cold Standby:

Code Standby servers need to be switched manually, and sometimes all the backups as well as the required OS need to be applied. Cold Standby just physically replaces the previous server.

What is Dirty Read?

A dirty read occurs when two operations, say, read and write occur together giving the incorrect or unedited data. Suppose, A changed a row but did not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

Why can’t I use Outer Join in an Indexed View?

Rows can logically disappear from an indexed view based on OUTER JOIN when you insert data into a base table. This makes incrementally updating OUTER JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.(Read More Here)

What is the Correct Order of the Logical Query Processing Phases?

The correct order of the Logical Query Processing Phases is as follows:

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. TOP
11. ORDER BY

I’m only going to touch the IDENTITY section – didn’t read much further than that.

“AutoNumber” is an Access term – be careful suggesting it to potential interviewees for a SQL Server position, especially if Access isn’t on their resume.

I’m not sure how a GUID column generates numbers. I think you meant to specify the UNIQUEIDENTIFIER data type, and specifically one with a default value of NEWID() or NEWSEQUENTIALID(). This is certainly not a number:

639A167C-13F7-46FD-9C99-74DAEB22D12D

You say that the value of an identity cannot be controlled, but I think you need to be more specific. I can reset an identity seed manually via TRUNCATE TABLE or DBCC CHECKIDENT – I can also skip as many rows as I want by issuing inserts and then rolling back. I think what you want to stress here is that you shouldn’t rely on it to produce a continuous sequence of numbers with no gaps.

Finally, you say that identity/GUID columns do not need to be indexed. Like a few of the other comments I think you need to be more specific here; the way I read that statement, it sounds like you are implying they shouldn’t be indexed. Maybe what you meant was that they don’t necessarily need to be indexed, or they don’t necessarily need to participate in the primary key?

If interviewees believe and repeat these things, they will get nowhere.

When you start with a begin tran and then you run an insert/delete/update, before you run commit tran to complete your insert/delete/update transaction, go to another query analyzer window, do a select statement with nolock hint, you can view that uncommitted data, that is a dirty read also, correct?

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.