10 | February | 2011 | SQL with Manoj

TSQL Interview Questions – Part 3

February 10, 2011 8 comments

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.