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/

i want to create procedure for create table in sql server 2005 for example create procedure create_table @table varchar(20) as create @table( id int, name char(20) ) but it will get error what is solution?

5 Answers   Aptech,

---

Is trigger fired implicitely?

2 Answers  

---

What is the syntax for encrypting a column in SQL Server?

0 Answers   MindCracker,

---

How display code or Text of Stored Procedure using Sql query in Sql Server ?

0 Answers   Wipro,

---

What is difference between delete & truncate commands?

0 Answers  

---

How to generate random numbers with the rand() function in ms sql server?

0 Answers  

---

Differentiate between truncate vs delete in mssql?

0 Answers  

---

Explain indexed views?

0 Answers  

---

1. Tell me your daily activities 2. If sql server installation fails at time installation what will do 3. Where does the sql server installation log details are stored 4. After the installation what will you do for memory configuration 5. What is the difference between SQL max maximum memory and AWE memory 6. How will you configure AWE memory 7. How will setup an email alert for the backup job 8. After the SQL installation what are the jobs will you configure 9. What does –g mean in the sql startup parameter 10. What is the difference between Bulked log and Full recovery model 11. What is the difference between mirroring and log shipping 12. What are the steps to be followed before in-place up gradation 13. After installing the patch the sql server does not start and application team tells to rollback the changes .In this scenario what will you do

0 Answers   CSC, IBM,

---

Difference between 2NF &3NF ?

0 Answers   Cap Gemini,

---

What are user defined datatypes and when you should go for them?

1 Answers  

---

What is ms sql server service broker?

0 Answers  

---