Home
MS SQL
Interview FAQs

FAQs of level

Procedures that are not part of a package are known as stand-alone because they independently defined. A good example of a stand-alone procedure is one written in a SQL*Forms application. These types of procedures are not available for reference from other Oracle tools. Another limitation of stand-alone procedures is that they are compiled at run time, which slows execution.
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.
SET ROWCOUNT N
select * from mytable
SET ROWCOUNT 0

To turn this option off (so that all rows are returned), specify SET ROWCOUNT 0.
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)
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.
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.
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.
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.
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.
SELECT table_name,index_name FROM user_constraints
PreviousDisplaying 8 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

*
*
*
*