Wednesday, April 23, 2014

TSQL Interview Questions

2. What tools do you use for performance tuning?
Query Analyzer, Profiler, Index Wizard, Performance Monitor
Link: http://www.sqlteam.com/article/sql-server-2000-performance-tuning-tools
3. What is SQL Profiler? What it does? What template do you use?
More Info: http://www.extremeexperts.com/SQL/Articles/TraceTemplate.aspx
http://msdn.microsoft.com/en-us/library/ms190176.aspx
5. 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
Link: Complete differences on in Delete & Truncate.
6. What are extended stored procedures? Can you create your own extended stored-proc?
More Info: http://msdn.microsoft.com/en-us/library/ms175200.aspx
http://msdn.microsoft.com/en-us/library/ms164627.aspx
7. How can you execute a DOS command from SQL or through SQL query by using xp_cmdshell?
1
exec xp_cmdshell 'dir c:\*.exe'
8. How will you insert result set of the above proc in a table?
1
2
insert into
exec xp_cmdshell 'dir c:\*.exe'
9. 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.
More Info: http://msdn.microsoft.com/en-us/library/ms180169.aspx
http://www.sql-server-performance.com/tips/cursors_p1.aspx
10. Why you should not use a cursor? What are its alternatives?
Alternatives: while loops with temp tables, derived tables, correlated sub-queries, CASE stmt
More Info: http://www.sql-server-performance.com/tips/cursors_p1.aspx
http://sqlserverpedia.com/wiki/Cursor_Performance_Issues
http://searchsqlserver.techtarget.com/feature/Part-3-Cursor-disadvantages
11. 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.
12. 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.
More Info: http://msdn.microsoft.com/en-us/library/cc966389.aspx
http://www.wpconfig.com/2010/03/26/ssis-package-configurations/
13. Multiple ways to execute a dynamic query.
EXEC sp_executesql, EXECUTE()
More Info: http://sqlwithmanoj.wordpress.com/2011/03/22/execute-or-exec-vs-sp_executesql/
14. Difference between COALESCE() & ISNULL()
More Info: http://sqlwithmanoj.wordpress.com/2010/12/23/isnull-vs-coalesce/
15. Which of the following has higher performance:
a. OR, AND
b. =, <>, >
c. IN, NOT IN, EXISTS
d. UNION, UNION ALL
16. 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…)
17. 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.
MS BOL link: http://msdn.microsoft.com/en-us/library/ms174639(v=SQL.90).aspx
18. Can you create a Primary key without clustered index?
Creation of PK automatically creates a clustered index upon the column(s).
More Info: http://vadivel.blogspot.com/2006/03/primary-keys-without-clustered-index.html
19. There are two tables one Master and another Feed table, both with 2 columns: ID & Price. Feed table gets truncated and re-populated every day.
Master Table  Feed Table
ID, Price  ID, Price
1    100  1    200
3    200  2    250
5    300  4    500
6    400  6    750
7    500   7    800
Create a job with an optimal script that will update the Master table by the Feed table.
20. What are CUBE & ROLLUP sets?
CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.
More Info: http://sqlwithmanoj.wordpress.com/2010/11/12/cube-rollup-compute-compute-by-grouping-sets/
http://msdn.microsoft.com/en-us/library/ms177673.aspx
21. What new indexes are introduced in SQL Server 2005 in comparison to 2000?
- Spatial
- XML
More Info: http://msdn.microsoft.com/en-us/library/ms175049.aspx
22. What are XML indexes, what is their use?
More Info: http://msdn.microsoft.com/en-us/library/ms345121%28SQL.90%29.aspx
23. How many types of functions (UDF) are there in SQL Server? What are inline functions?
- Scalar functions
- Inline Table-valued functions
- Multi-statement Table-valued functions
More Info: http://sqlwithmanoj.wordpress.com/2010/12/11/udf-user-defined-functions/
http://msdn.microsoft.com/en-us/library/ms189593.aspx
24. 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, link.
More Info: http://msdn.microsoft.com/en-us/library/ms179296%28v=SQL.90%29.aspx
25. How would you send an e-mail form SQL Server?
Configure Database mail here.
More Info: http://msdn.microsoft.com/en-us/library/ms175887%28v=SQL.90%29.aspx

Clustered Indexes, Non Clustered Indexes & why?

