We know that sorting can be one of the most expensive things in an execution plan as shown below. However, we continue to do ORDER BYs repeatedly. Yes, I 100% agree that there is a need to sort a results set and that this should be done in the procedure for good reason, but my concern is having multiple sorts, erroneous sorts, and the sorts that can be done elsewhere. These are the ones that waste resources and can stifle performance.

Many of us writing procedures tend to write in code blocks. We write the SELECT, JOINS, FROMs and WHERES then immediately follow it up with and ORDER BY as a way to check result sets before moving onto the next block of code. I admit I do this almost every time. But what most developers do not do is remove unneeded ORDER BYs that are not required. This is very costly and can lead to suboptimal performance not only of your procedure but also for TEMPDB as this is where all sorting takes place.
Do you sort in your procedures that are used for data consumers like reports, ETL or an application? If you do, I ask, why are you sorting in the procedure and not in the consumer ? Many report end users will resort the data in Excel, or the report itself gives parameters for custom sorts or the data doesn’t need a sort at all. Why are you wasting resources on the SQL Server side just for it to be nullified? By removing unneeded sorts or performing the sort in the application tier you can have big performance gains. I would rather have a report, ETL process or application take the performance hit then a procedure.
 
Let’s look at one of the procedures that are available in AdventureWorks2016CPT3 called uspGetOrderTrackingBySalesOrderID. We will run it using the example execution in the code and then remove the ORDER BY, compile and rerun. We will be able to see clearly see the difference.
    USE [AdventureWorks2016CTP3] 
     
    GO 
     
    /****** Object:  StoredProcedure [dbo].[uspGetOrderTrackingBySalesOrderID]    Script Date: 1/28/2020 11:31:16 AM ******/ 
     
    SET ANSI_NULLS ON 
     
    GO 
    SET QUOTED_IDENTIFIER ON 
    GO 
     
    ALTER PROCEDURE [dbo].[uspGetOrderTrackingBySalesOrderID] 
     
       @SalesOrderID [int] NULL 
    AS 
    BEGIN 
    /* Example: 
     
          exec dbo.uspGetOrderTrackingBySalesOrderID 53498 
    */ 
       SET NOCOUNT ON; 
       SET STATISTICS IO, TIME ON 
     
       SELECT 
          ot.SalesOrderID, 
          ot.CarrierTrackingNumber, 
          ot.OrderTrackingID, 
          ot.TrackingEventID, 
          te.EventName, 
          ot.EventDetails, 
          ot.EventDateTime 
       FROM 
          Sales.OrderTracking ot, 
          Sales.TrackingEvent te 
       WHERE 
          ot.SalesOrderID = @SalesOrderID AND 
          ot.TrackingEventID = te.TrackingEventID 
       --ORDER BY 
       --   ot.SalesOrderID, 
       --   ot.TrackingEventID; 
    END; 

Plan with ORDER BY

Plan without ORDER BY and Query Store graph showing the difference in duration between the two. You can clearly see the performance improvement, and this was just one sort in a very simple procedure. Take a moment and consider the sorting that happens in your code. I’d ask that when writing store procedures, doing code reviews or performance tuning that you take a second to ask why the sorts are being done in the data tier and if they can be performed elsewhere. You can see get some performance gains not only in your code but in TEMPDB as well when sorting is reigned in.

Nonclustered HASH Index
This index is used to access the In-Memory version of the table, called a Hash. These are great for predicates that are singleton lookups and not ranges of values. These are optimized for seeks of equality values. For example, WHERE Name = ‘Joe’. Something to keep in mind when determining what to include in your indexes is this; if your query has two or more fields as your predicate and your index only consists of one of those fields, you will get a scan. It will not seek on that one field that was included.

