SQL Server Questions

What are the OS services that the SQL Server installation adds?

MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

Which virtual table does a trigger use?

Inserted and Deleted.

How to know which INDEX a table is using in SQL SERVER?

SELECT table_name,index_name FROM user_constraints

What is Cross Join in SQL Server?

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

What is Self Join in SQL Server?

This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is the basic functions for master, msdb, model, tempdb databases?

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care. The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping. The tempdb holds temporary objects such as global and local temporary tables and stored procedures. The model is essentially a template database used in the creation of any new user database created in the instance.

What is the REPLACE function in SQL?

REPLACE function replaces all occurrences of the second given string expression in the first string expression with a third expression. Syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with eplacement_string.

Note: Returns NULL if any of the arguments is NULL.

What is the difference between CAST and CONVERT in SQL?

Both CAST and CONVERT perform datatye conversions. In most respects, they both do the same thing, with the exception that CONVERT also does some date formatting conversions that CAST doesn't offer.

Note:- If CONVERT does everything that CAST does, and CONVERT also does date conversions, then why would ever use CAST? Simple answer for that - ANSI compliance. CAST is ASNI-compliant, and CONVERT isn't.

How to use EXISTS operator OR How to use subqueries with the EXISTS operators?

A subquery can be used with the EXISTS operator as "EXISTS (subquery)", which returns true if the subquery returns one or more rows. Example - Select custID, CompanyName from Customers cu where EXISTS (Select OrderID from Orders o where o.custID = cu.custID)

Note:- When you use EXISTS, you don't really return data - instead, you return a simple TRUE/FLASE regarding the existence of data that meets the criteria established in the query that the EXISTS statement is operating against.

How to SELECT TOP N records from mytable without using TOP keyword?

SET ROWCOUNT N select * from mytable SET ROWCOUNT 0 To turn this option off (so that all rows are returned), specify SET ROWCOUNT 0.

What is the advantage of SET NOCOUNT ON?

When we use SELECT and DML statement in SQL. SQL SERVER return a message which specify the number of rows affected by these statements. This information helps developer when they are debugging the code other-wise this is not useful. We can disable this by typing SET NOCOUNT ON. Setting SET NOCOUNT ON for stored procedure contains lots of statements, loops increases the performance and boost network traffic.

What are the types of constraints in SQL Server?

There are three types of constraints in SQL Server - 1. Domain Constraint - deals with one are more columns. 2. Entity Constraint - are all about individual rows. 2. Referential Integrity Constraint - are created when a value in one column must match the value in another column - in either the same table or in a different table.

What is the CASCADE action in SQL Server?

Bu default, you can not delete a record or update the referenced column in a referenced table if that record is referenced from the dependent table. If you want to be able to delete or update such records, then you need to set up a CASCADE action for the delete and/or update.

What is the primary key in SQL Server?

Primary keys are the unique identifiers for each row in a table. They must contains unique values (and hence can not be NULL). Because of their importance in the relational database, primary keys are the most fundamental of all keys and constraints. A table can a maximum of one primary key. A primary key ensures uniqueness within the columns declared as being part of that primary key and that unique value serve as an identifier for each row. For Read More Click Here>>