SQL with Manoj

Next part of TSQL Interview Questions, contd. from my previous post.

101. What are the required/mandatory parameters that have to be passed with RAISEERROR statement?

– message _id or message_str
– severity, and
– state

102. Difference between RAISERROR and THROW
RAISERROR vs THROW: https://sqlwithmanoj.com/2012/12/20/new-throw-statement-in-sql-server-2012-vs-raiserror/

103. What severity level errors are managed in TRY-CATCH block?
A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection. Check about TRY-CATCH here: https://sqlwithmanoj.com/2010/06/16/try-catch-exception-handling/

104. What are row constructors?
Also known as Table Value Constructor: https://sqlwithmanoj.com/2015/02/16/sql-basics-what-are-row-constructors/.
Also check Method #5 of this post: https://sqlwithmanoj.com/2010/10/15/multiple-ways-to-insert-records-in-a-table/

105. What is SCD (Slowly Changing Dimensions)? What are its types?
Check this YouTube tutorial on SCD: v=aTnr00NnlXE

106. How many types of internal joins are there in SQL Server?
– Nested loop join
– Merge join
– Hash join
More about these: https://sqlwithmanoj.com/2010/10/06/physical-join-vs-logical-join/

107. What are pages in SQL Server? How many types of pages are there?
A page in SQL Server is an 8 KB data storage area. There are 8 types of pages, check details here: https://sqlwithmanoj.com/2011/10/12/what-are-records-pages-extents-heap/

108. How many data sources are available in SSIS?
– DataReader Source in 2005 & ADO NET Source in 2008 & above.
– Excel Source
– Flat File Source
– OLE DB Source
– Raw File Source
– Script Component
– XML Source

109. How will you deploy an SSIS package in testing, staging & production environments?
The dtutil is the command prompt utility which is used to manage SSIS packages.
This utility can copy, move, delete or verify the existence of a package.
More on: http://msdn.microsoft.com/en-us/library/ms162820.aspx

110. Where will you use Conditional Split transformation?
More info: http://msdn.microsoft.com/en-us/library/ms137886.aspx

111. What is live lock, deadlock and what is Lock escalation?
Check this YouTube tutorial on Deadlock: v=V_KLPWHfYFU

112. If you are working on a SQL database and if suddenly a developer changes the code and your queries results start giving errors, how will you check using a T-SQL query (on system tables) that what has changed in the database.
select o.name, o.object_id, o.modify_date, c.text
from sys.objects o
join sys.syscomments c
on o.object_id = c.id

113. Difference between Nested loops join, Merge join & Hash Join.
Check all differences here: https://sqlwithmanoj.com/2010/10/06/physical-join-vs-logical-join/

114. What are Surrogate Keys in a Data Warehouse?
Surrogate Keys are UNIQUE, SEQUENTIAL and MEANINGLESS generated integer numbers to identify every record in a Dimension table uniquely. The are used to JOIN Dimension tables with Fact tables.

… I’ll be adding more questions on upcoming post.

Comments welcome!!!

Next part of TSQL Interview Questions, contd. from my previous post.

76. What are Running totals and how would you calculate them? Create a single SQL statement starting with SELECT or ;WITH.
https://sqlwithmanoj.com/2011/07/04/calculating-running-totals/

77. What are the various SSIS logging mechanisms?
– Text file
– SQL Server Profiler
– SQL Server Log Provider, link
– Windows Event Log
– XML File
MS BOL link: http://msdn.microsoft.com/en-us/library/ms140246.aspx

78. On which table the SSIS SQL Server logs are stored?
On 2005 its sysdtslog90 & on 2008 its sysssislog.
More on SSIS logging on: https://sqlwithmanoj.com/2011/06/15/logging-in-ssis-using-sql-server-log-provider/

79. Reverse a String value without using REVERSE() function and WHILE loop. Should be a single SQL statement/query.
https://sqlwithmanoj.com/2011/08/18/reverse-a-string-without-using-tsqls-reverse-function/

80. What is the use of BCP utility in SQL Server and how will you use it?
https://sqlwithmanoj.com/2011/09/09/bcp-in-out-queryout-the-bcp-util/

81. Is there any difference between Excel Source in SSIS 2005 & 2008?
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ea770da2-6f0c-41c4-8093-52b5df73f460/#4267c1f1-74a0-4e2c-b5a4-e2909bf4e5f4