Nonclustered Index
These are also used to access the In-Memory version of the table however, these are optimized for range values such as less than and equal to, inequality predicates and sorts orders. Examples are WHERE DATE between ‘20190101’ and ‘20191231’ and WHERE DATE <> ‘20191231’. These indexes do not require a bucket count or fixed memory amount. The memory consumed by these indexes are determined by the actual row counts and size of the indexed key columns which makes it a simpler to create.

SQL Comments statement can make your application easier for you to read and maintain. For example, we can include a comment in a statement that describes the purpose of the statement within your application with the exception of hints, comments within SQL. The statement does not affect the statement execution. Please refer to using hints on using this particular form of comment statement. 
 
A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

• Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines.
• End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
• Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

Some of the tools used to enter SQL have additional restrictions. For example, if you are using SQL*plus, by default you cannot have a blank line inside a multiline comment.
 
For more information, please refer to the documentation for the tool you use as an interface to the database. A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.
 
The comment statement indicates the user-provided text. Comments can be inserted on a separate line, nested at the end of a SQL command line, or within a SQL statement. The server does not evaluate the comment. 
SQL Comment uses the two hyphens (--) for single-line or nested comments. Comments inserted with -- are terminated by a new line, which is specified with a carriage return. Character (U+000A), line feed character (U+000D), or a combination of the two in SQL comments.
 
There is no maximum length for comments. The following table lists the keyboard shortcuts that you can use to comment or uncomment text.
 
Syntax
    -- text_of_comment    

Examples
The following example uses the -- commenting characters.
 
Syntax
    -- Choose the sample database.     
    USE sample;     
    GO     
    -- Choose all columns and all rows from the Address table.     
    SELECT *     
    FROM OrderDetails     
    ORDER BY OrderId  ASC; -- We do not have to specify ASC because      
    -- that is the default.     

SQL Single Line Comments
Single line comments start with --. Any text between -- and the end of the line will be ignored (will not be executed). The following example uses a single-line comment as an explanation.
 
Syntax 
   --Select all:   
    SELECT * FROM OrderDetails ; 

The following example uses a single-line comment to ignore the end of a line.
 
Syntax
    SELECT * FROM OrderDetails -- WHERE OrderName='Coffee';  

The following example uses a single-line comment to ignore a statement.
 
Syntax
    --SELECT * FROM OrderDetails;   
    SELECT * FROM OrderDetails ;  

SQL Multi-line Comments
SQL Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored. The following example uses a multi-line comment as an explanation.
 
Syntax 
    /*Select all the columns   
    of all the records   
    in the OrderDetails table:*/   
    SELECT * FROM OrderDetails;  

 The following example uses a multi-line comment to ignore many statements.
 
Syntax
    /*SELECT * FROM Customers;   
    SELECT * FROM Products;   
    SELECT * FROM Orders;   
    SELECT * FROM Categories;*/   
    SELECT * FROM OrderDetails;  

To ignore just a part of a statement, also use the /* */ comment. The following example uses a comment to ignore part of a line.
 
Syntax
    SELECT CustomerName, /*City,*/ Country FROM Customers;  

The following example uses a comment to ignore part of a statement
 
Syntax
    SELECT * FROM OrderDetails WHERE (OrderName LIKE 'L%'   
    OR OrderName LIKE 'R%' /*OR OrderName  LIKE 'S%'   
    OR OrderName LIKE 'T%'*/ OR OrderName LIKE 'W%')   
    AND OrderName ='Mango'   
    ORDER BY OrderrAddress;  

Summary
In this article, you learned how to use a SQL Comments statement with various options.

 In this blog, I will explain the SQL Transaction. A transaction is a logical unit of work. Each transaction begins with a specific task and ends with all tasks in a group successfully complete. If any tasks fail, it means that the transaction fails. All steps must be committed (transaction Success) or rolled back (transaction failure). A transaction begins to initiate the execution of the SQL statement. A transaction must be committed or rolled back. It is separate operations succeed is transaction succeed and committed to the database. If any separate operation fails means transaction failure and must be undone rolled back. The following are the properties of a transaction.
 
1. Atomicity
2. Consistency
3. Isolation
4. Durability

Transaction Process

• BEGIN TRANSACTION - Starts the transaction
• ROLLBACK - If an error occurred, reverts the existing transaction changes
• COMMIT - No error occurred, then it saves all transaction states
• SAVEPOINT - Rollback particular named transaction

Example
    BEGIN TRANSACTION T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    COMMIT; 
     
    BEGIN TRANSACTION T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    ROLLBACK; 
     
    SAVEPOINT T1 
    UPDATE TB_NAME SET FIRST_NAME ='R' WHERE ID=1 
    ROLLBACK T1; 

HostForLIFE.eu SQL Server Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

A sequence object in MS-SQL Server is designated to define and get only integer values, such as int, bigint, smallint, tinyint. However, if we want to generate sequence value(s) that are alpha-numeric, then we can define a Stored Procedure that can combine to generate an alpha-numeric combination of sequence values. This blog gives a complete idea of how this can be implemented.

CREATE DATABASE sampdb1

use sampdb1

--First Create a sequence object s3 which will generate numbers from 1 to 5 and cycles

CREATE SEQUENCE s3

START WITH 1

INCREMENT BY 1

MINVALUE 1

MAXVALUE 5

CYCLE

--Ensuring that the sequence object is defined properly and generating defined values.

SELECT NEXT VALUE FOR s3

--Defining a Stored Procedure that generates a Custom-sequence of values. This stored procedure is going to just get the generated alpha-numeric combination of the sequence.

CREATE PROCEDURE genSeqVals AS

Begin

DECLARE @n as int, @msg varchar(4)

SELECT @n=NEXT VALUE FOR s3

PRINT 'A'+cast(@n as varchar(2))

End

--Execute the following code and check.

Exec genSeqVals

--Defining another Stored Procedure that generates a Custom-sequence of values. This stored procedure returns the alpha-numeric combination of the sequence value that is generated using an OUTPUT parameter.

CREATE PROCEDURE getSeqVals(@res varchar(4) OUTPUT) AS

Begin

DECLARE @n as int, @msg varchar(4)

SELECT @n=NEXT VALUE FOR s3

SET @res = 'A'+cast(@n as varchar(2))

End

--Execute the following code and check.

DECLARE @seqnum varchar(4)

EXECUTE getSeqVals @seqnum output

print @seqnum

This is one way in which sequence objects can be custom-implemented as per the requirement. I hope that the above lines of code have given you deeper insight into T-SQL for custom implementation. Happy coding!

In this article, I am going to tell about the dropping of indexes in View in SQL Server. Views are virtual tables in which data from one or more tables gives the result set as our SQL table does with rows and columns. A View doesn’t store the data permanently in the database and at the time of execution, only its result set gets determined. When a View contains a large amount of rows and has complex logic in it then we can create an index on a View to improve the query performance. A View consists of a Unique Clustered Index and it is stored in the database as the clustered index does. Now, let’s run a few scenarios to check when Clustered index which is created on a View gets dropped automatically.

First, I will create a table on which I will run those scenarios.
CREATE TABLE[dbo]. [Customer] 
    ( 
        [CustomerID][int] IDENTITY(1, 1) NOT NULL, [CustomerName][varchar](50) NOT NULL, [CEO][varchar](40) NULL, [Phone][varchar](20) NOT NULL PRIMARY KEY CLUSTERED( 
            [CustomerID] ASC)) 
GO 

Now, I will create a View which will use this Customer table.
-- Create view 
Create VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName, CEO 
  from dbo.Customer 
  GO 

Here in the definition of View, I have used WITH SCHEMABINDING which is necessary for creating an index on a View. This option simulates that we cannot delete any of the base table used in the View and in order to make any changes, first, we need to drop or alter the View.

Also, all the table references in a View should have two part naming convention (schemaname.tablename) as we have in vw_Customer view (dbo.Customer).

Now, I will create an index on our View.

Scenario 1
Create index IX_CustomerID  
 ON vw_customer (CustomerID); 
 GO 

Error returned: Cannot create index on view 'vw_customer'. It does not have a unique clustered index. On Views, the first index must be a unique clustered index, so this will throw the error.

So, the first index on View should be UNIQUE CLUSTERD INDEX, else it will throw an error.

Scenario 2
Create unique clustered index IX_CustomerID  
 ON vw_customer (CustomerID) 
 GO 

Now, our Indexed View is created having a clustered index on it. Now, I want to alter my View and add one more column GETDATE() as CurrentDate  in the View definition and alter the View.

Now, alter the View after Scenario 2.

Scenario 3
ALTER  VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName , GETDATE() as CurrentDate 
  from dbo.Customer 
GO 

Now, as I have altered the View, I want to create another index on column CustomerName which will be a NonClustered index.

Scenario 4
Create index IX_CustomerName  
 ON vw_customer (CustomerName); 
GO 

Again, I get an error: Cannot create index on view 'vw_customer'. It does not have a unique clustered index.

As we have already created Unique Clustered Index on View, still it gives an error. The interesting thing is that after updating the View, the index that was created in Scenario 2 is dropped and the code will throw the same error as in Scenario 1.

As mentioned here,
"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
Hope you will like this post.

In this blog, I will explain SQL Constraints. They are used to specify rules for the data in a table. The following SQL Constraints are commonly used (Primary key, foreign key, unique key, Composite key, null, Not Null, check).

Primary Key
The Primary key must contain be a unique value. It is the primary column and can’t have the null value. It uniquely identifies each row in a database table.
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype PRIMARY KEY(Column1)) 

Foreign Key
1. Foreign key always refers to the primary key column.
2. Foreign key accepted to duplicate value.
 
Syntax
    CREATE TABLE TB_NAME(column1 datatype FOREIGN KEY REFERENCES(primary_key column_name),cloumn2 datatype) 

Unique Key
 
The unique key is the same as the primary key, but one row is accepted for the null value.
 
Syntax
    CREATE TABLE TB_NAME(Column_name datatatype UNIQUE,column_name2 datatype) 

Composite key
A composite key is a set of multiple keys that, together, uniquely identifies each record
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype PRIMARY KEY(Column1,column2)) 

Not Null
Forces a column not to accept NULL values
 
Syntax
    CREATE TABLE TB_NAME(Column1 datatype,column2 datatype NOT NULL) 

Check
The CHECK constraint is used to limit the value range that can be placed in a column.
 
Syntax
    CREATE TABLE TB_NAME(MARKS INT CHECK(MARKS<=100))

Version control helps you to track the changes of a code repository. But, it doesn't much help to track database changes. General practice is to create a single script file that includes all the schema and update that file every time you make any changes into the database and commit it to version control. However, this is a bit longer a way to track the changes. Another way is to use popular tools like Red Gate Change Automation. But there is a native way around to handle tracking! simply put, DDL trigger can be used to track the DB changes.

Track Stored Procedure changes using DDL trigger

Here we'll see how to track stored procedure changes using DDL trigger.

Create your audit database and create a table.  
USE AuditDB;  
GO  
 
CREATE TABLE dbo.ProcedureChanges  
(  
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  
    EventType    NVARCHAR(100),  
    EventDDL     NVARCHAR(MAX),  
    DatabaseName NVARCHAR(255),  
    SchemaName   NVARCHAR(255),  
    ObjectName   NVARCHAR(255),  
    HostName     NVARCHAR(255),  
    IPAddress    VARCHAR(32),  
    ProgramName  NVARCHAR(255),  
    LoginName    NVARCHAR(255)  
);   
Add data of all existing stored procedures from your actual database (Product DB in this example)
USE ProductDB;  
GO  
 
