SQL SERVER 2005 (T-SQL ) Interview Questions - PART 1

1) Differences between Unique index and unique constraint?

· UNIQUE constraints are part of the ANSI SQL definition and defining UNIQUE constraints is part of defining a database's logical design.
· UNIQUE constraints can be created as part of a table's definition and, as a by-product, SQL Server will automatically create a unique index under the hood when the constraint is created.
· In contrast, defining unique indexes are part of designing the physical data model and they are not ANSI standard.
· From a performance standpoint, UNIQUE constraints and unique indexes are effectively the same to the query optimizer.
· Recommended the ANSI standard that implement column uniqueness via constraints.
· UNIQUE constraints help in better documenting a table and use them over defining unique indexes.


2) Differences between Users defined functions and Stored procedures?

· User defined functions can be used in sql statements anywhere in the Where/Select /having sections where as Stored Procedure cannot be used.


3) Differences between Truncate and Delete statements?

· Truncate statement deletes all the rows from table at single execution
· Delete statement deletes specific rows depends upon condition. If, no condition it also delete all rows in row by row
· Where clause can be used in Delete statement, whereas in Truncate cannot be.
· Truncate statement faster than Delete statement
· Truncate statements do not log entries, whereas Delete statement does log entry.
· Because of no log entries truncate statement faster than delete statement.
· Because of no log entries it is not possible roll back data after Truncate statement execution, whereas it can possible roll back data after Delete.
· Truncate operation is a minimally logged operation, whereas Delete operation is fully logged.


4) How many types of Triggers in sql server 2005?


· Sql server 2005 provides 2 types Triggers namely DML Triggers and DDL Triggers


5) Differences between DDL and DML Triggers?

· DML Triggers generally used to perform operations against the data that was modified in a table.
· DML Triggers fire in response to INSERT, DELETE, and UPDATE statements executed against a specific table.
· DDL Triggers fire in response to DDL commands executed on the server.
· DDL Triggers are generally used for security and auditing purposes.


6) What are Recursive and Nested Triggers?

· Only DML Triggers can have recursive riggers or nested triggers.
· When Triggers are fire in response to DML operations, there is a possibility for a Trigger to cause itself to fire or to fire other additional Triggers.
· If a trigger causing itself to fire is called Recursive Trigger
· When a trigger change cause other Trigger to fire it is called Nested trigger.
· Sql Server has a mechanism to control recursive Triggers
· By default Recursive Triggers option of a database is normally set to off. If need turn it on explicitly.
Note:- An Instead of Trigger does not fire recursively


7) What type of commands or constructs cannot use within Trigger?

· Databases cannot be created, altered, dropped, backed up and restored.
· Cannot create, alter, and drop Indexes on Tables which causes structural changes.
· Sql Server does not support Triggers against system Tables and dynamic management views (DMVS).
· Triggers will fire only in response to logged operations ,
· Triggers will not fire in response to Minimally Logged operations such as Write Text and Truncate operations.


8) What is a Trigger?

· A Trigger is a specialized implementation of a Transact sql or CLR batch that automatically runs in response to an event database.
· A Trigger can fire in 2 different modes namely 1)After 2)Instead

9) Differences between After and Instead of Triggers?

· After Triggers fires when sql server completes all action successfully.
· Instead of Trigger causes sql server to execute code inside of Trigger.
· After Triggers can create on tables only
· Instead of Triggers can define on Tables and Views.
· The most common usage is to use Instead of Triggers on views to update multiple base tables through view.
· After Triggers create any number on table
· Only one Instead of Trigger for each type insert, update or delete.

10) What are the restrictions on the select statement with in a view?

· Compute and compute by clause are not allowed.
· Cannot used INTO keyword and OPTION clause.
· Temporary tables and table variables cannot be referenced.
· An order by clause can specified only when TOP clause also used.

11) Differences between regular views and indexed views?

· A regular view is a select statement that is referenced by a name and stored in a sql server.
· A regular view dose not contains any data.
· An indexed view is a view it has a clustered index created against it, which causes sql server to materialize and store the results of the query defined in the view on disk.

12) How to find Top 10 worst performance Queries in a Database?

· Open Management studio
· Connect to required server
· Open object explorer
· Double click on data bases folder to open databases list
· Right click on required database
· Select report à Custom reportsàTop Transactions by age.

13) How to find number of rows per index page?

· Sql server 2005 allows 8060 bytes per index/data page. The data or index rows allowed per page is the result of 8060/ size of single index row
· Number of rows per index/data page =8060/size of index/data page

14) What is external fragmentation?

· External fragmentation is the condition in which physical order of index pages does not match logical order

15) Why are statistics important to query performance?

· During the query evaluation, the query optimizer uses the statistical information to estimate the cost of using an index and determine the optimal plan for a query