Home > Interview Q > TSQL Interview Questions – Part 3

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.

About these ads
  1. March 23, 2014 at 9:17 pm

    Is it possible to create temp. tables inside trigger code ?.. I think it’s not. It’s being taken care of by INSERTED & DELETED inside trigger.

  2. March 24, 2014 at 9:49 am

    Yes, you can create temp tables inside a trigger. INSERTED/DELETED tables are called virtual or magic tables, their purpose is different than temp tables.

    ~Manoj

  1. February 8, 2011 at 11:28 am
  2. June 2, 2011 at 5:07 am
  3. July 4, 2011 at 3:44 am
  4. July 4, 2011 at 10:23 am
  5. September 21, 2011 at 6:36 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 401 other followers

%d bloggers like this: