In this blog, I will show you how to write multiple SQL Statements in one procedure. Instead of writing separate procedures for Select, Insert, Delete and Update operations into the table, I am going to show you how to combine all operations into one single Stored Procedure.

This Table is called tblEmployee with the below structure:
We want to write a procedure for this table for Select, Insert, Delete and Update records.

Instead of writing separate a stored procedure for each operation we are going to write only one stored procedure to perform Select, Insert, Delete and Update records.

How To Achieve It?
Are you wondering how to accomplish this? It is simple -- just add a parameter to the stored procedure. Depending on this parameter we are going to execute the appropriate operations.

Here is the stored procedure:
Createprocedure [dbo].[USP_Employee] @empid asint=0, @empname asvarchar(50)=NULL, @age asint=0, @salary asint=0, @dob asvarchar(20)=NULL, @designation asvarchar(50)=NULL, @Reqtype asvarchar(10)=NULL AS  
BEGINIF @Reqtype='SELECT'   
BEGIN   
SELECT empid,   
       empname,   
       age,   
       salary,   
       dob,   
       designation   
FROM   tblemployee   
ENDIF @Reqtype='INSERT'   
BEGIN   
insertinto tblemployee VALUES(@empid,@empname,@age,@salary,@dob,@designation)   
ENDIF @Reqtype='DELETE'   
BEGIN   
deletefrom tblemployee WHERE empid=@empid   
ENDIF @Reqtype='UPDATE'   
BEGIN   
UPDATE tblemployee   
SET    empname=@empname,   
       age=@age,   
       salary=@salary,   
       dob=@dob,   
       designation=@designation   
WHERE  empid=@empid   
ENDEND 

In this blog, we have learned how to write multiple SQL operations into a single SQL procedure.

Subqueries In SQL Server
Subqueries are enclosed in parentheses. Subquery is also called an inner query and the query which encloses that inner query is called an outer query. Many times subqueries can be replaced with joins.
    select * from Employee where DepartmentID not in (select distinct DepartmentID from Department) 

Another example:
    select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d; 

The above query is an example of using subquery in the select list. The above result can be achieved using join also; see the below query
    select d.Department_Name,COUNT(e.empid) as empcount from Department d 
    join Employee e on e.DepartmentID=d.DepartmentID 
    group by d.Department_Name 
    order by empcount; 

According to MSDN, you can nest up to 32 levels.
 
Columns present in subqueries cannot be used in the outer select list of a query.
 
Correlated Subqueries
If our subquery depends on the outer query for its value then it is called a Correlated Subquery. It means subquery depends on outer subquery. Correlated subqueries are executed for every single row executed by outer subqueries.
 
A correlated subquery can be executed independently,
select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d order by empcount; 

What to choose for performance --  Subquery or Join?
According to MSDN, there is no big difference between queries that use sub-queries and joins.
 
But in some cases, we need to check the performance, and Join produces better performance because the nested query must be processed for each result of the outer query. In such cases, JOIN will perform better.
 
In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by the SQL Server. If the SQL server generates the same execution plan then you will get the same result.

Last month I blogged about using Docker to run SQL Server as a quick and easy way to get SQL Server up and running.  While it continues to be immensely useful, there aren’t any user databases  running on it.  The only databases present are the system databases. Yes, I could manually create a database, but it would be a lot easier to have a sample database available.

How do we do restore a sample database, in a container, that is running on a Mac laptop?  Let’s check it out!
 
Disclaimer
Not a container expert so there might be various ways of doing it.  This is just how I’ve figured out how to make the magic happen.
 
Also, if you have not read the first blog on how to get SQL Server running in Docker on your Mac, take a few minutes to read through it.
 
Here are the steps that we will take to make this work,
    Download one of the sample databases from I have a “mssql” directory in my local profile to make things easier
    Make sure the container is started.  You can issue a “docker ps” command terminal to see which containers are running
    Create a directory within the container
    Copy the sample database backup file into the directory you just created
    Restore the database onto the SQL instance that is running within the container

Sounds simple, right? Let’s see!
 
Create a directory
To create a directory within the container (remember that the container is running Linux), we can issue a command to the container that will create the directory.  Using Terminal (you can go to Spotlight to find the Terminal program or it is under Utilities in the Applications folder), execute the following command,
    Docker exec -it sql2019 mkdir /var/opt/mssql/backups  

Let us break that command down,
    Docker – this indicates that we are going to be doing something with Docker
    Exec – this tells the specified container to exec the command we pass into it
    -it – this basically allows for an interactive session with the container
    Sql2019 – this is the name of the container. You can specify the container name when you start the container or Docker will name it for you
    Mkdir – this is short for “make directory”
    /var/opt/mssql/backups – this is the directory path that is to be created.

    Restoring A SQL Server Database In Docker

Copy the Backup File
Now that the directory has been created, we need to get the backup file of the sample database into the container.  In my case, I am using AdventureWorks2017.bak
    Docker cp ./mssql/AdventureWorks2017.bak sql2019:/var/opt/mssql/backups 

Here is how that command breaks down,
    Docker – this indicates that we are going to be doing something with Docker
    cp – this is short for “copy”
    ./mssql/AdventureWorks2017.bak – this is the path of the source file that is being copied into the container. The “.” Indicates start with whatever working directory I am in, which is my profile directory as indicated by the “jmorehouse$”
    Sql2019 – this is the name of the container.
    :/var/opt/mssql/backups – this is the destination directory that is within the container.

Once the command is complete, we can check to make sure that the file was copied successfully.
    Docker exec -it sql2019 ls /var/opt/mssql/backups  

The “ls” refers to “list”.  This is equivalent to executing a “dir” command in DOS.
 
