Answer / praveen

Static queries are the queries we normally use like SELECT *
FROM EMPLOYEE. where as dynamic queries can be built and
executed dynamically. sp_executesql system stored procedure
is used to execute dynamic sql statements.

Eg: sp_executesql N'select * from employee'

dynamic sql queries will take more time to execute when
compared to static queries

Answer / manoj

Static Queries are permanent and cannot be changed during run-time, like: "SELECT * FROM Employees"

Dynamic Queries can be changed during run-time as they are created by using variables and these variables contain parts of SQL Query, like:

DECLARE @SQL VARCHAR(MAX)
DECLARE @WHENSQL VARCHAR(2000)
DECLARE @SEARCHSQL VARCHAR(500)

SELECT @WHENSQL = 'EmployeeID'
SELECT @SEARCHSQL = '100'

SELECT @SQL = 'SELECT * FROM Employees WHERE ' + @WHENSQL + ' = ' + @SEARCHSQL

EXEC (@SQL)
-- or
EXEC sp_executesql @SQL


For more interview Questions on SQL Server: http://sqlwithmanoj.wordpress.com/interview-questions/

How many types of keys are there?

0 Answers  

---

What types of integrity are enforced by a foreign-key constraint

1 Answers  

---

Distinguish between commit and rollback?

0 Answers  

---

How to get a list of columns using the "sp_help" stored procedure in ms sql server?

0 Answers  

---

Why does sql studio use a single registered database repository? : sql server management studio

0 Answers  

---

How to list all tables in the database using odbc_tables()?

0 Answers  

---

What is nonclustered index on computed columns?

0 Answers  

---

What is the difference between in and exists. Ex: select * from emp where empno in(....) and select * from emp where empno exists(....) What is the difference between a Join and Union and Union and UnionAll.

5 Answers   ABC, Accenture,

---

What happens if null values are involved in datetime operations?

0 Answers  

---

Explain the dirty pages?

0 Answers  

---

What is the difference between delete and truncate statements?

0 Answers  

---

What method is used by the Command classes to execute SQL statements that return single values?

0 Answers   B-Ways TecnoSoft,

---