Creating Indexes on tables reduces the query retrieval time and increase the efficiency of SQL queries or statements fired against a database in SQL Server. Indexes are just like a Table of Contents in front side of the book or Index section at the back side of the book.
There are mainly 2 types of Indexes, CLUSTERED NON-CLUSTERED index which can be created on a table.
- Clustered indexes are similar to a telephone directory where you search a person’s name alphabetically and get his phone number there only.
- Non Clustered indexes are similar to the Index of a book where you get the page number of the item you were searching for. Then turn to that page and read what you were looking for.
According to MS BOL one can create only one Clustered index & as many 249 Non Clustered indexes on a single table.
But why there is a need to create these indexes, what causes the fast retrival of data from the tables.
Let’s check this by creating a large table and creating these Indexes one by one and checking as we go one:



USE [AdventureWorks]
GO

select * from Sales.SalesOrderDetail -- Total 121317 records
select * from Production.Product -- Total 504 records

SELECT s.SalesOrderDetailID, s.SalesOrderID, s.ProductID, p.Name as ProductName, s.ModifiedDate
INTO IndexTestTable
FROM Sales.SalesOrderDetail s
JOIN Production.Product p
on p.ProductID = s.ProductID
GO

-- Test the table without any Indexes which is also a HEAP
SELECT TOP 10 * FROM IndexTestTable

--////////////////////////////////////////////////
--// Scenario 1 : When there is no Clustered Index
--////////////////////////////////////////////////
SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SELECT SalesOrderDetailID FROM IndexTestTable WHERE SalesOrderDetailID = 60000
SET STATISTICS TIME OFF
SET STATISTICS PROFILE OFF
GO

Avoid CURSORS?

CURSORS or WHILE loops with temp-tables & counter, what do you prefer, personally and perofrmance wise?
This has been discussed in lots of forums, threads, posts and blogs previously. Many people and experts claim to use the either one and most of them are inclined to WHILE loops, and suggest to avoid CURSORS without any proof and logic.

Fibonacci Series,Factorial in SQL Server using CTE Recursion

-- Factorial

;with fact as (
    select 1 as fac, 1 as num
    union all
    select fac*(num+1), num+1
    from fact
    where num<12)
select fac
from fact
where num=5

-- Fibonacci Series

;with fibo as (
    select 0 as fibA, 0 as fibB, 1 as seed, 1 as  num
    union all
    select seed+fibA, fibA+fibB, fibA, num+1
    from fibo
    where num<12)
select fibA
from fibo

-- Number Sequence: 1 to 10

;with num_seq as (
    select 1 as num
    union all
    select num+1
    from num_seq
    where num<100)
select num
from num_seq
where num < 10

-- Date Sequence: May 2011

;with dt_seq as (
    select cast('5/1/2011' as datetime) as dt, 1 as num
    union all
    select dt+1, num+1
    from dt_seq
    where num<31)
select dt
from dt_seq



for more details http://msdn.microsoft.com/en-us/library/ms190766.aspx
                          http://sqlwithmanoj.com/2011/05/23/cte-recursion-sequence-dates-factorial-fibonacci-series/

Difference between Varchar and Nvarchar data types in SQL Server

This is really an interesting topic for me as well as for many developers who all are working on SQL Server.I have seen many developers who are quite confused in differentiating between Varchar and Nvarchar datatypes.I hope after reading my article, you will be able to answer yourself where to use Varchar and where to use Nvarchar.

So let’s see what is the main difference between Varchar and Nvarchar datatype.

There are mainly two differences between Varchar and Nvarchar data types.
Varchar data type can only store non Unicode values while Nvarchar data type can store Unicode + Non Unicode values.
Varchar data type takes 1 byte per character while Nvarchar data type takes 2 bytes per character.
Unicode Characters :- It is the International standard of a character encoding and decoding that is globally accepted.
All the characters from different languages can be encoded by Unicode standard.

Non Unicode Standard or Traditional Standard:- In earlier days when Unicode characters standard was not there for representation of a character,
We were using ASCII standard that was basically based on American and European languages encoding of characters.

Let’s take an example to understand this fact clearly.

Declare @NameNonUnicode as varchar(100)
Declare @NameUnicode as Nvarchar(100)

Set @NameUnicode='Neeraj Kumar Yadav'
Set @NameNonUnicode ='Neeraj kumar Yadav'

Select @NameNonUnicode as NonUnicode, @NameUnicode as Unicode