Developed by Emergent Software's Kevin Martin, sp_CRUDGen is a free open-source SQL Server stored procedure that generates stored procedures based on your tables and metadata like foreign keys and data types. The generated stored procedure code utilizes the SQL Server community best practices.

sp_CRUDGen is being used to generate dynamic T-SQL searches (kitchen sync, optional parameters) for a receivables management web application.

Search Accounts User InterfaceSearch Accounts Interface Screenshot

The Data Source

In this project we created a view named 'dbo.AccountLookup' that has 102 columns from 14 tables. Each of the 102 columns in the view are turned into stored procedure parameters you can see in the 'Field Type' drop down in the 'Search Accounts User Interface Screenshot' above. Currently there are close to 17 million rows in the 14 tables that the generated dynamic T-SQL queries. The expectations are to scale to 100's of millions of rows without breaking a sweat, assuming proper indexing is maintained of course. ;-)

Search Features

Application users can add as many or few search conditions as they wish to dig up the data they need for performing they daily data duties.

The 'Logical Operator' drop down supports all the normal T-SQL WHERE clause predicates like [Not] Equals, [Greater | Less] Than, Like, Between, … Because we are utilizing system-versioned temporal table the search stored procedure supports the sub-clauses of {AS OF | FROM | BETWEEN | CONTAINED IN | ALL}.

Multicolumn sorting and pagination via scrolling are two other features in use from the generated dynamic stored procedure sp_CRUDGen created.

T-SQL Code

The generated stored procedure is 9,281 lines of T-SQL code. A few tweaks are made like removing the auto-generated tag, adding two quick search parameters and T-SQL code, changing up the default order by column, and modifying the CTE to account for returning a smaller subset of columns than the 102 columns from 14 tables. With these modifications there are now 9,221 lines of code. We have a wiki setup for when the stored procedure needs to be regenerated and what customizations need to be added back afterwards.

How Does it Perform?

Fast! On a physical SQL Server with 16 Cores / 32 GB or memory / NVMe disk, the runtime is sub second 661-708 ms. There is a 5 second data warm-up (memory caching). This is searching for 10 of the most common last names in the US. The cost is so low on a physical SQL Server, the execution plan does not even need to go parallel. The web app and database are hosted in Azure, so what do the metrics look like there? Still fast when you are using Azure Premium SQL Database or enough DTUs. With premium you get some fast local SSD vs the slower remote SSD you get with Azure Standard SQL Database. With premium you get 1 second execution time and the execution cost is high enough to use 8 cores if you provisioned 800 DTUs or 8 vCores. There is the same 5 seconds data warm-up time. Your runtimes increase the lower you go on the pricing tier due to available memory and CPU core count. I playfully call 50 Standard Azure DTUs the Game Boy emulator. ;-)

  • SQL Server with 16 Core/32GB RAM/NVMe | 661-708 ms. | after 5 seconds data warm-up
  • 2,000 Premium DTUs | 1-1 seconds | after 5 seconds of warm-up | only 8 cores are used, so effectively 800 DTUs
  • 800 Standard DTUs | 1-2 seconds | after 47 seconds of warm-up
  • 400 Standard DTUs | 2-3 seconds | after 1 minute 13 seconds of warm-up
  • 300 Standard DTUs | 3-5 seconds | after 1 minute 32 seconds of warm-up
  • 200 Standard DTUs | 4-8 seconds | after 1 minute 36 seconds of warm-up
  • 100 Standard DTUs | 9-10 seconds | after 5 minutes 57 seconds of warm-up
  • 50 Standard DTUs | 17-23 seconds | after 9 minutes - 19 minutes data warmish-up

Additional Information & Links

You can use sp_CRUDGen to generate 11 different stored procedures from basic your Create, Read, Update, Delete, Upsert stored procedures to extremely advanced safe dynamic search stored procedures otherwise known as optional parameters, kitchen sink, Swiss army knife, catch-all queries.