SQL-Server Interview Questions

1. What is RDBMS?
2. What is normalization?
3. What is Stored Procedure?
4. What is Trigger?
5. What is View?
6. What is Index?
7. What is the difference between clustered and a non-clustered index?
8. What are the different index configurations a table can have?
9. What is cursors?
10. What is the use of DBCC commands?
11. What is a Linked Server?
12. What is Collation?
13. What are different type of Collation Sensitivity?
14. What’s the difference between a primary key and a unique key?
15. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
16. What is a NOLOCK?
17. What is difference between DELETE & TRUNCATE commands?
18. When is the use of UPDATE_STATISTICS command?
19. What types of Joins are possible with Sql Server?
20. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
21. What is sub-query? Explain properties of sub-query.
22. What are types of sub-queries?
23. What is SQL Profiler?
24. What is User Defined Functions?
25. What kind of User-Defined Functions can be created?
26. What are the authentication modes in SQL Server? How can it be changed?
27. Where are SQL server users names and passwords are stored in sql server?
28. Which command using Query Analyzer will give you the version of SQL server and operating system?
29. Can a stored procedure call itself or recursive stored procedure? How many level SP nesting possible?
What is @@ERROR?
What is Raiseerror?
What is log shipping?
What is the difference between a local and a global variable?
What command do we use to rename a db?
What is sp_configure commands and set commands?
What are the different types of replication? Explain.
What are the OS services that the SQL Server installation adds?
What are three SQL keywords used to change or set someone’s permissions?
What does it mean to have quoted_identifier on? What are the implications of having it off?
What is the STUFF function and how does it differ from the REPLACE function?
Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
How to rebuild Master Database?
What is the basic functions for master, msdb, model, tempdb databases?
What are primary keys and foreign keys?
What is data integrity? Explain constraints?
What are the properties of the Relational tables?
What is De-normalization?
How to get @@error and @@rowcount at the same time?
What is Identity?
What is a Scheduled Jobs or What is a Scheduled Tasks?
What is a table called, if it does not have neither Cluster nor Non-cluster Index?
What is it used for?
What is BCP? When does it used?
Can we rewrite subqueries into simple select statements or with joins?
Can SQL Servers linked to other servers like Oracle?
How to know which index a table is using?
How to copy the tables, schema and views from one SQL server to another?
What is Self Join?
What is Cross Join?
Which virtual table does a trigger use?
List few advantages of Stored Procedure.
What is DataWarehousing?
What is OLTP (Online Transaction Processing)?
How do SQL server 2000 and XML linked? Can XML be used to access data?FOR XML (ROW, AUTO, EXPLICIT)
What is an execution plan? When would you use it? How would you view the execution plan?
How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
What’s the difference between a primary key and a unique key?
What is user defined data types and when you should go for them?
What is bit data type and what’s the information that can be stored inside a bit column?
Define candidate key, alternate key, and composite key.
What are defaults? Is there a column to which a default can’t be bound?
What is a transaction and what are ACID properties?
CREATE INDEX myIndex ON myTable(myColumn)What type of Index will get created after executing the above statement?
What’s the maximum size of a row?
What is lock escalation?
What’s the difference between DELETE TABLE and TRUNCATE TABLE commands?
Explain the storage models of OLAP
What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version?
What are constraints? Explain different types of constraints.
What is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. What are the advantages and disadvantages of this approach?
What is RAID and what are different types of RAID configurations?
What are the steps you will take to improve performance of a poor performing query?
What are the steps you will take, if you are tasked with securing an SQL Server?
What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
What is blocking and how would you troubleshoot it?
How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?
As a part of your job, what are the DBCC commands that you commonly use for database maintenance?
What are statistics, under what circumstances they go out of date, how do you update them?
What are the different ways of moving data/databases between servers and databases in SQL Server?
Explain different types of BACKUPs available in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
What is database replication? What are the different types of replication you can set up in SQL Server?
How to determine the service pack currently installed on SQL Server?
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
What is a join and explain different types of joins.
What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?
What is the system function to get the current user’s user id?
What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?
There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly inserted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
What is a self-join? Explain it with an example.