The backstage architecture
Many recent sessions emphasize the role of Query Store. Today, I attended another session regarding Query Store. The topic of the session is "Going Beyond the Basics with Query Store" by Joseph Fleming @muad_dba (the recorded video can be found here: https://www.twitch.tv/trianglessug), who reviewed some query store basics, talked about limitations, and explored a way to work around some of those limitations by using data stored in the query store. His contact slide is as follows,
The query store, introduced with SQL 2016, has proven to be a valuable tool for gaining insight into performance issues and applying some basic first aid to troublesome queries. With SQL 2017, the ability to do automatic regression correction was introduced. Still, there are some limitations on what the query store can handle.
Query is the core of SQL. In fact, SQL means "Structured Query Language", which is used to 'talk' to SQL server in the database layer. The database layer, in turn, is the foundation of the whole structure.
Let's break them down a bit, and I'll offer up the relevant course or courses for each.
Front-End—The front end is the payload delivered to the browser (HTML, CSS, and JavaScript).
Web Tier—The Web Tier is the framework that resides on the web server (in this case .NET and ASP.NET).
SQL (Structured Query Language)—This is used to 'talk' to SQL server in the database layer.
For the web tier, there are many other options, such as PHP, Ruby on Rails, Python Django, Nodejs. A good article is the one by Rob Foulkrod => https://blog.nhlearningsolutions.com/blog/writing-web-applications-that-access-sql-databases
We normally, however, use stored procedure instead of a query to communicate with SQL server. This is because Stored Procedure have many advantages over a query, in terms of Security, Performance, Network Traffic, Transaction and Reuse and Maintains.
There are four types of Stored Procedures in SQL Server. These are:
System Defined Stored Procedure
Extended Stored Procedure
User-Defined Stored Procedure
CLR Stored Procedure
See https://www.c-sharpcorner.com/UploadFile/3d39b4/basic-of-stored-procedures for a overview.
To complete a task, database objects like the stored procedures and Functions contain a set of SQL statements.
Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved, which executes (compiled code) whenever it is called.
A function is compiled and executed every time whenever it is called. This cannot modify the data received as parameters and function must return a value. A function must return a value and cannot modify the data received as parameters.
Basic Differences between Stored Procedure and Function in SQL Server:
The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.
Functions can have only input parameters for it whereas Procedures can have input or output parameters.
Functions can be called from Procedure whereas Procedures cannot be called from a Function.
For advanced difference between Stored Procedure and Function see https://www.dotnettricks.com/learn/sqlserver/difference-between-stored-procedure-and-function-in-sql-server, and https://intellipaat.com/community/3394/function-vs-stored-procedure-in-sql-server