TSQL Interview Questions – Part 3

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

51. What are Integrity Constraints?
http://sqlwithmanoj.wordpress.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
- 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
http://sqlwithmanoj.wordpress.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?
http://sqlwithmanoj.wordpress.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?
A – Atomicity (Transaction is atomic, if one part fails, then the entire transaction fails)
C – Consistency (Any transaction the database performs will take it from one consistent state to another, only valid data will be written to the database)
I – Isolation (Other operations cannot access data that has been modified during a transaction that has not yet completed)
D – Durability (On a transaction’s success the transaction will not be lost, the transaction’s data changes will survive system failure, and that all integrity constraints have been satisfied)
More on: http://en.wikipedia.org/wiki/ACID

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?
http://sqlwithmanoj.wordpress.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.
http://sqlwithmanoj.wordpress.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
http://sqlwithmanoj.wordpress.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.