Home
MS SQL
Interview FAQs

FAQs of level

A DEFAULT constraint, like all constraints, becomes an integral part of the table definition. It defines what to do when new row is inserted that does not include data for the column on which you have defined the DEFAULT constraint. You can either define it as a literal value(like default salary to zero or UNKNOWN for a string column) or as one of several values such as GETDATE().

The main things to understand about a DEFAULT constraint are -

1. DEFAULTs are only used in INSERT statements- They are ignored for UPDATE and DELETE statements.
2. If any value is supplied in the INSERT then the default is not used.
3. If no values supplied the default will always be used.

The IDENTITY property enables you to use system generated values in your tables column. It is similar to the auto number datatype in MS ACCESS. You are allowed a single column in each table with the IDENTITY property. Typically, IDENTITY column generates system assigned keys. To enforce entity integrity, you must uniquely identify each row in a table. If no natural column or set of column does this, you might went to create an IDENTITY column.

You can use the IDENTITY property if the column to which it is being assigned is an integer or compatible with an integer. Therefore you can use the following datatypes - tinyint, bigint, smallint, numeric, integer, decimal.

You can use numeric and decimal only if they have scale of 0 (zero). It must also not allowed to NULL.

Using the table tablename, write the query as follows -
Select * from information_schema.columns where table_name = tablename
A deadlock is a situation in which two transactions conflict with each other and the only resolution is to cancel one transaction.
A Subquery is a normal T-SQL query that is nested inside another query. They are created using parentheses when you have a SELECT statement that serve as the basis for the either part of the data or the condition in another query.

Subqueries are generally used to fill one of couple of needs -

1. Break a query up into a series of a logical steps.
2. Provide a listing to be the target of a WHERE clause together with [IN|ESISTS|ANY|ALL].
3. TO provide a lookup driven by each individual record in a parent query.

Eg.
SELECT SelectList FROM SomeTable WHERE SomeColumn = (SELECT SingleColumn FROM SomeTable WHERE Condition that results in only one row returned)

When you use EXISTS, you do not really returned data - instead you return a simple TRUE/FALSE regarding the existence of data that meets the criteria established in the query that the EXISTS statement is operated against.

EXISTS simply test whether the inner query returns any row. If it does, then the outer query proceeds, if not, the outer query does not executes, and the entire SQL statement returns nothing.

The EXISTS condition is considered "to be met " if the subquery returns at least one row.

The syntax for EXISTS condition is -
SELECT columns FROM sometable WHERE EXISTS (subquery)

Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.
Recompilations might be the source of the slower stored procedure speed. To find out for sure, you need to do some performance investigation, such as looking at Showplans for each type of query versus calling the stored procedures and comparing query plan cache hits to cache misses. You can also try coding the object owner for referenced tables, views, and procedures inside your stored procedures, as the following example shows:

SELECT * FROM dbo.mytable

This technique helps you reuse plans and prevent cache misses.
The pros are that you can implement additional functionality and access data from DLLs from within SQL Server. If you need to do something that can be done only in C or C++, or if you have data that can be accessed only outside of SQL Server, you can still provide a link to it. The biggest con to extended stored procedures is that they run in the same process space as SQL Server. So an errant DLL could overwrite memory and cause SQL Server to crash or even corrupt data. The biggest safeguard against these problems is thorough testing of the procedure
PreviousDisplaying 7 of 15Next
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*