82. Difference between Bookmark/Key lookup & RID lookup?
Lookups happen when an Index does not cover a Query, or SELECTED columns in a Query are not available in an Index, thus it lookup in the Clustered Index or the table for missing information/columns. Bookmark/Key lookup happens when a Clustered Index is present, and RID lookup happen when there is no Clustered Index present.

83. How nested transactions behave?
Here is a scenario:
I have a transaction T and inside this there are 2 transactions T1 AND T2.
If TRANSACTION T2 fails then what happens to transaction T and T1.
There is no concept of Nested Transactions in SQL Server and creating them does not make sense. Any Transaction fails will Rollback all outer Transactions, and thus all inner/nested Transactions will also Rollback, check here: https://sqlwithmanoj.com/2015/05/26/sql-myth-nested-transactions-in-sql-server-and-hidden-secrets/

84. How will you check if a stored procedure with 500 line of code is running very slow? What steps will you take to optimize it?

85. New features in SQL Server 2005 compared to SQL Server 2000 you’ve worked with.
– PIVOT, UNPIVOT, Ranking functions (row_number, rank, dense_rank, ntile), CTEs, Grouping sets (ROLUP, CUBE), Intersect, Except, OUTPUT clause, Merge statement, Try-Catch, BIDS (SSIS, SSRS, SSAS), CLR, SMO.
New datatypes: XML, VARCHAR(max), NVARCHAR(max), VARBINARY(max) deprecating the TEXT, NTEXT AND IMAGE datatypes.
– XML  indexes.
– Database (SMTP) mail, SSMS, DMVs, Express Edition, Service Broker, Data Encryption, MARS
For all the new SQL Server 2005 features discussed on this blog check here: https://sqlwithmanoj.com/category/sql-server-versions/sql-server-2005-sql-server-versions/

86. How will you copy unique records from duplicates in source to destination in SSIS?

87. What transformation will you use to concatenate First name and Last name in SSIS?

88. What do you mean by selectivity of a column/table?
The selectivity is what goes with the cardinality concept. The “cardinality” refers to the number of “distinct” values, as in the set theory so, take a column “SEX”.  The possible values are “male” and “female” (ignoring for the moment other possible values like “unknown” or even “other”) … so, your cardinality for that column would be 2, no matter how many rows you have in that table.

The selectivity is the “number of rows” / “cardinality”, so if you have 10K customers, and search for all “female”, you have to consider that the search would return 10K/2 = 5K rows, so a very “bad” selectivity.

The column for the primary key on the other side is “unique”, and hence the cardinality is equal to the number of rows, by definition.  So, the selectivity for searching a value in that column will be 1, by definition, which is the best selectivity possible.

89. Difference between EXISTS & IN, which one gives good performance?
EXISTS vs IN performance: https://sqlwithmanoj.com/2011/02/15/not-in-not-exists-joins-with-null-values/

90. What output will “SELECT 1/2” statement give?
0, it will give zero.

91. What is Database Partitioning?
This involves 4 steps:
1. Create Database with different file groups
2. Create Partition Function
3. Create Partition Scheme
4. Create Partitioned Table or Index

92. What is the use of NOLOCK option?
https://sqlwithmanoj.com/2013/10/04/difference-between-nolock-and-readpast-table-hints/

93. How many types of recovery models are available for a database?
1. Simple
2. Bulk logged
3. Full

