Next part of TSQL Interview Questions, contd. from my previous post.
51. What are 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.
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
56. What is ANSI_NULL?
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?
60. Difference between “Dirty Read” & “Phantom Read”. Explain both of them?
61. What is BITMAP index and BITMAP filtering?
62. What are different ISOLATION Levels (High to Low)?
– REPEATABLE READ (phantom read)
– READ COMMITTED
– READ UNCOMMITTED (dirty read)
63. What is the highest, lowest & default ISOLATION Level?
Lowest: READ UNCOMMITTED
Default: READ COMMITTED
64. What is SERIALIZABLE Isolation Level?
65. What is a CTE & how it is different from a Derived table? Example of recursive CTE.
66. What are design considerations for a Clustered Index & Non Clustered Index?
67. What are UDFs and their usage in a SELECT query?
– TABLE VALUED
– MULTI LINE TABLE VALUED
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.
70. How will you handle & avoid Deadlock?
71. What are Implicit Transactions?
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?
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.
– 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?
75. What do you understand by Star & Snowflake schema and whats the difference between them?
… more questions on next post Part-4.