T-SQL Training Questions

SQL Server Interview Questions

1. What is SQL Server?
SQL Server is a vast, easy, powerful Relational Database Management (also Datawarehouse Management) application from Microsoft. It offers Database Development, Database Management and Business Intelligence capabilities. This wonderful technology is very easy to master and manage. This offers following advantages:

It is positioned Number 2 in the RDBMS market for FY 2011 as per Official Release
Microsoft SQL Server 2008 R2 is the Production release of SQL Server. Next version is SQL Server 2012 (currently in CTP - Community Technology Preview)

 

2. What are the requirements in SQL server?
SQL Server from Microsoft is mainly intended to be used on Windows Systems. Prior versions of SQL Server are compatible on Linux (Redhat Linux) as well. SQL Server comes with different Editions (Flavors) in each Version (Product Release). SQL Server is available in 32 bit and 64 bit compilers. Generic (Minimal)Requirements include:

OS System requirements depend on the Version and Edition you are using. Enterprise and Datacenter Editions require Windows Server 2003 Sp2 or Windows Server 2008 for complete features support.
Here is the list of Editions in SQL Server 2008 R2 : Core Server Editions of SQL Server 2008 R2

Specialized Editions of SQL Server 2008 R2

 

3. What is the purpose of SQL Profiler in SQL server?
SQL Profiler captures SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose problem.
The different purposes of using SQL Profiler are:
It is used to find the cause of the problem by stepping through problem queries.
It is very useful to analyze the cause of slow running queries.
It can be used to tune workload of the SQL server.
It also stores security-related actions that can be reviewed by a security administrator.
SQL Profiler also supports auditing the actions performed on instances of SQL Server.

4. Difference between DELETE & TRUNCATE statement? Which statement can be Rollbacked?
- With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
- TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
- Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
- DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP

5. What are extended stored procedures? Can you create your own extended stored-proc?
Extended stored procedures let you create your own external routines in a programming language such as C. The extended stored procedures appear to users as regular stored procedures and are executed in the same way. Parameters can be passed to extended stored procedures, and extended stored procedures can return results and return status. Extended stored procedures are DLLs that an instance of SQL Server can dynamically load and run. Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API. Yes we can create our own extended stored-proc.

6. How can you execute a DOS command from SQL or through SQL query by using xp_cmdshell?
exec xp_cmdshell 'dir c:\*.exe'

7. How will you insert result set of the above proc in a table?
insert into exec xp_cmdshell 'dir c:\*.exe'

8. What are Cursors and their types? What type do you use most and which one is fast?
FORWARD-ONLY, FAST-FORWARD or READ-ONLY cursors.
Fastest to slowest: Dynamic, Static, and Keyset.

9. Why you should not use a cursor? What are its alternatives?
We should avoid using cursors due to overhead and inconvenience.
Alternatives: while loops with temp tables, derived tables, correlated sub-queries, CASE stmt

10. Difference between LEFT JOIN with WHERE clause & LEFT JOIN with no WHERE clause.
OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.

11. How will you migrate an SSIS package from Development to Production environment?
Do not include db connections and file paths in your workflow, instead create configuration files. This will help in deploying the pkg created in DEV server to Testing and finally to the PROD environment.