INSERT AuditDB.dbo.ProcedureChanges  
(  
    EventType,  
    EventDDL,  
    DatabaseName,  
    SchemaName,  
    ObjectName  
)  
SELECT  
    N'Initial control',  
    OBJECT_DEFINITION([object_id]),  
    DB_NAME(),  
    OBJECT_SCHEMA_NAME([object_id]),  
    OBJECT_NAME([object_id])  
FROM  
    sys.procedures;  
Create DDL trigger to capture changes
USE ProductDB;  
GO  
 
CREATE TRIGGER CaptureStoredProcedureChanges  
    ON DATABASE  
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,   
    ALTER_SCHEMA, RENAME  
AS  
BEGIN  
    SET NOCOUNT ON;  
 
    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);  
 
    SELECT @ip = client_net_address  
        FROM sys.dm_exec_connections  
        WHERE session_id = @@SPID;  
 
    INSERT AuditDB.dbo.ProcedureChanges  
    (  
        EventType,  
        EventDDL,  
        SchemaName,  
        ObjectName,  
        DatabaseName,  
        HostName,  
        IPAddress,  
        ProgramName,  
        LoginName  
    )  
    SELECT  
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),   
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),  
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),   
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),  
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();  
END  
GO  

Modify any stored procedure and check the ProcedureChanges table from AuditDB.

The method might have some limitations, but this is the simplest way to tracking changes of small size databases.

There was a situation where I had to implement fast/effective Paging, Sorting and Filtering with Stored Procedure in MS SQL Server. There are a number of articles and blogs where you can find about to do Paging, Sorting and Filtering with Stored Procedure in MS SQL Server. Hence, I started and done some research on this to find out the best solution. I found it in “Pagination with OFFSET / FETCH : A better way”

Using OFFSET / FETCH into the CTE I have created a stored procedure that was at least faster twice in return time as the alternatives found on the internet.

Here, I have kept all the implementation details along with a complete example.

Prerequisites
You should have a basic knowledge of MS SQL Stored Procedure, queries and CTE.

How to do effective Paging, Sorting and Filtering with Stored Procedure

To implement and execute this let us follow three steps:

• Create table.
• Insert data into the table.
• Create stored procedure.

Create table
CREATE TABLE Employee 
( 
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
    Name varchar(25) NOT NULL, 
    City varchar(25) NOT NULL 
) 

Insert data into the table
declare @i int = 1 
declare @total int = 500000 
 
while @i <= @total 
begin 
    insert into Employee2 (Name, City) values (RIGHT('000000'+convert(varchar, @i),6), convert(varchar, @i%4)) 
    set @i += 1; 
end

For testing purpose, I have added 5 lakh records into the Employee table.

Create stored procedure
--GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc' 
--GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc' 
--GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc' 
CREATE PROCEDURE [dbo].[GetAllEmployeesWay4] 
( 
    @SearchColumn NVARCHAR(50) = NULL, 
    @SearchValue NVARCHAR(50) = NULL, 
    @PageNo INT = 1, 
    @PageSize INT = 10, 
    @SortColumn NVARCHAR(20) = 'Name', 
    @SortOrder NVARCHAR(20) = 'ASC' 
) 
AS BEGIN 
    SET NOCOUNT ON; 
 
    SET @SearchColumn = LTRIM(RTRIM(@SearchColumn)) 
    SET @SearchValue = LTRIM(RTRIM(@SearchValue)) 
 
    ; WITH CTE_Results AS  
    ( 
        SELECT Id, Name, City from Employee 
 
        WHERE @SearchColumn= '' OR  (  
                CASE @SearchColumn  
                    WHEN 'Name' THEN Name  
                    WHEN 'City' THEN City 
                END 
            ) LIKE '%' + @SearchValue + '%' 
 
            ORDER BY 
            CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC') 
                        THEN Name 
            END ASC, 
            CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC') 
                        THEN Name 
            END DESC, 
            CASE WHEN (@SortColumn = 'City' AND @SortOrder='ASC') 
                        THEN City 
            END ASC, 
            CASE WHEN (@SortColumn = 'City' AND @SortOrder='DESC') 
                        THEN City 
            END DESC  
            OFFSET @PageSize * (@PageNo - 1) ROWS 
            FETCH NEXT @PageSize ROWS ONLY 
    ), 
    CTE_TotalRows AS  
    ( 
        select count(ID) as TotalRows from Employee 
        WHERE @SearchColumn= '' OR  (  
                CASE @SearchColumn  
                    WHEN 'Name' THEN Name  
                    WHEN 'City' THEN City 
                END 
            ) LIKE '%' + @SearchValue + '%' 
    ) 
    Select TotalRows, t.Id, t.Name, t.City from dbo.Employee as t, CTE_TotalRows  
    WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.ID = t.ID) 
 
    OPTION (RECOMPILE) 