94. How many types of temporary tables are there in SQL Server?
– Local Temp tables (#)
– Global temp tables (##)
– Table variables (@)
Check here to know about all these temporary tables: https://sqlwithmanoj.com/2010/05/15/temporary-tables-vs-table-variables/ | YouTube

95. In how many ways you can get a table’s row count?

--// 1. Using COUNT(*)
Select count(*) from Person.Contact

--// 2. using COUNT(1)
select count(1) from Person.Contact

--// 3. Using SUM() aggregate function
select sum(1) from Person.Contact

--// 4. Using sysindexes view
select object_name(id), rows from sys.sysindexes where object_name(id) = 'Contact' and indid<2

--// 5. Using sp_spaceused system SP
exec sp_spaceused 'Person.Contact' 

--// 6. Using DBCC CHECKTABLE function
DBCC CHECKTABLE('Person.Contact') 

--// Note: Before running 5 & 6 you may need to run this script:
DBCC UPDATEUSAGE ('AdventureWorks','Person.Contact') WITH COUNT_ROWS

Reference

96. In how many ways you can select distinct records from a table?

97. In how many ways you can select top 1 row?

98. What are the new features introduced in SQL Server 2008 R2?
SQL Server 2008 new features: https://sqlwithmanoj.com/category/sql-server-versions/sql-server-2008-sql-server-versions/

99. What are the new features added SQL Server 2012 (Denali)?
SQL Server 2012 (denali) new features: https://sqlwithmanoj.com/denali-2012/

100. What new feature has been added to TRY-CATCH construct?
Check here for all posts related to TRY-CATCH: https://sqlwithmanoj.com/tag/try-catch/

… more questions on next post Part-5.

Next part of TSQL Interview Questions, contd. from my previous post.

51. What are Integrity Constraints?
https://sqlwithmanoj.com/2010/11/23/integrity-constraints/

52. Difference between:
– Views, Tables & Stored Procedures
– Stored Procedures & Functions
– Sub Query & Co-related sub-query
– Physical & Logical Schema
– Table variable & Temporary Tables – YouTube
– UNIQUE and CLUSTERED INDEXES
– Triggers and Constraints
– Primary Key & Unique Key

53. What do you mean by Referential Integrity? How will you attain it?
By using Foreign Keys.
http://www.databasedesign-resource.com/referential-integrity.html

54. What is the sequence for logical query processing, what is the order?
FROM, [JOIN CONDITION, JOIN TABLE …], ON, OUTER, WHERE, GROUP BY, CUBE/ROLLUP/GROUPING SETS, HAVING, SELECT, DISTINCT, ORDER BY, TOP
https://sqlwithmanoj.com/2010/10/28/sql-logical-query-processing-order/

55. How you debug Stored Procedures?
http://support.microsoft.com/kb/316549
http://www.sqlteam.com/article/debugging-stored-procedures-in-visual-studio-2005
http://www.15seconds.com/issue/050106.htm

56. What is ANSI_NULL?
https://sqlwithmanoj.com/2010/12/10/set-ansi_nulls-quoted_identifier-ansi_padding/

57. How will you rename a table?
By using sp_rename stored procedure.

58. What are ACID properties, define them?
https://sqlwithmanoj.com/2014/09/28/db-basics-what-are-acid-properties-of-a-transaction-in-an-rdbms/

59. What is a Live Lock?
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/478aa50f-b7dd-43fb-bb90-813057a6a1ed
http://blog.sqlauthority.com/2008/03/21/sql-server-introduction-to-live-lock-what-is-live-lock/

60. Difference between “Dirty Read” & “Phantom Read”. Explain both of them?
https://sqlwithmanoj.com/2011/07/20/dirty-reads-and-phantom-reads/

61. What is BITMAP index and BITMAP filtering?
http://msdn.microsoft.com/en-us/library/bb522541%28v=SQL.100%29.aspx
http://social.msdn.microsoft.com/Forums/hu-HU/sqldatabaseengine/thread/4717addd-1c8d-4c6b-8607-e191324c1cd8

62. What are different ISOLATION Levels (High to Low)?
http://msdn.microsoft.com/en-us/library/ms189122.aspx
– SERIALIZABLE
– SNAPSHOT
– REPEATABLE READ (phantom read)
– READ COMMITTED
– READ UNCOMMITTED (dirty read)
http://blogs.msdn.com/b/sqlcat/archive/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels.aspx

63. What is the highest, lowest & default ISOLATION Level?
Highest: SERIALIZABLE
Lowest: READ UNCOMMITTED
Default: READ COMMITTED

64. What is SERIALIZABLE Isolation Level?
http://msdn.microsoft.com/en-us/library/ms173763.aspx

65. What is a CTE & how it is different from a Derived table? Example of recursive CTE.
https://sqlwithmanoj.com/2011/05/23/cte-recursion-sequence-dates-factorial-fibonacci-series/

66. What are design considerations for a Clustered Index & Non Clustered Index?

67. What are UDFs and their usage in a SELECT query?
– SCALAR
– TABLE VALUED
– MULTI LINE TABLE VALUED
https://sqlwithmanoj.com/2010/12/11/udf-user-defined-functions/

68. What are UNION, UNION ALL, EXCEPT & INTERSECTION keywords?

69. What are File Groups in SQL Server? What is its benefit? Explain any scenario where you will use multiple file groups.
http://msdn.microsoft.com/en-us/library/ms179316.aspx

70. How will you handle & avoid Deadlock?
http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
http://support.microsoft.com/kb/169960
http://www.devx.com/getHelpOn/10MinuteSolution/16488/1954

71. What are Implicit Transactions?
http://msdn.microsoft.com/en-us/library/ms188317.aspx
http://msdn.microsoft.com/en-us/library/ms190230.aspx

72. How will you know Index usage on tables?
– Execution plan
– SET STATISTICS PROFILE ON

73. What are Indexed Views and their use? How will you create them?
http://msdn.microsoft.com/en-us/library/ms191432.aspx
A view must meet the following requirements before you can create a clustered index on it:
–          The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed.
–          The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
–          The view must not reference any other views, only base tables.
–          All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
–          The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.
–          User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
–          Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed.
–          If the view definition uses an aggregate function, the SELECT list must also include COUNT_BIG (*).
–          If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, ROLLUP, CUBE, or GROUPING SETS.
The SELECT statement in the view cannot contain the following Transact-SQL syntax elements:
–          The * or table_name.* syntax to specify columns. Column names must be explicitly stated.
–          An expression on a column used in the GROUP BY clause, or an expression on the results of an aggregate.
–          A derived table.
–          A common table expression (CTE).
–          Rowset functions.
–          UNION, EXCEPT or INTERSECT operators.
–          Subqueries.
–          Outer or self joins.
–          TOP clause.
–          ORDER BY clause.
–          DISTINCT keyword.
–          COUNT (COUNT_BIG(*) is allowed.)
–          A SUM function that references a nullable expression.
–          The OVER clause, which includes ranking or aggregate window functions.
–          A CLR user-defined aggregate function.
–          The full-text predicates CONTAINS or FREETEXT.
–          COMPUTE or COMPUTE BY clause.
–          The CROSS APPLY or OUTER APPLY operators.
–          The PIVOT or UNPIVOT operators
–          Table hints (applies to compatibility level of 90 or higher only).
–          Join hints.
–          Direct references to Xquery expressions. Indirect references, such as Xquery expressions inside a schema-bound user-defined function, are acceptable.

74. What do you mean by Concurrency control?
http://msdn.microsoft.com/en-us/library/ms189130.aspx

75. What do you understand by Star & Snowflake schema and whats the difference between them?

… more questions on next post Part-4.

Next part of TSQL Interview Questions, contd. from my previous post.

26. What are the virtual tables in Triggers?
Inserted & Deleted

27. What is benefit of a having stored-procedure?

28. Can stored-procedures be recursive? And upto how much level?
Yes, 32 levels.

29. 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, check here: https://sqlwithmanoj.com/2011/09/09/bcp-in-out-queryout-the-bcp-util/

30. What is the trade-offs of a BCP command, when various users are loading data in a particular table at same time?

31. How can you copy schema from one SQL Server to another?
DTS, import/export wizard.
Scripting out Database objects.

32. What is the benefit of a Temporary Table, how would you define it?
Benefits of a Temp #Table: http://wp.me/p12rgl-r | YouTube

33. 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.

34. What is the scope of a temporary table?
Scope is limited to its session only.

35. 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)?
https://sqlwithmanoj.com/2013/10/04/difference-between-nolock-and-readpast-table-hints/
MSDN: http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx?wa=wsignin1.0

37. 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
http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx

38. 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.
https://sqlwithmanoj.wordpress.com/2010/10/24/sql-server-cursor-life-cycle/

40. How would you know that if a cursor is open or closed?

declare @mycursor cursor
declare @FirstName varchar(12)

select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable)

