SQL Server Interview Questions

<<PREV	SQL Interview Questions	NEXT >>
41. 1. What’s the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
42. Write a SQL Query to find first Week Day of month
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay
43. How to find 6th highest salary from Employee table?
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DESC) a ORDER BY salary
44. How can I enforce to use particular index?
You can use index hint (index=index_name) after the table name. SELECT au_lname FROM authors (index=aunmind)
45. What is the Referential Integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value
46. How to determine the service pack currently installed on SQL Server?
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed.
47. What is the use of SCOPE_IDENTITY() function?
Returns the most recently created identity value for the tables in the current execution scope.
48. What are the different ways of moving data/databases between servers and databases in SQL Server?
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.
49. What is a deadlock?
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
50. What are defaults? Is there a column to which a default can't be bound?
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them.
<<PREV	SQL Interview Questions on Stored Procedures	NEXT >>

SQL Interview Questions on Stored Procedures, Interview Questions on SP's,SQL FAQS, stored procedures faqs.