Stored procedure concept

sp_lock

sp_who 155

kill 155

sp_find

shift +f9

important database concepts

Stuff is a Sql server function.It Deletes a specified
length of characters and inserts another set of characters
at a specified starting point.



SELECT STUFF('abcdef', 2, 3, 'ijklmn')
::will return te following result::

aijklmnef

2)materialized

A materialized view is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data. Materialized views, which store data based on remote tables are also, know as snapshots.A materialized view can query tables, views, and other materialized views. Collectively these are called master tables (a replication term) or detail tables (a data warehouse term).

Above answer was rated as good by the following members:
jabir.mkk, kperumal75

Cursors

Source is picked from internet
WHILE (Transact-SQL)

Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

Topic link iconTransact-SQL Syntax Conventions
Copy

WHILE Boolean_expression
{ sql_statement | statement_block | BREAK | CONTINUE }


Arguments

Boolean_expression

Is an expression that returns TRUE or FALSE. If the Boolean expression contains a SELECT statement, the SELECT statement must be enclosed in parentheses.
{sql_statement | statement_block}

Is any Transact-SQL statement or statement grouping as defined with a statement block. To define a statement block, use the control-of-flow keywords BEGIN and END.
BREAK

Causes an exit from the innermost WHILE loop. Any statements that appear after the END keyword, marking the end of the loop, are executed.
CONTINUE

Causes the WHILE loop to restart, ignoring any statements after the CONTINUE keyword.

Remarks

If two or more WHILE loops are nested, the inner BREAK exits to the next outermost loop. All the statements after the end of the inner loop run first, and then the next outermost loop restarts.
Examples
A. Using BREAK and CONTINUE with nested IF...ELSE and WHILE

In the following example, if the average list price of a product is less than $300, the WHILE loop doubles the prices and then selects the maximum price. If the maximum price is less than or equal to $500, the WHILE loop restarts and doubles the prices again. This loop continues doubling the prices until the maximum price is greater than $500, and then exits the WHILE loop and prints a message.
Copy

USE AdventureWorks2008R2;
GO
WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
UPDATE Production.Product
SET ListPrice = ListPrice * 2
SELECT MAX(ListPrice) FROM Production.Product
IF (SELECT MAX(ListPrice) FROM Production.Product) > $500
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';



B. Using WHILE in a cursor

The following example uses @@FETCH_STATUS to control cursor activities in a WHILE loop.
Copy

DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title
FROM AdventureWorks2008R2.HumanResources.Employee
WHERE JobTitle = 'Marketing Specialist';
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM Employee_Cursor;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

sql server interview queries

1)delete duplicates
By using temporary table, But it will delete exactly
duplicate rows NOT RECOMMANDABLE FOR HUGE TABLE. Query will
be.

SELECT DISTINCT * INTO #A FROM TABLE1

TRUNCATE TABLE TABLE1

INSERT INTO TABLE1
SELECT * FROM #A

2)second highest salary
select max(salary) from salary_table where salary in(select
top 2 salary from salary_table order by salary desc)

3)Receed identity

DBCC CHECKIDENT (orders, NORESEED)

To set the value of the next ID to be 1000, I can use this command:

DBCC CHECKIDENT (orders, RESEED, 999) 2 comments 8/1/10 by cooldude Delete

4) write query for fourth maximum salary from employee table using dense rank
select
distinct salary
from
(
select
DENSE_RANK() over(order by salary desc) as rnk,
salary
from
employee
) a
where
rnk = 4


sql interview question
http://allinterview.com/showanswers/131076.html