12. Multiple ways to execute a dynamic query.
EXEC sp_executesql, EXECUTE(

13. Difference between COALESCE() & ISNULL()
Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence

The NULLability of result expression is different for ISNULL and COALESCE. ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different NULLability values. This makes a difference if you are using these expressions in computed columns and creating key constraints or making return value of a scalar UDF deterministic so that it can be indexed.

14. Which of the following has higher performance:
a. OR, AND
b. =, <>, >
c. IN, NOT IN, EXISTS
d. UNION, UNION ALL

15. What should be the ideal combination with IN & UNION (ALL) in terms of performance?
a. SELECT *FROM
WHERE
IN (SELECT… UNION SELECT…)

OR

b. SELECT * FROM
WHERE
IN (SELECT… UNION ALL SELECT…)

16. What is an IDENTITY column and its usage in INSERT statements?
IDENTITY column can be used with a tables column to make it auto incremental, or a surrogate key.

17. Can you create a Primary key without clustered index?
Creation of PK automatically creates a clustered index upon the column(s).

18. What are CUBE & ROLLUP sets?
CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.

19. What new indexes are introduced in SQL Server 2005 in comparison to 2000?
- Spatial
- XML

20. What are types of XML indexes?
SQL Server 2005 supports four different types of XML indexes. Since an XML index is somewhat different than a relational index, it is necessary to know their implementation before we approach how to use them for maximum effectiveness. There is a single "primary XML index" and three different flavors of "secondary XML index". And it turns out that the primary XML index isn't strictly an index on the original form of the XML. The primary XML index on an XML column is a clustered index on an internal table known as the node table that users cannot use directly from their T-SQL statements. The primary XML index is a B+tree and its usefulness is due to the way that the optimizer creates a plan for the entire query. Although the optimizer can operate on the entire XML column as though it is a blob, when you need to execute XML queries, it is more often useful to decompose the XML into relational columns and rows. The primary XML index essentially contains one row for each node in the XML instance.

21. How will you handle exceptions in SQL Server programming?
By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY block and error handling in the CATCH block.

22. What are the virtual tables in Triggers?
Inserted & Deleted
23. What is benefit of a having stored-procedure?
24. Can stored-procedures be recursive? And upto how much level?
Yes, 32 levels.
25. How you can load large data in SQL Server?
BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.
26. What is the trade-offs of a BCP command, when various users are loading data in a particular table at same time?
27. How can you copy schema from one SQL Server to another?
DTS, import/export wizard.
Scripting out Database objects.

28. What is a table called that has ## before its name, what is its scope?
Table with ## (double pound signs) is called Global Temp table. Scope is outside the session but only till the original session lasts.
29. What is the scope of a temporary table?
Scope is limited to its session only.

30. What are the various Isolation levels?
a) Read Uncommitted Isolation Level
b) Read Committed Isolation Level
c) Repeatable Read Isolation Level
d) Serializable Isolation Level
e) Snapshot Isolation Level
f) Read Committed Snapshot Isolation Level

31. How many non-clustered indexes can you have in a table?
Upto 249 non-clustered indexes can be created in a table.

32. What all indexes can you have in a table?
One Clustered Index, one or more than one non-clustered index, unique index, filtered, spatial, xml, etc.

33. What is the benefit of cross joins? How would you use a where clause with Cross Joins?
The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

34. Difference between VARCHAR & VARCHAR2?
VARCHAR2 is specific to Oracle. MS SQL Server has VARCHAR & VARCHAR(MAX) data types.

35. What is de-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

36. How would you get error & row number at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset.
And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

37. How do we handle Error?
Ans: I think we can use @@Error. Right after the query condition is executed we can check for
@@Error <> 0, if it is not returning zero mean some error occured. Raiserror is another command for raising error
We can also use Try catch block

38. What is PatIndex?
Ans: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
Syntax - PATINDEX ( '%pattern%' , expression )
Eg: USE AdventureWorks;
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO

39. How to query a string contains %?
Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'

40. How to get values from a table with comma seperated?
Ans: declare @vName nvarchar(100)
set @vName = ''
select @vName = @vName + ','+ [Name] from HumanResources.Shift
select @vName

41. How to update 'Yes' to 'No' and viceversa in a query?
Ans: Update tablename set ColumnName1 = (case ColumnName1 when 'Yes'
then 'No'else 'Yes' end)

42. How to create temporary table? How do we apply noncluster index? What is nolock? When and where is nolock applied normally?

Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.
1.
CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(4)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)

2.
select
OfficeName
, officeid
, o.CustID
, AgentID -
, o.mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices o
LEFT JOIN dbo.planparts WITH (NOLOCK)
ON bppa.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)

38. How would you send an e-mail form SQL Server?

39. What is Collation?

50. What can you do with COLASCE function?

51. How to use sql query to copy only structure?

40. How will you know what indexes a particular table is using?

41. What are implicit & explicit cursors?
http://geekexplains.blogspot.com/2008/11/implicit-vs-explicit-cursors-static-vs.html
39. Define the life cycle of a Cursor.
http://sqlwithmanoj.wordpress.com/2010/10/24/sql-server-cursor-life-cycle/

42. What is Mutex error in Triggers?
MSDN link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/c86c97a7-73ea-482e-9529-e2407bd7018c
36. What is the use of WITH (nolock)?
Table Hints, MS BOL: http://msdn.microsoft.com/en-us/library/ms187373(v=SQL.90).aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx?wa=wsignin1.0

43. What is the benefit of a Temporary Table, how would you define it?

 

Sample Resumes and Resources

Register Today For Free Demo