SQL Server Extended and Remote stored procedure

Define temporary and extended stored procedure.

Temporary Stored Procedure

• Temporary Stored Procedure is stored in TempDB database.
• It is volatile and is deleted once connection gets terminated or server is restarted.
• Temporary stored procedures are prefixed with pound sign #.
• One pound sign means that it is temporary within the session.
• Two pound signs ## means it is a global temporary procedure.
• Global temporary procedure can be called by any connection to the SQL server during its lifetime.

Extended Stored Procedure

It is basically created to expand features of stored procedure.
It uses external program and compiles as DLL. Mostly xp_ prefix is used as naming convention for extended stored procedure.

Define stored procedure.

Answer
Stored procedure is a set of SQL commands that have been complied and stored on the database sever. They can be used in the code as and when required since hey stored. They need not be complied over and over again. They can be invoked by CALL procedure (..) or EXECUTE procedure(..)

What are the purposes and advantages stored procedure?

Answer
Purposes and advantages of stored procedures:

• Manage, control and validate data
• It can also be used for access mechanisms
• Large queries can be avoided
• Reduces network traffic since they need not be recompiled
• Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
• Permissions can be granted for stored procedures. Hence, increases security.

Determine when to use stored procedure to complete SQL Server tasks.

Answer

• If a large piece of code needs to be performed repeatedly, stored procedures are ideal
• When hundreds of lines of SQL code need to be sent; it is better to use stored procedure through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
• When security is required.

The answers to following questions will be made available soon. Keep visiting.

Explain the categories of stored procedure i.e. System stored procedure, local stored procedure, temporary stored procedure, extended stored procedure, remote stored procedure.
Explain nested stored procedure. Syntax and an example for create nested stored procedure.
What security features are available for stored procedure?
In what three ways is the RETURN statement used in a stored procedure.

NEXT>>

SQL Server Interview questions part 2 includes following questions with answers

Difference between DELETE and TRUNCATE commands in SQL Server. | What are constraints in SQL Server? | What is an index? | What is RAID? | Ways to troubleshoot performance problems in SQL Server. | Steps to secure an SQL Server. | What is a deadlock and what is a live lock? | What is blocking and how would you troubleshoot it? | Ways of moving data between servers and databases in SQL Server? | Explian different types of BACKUPs avaialabe in SQL Server? | What is database replicaion?

SQL Server Interview questions part 3 includes following questions with answers

What are cursors in SQL Server? | What is a join and explain different types of joins. | What is an extended stored procedure in SQL Server? | What are triggers in SQL Server? | What is a self join in SQL Server? | What is the difference between UNION ALL Statement and UNION? | Write some disadvantage of Cursor? | What is Log Shipping in SQL Server?

SQL Server Interview questions part 4 includes following questions with answers

What are the different types of Locks in SQL Server? | What is Extent and types of Extent? | What is the use of DBCC commands defined in SQL Server? | Define COLLATION in SQL Server? | Where is users names and passwords stored in SQL Server? | What is BCP? | How can we move data along with schema of the server object? | Define sub-query in SQL Server? | Define sp_grantlogin, sp_denylogin and sp_revokelogin. | Write SQL query to retrieve all tables of the database.

Question: What is transact-SQL? Describe its types?

SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML....

Question: Define Identity and uniqueidentifier property of Column.

Column with identity property contains unique system generated value in the table. Column with identity property is similar to AutoNumber field in MS Access....