set @mycursor = cursor for
select FirstName from Person.Contact
select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed)

open @mycursor

select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row)

fetch next from @mycursor into @FirstName
select CURSOR_STATUS('variable','@mycursor') --// 1 (The result set of the cursor has at least one row)

close @mycursor

select CURSOR_STATUS('variable','@mycursor') --// -1 (The cursor is closed)

deallocate @mycursor

select CURSOR_STATUS('variable','@mycursor') --// -2 (Not applicable)

select CURSOR_STATUS('variable','@nocursor') --// -3 (A cursor with the specified name does not exist)

41. How many non-clustered indexes can you have in a table?
Upto 999 non-clustered indexes can be created in a table in SQL Server 2012/2014.

42. 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.
MS BOL: http://msdn.microsoft.com/en-us/library/ms175049.aspx

43. How will you know what indexes a particular table is using?
1. By checking the graphical Execution Plan.
2. By using sys.dm_db_index_usage_stats DMV, link: https://sqlwithmanoj.com/2015/04/02/index-usage-stats-indexes-used-unused-and-updated/

44. 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.
http://weblogs.sqlteam.com/jeffs/archive/2005/09/12/7755.aspx
http://www.dotnetspider.com/forum/44591-why-we-use-cross-join-sqlserver.aspx

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

