European Windows 2019 Hosting BLOG

BLOG about Windows 2019 Hosting and SQL 2019 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2019 Hosting - HostForLIFEASP.NET :: SQL Index Creation Using DROP EXISTING ON

clock February 23, 2021 05:46 by author Peter

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

 



SQL Server 2019 Hosting - HostForLIFEASP.NET :: Split Alphabets From Alphanumeric String In SQL Server

clock February 16, 2021 07:32 by author Peter
This article gives an explanation about how to split alphabets from the alphanumeric string in an SQL server. Here I'll also explain how to create a function in an SQL server.
In my previous article, I explained what is an alphanumeric string and how to split numbers from an alphanumeric string in an SQL server that you might like to read.
 
While working with any data-driven applications, sometimes we need to split the numbers and alphabets from the input string as per the given requirement. I got many emails from students and beginner programmers to write an article on ways to get only varchar values from the string in the SQL server. So today in this article I'll explain how to archieve this requirement to split the numbers and alphabets and return only varchar value from the string.
 
Requirement
  1. How to get alphabets from an alphanumeric string in SQL Server?

Implementation
So, let's create a query to split the alphabets from the string in the SQL server.

Get Alphabets from string
Let's split the alphanumeric string and get only alphabets from the string. So, we will take an example for demonstration.
 
I have my enrollment number, which is a combination of numbers and alphabets, and I want only alphabets from my enrollment number.
 
Example
  1. Input (Enrollment Number): SOE14CE13017  
  2. Expected Output: SOECE  
SQL Query to Get Alphabets From String
DECLARE @strEnrollmentNumber NVARCHAR(MAX) = 'SOE14CE13017'  
DECLARE @intNumber INT    
   
SET @intNumber = PATINDEX('%[^A-Za-z]%', @strEnrollmentNumber)    
   
WHILE @intNumber > 0    
  BEGIN  
    SET @strEnrollmentNumber = STUFF(@strEnrollmentNumber, @intNumber, 1, '' )    
    SET @intNumber = PATINDEX('%[^A-Za-z]%', @strEnrollmentNumber )    
END  

Explanation
As you can see in the query above, here, we have declared two different temp variables @strEnrollmentNumber which indicates an Input string, and @intNumber that is taken to check whether the input string contains a number or not. Then using the PATINDEX function of the SQL server we have identified that the string input string contains a number or not and stored the return value of this function into @intNumber.
 
In SQL server PATINDEX is a function that accepts search pattern and expression(input string) as a parameter and returns, starting position of the first occurrence of the pattern in a specified expression(input string), PATINDEX will return 0 if the pattern is not found in the specified expression(input string). Here, we have used a pattern '%[^A-Za-z]%' that indicates only alphabets from a to z and A to Z.
 
Now, by using the while loop in the SQL server we removed the numbers from the input string which not match with the given pattern '%[^A-Za-z]%' one by one using the STUFF function and store the result in the @strEnrollmentNumber variable and again set the value of @intNumber as per the specified pattern '%[^A-Za-z]%' as we used condition @intNumber > 0 in while loop, So it will do the same process again and again and remove numbers from the input string one by one till @intNumber gets 0 and remove all the numbers from the input string.
 
In SQL Server STUFF() function is used to deletes a specified sequence of characters from a source/Input string and then inserts another set of sequence of characters at a specified starting point. I have written an article on STUFF() function with syntax and examples that you might like to read.
 
Use of Query
SELECT @strEnrollmentNumber  

Output
SOECE 

You also can create a function to get only alphabets from the input string to reduce the complexity of the query.
 
Function to Get Alphabets From String
CREATE FUNCTION [dbo].[GetAlphabetsFromString]  
(  
    @strInputString  VARCHAR(MAX)  
)    
RETURNS VARCHAR(MAX)  
AS    
BEGIN    
    DECLARE @intValue INT    
    SET @intValue = PATINDEX('%[^A-Za-z]%', @strInputString)    
BEGIN    
    WHILE @intValue > 0    
    BEGIN    
        SET @strInputString = STUFF(@strInputString, @intValue, 1, '' )    
        SET @intValue = PATINDEX('%[^A-Za-z]%', @strInputString )    
    END    
END    
    RETURN ISNULL(@strInputString,'')    
END    
GO  
Use of Function
SELECT dbo.GetAlphabetsFromString('SOE14CE13017')  

Output
SOECE  

Explanation
As you can see in the created function above here we have created a function that accepts inputString as an argument and all the logic is the same as I explained in the above SQL query to return only alphabets from the string. Finally, this function returns the varchar value from the string and if the input string dose does not contain any alphabets then this function will return an empty string.
 

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



SQL Server Hosting - HostForLIFE :: How to Fix SQL Server Master Database Corruption?

clock February 8, 2021 08:46 by author Peter

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



About HostForLIFE

HostForLIFE 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.

We have offered the latest Windows 2019 Hosting, ASP.NET 5 Hosting, ASP.NET MVC 6 Hosting and SQL 2019 Hosting.


Month List

Tag cloud

Sign in