SQL Server Interview Questions

SQL Server Interview Questions:

How to implement many-to-many relationships?

What's the difference between a primary key and a unique key?

What is user defined datatypes and when to use them?

Define candidate key, alternate key, and composite key?

Is there a column to which a default value can't be bound?

What are ACID properties?

Explain different isolation levels?

What is MOLAP, ROLAP and HOLAP?

Types of constraints?

What are the types of indexes ? What are the type of the NonClustered Indexes ?

In which situation NonClustered is more fast then clustered index?

What is the difference between deadlock , live lock and blocking ? And How to resolve them?

What are the Query Hints?

What are the different types of DBCC commands?

What are instead of triggers and what’s the difference between Insert and Instead of trigger , which trigger will be fired first ?

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? Why DBAs don’t like Cursors ?

What is a self join? Is self join physically exists in Sql Server ?

What is the difference between OSQL and Query Analyzer ?

What is BCP and when to use it ?

What is collation ?

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

When is the use of UPDATE_STATISTICS command?

What types of Joins are possible in Sql Server?
Where are SQL server users names and passwords are stored in sql server?

What is log shipping?
What is the difference between a local and a global variable?
What are the OS services that the SQL Server installation adds?
Specify  3 ways to get an accurate count of the number of records in a table?

What is the basic functions for master, msdb, model, tempdb databases?

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
How do you load large data to the SQL server database?
What is Cross Join?

What is OLTP(OnLine Transaction Processing)? How to define that a Database is OLTP ?

What is the Diffrence between Extents and Page

What is the Diffrence between temp table and table variable

What is SQL injection

Specify the Tips when Optimizing Sql Server 2005 Query

What is the difference between UNION ALL Statement and UNION

What are the different types of Locks

What is Write ahead log?

How to get which Process is Blocked in SQL SERVER

What is SQL Server English Query?

What is XPath?

What is the STUFF and how does it differ from the REPLACE function?

SQL Server Interview Questions (Part 2)

SQL Server Interview Questions (Part 2): Few days back, i went for an interview , following questions were asked

• How to Performance Tune a Stored Procedure?
• Type of Triggers?
• How to get the old value of a field from a trigger when updating a table?
• What is the difference between horizontal partition and vertical partition?
• if we have three parameters in the where condition (query has the joins with two or more tables table1.filed1=4 and table2.field2=5 and table3.field3=1)
  what will be the Order of execution of the where condition
  Which condition would be executed first i.e. field1 or field2 or field3?
• What are the disadvantages of the cursors?
• A stored procedure has cursors in it, how we can eliminate the cursors?
• What is the difference between temporary table and table variable?
• How to restore the Differential Backup?
• What are Backup Types and difference between them?
• What are Replication Types, What is snapshot replication?
• What is the difference between Replication and Mirroring?
• Can a sub report in Crystal Reports can have another sub report?
• What is log shipping?
• Should we normalize the Database on more than 3rd Normal form?

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column:

I was trying to add a filestream column in one of my existing tables, initially i thought it would be very easy for me, but when i got the error "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column."  i started to look here and there. Surfed on the net but didn't find a solution.
Below is the solution which i tried.

Workaround to produce the problem again

CREATE TABLE Employee (
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [emp_name] [nvarchar](150) NULL,
    [emp_FHname] [nvarchar](150) NULL,
    [emp_age] [int] NULL,
    [emp_sex] [bit] NULL,
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
    [emp_id] ASC
)
) ON [PRIMARY]

GO
emp_id is defined as IDENTITY and Primary key

now i wants to add a filestream enabled column named "emp_pic" to store employee pictures, and the table doesn't have any ROWGUIDCOL so i added a empid

ALTER TABLE Employee ADD
    empid UNIQUEIDENTIFIER DEFAULT (newid()) ROWGUIDCOL NOT NULL

After this i executed this statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
 
it will give you the error  Msg 5505, Level 16, State 1, Line 1
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

To solve this problem i just changed my primary key column to empid that i added later as ROWGUIDCOL
After changing the primary key, execute the statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
It will be executed successfully