Click here to get free chapters (PDF) in the mailbox
Please read the Introductory Post before continue reading interview question and answers.
List of all the Interview Questions and Answers Series blogs
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
List of all the Interview Questions and Answers Series blogs
Reference: Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
Pinal,
What do you mean by ” Identity/GUID columns do not need to be indexed.”?
I’d have thought that 99% of the time, identity/guids form the basis of the primary/foreign key relationships and therefore should be indexed.
Hi David,
You are correct. I have fixed the issue. The original answer was much longer when edited this was left which was incorrect and confusing. Now fixed.
Thanks again,
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.
I agree with your point Aaron. Very well said. Your comment completes the question. I am updating the question to read your comment.
TOUGH TIME !!!
ISN’T IT PINAL ??
Why did U miss the Day 4??
Dirty read is only possible in “READ UNCOMMITTED” isolation level. Please confirm . This question always raise when interviewer is asking about read Phenomena as this happens to me all the time :-) .
yes, dirty read is only happens when Isolation level set to “READ UNCOMITTED”
Thanks,
Ramesh
How do we make a connection between Access forms and SQL Server. I know, we can not through link tables. Is there any better way than this.?
In Access, cant you make use of connection string to connect to SQL Server?
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?
Hi pinaldave, I found the Order of the Logical Query Processing Phases in Microsoft.Press.Inside.Microsoft.SQL.Server.2005.T-SQL.Querying.Apr.2006 : (edited special quotes from the book name)
(8) SELECT (9) DISTINCT (11)
(1) FROM
(3) JOIN
(2) ON
(4) WHERE
(5) GROUP BY
(6) WITH {CUBE | ROLLUP}
(7) HAVING
(10) ORDER BY
the order of TOP and ORDER BY is different from what you listed.
1.How can I set the Isolation level as ‘READ UNCOMMITED’?
2.What are the other Isolation levels?
3.Can you provide Syntax or example of it.
4.Also is there anything called Phantom Read. If Yes please explain
I have seen that DISTINCT affect the performance of any SQL query.
Is there any alternative of DISTINCT which will not affect the performance