SQL Server Knowledge points

In the company every day write SQL write, stored procedures, but the Company tool template to create the statements are written, only responsible for writing the logic inside, over time, the creation of statements will not be written. There are some knowledge points are very vague, ordinary use of the time are not clear, the confusion is in use. Here, tidy up a bit. Consolidate your review.

A. Stored procedure.

1. The stored procedure is similar to the inside of a programming language function, method. The SQL statements are encapsulated inside for easy invocation.

2. First look at creating the parameterless stored procedure syntax.

CREATE PROCEDURE myprotest  as    SELECT *  from Dbo.sauser GO

To create a stored procedure with parameters

CREATE PROCEDURE myprotest (    @IDINT=0)with/*  { RECOMPILE | Encryption | RECOMPILE, encryption}]* * as    SELECT* from Dbo.sauserGO

This place is followed by the following with the value to explain the next

A.recompile indicates that SQL Server does not cache the schedule for this procedure, which is recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and you do not want to overwrite the execution plan that is cached in memory.

B.encryption represents an entry in SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Use encryption to prevent the process from being published as part of SQL Server replication. Description during the upgrade process, SQL Server re-creates the encryption process by leveraging the cryptographic annotations stored in syscomments. To be blunt is to encrypt stored procedures. This I often use, the above with evil is not used frequently, here to understand.

3. Execute the Stored procedure

EXEC @ID = 1

4. Delete a stored procedure

DROP PROC dbo. Myprotest

Delete the syntax simply say it. Whether you delete a table, a stored procedure, a view, or a function. The delete syntax is drop keyword + type + name.

Two Function

SQL Server Knowledge points