We use merge statement  when we have to merge data from the source table to the target table. Based on the condition specified it will Insert, Update and Delete rows in the targeted table all within the same statement. Merge statement is very useful when we have large data tables to load, especially when specific actions to be taken when rows are matching and when they are not matching.
 
The statement has many practical uses in both online transaction processing (OLTP) scenarios and in data warehousing ones. As an example of an OLTP use case, suppose that you have a table that isn’t updated directly by your application and instead, you get a delta of changes periodically from an external system. You first load the delta of changes into a staging table and then use the staging table as the source for the merge operation into the target.
 
The below diagram shows the source table and target table with corresponding actions: Insert, Delete and Update

It shows three use cases,
    When the source table has some rows matching that do not exist in the target table, then we have to insert these rows to the target table.
    When the target table has some rows that do not exist in the source table, then we have to delete these rows from the target table.
    When the source table has some keys matching keys with the target table, then we need to update the rows in a targeted table with the values coming from the source table.

Below is the basic structure of the Merge statement,
    MERGE INTO <target_table> AS TGT USING <source_table> AS SRC    
    ON <merge_condition>    
    WHEN MATCHED    
        THEN update_statement    -- When we have a key matching row
    WHEN NOT MATCHED    
        THEN insert_statement    -- when row exists in the source table and doesn't exist in the target table
    WHEN NOT MATCHED BY SOURCE    
        THEN DELETE;             -- Row doesn't exist in the source table

Consider the below example,
It is very easy to understand the merging concept here. We have two tables, the source table and a target table. The Source table has a new price for fruits [ex: Orange rate changed from 15.00 to 25.00] and also new fruits arrived at the store. When we merge we are deleting a few rows which do not exist in the source table.

 
Merge Statement In SQL
Code to merge tables.
    MERGE INTO Fruits WITH(SERIALIZABLE) f
    USING source s
    ON (s.id = f.id)
    WHEN MATCHED
    THEN UPDATE SET
    f.name= s.name,
    f.amount = s.amount
    WHEN NOT MATCHED BY TARGET
    THEN INSERT (id, name, amount) VALUES (s.id, s.name, s.amount)
    WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

    SELECT @@ROWCOUNT;
    GO

Important Merge Conflict
Suppose that a certain key K doesn’t yet exist in the target table. Two processes, P1 and P2, run a MERGE statement such as the previous one at the same time with the same source key K. It is normally possible for the MERGE statement issued by P1 to insert a new row with the key K between the points in time when the MERGE statement issued by P2 checks whether the target already has that key and inserts rows. In such a case, the MERGE statement issued by P2 fails due to a primary key violation. To prevent such a failure, use the hint SERIALIZABLE or HOLDLOCK (both have equivalent meanings) against the target as shown in the previous statement. This hint means that the statement uses a serializable isolation level to serialize access to the data, meaning that once you get access to the data, it’s as if you’re the only one interacting with it.
 
In this article, we learned how a Merge statement improves performance by reading and processing data in a single query. There is no need to write three different statements. This will avoid multiple I/O operations from the disk for each of three statements individually because now data is read only once from the source table.

HostForLIFEASP.NET SQL Server 2019 Hosting

When you are making changes to an existing Non-Clustered index SQL Server provides a wide variety of options. One of the more frequently used methods is DROP EXISTING; in this post you will learn all about that option. This option automatically drops an existing index after recreating it, without the index being explicitly dropped. Let us take a moment understand the behavior of this choice.
 
