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.

Today, I'm gonna show you how to sort numbers in SQL Server. It's not a difficult task but not an easy way. In the front end are many functions that for sorting values but SQL Server has no predefined function available.

For example I will sort the numbers 12,5,8,64,548,987,6542,4,285,11,26. SQL Server has no array list or array so how can we hold the values after sorting the numbers? SQL Server has temporary tables. Temporary automatically creates and drops the table after the execution.

First of all, create a temporary table. Suppose a problem occurs in SQL Server or during program execution. A Temporary table can't be deleted or dropped the proper way. When we want to create a table a second time a confirm error occurs as in the following:

There is already an object named '#temp' in the database.

So this type of problem is avoided by checking first if the table exists like this:

    IF  EXISTS (SELECT * FROM sys.tables 
    WHERE name = N'#temp' AND type = 'U') --check the #temp already exists in database or not  
    --Not:-  type U stand for user 
    begin 
    drop table #temp 
    end 

If the table already exists in the database then drop the table #temp.

My values are 12,5,8,64,548,987,6542,4,285,11,26. They need to be be split up before the sort. How can we split the numbers? Of course we can at the comma (,).  If I split the at the comma then I get the numbers like this: 12 5 8 64 548 and so on. One question then arises is, how to split the value? Don't worry, I have done that.

select left('12,45,18,95',

CHARINDEX(',','12,45,18,95')-1))

If i run this query it should be return the value is 12

After that everything is fine, we get the value from the #temp table.

    select ROW_NUMBER() over (order by value) 'srNo', value from #temp order by value

The following is a complete Stored Procedure to sort the numbers. 

   ALTER proc [dbo].[Porc_sortnumber] 
    as 
    begin 
    DECLARE @value VARCHAR(MAX)='1,2,5,6,12,88,47,95,56,20' 
    declare @lenth int =1 
    IF  EXISTS (SELECT * FROM sys.tables 
    WHERE name = N'#temp' AND type = 'U') --check the #temp allready exists in database or not  
    --Not:-  type U stand for user 
    begin 
    drop table #temp 
    end 
    create table #temp (id int identity(1,1),value int)  
    while(@lenth!=0 ) 
    begin 
    insert into #temp(value) values(left(@value,(CHARINDEX(',',@value)-1))) 
    set @value= right(@value,len(@value)-((CHARINDEX(',',@value)))) 
    set @lenth=CHARINDEX(',',@value) 
     
    end 
    insert into #temp(value) values(@value) 
    select ROW_NUMBER() over (order by value) 'srNo', value from #temp order by value 
    end 

Output

I hope this article will helpful!

HostForLIFE.eu SQL Server 2014 Hosting

HostForLIFE.eu is a popular online Windows and ASP.NET based hosting service provider catering to those people who face such issues. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market.

.NET 5 is the next version of .NET Core and the future of the .NET platform. With .NET 5 you have everything you need to build rich, interactive front end web UI and powerful backend services. .NET 5 contains great performance improvements in the runtime and libraries and for the gRPC components. These improvements, when applied to ASP.NET Core, result in some significant wins in throughput (RPS) and latency.

HostForLIFE.eu hosts its servers in top rate data centers that's located in Amsterdam (NL), London (UK), Washington, D.C. (US), Paris (France), Frankfurt (Germany), Chennai (India), Milan (Italy), Toronto (Canada) and São Paulo (Brazil) to ensure 99.9% network period. All data center feature redundancies in network connectivity, power, HVAC, security, and fire suppression. HostForLIFE.eu proudly announces available ASP.NET 5 feature for new customers and existing customers.

HostForLIFE.eu is a popular online Windows based hosting service provider catering to those people who face such issues. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market. Their powerful servers are especially optimized and ensure ASP.NET 5 performance. They have best data centers on three continent, unique account isolation for security, and 24/7 proactive uptime monitoring.

Further information and the full range of features ASP.NET 5 Hosting can be viewed here https://hostforlife.eu/European-ASPNET-5-Hosting.

You have a set of data, and you want to execute a set of queries on the records in a SQL table which are matching with this set of data. You have got a list of Product IDs from the QA department, you need to get the details of those products from the SQL table. And this situation happens frequently. How would you do this? What are the options you have?

Easy solution – You would write select query and put product ID in where clause and get the details. Do this for all the product IDs you have got. Its a time consuming task.
Is there any better way of achieving it where we can get the result in one query?
 
Solution
Yes, we can write a stored procedure, or you can just prepare a set of queries together to run for a single time also.
 
We will use temporary table variable to create arrays in SQL. We will insert the set of data (what you already have) into a temporary array variable. Use While clause, insert into clauses to generate our result table – which will show details of products matching product IDs.
 
Below is a sample set of queries which generates array variable named MYARRAY, you can store your data in this array. Then we will iterate through these array values using WHILE clause, we have used two variables to loop through array – INDEXVAR and TOTALCOUNT. As usual the loop will continue until INDEXVAR is smaller than TOTALCOUNT.
 
Using INDEXVAR and Where clause we will get current array index value. We will use this value to fetch data from actual table and insert it into our temporary result table PRODUCTDETAILSTABLE.
    Use DATABASENAME  
    GO  
      
    DECLARE @PRODUCTDETAILSTABLE table (PRODUCTNAME nvarchar(100), PRODUCTID int, PRODUCTCOST int)  
      
    -- Declare your array table variable  
    DECLARE @MYARRAY table (TEMPCOL nvarchar(50), ARRAYINDEX int identity(1,1) )  
      
    -- Add values to your array table, these are the values which you need to look for in your database  
    INSERT INTO @MYARRAY (TEMPCOL)  
       VALUES  
    ('PRD-2222'), ('PRD-3333'), ('PRD-4563'), ('PRD-4569'), ('PRD-6657'), ('PRD-3452'), ('PRD-6578')  
      
    --select * from @MYARRAY  
      
    DECLARE @INDEXVAR int  
    DECLARE @TOTALCOUNT int  
    DECLARE @CURINDEXEDPRODUCTID nvarchar (50)  
    SET @INDEXVAR = 0  
    SELECT @TOTALCOUNT= COUNT(*) FROM @MYARRAY  
    WHILE @INDEXVAR < @TOTALCOUNT  
    BEGIN  
        SELECT @INDEXVAR = @INDEXVAR + 1  
      
        -- Get value of current indexed product ID from array table  
        SELECT @CURINDEXEDPRODUCTID = TEMPCOL from @MYARRAY where ARRAYINDEX = @INDEXVAR  
      
        -- Get details of Product matching current indexed product ID from array  
        BEGIN  
            INSERT INTO @PRODUCTDETAILSTABLE (PRODUCTNAME, PRODUCTID, PRODUCTCOST)  
            (  
                select top 1 ProductName as PRODUCTNAME, ProductID as PRODUCTID, Cost as PRODUCTCOST  
                FROM  
                dbo.ProductDetails t  
                where t.ProductID= @CURINDEXEDPRODUCTID  
            )   
        END  
    END  
      
    Select * from @PRODUCTDETAILSTABLE  

You might come across a scenario where you need to update a set of records from your database or delete specific rows from your table – at that time you might need these array queries to fulfill your requirements. That’s it for this article. When someone asks you to pull data for specific list of IDs then I hope these queries help you to pull out data easily from the database.