46. 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. http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8c54d1d2-5fbd-4b62-a07f-16c34a863668
Normalization: https://sqlwithmanoj.com/2009/03/25/database-normalization/

47. How would you get @@ERROR & @@ROWCOUNT at the same time?
Both the statements should be applied immediately after the SQL query you want to get the details of. If either of them is used later you will miss the information. So to get @@ERROR and @@ROWCOUNT details you need to execute them together in one statement like:
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

48. What is Collation?
https://sqlwithmanoj.com/2010/02/23/collation-conflict-in-sql-server/
https://sqlwithmanoj.com/2010/03/25/change-a-database-collation/
MSDN: http://msdn.microsoft.com/en-us/library/aa174903(v=sql.80).aspx

49. Types of Replication? Difference between Merge & Transactional Replication.
MS BOL link: http://msdn.microsoft.com/en-us/library/ms165713(v=sql.90).aspx

50. What can you do with COALESCE function?
COALESCE() and difference with ISNULL(): https://sqlwithmanoj.com/2010/12/23/isnull-vs-coalesce/

… more questions on next post [Part-3].

These are the following common question that I faced in various TSQL interviews.

… I’ll be updating this post with more questions.

1. Difference between SQL Server 2000 & SQL Server 2005 features, or new features of 2005 vs 2000.
– Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [YouTube]
– Exception handling (TRY-CATCH block)
– CTE (Common Table Expressions)
– PIVOT, UNPOVIT
– CUBE, ROLUP & GROUPING SET
– SYNOMYMS

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

4. How can you execute an SQL query from command prompt?
OSQL & SQLCMD
More Info: http://msdn.microsoft.com/en-us/library/ms162773.aspx
http://blog.sqlauthority.com/2009/01/05/sql-server-sqlcmd-vs-osql-basic-comparison/
http://www.databasejournal.com/features/mssql/article.php/3654176/SQL-Server-2005-Command-Line-Tool-SQLCMD–Part-I.htm

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
More differences on Delete & Truncate: http://wp.me/p12rgl-7 | YouTube.

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?

exec xp_cmdshell 'dir c:\*.exe'

More Info: http://msdn.microsoft.com/en-us/library/aa260689%28SQL.80%29.aspx
http://msdn.microsoft.com/en-us/library/ms175046.aspx

8. How will you insert result set of the above proc in a table?

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
Check how to use Cursors optimally: https://sqlwithmanoj.com/2011/02/07/avoid-cursors-or-use-them-optimally/

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.
JOINs and its types: https://sqlwithmanoj.com/2009/03/12/sql-server-joins-and-types/

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: https://sqlwithmanoj.com/2011/03/22/execute-or-exec-vs-sp_executesql/

14. Difference between COALESCE() & ISNULL()
More Info: https://sqlwithmanoj.com/2010/12/23/isnull-vs-coalesce/

15. Difference between Temporary Table and Table Variable.
Check here: http://wp.me/p12rgl-r | YouTube

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.
Check my all blog posts related to IDENTITY: https://sqlwithmanoj.com/tag/identity/

18. Can you create a Primary key without clustered index?
Yes, you can create a Primary Key with a Non Clustered Index. But by default creation of PK automatically creates a clustered index upon the table, check here: https://sqlwithmanoj.com/2015/05/24/sql-myth-primary-key-pk-always-creates-clustered-index/

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.
Use MERGE statement to INSERT any new record form the Feed table, update any existing record in Master table and DELETE existing records in Master table that is not present in 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: https://sqlwithmanoj.com/tag/cube/

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: https://sqlwithmanoj.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, https://sqlwithmanoj.com/2010/06/16/try-catch-exception-handling/.

25. How would you send an e-mail form SQL Server?
Configure Database mail here: https://sqlwithmanoj.com/2010/09/29/database-mail-setup-sql-server-2005/.

… more questions on next post [Part-2].