DROP EXSITING=ON
Which is my preferred method, will DROP the current index only after it finishes creating and building the index with the new definition. The pitfall is that if the index does not exist, you will get an error and must create it without the option or set it to OFF. However, the more important benefit of using this one is all about performance. The index will still be used by active queries until it is rebuilt with the new definition.
    CREATE NONCLUSTERED INDEX [dcacIDX_ServiceType] ON [dbo].[Accounts]  
    (  
       [ServiceType] ASC  
    )  
    INCLUDE([AccountId]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
    GO  

If index does not exist, you will get a 7999 error.
 
Msg 7999, Level 16, State 9, Line 1
 
Could not find any index named 'dcacIDX_ServiceType' for table 'dbo.Accounts'.
 
There are a few exceptions to keep in mind per docs.microsoft.com.
 
With DROP_EXISTING, you can change,

• A nonclustered rowstore index to a clustered rowstore index.

With DROP_EXISTING, you cannot change,

• A clustered rowstore index to a nonclustered rowstore index.
• A clustered columnstore index to any type of rowstore index.

DROP and CREATE
This option is a cleaner and wont error if the index doesn’t already exist. However, I caution you when using this especially when it is a large table. Using this option drops the index before it creates the new, leaving your system without the previous index definition. This can create a huge performance issue while the system waits for the new index to be created. I know this firsthand, as I did this with a client a few years ago, during the day while trying to fix a performance issue. I created a worse issue while the waiting for the new one to be created. It took 45 mins to create the new index with the new definition which caused CPU to spike to 100% while active queries were trying to come through. Which sadly, in turn, slowed down the new index creation.

    DROP INDEX IF EXISTS [dcacIDX_ServiceType] ON [dbo].[Accounts]  
    GO  
    CREATE NONCLUSTERED INDEX [dcacIDX_ServiceType] ON [dbo].[Accounts]  
    (  
       [ServiceType] ASC  
    )  
    INCLUDE([AccountId] WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
    GO  

Now I should also note that the DROP_EXISITING method is also faster when you must modify a Clustered index. Every Non-Clustered index refers to the Clustered index using what is called a clustering key, essentially, a pointer to the row in the clustered index. When a clustered index is dropped and re-created, SQL Server must rebuild the Non-Clustered indexes on that table. In fact, it gets done twice by actually rebuilding them upon drop and rebuild again on the create of the Clustered index. Using DROP_EXISTING=ON prevents you from having to rebuild all these indexes as their keys will stay the same, thus making it significantly faster.
 
The reason I took the time to write this quick blog is to remind those to consider using the DROP EXSITING=ON rather than using the DROP and CREATE method when possible. Do not introduce a performance issue when you can avoid it and you can more efficiently make the desire changes you need. Just a friendly reminder.

HostForLIFEASP.NET SQL Server 2019 Hosting

Summary
In this article, we learned how to split the alphabets from the alphanumeric string in the SQL server as well as about the PATINDEX() and STUFF() function of the SQL server and way create a function in the SQL server that returns a varchar value.

HostForLIFEASP.NET SQL Server 2019 Hosting

The master database is the most important database in the SQL Server. The SQL server has no meaning without the master database, and a user is unable to access SQL database without it. It stores all the primary configuration details of the SQL Server. Whenever a user installs the SQL Server, it creates master, MSDB, model, and TEMPDB system database by default. All these system databases, along with the master database, create system tables, which record all the Server parameters and detailed information about every database and user. Moreover, the master database is stored in a physical file known as master.mdf,  and transaction log file corresponding to the master file is named as masterlog.ldf file. This database is stored at a default location, with a small size. However, if SQL Server master database gets corrupted due to some reason, the system database will not start with the user database.

Master database corruption in SQL Server is a common problem faced by users. Therefore, it is always suggested to take backup of the master database on a  regular basis to have permanent access to the SQL Server. If the level of corruption is really high in master.mdf file, then SQL Server will not get started. However, to fix the issue, a user needs to rebuild master.mdf using command prompt. Moreover, if a master database is suffering from a minor level of corruption, a user can start the database but is not allowed to access the details stored in the database.

How to Fix SQL Server Master Database Corruption?
In this segment of the article, different solutions to overcome master database corruption are discussed. Users can choose any of these according to their choice.

Restore Master Database from Backup
In order to restore the master database from backup, a user must have a complete backup of master.mdf file. Moreover, before you begin, please start SQL Server in single user mode. For this, follow the steps given below.

    First of all, open SQL Server Configuration Manager and choose SQL Server Services option.
        After that, a user needs to choose SQL Server instance.
    Now, right-click on it and select Properties.
    In the Properties window, click on Advanced tab to open it.
    Now, go to the Startup Parameters option and add -m; prefix before already existing parameters.
    Then, start the SQL Server in single user mode.

    Now, to restore master.mdf file, follow the given steps.

    Start the SQL Server and open cmd.exe from the Start menu.
    Enter SQLCMD on command prompt.
    Now, to restore the master database, run the following command:

    RESTORE DATABASE master FROM DISK = ‘D:\Backupfolder\master.bak’ WITH REPLACE

    After executing the above command, remove prefix (-m) parameter and start SQL Server.

A user can use this method to fix the SQL Server master database corruption problem without any hassle. But, the only condition is that one must have the backup to use this method.

Rebuild Master Database in SQL Server

To rebuild SQL Server master database, follow the steps mentioned below
First of all, open the command prompt and try to change the directories to the location of setup.exe file on the local server. However, its default location on the server is

C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release

Now, run the following commands in the command prompt setup.
Setup / QUIET / ACTION = REBUILDDATABASE / INSTANCENAME = InstanceName1 / SQLSYSADMINACCOUNTS = accounts[/SAPWD= Strong_Password ] [ /SQLCOLLATION=CollationName]  

As the rebuilding process completes, it returns the command prompt without any message.

To confirm, one can view the summary.txt log file. The default location of the summary.txt log file is:
C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs

Alternate Solution
Another option that a user can use is third party SQL database recovery tool to have an effortless solution. They help users in removing almost all types of corruption from SQL databases. Moreover, the tool is very easy-to-use as compared to the manual solution.

Conclusion
The master database or master.mdf stores all the available meta data related to the SQL Server, for example, login details, configuration details, information about pointers, file location, and much more. As it is not possible for a user to start the SQL Server with an inconsistent or corrupted master database, there is a need to recover the corrupt master database. Therefore, in this post, we have discussed tricks to resolve master database corruption in SQL Server. A user can use any of these methods depending upon the criteria. However, the above suggested solutions are tried-and-tested before for master database recovery and can be used without any risk.

HostForLIFE.eu SQL Server 2019 Hosting

In this blog, I will show you how to fix the following error:
“Reference to type ‘SqlConnection’ claims is defined in ‘System.Data’, but it could not be found”.

Are you anxious? Then go to “THE SOLUTION."

SPOILER
It’s a little bit painful but it can be solved.
You’ll be able to use .NET Framework Desktop App or ASP.NET inherit/use SqlConnection from netstandard2.

PROBLEM
If you are getting this message: “Reference to type ‘SqlConnection’ claims it is defined in ‘System.Data’, but it could not be found”; it is because you are trying to use the .NET Standard component directly from the .NET Framework (Desktop or ASP.NET).

Why is that you cannot use the object SqlConnection from .NET Framework hosted inside NetStandard 2.0?

ABOUT MY PROJECT
I do have an application, that is ASP.NET and WinForms, using .NET Framework 4.7.1. The core of the code is in assemblies that are shared between both, using System.Web and System.WebForms.

I enjoy migrating to new technologies to see how far they can go. And in this case, my hope was to use less memory on the server side.

I started using .NET Standard 1.6, but I didn’t feel comfortable to deploy it on production. And when 2.0 was released with the SQL Client, I imagined that it was the time. However, I didn't have time at that moment, so I started to migrate a month ago. Well, it took only two days.

I’ve decided to write this post because there isn't a good solution for my purpose. And, I made this on my own.

ATTACHED PROJECTS
I’ve attached two projects, one with the error and other with the error fixed, so you can analyze them both.

THE SOLUTION

STEPS
Create an intermediate library in .NET Standard 2.0 to open the Database Connection.
All the code to open connections must be in .NET Standard 2.0.

Install all the projects that use System.Data, the NuGet System.Data.SqlClient 4.5.1. or later.

Change all the code from .NET Framework libraries that use System.Data to the .NET Standard 2.0.
In the main program/ASP.NET, start the connection with this.
    using(var oCnn = Configurations.GetConnection())  
    {  
    …  
    }  

Why is this necessary?
If you don’t use “var”, your main code will make a reference to the System.Data on the current .NET Framework project. But with “var”, the compilation will refer to “netstandard2” System.Data.SqlClient at the compiling time.
That's all!

SAMPLE
See attached projects.

CONCLUSION
I always migrate to new technologies and make modern ways to do the same job more secure, better, faster, and modern to the end users. I calculate there will be a long time before the technology gets deprecated,  and I avoid using less than the 3rd version - I made an exception for this case, but only because I knew that the roadmap for .NET Standard 3.0 will be supporting WinForms. So I think it's good to refactor the codes to make it updated.
In these samples, I have used .NET Framework 4.7.1 and C# 7.2 (to use: “in” SqlConnection).

HostForLIFE.eu SQL Server 2019 Hosting

“One Gerbil, Two Gerbils or Three Gerbils?” is a common DBA joke about server and storage performance. No matter how many gerbils power your storage, you need to know what type they are and the power that they provide. Storage is not about gerbils it is about IOPs, bandwidth, latency, and tiers.

As a DBA it is important for you to understand and know what kind of storage is attached to your servers and how it is handling your data. It is not important to master everything about it, but it is very advantageous to be able to talk to your storage admins or “Gerbil CoLo, LLC” provider intelligently especially when you experience performance issues. Here is a list of things to I encourage you to know and ask.
 
Terminology
IOPs
IOPS stands for I/O (single read/write request) Operations Per Second. This is a performance metric that is dependent on the type of storage being used and can vary widely. It is important to understand how fast your storage can process data by knowing the expected IOPs and the actual IOPs once the array is processing workloads.
 
Bandwidth or Throughput
This is the measure of the size of the data in the I/O request. You can figure out throughput by taking I/O request size multiplied by the IOPs the Measure will be in Megabytes and Gigabytes per second.
 
Latency
In my opinion this is the most important metric to understand. It’s the time it takes to process that I/O request. Its an indicator of a possible storage bottleneck. You measure this time from when the request is issued to when the request is completed. This determines the responsiveness of your storage.
 
Storage Tier & Automatic Storage Tiers
A modern day array can be divided into tiers some of those tiers can be slower spinning disks while others can be fast flash or a hybrid of both. I think of these in terms of gerbils. You can get a small gerbil who has little legs that can run a marathon, a medium one that runs at a moderate speed 5k and a large gerbil that’s a speed racer. These together can work separately (pinned) or merged into a team like in a relay. Your data can be pass like a baton through each tier (automatic). Another words your data can be demoted or promoted between tiers of the storage device when needed for performance and compacity.
 
Performance Metrics
Note these apply to the Guest OS, there are metrics for the Hypervisor/Storage Stack that DBA’s do not normally have access to. The important part is that the different parts of the stack should mainly be in agreement about those numbers. If latency at the array side > latency at the Guest OS level, there is a big issue somewhere

• Avg. Disk sec/Read – Shows the average read latency.
• Avg. Disk sec/Write – Shows the average write latency.
• Avg. Disk sec/Transfer – Shows the combined averages for both read and writes.
• Disk Transfers/sec - is the rate of read and write operations on the disk.
• Disk Reads/sec - is the rate of read operations on the disk.
• Disk Writes/sec - is the rate of write operations on the disk.
• Avg. Disk Queue Length - is the average number of both read and write requests that were queued for the selected disk during the sample interval.
• Current Disk Queue Length - is the number of requests outstanding on the disk at the time the performance data is collected.

Storage Types
RAID (redundant array of independent disks)
 
RAID is a solution that protects your data from a disk failure. You tend to hear administrators talk in terms of RAID 0,1,5,6 and 10. As database administrators you need to know what RAID type your data is on. For Tempdb you want it on the fastest RAID as possible RAID 1 or 10 while maintaining disk fault tolerance. This is usually old SANs and no longer a concern with modern storage arrays. Modern arrays take a different approach with object-based storage models, which is more like the cloud.
 
FLASH
High speed storage based on non-volatile memory, you may see it referred to as NVMe, Non-volatile Memory Express. These are SSD, solid state drives. One thing to keep in mind is that NVMe’s are SSDs but not all SSDs are NVMe’s there are different types of SSDs. Not matter what type of SSD it is these are really great for Tempdb workloads.
 
Hyper-converged
This is referred to as HCI. Both the storage, networking and compute are bundled into one. This is a newest all in one hardware that claims to save money and creates ease of use. Keep in mind that these now means the HCI processing power is now handling everything (networking, storage, IOPS, etc).
 
Services
Snapshots
A capture of the state of your data is taken at a point in time. These snapshots can be used as restores or backup copies. These are usually snapshot copies of your mdf and ldf files. Note: Uncommitted transactions are not captured, and snapshots are not necessarily a replacement for backups. If your sysadmin asks about doing snapshots in lieu of backups, it’s your job ask a lot of hard questions to backup or storage vendor who is doing the snapshots and test both the back and more importantly the recovery. You need to be asking about to point in time recovery and how to handle page level restores for corruption just to name a couple.
 
Clones
A volume copy of your data, think of this a disk drive copy. It takes the files a makes a replica from snapshots creating a database copy.
 
Disk Replication (sync and async)
 
The replication of logical disk volumes from one array to another in real time (synchronous) or asynchronous for disaster recovery and continuity.
 
Summary
If you can educate yourself on these it will go a long way to being able to make sure you can have intelligent conversations with your storage admins or providers. This will enable you to better ensure you can advocate for your SQL environment when you experience performance issues related to storage. If your data is hosted elsewhere, like Gerbil Colo, LLC or even in a public cloud like Azure, make sure they can provide the above metrics to you. If they can’t, it might be time to host your data elsewhere.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article I am going to explain how to create and execute parameterized stored procedure from another stored procedure, how to prevent SQL Injection attacks, and how to insert data in the table using stored procedure in SQL Server with an example. And also I'll show you how you can use the procedure in SQL Server with an example.
 
When working with any data-driven application you need the database and you also need some data manipulation operations in DBMS (Database Management System) such as selection, insertion, updating, deletion and etc.
 
Your database may contain some useful and confidential information so it is necessary to protect it from any unauthorized activity and implement some security credentials with your DBMS (Database Management System) to secure your data from unauthorized activity.
 
If you're working with an SQL server then a stored procedure is one of the best options for data manipulation operations because a stored procedure accepts parameters as an argument, and parameterized statements prevent code injection techniques such as "SQL Injection" that might destroy your database.
 
So, here I will show you how you can use stored procedures in your SQL server database.
 
Requirements
Insert data in the table using a stored procedure.
Create a Stored Procedure in SQL Server.
Call or Execute Stored Procedure in SQL Server.
Call or Execute Stored Procedure From Another Stored Procedure in SQL Server.
Select and Display Inserted Data in Tabular Format in SQL Server Using Stored Procedure.

Before starting the actual implementation of our example we need a database for demonstration, so first we will create a database.
 
Create Database
    CREATE DATABASE db_europeanwindowshosting 

Before creating a stored procedure I will show you the syntax of the stored procedure in SQL Server.
 
Syntax
    CREATE PROCEDURE Your_Procedure_Name  
    -- list of parameters i.g: @Id INT = 0, @EmpName VARCHAR(50)=''  
    AS  
    BEGIN  
    -- SQL statements  
    END  

Now, we will start to write our stored procedure something like,
 
Create First Stored Procedure
    CREATE PROCEDURE Employee_Insert  
    @EmpId INT,   
    @EmpName VARCHAR(50),  
    @EmpDesignation  VARCHAR(50)  
    AS  
    BEGIN  
    -- Declare temporary table  
       
    DECLARE @Temp1 TABLE (EmployeeId INT, EmployeeName VARCHAR(50), EmployeeDesignation VARCHAR(50))  
       
    -- insert records in the temporary table  
       
    INSERT INTO @temp1 (EmployeeId, EmployeeName, EmployeeDesignation)  
         VALUES(1,'Peter','Software Engineer'),  
         (2,'Scott','Web Developer'),  
         (3,'Alexandria','Business Development Executive'),  
         (4,'Tom','Business Development Executive'),  
         (5,'Martin','Software Engineer')  
           
    -- Select all records from the temporary table      
    SELECT EmployeeId, EmployeeName, EmployeeDesignation FROM @Temp1  
    -- Select specific records from temporary table  
    SELECT EmployeeId, EmployeeName, EmployeeDesignation FROM @Temp1 WHERE EmployeeId = @EmpId AND EmployeeName = @EmpName AND EmployeeDesignation = @EmpDesignation  
       
    END  

If you analyzed the above procedure then you can see @EmpId, @EmpName, and @EmpDesignation where @ can be described as a parameter.
 
Now, I will show you how to create a second stored procedure and how you can insert data in the table using the first created stored procedure, and for that, I used a temporary table.
 
Create Second Stored Procedure
 
Now it's time to call/execute/create the stored procedure and insert data in table and also display the result in tabular format, for that here we will create another stored procedure and will call/execute the created stored procedure.

    CREATE PROCEDURE Employee_GetData  
    @Id INT,   
    @Name VARCHAR(50),  
    @Designation  VARCHAR(50)  
    AS  
    BEGIN  
    -- Execute First Procedure  
    EXECUTE Employee_Insert @EmpId = @Id , @EmpName = @Name, @EmpDesignation = @Designation  
    END  

Again if you analyze the above procedure then you can see our first created stored procedure is executed with "EXEC." You  can also use "EXECUTE", where @Id, @Name, and @Designation send parameters to the first stored procedure.

    --Execute Second Procedure By Passing Parameters  
    EXEC Employee_GetData @id=1, @name='Peter', @Designation ='Software Engineer'  

Now we will call/execute our second created procedure that will call/execute our first created procedure with the actual result as per our requirement.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this blog we will discuss how we can get primary column name of any table programmatically. However we can see primary column name by just right clicking on a table and see design view. But when we have dynamically create table and set primary key then might be we does not know the primary column name. So in this article we discuss about that.
 
In my database I have only one table named Employee, and you can see in the below image the primary key column is EmployeeId.
 
Get Primary Key Column In SQL Server
Here we use INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view so first we get what's inside that view.
 
Run the following queries to get the output of both views.
 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS   

Output

 
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
    select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE   

Output

 
Get Primary Key Column In SQL Server
 

Here are a few lines of sql query using which we can get the primary column name.
    select C.COLUMN_NAME FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C  
    ON C.CONSTRAINT_NAME=T.CONSTRAINT_NAME  
    WHERE  
    C.TABLE_NAME='Employee'  
    and T.CONSTRAINT_TYPE='PRIMARY KEY'   

Output

 
Explanation
    Here we join these two views, TABLE_CONSTRAINTS And CONSTRAINT_COLUMN_USAGE on CONSTRAINT_NAME.
    Then select those records where CONSTRAINT_COLUMN_USAGE.TABLE_NAME is Employee and TABLE_CONSTRAINTS.CONSTRAINT_TYPE is Primary Key.
    And then select CONSTRAINT_COLUMN_USAGE. COLUMN_NAME.

So that is just two or three lines of sql query for getting primary column name of any table. I hope you find some useful information in this article. If you find this helpful kindly share it with your friends.

HostForLIFEASP.NET SQL Server 2019 Hosting

This article shows how we can determine the size of an entire database using C# and the size of each and every table in the database using a single SQL command. To explain this, it is divided into three sections,

• Getting Size of one Table in the Database using single SQL Command
• Getting Size of each Table in the Database using single SQL Command
• Getting Size of the entire Database using C#

Getting Size of one Table in the Database using single SQL Command
To get the table size in SQL Server, we need to use the system stored procedure sp_spaceused. If we Table Name as an argument, it gives the disk space used by the table and some other information like: Number of rows existing in the table, Total amount of reserved space for Table, Total amount of space reserved for the table but not yet used and Total amount of space used by indexes in Table.

Example
For the ADDRESS table in our database, if we run,
sp_spaceused 'TADDRS'

It will give the following result,

Getting Size of each Table in the Database using single SQL Command

We have seen how we can determine the size of one table. Now, suppose we want to determine the size of each table in the entire database. We could find the size of any table using this command just by changing the Table name in the parameter. Is that right? But would it not be much better if we have a one-line SQL command that gives the size of each table? Right?
 
Fortunately, SQL Server provides a way to do this. A stored procedure "sp_MSforeachtable" could do easily for us!
 
The sp_MSforeachtable stored procedure is one of many undocumented stored procedures tucked away in the depths of SQL Server. You can find more details of "sp_MSforeachtable" in this link.
 
sp_MSforeachtable is an undocumented stored procedure that is not listed in MSDN Books Online and can be used to run a query against each table in the database. In short, you can use this as,
EXEC sp_MSforeachtable @command1="command to run"
 
In the "command to run" put a "?" , where you want the table name to be inserted. For example, to run the sp_spaceused stored procedure for each table in the database, we'd use,

It will give the size of each table (including other details) like,

Getting the Size of the entire Database
Now we want to get the total size used by a database i.e. the additional space used by each table in the database. We have seen in the previous section how we can get the size of each table in the database. Here is sample code in C# that can be used to calculate the size of the entire database.
 
In this, we are executing the same command what we discussed in the above section and are querying the database using simple ADO.Net. We get the result in a DataSet and then iterate each table to get its size. The table size is stored in the "data" column of each table (see the above Picture-1). We just add the "data" column value of each table to get the size of the entire database. Sample code is also attached with this article.
    class MemorySizeCalculator  
    {  
      public void GetDbSize()  
      {  
        int sum = 0;  
       
     // Database Connection String   
     string sConnectionString = "Server = .; Integrated Security = true; database = HKS";  
       
      // SQL Command [Same command discussed in section-B of this article]  
      string sSqlquery = "EXEC sp_MSforeachtable @command1=\"EXEC sp_spaceused '?'\" ";  
       
      DataSet oDataSet = new DataSet();  
       
      // Executing SQL Command using ADO.Net  
      using (SqlConnection oConn = new SqlConnection(sConnectionString))  
      {  
             oConn.Open();  
             using (SqlCommand oCmdGetData = new SqlCommand(sSqlquery, oConn))  
             {  
                  oCmdGetData.ExecuteNonQuery();  
                  SqlDataAdapter executeAdapter = new SqlDataAdapter(oCmdGetData);  
                  executeAdapter.Fill(oDataSet);  
             }  
             oConn.Close();  
      }  
      // Iterating each table  
      for (int i = 0; i < oDataSet.Tables.Count; i++)  
      {  
        // We want to add only "data" column value of each table  
        sum = sum + Convert.ToInt32(oDataSet.Tables[i].Rows[0]["data"].ToString().Replace    
        ("KB", "").Trim());  
      }  
        Console.WriteLine("Total size of the database is : " + sum + " KB");  
     }  
    } 

HostForLIFE.eu SQL Server 2019 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.

I have not found any direct way to call. So, we need to write our own logic inside a method provided by SignalR Library.
 

There is a Hub class provided by the SignalR library.
 
In this class, we have 2 methods.
    OnConnectedAsync()
    OnDisconnectedAsync(Exception exception)

So, the OnConnectedAsync() method will add user and OnDisconnectedAsyn will disconnect a user because when any client gets connected, this OnConnectedAsync method gets called.

In the same way, when any client gets disconnected, then the OnDisconnectedAsync method is called.
 
So, let us see it by example.
 
Step 1
Here, I am going to define a class SignalRHub and inherit the Hub class that provides virtual method and add the Context.ConnectionId: It is a unique id generated by the SignalR HubCallerContext class.
    public class SignalRHub : Hub  
    {  
    public override Task OnConnected()  
    {  
    ConnectedUser.Ids.Add(Context.ConnectionId);  
    return base.OnConnected();  
    }  
    public override Task OnDisconnected()  
    {  
    ConnectedUser.Ids.Remove(Context.ConnectionId);  
    return base.OnDisconnected();  
    }  
    }  

Step 2
In this step, we need to define our class ConnectedUser with property Id that is used to Add/Remove when any client gets connected or disconnected.
 
Let us see this with an example.
    public static class ConnectedUser  
    {  
    public static List<string> Ids = new List<string>();  
    }  

Now, you will get the result of currently connected client using ConnectedUser.Ids.Count.
 
As you see, here I am using a static class that will work fine when you have only one server, but when you will work on multiple servers, then it will not work as expected. In this case, you could use a cache server like Redis cache, SQL cache.