END

Execute stored procedure
Execute the above stored procedure with different parameters and you can get result accordingly:
GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc'
GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc'
GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc'

Complete example

For your reference, I have kept complete example in a single folder and uploaded that with this article and it contains below script files:
Step1_Create_Table
Step2_Insert_Data_into_Table
Step3_Create_Stored_Procedure

Summary
Now, I believe you will be able to do Effective Paging, Sorting and Filtering with Stored Procedure in MS SQL Server using.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Version control helps you to track the changes of a code repository. But, it doesn't much help to track database changes. General practice is to create a single script file that includes all the schema and update that file every time you make any changes into the database and commit it to version control.

However, this is a bit longer a way to track the changes. Another way is to use popular tools like Red Gate Change Automation. But there is a native way around to handle tracking! simply put, DDL trigger can be used to track the DB changes.

Track Stored Procedure changes using DDL trigger

Here we'll see how to track stored procedure changes using DDL trigger.

Create your audit database and create a table. 
USE AuditDB; 
GO 
 
CREATE TABLE dbo.ProcedureChanges 
( 
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    EventType    NVARCHAR(100), 
    EventDDL     NVARCHAR(MAX), 
    DatabaseName NVARCHAR(255), 
    SchemaName   NVARCHAR(255), 
    ObjectName   NVARCHAR(255), 
    HostName     NVARCHAR(255), 
    IPAddress    VARCHAR(32), 
    ProgramName  NVARCHAR(255), 
    LoginName    NVARCHAR(255) 
);  
Add data of all existing stored procedures from your actual database (Product DB in this example)
USE ProductDB; 
GO 
 
INSERT AuditDB.dbo.ProcedureChanges 
( 
    EventType, 
    EventDDL, 
    DatabaseName, 
    SchemaName, 
    ObjectName 
) 
SELECT 
    N'Initial control', 
    OBJECT_DEFINITION([object_id]), 
    DB_NAME(), 
    OBJECT_SCHEMA_NAME([object_id]), 
    OBJECT_NAME([object_id]) 
FROM 
    sys.procedures; 
Create DDL trigger to capture changes
USE ProductDB; 
GO 
 
CREATE TRIGGER CaptureStoredProcedureChanges 
    ON DATABASE 
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,  
    ALTER_SCHEMA, RENAME 
AS 
BEGIN 
    SET NOCOUNT ON; 
 
    DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32); 
 
    SELECT @ip = client_net_address 
        FROM sys.dm_exec_connections 
        WHERE session_id = @@SPID; 
 
    INSERT AuditDB.dbo.ProcedureChanges 
    ( 
        EventType, 
        EventDDL, 
        SchemaName, 
        ObjectName, 
        DatabaseName, 
        HostName, 
        IPAddress, 
        ProgramName, 
        LoginName 
    ) 
    SELECT 
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),  
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'), 
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),  
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'), 
        DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME(); 
END 
GO  

Modify any stored procedure and check the ProcedureChanges table from AuditDB.

The method might have some limitations, but this is the simplest way to tracking changes of small size databases.