Restoring A SQL Server Database In Docker
 
Restore the Database
The backup file now resides within the container and we just need to tell SQL Server to restore it.  In this section, I will be using Azure Data Studio and native T-SQL commands.
 
Let us first check that SQL Server can see the file.
    RESTORE FILELISTONLY FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’ 
    GO 

Restoring A SQL Server Database In Docker
Excellent!  SQL Server can see the backup file which means that we can restore it.  Notice on the left-hand side, there are no user databases, just system databases. Also notice that the physical names of the database shown above are from the Windows Operating System.  Since SQL Server is running on Linux within the container, we will have to move the physical files to a different location.
    RESTORE DATABASE AdventureWorks2017 FROM DISK=N’/var/opt/mssql/backups/AdventureWorks2017.bak’ WITH 
    MOVE ‘AdventureWorks2017’ to ‘/var/opt/mssql/data/AdventureWorks2017.mdf’, 
    MOVE ‘AdventureWorks2017_log’ to ‘/var/opt/mssql/data/AdventureWorks2017_log.ldf’ 
    GO 

Restoring A SQL Server Database In Docker
Above we can see that the database was restored and then subsequently upgraded to the SQL Server 2019 database version.  If you refresh the Databases branch on the left-hand side, the AdventureWorks2017 database is now present!
 
Docker continues to be my current choice of “go to” when I need a quick and easy SQL Server solution to play around.  While I absolutely recommend Azure and its offerings, utilizing Docker on my local laptop is just faster, and frankly, fun to play around it.   Now that I can easily restore databases, it just makes it that much better of a solution for non-production uses.

Under compatibility level 150, in both SQL Server 2019 and Azure SQL Database, you now can use batch mode for CPU-bound analytic type workloads without requiring columnstore indexes. There is no action needed to turn on batch mode aside from being on the proper compatibility mode. You also have the ability to enable it as a database scoped configuration option (as shown below), and you can hint individual queries to either use or not use batch mode (also shown below). If you recall in my earlier blogs on columnstore, it is batch mode in conjunction with page compression that drastically increases query performance. This feature, Batch Mode on Rowstore, allows all operators enabled for batch mode to operate as such in SQL Server.
 

What does this mean? It means that query operations can process data faster, more efficiently and mimic what makes columnstore so fast. Instead of reading row by row (row store mode) it can read in chunks i.e. batches of 900 rows at a time. The performance impact of this can be tremendous which effectively uses CPU more efficiently.
 
Just like columnstore this only benefits analytic type workloads or data warehouses, as mentioned above. This is meant for aggregations and joins that process thousands of rows. It will not benefit you when processing singleton lookups. If where clause that does not look up a range of values and is just satisfying predicates, then batch mode does not provide a benefit.
 
How does the engine know when to use batch mode? According to docs.microsoft.com the query processor uses heuristics and will make decision based on three checks. An initial check on tables sizes, operators used and cardinality estimates. Then the optimize checks to see if there are cheaper plans it can use. If no alternative better plans are available, the optimizer will choose batch mode. There are some limitations that will prevent the use of batch mode such as, in-memory OLTP tables or for any index other than B-Trees or on-disk heaps. It will also not work for LOB columns including sparse and XML columns.
 
You can easily decipher when batch mode is used to run query inside the operator’s properties. Let’s see a demo.
 
To demo I want to first show you a plan NOT using Batch Mode on Row Store, so let’s turn the feature off because as I mentioned earlier it is already enabled for compatibility mode 150 by default. Run the below database scope configuration script to turn it off.
    USE AdventureworksDW2016CTP3 
    GOALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF; 

Now let’s run this query and make sure we capture the execution plan.
    SELECT FS.[ProductKey], 
           AVG([SalesAmount]), 
           [UnitPrice] 
    FROM [dbo].[FactResellerSalesXL] FS 
        JOIN dbo.DimProduct DP 
            ON DP.ProductKey = FS.ProductKey 
    GROUP BY FS.[ProductKey], 
                [UnitPrice] 

Note the Table Scan. By hovering over it you can see the operator’s properties and see the Actual Execution Mode says ROW and it processed 11,669,653 rows.

Now let’s run it again in Batch. Instead of changing compatibility lets just turn on the feature with an OPTION HINT.

SELECT FS.[ProductKey], 
       AVG([SalesAmount]), 
       [UnitPrice] 
FROM [dbo].[FactResellerSalesXL] FS 
    JOIN dbo.DimProduct DP 
        ON DP.ProductKey = FS.ProductKey 
GROUP BY FS.[ProductKey], 
            [UnitPrice] 
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE')); 

You can clearly see the optimizer chose to use BATCH mode based on our HINT. In addition, you can see it ran significantly faster at only 405 ms versus 1.119s using row mode. In general, we’ve seen queries that benefit from batch mode running in almost half of what row mode performance is and columnstore in batch mode performance.

Let’s go ahead and change back to the default Batch Mode again for our database just to prove it would have used batch mode without the use of our hint. Run the below and look at the plan.

    ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON; 
    GO 
     
    SELECT FS.[ProductKey], 
           AVG([SalesAmount]), 
           [UnitPrice] 
    FROM [dbo].[FactResellerSalesXL] FS 
        JOIN dbo.DimProduct DP 
            ON DP.ProductKey = FS.ProductKey 
    GROUP BY FS.[ProductKey], 
                [UnitPrice] 

BINGO! There you have it!

If you are already using compatibility mode 150 you are already taking advantage of this feature and may not even realized. For those that have not made the leap to 2019 I highly recommend it, if only for this little gem which is one of the many reasons why you should upgrade.