SQL Basic Query

I decided to write an article on these queries, because I think that such type of query we required many times in daily programming work and basic knowledge of these query can save our time to retrieve the specific information about the database, table or any other object.

Let’s start today's session:

List of Stored procedure modified in last N days:

This script can be used to retrieve the name and time of modification about stored procedure that are modified within a specific time period. The following script provide the name and time of stored procedure that are modified within last 10 days.

  1. SELECT name,modify_date  
  2. FROM sys.objects  
  3. WHERE type='P'  
  4. ANDDATEDIFF(D,modify_date,GETDATE())< 10  
Output:

output

You can also retrieve information about any other objects of database by defining there types. I mean if you want to retrieve the name of all Triggers that are modified with in a specific time period then use (Type=”TR”) instead of(Type=”P”). The following table provide information about the types of objects.

 

Object Name Object Symbol
Internal Table IT
Stored Procedure P
System Base Table S
Trigger TR
Table Types TT
View V

List of Stored procedure created in last N days:

The following script can be used to provide the name and time of all stored procedure that are created in last N days.

  1. SELECT name,sys.objects.create_date  
  2. FROM sys.objects  
  3. WHERE type='P'  
  4. ANDDATEDIFF(D,sys.objects.create_date,GETDATE())< 40  
Output:

output

Retrieve Names of Stored Procedure Related to Specific Table:

The following script can be used to retrieve the name of stored procedure that are related to a specific table.
  1. SELECT DISTINCT SYS2.Name  
  2. FROM sys.syscommentsASSYS  
  3. INNERJOIN sys.sysobjectsASSYS2 ONSYS.id=SYS2.id  
  4. WHERE SYS.TEXTLIKE'%Basic_Detail%'  
  5. --Here BAsic_Detail is a Table Name  
Output:

output

Recompile A Specific Stored Procedure

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. We should prefer to recompile all the stored procedure on the table, which has faced mass insert or update. When a procedure is compiled for the first time or recompiled, then the procedure’s query plan is optimized for the current state of the database and table. If a database or table’s data or structure are changed recompiling a procedure updates and optimizes the procedure’s query plan for those changes. This can improve the procedure’s processing performance.

There are three ways to recompile the stored procedure.

 

  1. Using sp_recompile system stored procedure: Below script can be used to recompile a stored procedure .

    Example:
    1. --Recompile Stored Procedure  
    2. EXEC sp_recompile'Searching_Result_AllKeyword';  
    3. GO  
    Output:

    output

  2. Recompile Query Hint:

    This method is used when the procedure is created and the hint is included in Transact-SQL statements in the procedure. You must apply only to statements in the stored procedure where you’d like to implement the recompile

    Example:
    1. GO  
    2. ALTER proc [dbo].[Retrive_Service]  
    3. as begin  
    4. select*fromTblMaster_Service_Type_InfoOPTION (RECOMPILE)  
    5. end  
  3. With Recompile Option:

    This option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created. The option most folks discover first is to use ‘WITH RECOMPILE’ in the header of the stored procedure.

    Example:
    1. CREATE proc [dbo].[Retrive_Service]  
    2. WITH RECOMPILE  
    3. as begin  
    4. select*from TblMaster_Service_Type_Info  
    5. end  

Recompile All Stored Procedure of a Table:

Below script can be used to recompile all stored procedure of a specific table.

  1. --Recompile All Stored Procedure of specifc table  
  2. EXEC sp_recompileTblCompany_Basic_Detail;  
  3. GO  
Output:

output

Execute Same Query Multiple Times

Using GO command we can execute same query or statement multiple times.

Example:
  1. SELECTGETDATE()ASDateTime  
  2. GO 7  
Output:

output

Select First Row from Top and Bottom of Table:

Below query explain that how to retrieve first from the top and bottom of table.

We will execute the query for below table.

table

Query:
  1. SELECT * FROM dbo.EmployeeAS EMP  
  2. WHERE EMP.Emp_IdIN(SELECTTOP 1 EMP2.Emp_Id FROMdbo.EmployeeAS EMP2 ORDERBY EMP2.Emp_Id)  
  3. UNIONALL  
  4. SELECT*FROMdbo.EmployeeAS EMP  
  5. WHERE EMP.Emp_IdIN(SELECTTOP 1 EMP3.Emp_Id FROMdbo.EmployeeAS EMP3 ORDERBY EMP3.Emp_Id DESC)  
Output:

output

Single Stored Procedure For DML Queries

We generally use the stored procedure for retrieve, insert, and delete data from table. We create separate stored procedure for Insert, Delete, and Update operation. But suppose we have 1000 of tables, if we create separate stored procedure for each DML query (Insert, Update, Delete), then it will take a large space in Context menu and it will be very difficult to search any specific stored procedure.

To solve this problem we can create a common stored procedure for DML query that contain one extra parameter for recognize the type of query. This approach will save our space and time.

Now we will take an example how can we achieve this approach.

I have a table Employee. Now I will create a stored procedure for this table that contains all queries of DML.

Stored Procedure
  1. CREATE PROC USP_DML_EMPLOYEE(  
  2. @EMP_ID INT,  
  3. @EMP_NAME NVARCHAR(MAX),  
  4. @EMP_SALARY INT,  
  5. @EMP_CITY NVARCHAR(MAX),  
  6. @QUERY_TYPE INT  
  7. )AS BEGIN IF @QUERY_TYPE = 1 BEGININSERTINTO Employee(  
  8. Emp_ID,Emp_Name,Emp_Salary,Emp_City  
  9. )  
  10. VALUES  
  11. (  
  12. @EMP_ID, @EMP_NAME, @EMP_SALARY, @EMP_CITY   
  13. )END ELSEIF @QUERY_TYPE = 2 BEGIN  
  14. UPDATE  
  15. Employee   
  16. SET  
  17. Emp_Name= @EMP_NAME,  
  18. Emp_Salary= @EMP_SALARY,  
  19. @EMP_CITY = @EMP_CITY   
  20. WHERE  
  21. Emp_ID= @EMP_ID ENDELSEIF @QUERY_TYPE = 3   
  22. DELETE FROM  
  23. Employee   
  24. WHERE  
  25. Emp_ID= @EMP_ID END  
In this procedure we pass one extra parameter (i.e. @QUERY_TYPE.) This parameter is used for recognizing the type of DML query. If we want to perform insert operation then we pass 1 for Insert , 2 for Update and 3 for Delete.

Today we read some basic queries, I hope you enjoyed today’s session. I request that if you have a better solution for any of the above query then please share that, it will be very helpful.

 

Up Next
    Ebook Download
    View all
    Learn
    View all