This small article is intended for the audience stuck in their interview when asked for the differences among CHAR, VARCHAR, NCHAR and NVARCHAR data types. Actually it is simple but sometimes people get confused.

To store data as characters, numeric values and special characters in a database, there are 4 data types that can be used. So what is the difference among all 4 of these data types?

• CHAR vs VARCHAR
• NCHAR vs NVARCHAR

Considering an example, we will look into each one of them.
    DECLARE @string CHAR(20)  
   SET @string = 'Robin'  
   SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'  

Note: The LEN() method provides the length of a character excluding trailing blanks stored in the string expression whereas the DATALENGTH() method provides the number of byte spaces occupied by the characters in a string expression.
 
As you know we represent the character values within single quotes, for example 'Robin'. But do you know we can represent these same characters within double quotes similar to programming languages representing a string, for example “Robin”? This can be done by setting the value:
    SET QUOTED_IDENTIFIER OFF

By default, it is set to ON
 
CHAR vs VARCHAR
Talking about the CHAR data type:
    It is a fixed length data type
    Used to store non-Unicode characters
    Occupiers 1 byte of space for each character

If the value provided to a variable of CHAR data type is shorter than the length of a column of declared the size of the variable, then the value would be right-padded with blanks to match the size of column length.
    DECLARE @string CHAR(20)  
    SET @string = 'Robin'  
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As you can see above, the bytes occupied by the variable are 20 even though the length of the characters is 5. That means that irrespective of the character stored in the column, it will occupy all bytes to store the value.

About the VARCHAR data type:
    It is a variable length data type
    Used to store non-Unicode characters
    Occupies 1 byte of space for each character

    DECLARE @string VARCHAR(20)  
    SET @string = 'Robin'  
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As you can see above, it is showing DATALENGTH as 5 which means it will use only the number of bytes equal to the number of characters. This will allow me to avoid wasting database space.

Note:  If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a CHAR column defined as NULL is considered as VARCHAR.

When to use what?
If you are sure about the fixed length of the data that would be captured for any specific column then go for CHAR data type and if the data may vary then go for VARCHAR.
 
NCHAR vs NVARCHAR
Similar to CHAR data type, the NCHAR data type:
    Is a fixed length data type
    Used to store Unicode characters (for example the languages Arabic, German and so on)
    Occupies 2 bytes of space for each character

    DECLARE @string NCHAR(20)  
    SET @string = 'Robin'  
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As you can see above, the data length column shows 40 bytes even though the size declared is 20. It's because NCHAR holds 2 bytes of space for each character.

About the NVARCHAR data type:
    It is a variable-length data type
    Used to store Unicode characters
    Occupies 2 bytes of space for each character
    DECLARE @string NVARCHAR(20)  
    SET @string = 'Robin'  
    SELECT @string AS 'String', DATALENGTH(@string) AS 'Datalength' , LEN(@string) AS 'Len'

As in the output above, you will observe DATALENGTH column is showing only 10 as a value. That is because it occupies 2 bytes of space for each character and the data length is only 5 characters, therefore it will occupy 10 bytes of space in the database.

When to use what?
If your column will store a fixed-length Unicode characters like French, Arabic and so on characters then go for NCHAR. If the data stored in a column is Unicode and can vary in length, then go for NVARCHAR.
 
Querying to NCHAR or NVARCHAR is a bit slower then CHAR or VARCHAR. So don't go for NCHAR or NVARCHAR to store non-Unicode characters even though this data type supports that.

This small article is just to make you aware of the differences among the CHAR, VARCHAR, NCHAR and NVARCHAR data types since they are all used to store characters, numbers or special characters. I hope you like this small article and will that it will be helpful to you at some point of time. Leave your comments whether its good or bad. Sharing is valuable no matter what.

HostForLIFEASP.NET SQL Server 2019 Hosting

SQL Server Log Shipping is one of the basic levels of SQL Server High-Availability (HA) features. It is an automated system/way to keep backup & restore your database from one server/instance to another or more server/instances. The primary purpose of log shipping is to increase database availability by maintaining a backup server that can replace a production server quickly.

Limitation of Log Shipping

• SQL Server Log Shipping is one of the basic level of SQL Server High-Availability (HA) features.
• Here is the possibility of data loss in log shipping for a set of times you’ve set for log shipment to the primary server to the secondary server(s) when the primary server fails on a disaster.
• Failover does not work for log shipping.
• SQL Server Express edition does not support log shipping.

Benefits of Log Shipping

• Easy setup and maintenance.
• Provides both Disaster Recovery and High Availability solutions.
• Low maintenance.
• Multiple standby servers can be configured.
• Standby databases can be available for read-only queries
• From SQL Server 2008 Log Shipping technology is available in all later SQL Server Version except express edition.
• Log Shipping allows the auto-update of the schema(table, views, etc.) on a secondary server.
• Let’s see how the log shipping technology works in the following diagram.

Development/Tested Environment Details

Note
Though, Here we’ve used different editions of SQL Server (Enterprise & Developer) but it is good practice to keep the same version & same edition of SQL Server in both/all server(s).

Configure SQL Server Log Shipping(Without active directory)

Follow the steps one by one to configure SQL Server Log Shipping(Without active directory).

Step 1

Create a folder on your Primary Server. Then go to Properties > Sharing Tab > Click On Share > Select Everyone > Click On Add > Give it Read/Write Permission > finally click on Share.

Step 2
Do the same thing on your Secondary Server as in Step 1.

Step 3
As we’re going to setup Log Shipping without using Active Directory (AD), To do that we need to create a user with the same name and password in both servers (Primary & Secondary), so that we can use them for SQL Server Service & SQL Server Agent Service read/write permission. First create a new user on your Primary Server.

To create a new User go to Start > Settings > Accounts

The following screen will appear. Then follow the following steps

Now we’ll create a user without a Microsoft account.

Provide the required field and click on Next Button. Hope User will be created.

Step 4
Do the same thing on your Secondary Server as in Step 3.

Note: Keep the user name & password the same as Primary Server you’ve saved.

Step 5
Now we’ll give MSSQLSERVER / SQL Server(Version) and SQL Server Agent (Version) permission/log on with our recently created user in both (primary & secondary) servers. For doing that follow the following steps. Go to Run (Windows Key + R) and Type services.msc as like the following image.

Then following window/screen will be arrived. Then Select SQL Server(MSSQLSERVER) > Right-click on that > Click On Properties as following image.

When you click on Properties following screen will come up on your screen. Click on Log On tab & then click on the Browse as per the following image(s).

Step 6
Do the same thing on your Primary Server for SQL Server Agent as in Step 5.

Step 7
Do the same thing on your Secondary Server as Step 5 & Step 6.

Note: Keep the user name & password the same for SQL Server service log on as Primary Server you’ve saved. And also make sure MSSQLSERVER & SQL Server Agent Service is running on your both server. For confirmation, you may restart these services in primary

Step 8
We’re almost done with all other environment settings except SQL Server. Now log on your primary server(SQL Server) with a user(Like: user = ***** , Password= *****).
Note: Your login user must have the sysadmin role. Right Click on your desired Database and click on Properties.

Step 9
Now take a full backup of that database. And take the backup file to the secondary server & restore it as the following image. Here I’m not showing the full backup process but after selecting the database (.bak) file, you’ve to go Option > Then select “RESTORE WITH NORECOVERY”.

Step 10
Now we’ll create the main things/configurations for our desired log shipping. Go to the primary server and select your database. Now follow these following images one by one.

After clicking on Properties following screen will be open. Now follow the marked serial.

After Clicking on Backup Settings following screen will appear. Now follow the marked step one by one as a mentioned serial.

When you click on the Schedule button following screen will appear. Here you can set up the necessary configuration(s). Here we’ll change only the marked field value.

After setting(s) all changes/configurations click OK. Then the following screen will appear. Now click OK.

After clicking on OK from the previous screen the following screen will open. Now Click On Add as marked in the following screen.

When you click on Add in the previous screen then the following screen will be come-up. Now it’s time to connect with your secondary server(s)/instance(s). Now follow the marked steps & provide the necessary data.

In the following image first tab, we’ll just work on the database restore/initialing process on our secondary server. You know, we’ve already restored our database in our secondary server at Step 9. So we’ll select “No, the secondary database is initialized.” Option and click OK.  Now go to the second tab named “Copy Files”, put your secondary server shared folder network path, And click on the Schedule… button

After clicking on the Schedule… from the Copy Files tab following screen will appear. Change the marked field value as your requirement and leave others as usual if it is not necessary for you. Then click OK.

Now click on the Restore Transaction Log tab Select Standby Mode > Check on “Disconnect users in the database when restoring backups” > Put your delay restoring time (It is optional) > click on Schedule… button

After clicking on Schedule… from the Restore Transaction Log tab following screen will appear. Change the marked field value as your requirement and leave others as usual if it is not necessary for you. Then click OK.

After clicking on OK from the previous screen following image will be open. Our secondary server instance was successfully added. Now click on OK from the following image.

We’ve done all settings & configurations for our desired log shipping feature. When clicking OK from the previous screen then the following screen will open. If you’ve everything as our document, we hope you’ll be able to see the following screen with success a message. Click On the Close button.

Step 11
Congratulations, you’ve successfully configured log shipping. Now we’ll manually test, does our log shipping is working or not as our expectation. For manual testing first go to your Primary Server > Expand SQL Server Agent > Right-click on your Log Ship Backup Job > Click on Start Job at Step.

If everything is fine you’ll get a success message like the bellow image.

Now go to your secondary server. > Expand SQL Server Agent > Right-click on your Log Ship Copy Job > Click on Start Job at Step..

If everything is fine you’ll get a success message like the bellow image.

Tips: For testing data update, you may insert some data on your primary database and then you may follow the step (Step-11) for manual testing.

HostForLIFEASP.NET SQL Server 2019 Hosting

Introduction
In this article, we are going to discuss STUFF and REPLACE functions in SQL Server. Also, we will discuss the differences between both of them and in which scenario it will be used.

We will cover,
    Use Case
    STUFF Function
    Replace Function
    Differences between STUFF and REPLACE

USE CASE
First, let's discuss the below use case,
Suppose we have a requirement to replace part of the string with a new string what you will do?

Your answer would be, REPLACE Function. Right?
Let me make this scenario more complex, suppose you have a string that has multiple words that are the same and you want to replace a word from a specific position. I mean only one word not all. How will you do that?

The answer would be, STUFF function.

Replace function will replace all the words so it will not fit in this case.

This is a difference between STUFF and REPLACE. REPLACE function replace all words from the text and STUFF will Replace word from a specific place.
STUFF Function in SQL Server

In SQL Server, the STUFF function deletes the sequence of the characters from the source string first and then inserts another string, starting at a given position.

Syntax
STUFF( source_string, start_position, length, another_string)

SQL
Source_string

The source string we will modify.
start_position

The start position is to delete characters from the source string.

Length

Number of characters to delete from the source string.

another_string
new string which will insert into the source string

Example 1 – Delete old string and insert a new string
Suppose I have a string "Hello All. Welcome and Nice to see you All in this article", I want to replace first ‘All’ with ‘World’.
SELECT  STUFF('Hello All. Welcome and Nice to see you All in this article',7,3,'World')

OUTPUT

Example 2 – Insert new string
I want to add a new string after "Hello" in the below example hence we have given 0 for length.
SELECT  STUFF('Hello . Welcome and Nice to see you All in this article',7,0,'World')

Example 3 – Format Date
We will change the date format from DDMMYYYY to DD/MM/YYYY using the STUFF function. This will teach you how to add nested STUFF.
SELECT STUFF(STUFF('23112021', 3, 0, '/'), 6, 0, '/') as FormattedDate;

REPLACE Function in SQL Server
The Replace function replace all occurrence within the string with a new string.

Syntax
REPLACE(original string, old_string, new_string)

Original string
This is the original string.

old_string
The string is to be replaced.

new_string
The new replacement string.

Example 4
We have replaced ‘All’ with ‘’world’.
SELECT REPLACE('Hello All. Welcome and Nice to see you All in this article','All','World')

Difference between STUFF and REPLACE
To understand the difference please see example 1 and example 4 or see below code,
SELECT  STUFF('Hello All. Welcome and Nice to see you All in this article',7,3,'World') as STUFFRESULT
SELECT REPLACE('Hello All. Welcome and Nice to see you All in this article','All','World') as REPLACERESULT

OUTPUT

In the above example, you can notice that REPLACE function replaces all the occurrences in the original string, but the STUFF function deletes a substring from the original string and inserts a new string at a given position.

I hope you enjoyed this article and find it useful.

HostForLIFEASP.NET SQL Server 2019 Hosting

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts.

Use SSMS to query, design, and manage your databases and data warehouses, wherever they are – on your local computer, or in the cloud.

In this article, will see how to install Microsoft SQL Server Management Studio (SSMS) on your PC.

Follow the below steps for the same.

Step 1
Click here to navigate to SQL Server Downloads web page.

Step 2
Click on Download SQL Server Management Studio (SSMS).

Step 3
You can see the latest version of the SSMS here.
Under the Download SSMS section, Click on Free Download for SQL Server Management Studio (SSMS) 18.10 to download the executable file.
Note: SSMS version will be changed based on the new releases by Microsoft. Download the latest SSMS as shown in the Download SSMS section.

SSMS-Setup-ENU.exe will be downloaded to your PC.

Step 4
Double click on SSMS-Setup-ENU.exe executable file to start the SSMS installation.

Step 5
Click on Change, if you wish to change the location of the SQL Server.
Otherwise, go to Next Step.

Step 6
Click on Install.

Installation steps are in progress.

SSMS Installation completed successfully.

Step 7
Click on Microsoft button and Search for SSMS. Click on Open.

Microsoft SQL Server Management Studio will be launched on your PC.

Step 8
Provide all the required details and Click on Connect to open your required Database.

Hope you have successfully Downloaded, Installed and Connected to your database in SSMS on your PC.

HostForLIFEASP.NET SQL Server 2019 Hosting

First, let’s create a sample database table, this table should have only one column date. So the scenario is, let’s insert current datetime in table using stored procedure in every one-minute duration from SQL Job.
 
Here is table script,
    USE [AdventureWorks2017]  
    GO  
    /****** Object:  Table [dbo].[SampleDateTime]    Script Date: 10/1/2019 9:08:22 PM ******/  
    SET ANSI_NULLS ON  
    GO  
    SET QUOTED_IDENTIFIER ON  
    GO  
    CREATE TABLE [dbo].[SampleDateTime](  
        [id] [int] IDENTITY(1,1) NOT NULL,  
        [CurrentDateTime] [datetime] NULL,  
     CONSTRAINT [PK_SampleDateTime] PRIMARY KEY CLUSTERED   
    (  
        [id] ASC  
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
    ) ON [PRIMARY]  
    GO  
    Store Proc:  
    SET ANSI_NULLS ON  
    GO  
    SET QUOTED_IDENTIFIER ON  
    GO  
    CREATE PROCEDURE PROC_InsertDateTime  
    AS  
    BEGIN  
        -- SET NOCOUNT ON added to prevent extra result sets from  
        -- interfering with INSERT statements.  
        SET NOCOUNT ON;  
        INSERT INTO dbo.SampleDateTime (CurrentDateTime) VALUES (GETDATE())  
    END  
    GO 

Let’s set up the SQL job now to run this process and schedule it for every 1 minute.
 
Why SQL Job?
A job is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command prompt applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks.
 
Create a SQL Job
 
Step 1
Expand the SQL Server Agent and right click on Jobs and click on New Job

In General tab, Enter job name, owner, category and description.

In Steps tab, click New and enter step name, select Type as Transact-SQL script (T-SQL) and select database and put EXEC procedure name in command area.

From schedules tab, click new button and put schedule name, frequency, daily frequency and duration.
In my job, I have scheduled it for every 1 minute.

Now we are done here with job part, let’s start the job. Right click on job and hit Start Job at Step

As you can see job has been successfully run, now let’s check in database table.
Our expectation is one record will insert in table on every minute.

Here you go, as you can see one datetime entry in inserting in table.
In this article, we have learned how to create a SQL Job and schedule it and run stored procedure.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we are going to configure Microsoft SQL Server on a Windows machine to listen to a specific TCP port. We will enable TCP/IP and Named Pipe and configure firewall rules so that other network computers can communicate with SQL Server instances.

Pre-requisites
Microsoft SQL Server installed on the machine (please check my article to install SQL Server here).

Configure Named pipe and TCP/IP protocols

Let us first configure Named Pipe and TCP/IP for SQL Server. I have installed SQL Server 2019 on my local machine.

Step 1
Open SQL Server Configuration Manager from the Start menu.

Step 2
Let's first enable Named Pipe for the SQL Server Instance (in my case MSSQLSERVER). Go to SQL Server Network Configuration (in Console pane) and click on the Protocols for MSSQLSERVER.

Step 3
In the details pane (right panel), right-click on the Named Pipes protocol, and then click Enable to enable the named pipe for that particular SQL instance.
Configure Named Pipe and TCP/IP Settings of SQL Server

Step 4
Next, we will enable TCP/IP connection for the SQL Server Instance. So right-click on the TCP/IP protocol and click on the Enable option.

Step 5
Now again right-click on TCP/IP protocol, and then click the Properties option.

Step 6
Now you can see the TCP/IP Properties window, click on the Protocol tab, select Yes in Listen All property.

Step 7
Now click on the IP Addresses tab, scroll down to the IPALL section. Now change the value of the TCP Dynamic Ports property to blank. If its value is zero, the Database Engine will listen on dynamic ports. Next, in the TCP Port property, enter the port number you want this IP address to listen on (in my case 1717), and then click the OK button. You can also specify multiple ports by separating them with a comma.

Note
If you want to use individual IP addresses (IPn sections), please set Listen to All property to No in the Protocol tab. Here, I want to use IPAll section, for that reason, I have set Listen All property to Yes in the Protocol tab.

Step 7
The above configurations will only take effect after we restart SQL Server Instance services. So now select the SQL Server Services option from the Console Pane.

Step 8
Now, right-click on the SQL Server (MSSQLSERVER) from the Details Pane and then click the Restart button to restart the SQL Server service.

We have successfully configured an instance of the SQL Server Database Engine to listen on a specific fixed port by using the SQL Server Configuration Manager. Next, we need to configure a firewall so that our specified port (in my case 1717) can be opened in the firewall.
Firewall configurations to allow SQL port

Now that we have configured SQL Server to listen on a specific port, we need to open that particular port in the firewall. Let us see how to configure the firewall.

Step 1
Open Windows Defender Firewall from the Start menu.

Step 2
You will see the Windows Defender Firewall window from Control Panel. Click on the Advanced Settings link from the left panel.

Step 3
Now you will see Windows Defender Firewall with Advanced Security window. Click on the Inbound Rules from the left panel and then click on the New Rule button from the right panel.

Step 4
Now you will see the New Inbound Rule Wizard window. In the Rule Type step, select Port because we want to allow SQL port 1717 from the firewall and click on the Next button.

Step 5
In Protocol and Ports step, select Specific local ports and enter our SQL port 1717 in the textbox and click the Next button.

Step 6
In the Action step, select Allow the connection and click on the Next button.

Step 7
In the Profile step, check all the checkboxes and click on the Next button.

Step 8
In the Name step, specify a meaningful name for the rule and provide a description and click on the Finish button.

Step 9
Now you can see our new rule is listed in the Inbound Rules list.

Excellent! We have successfully configured a firewall to open the SQL port for the database instance connection from outside.

Expected errors/issues
Sometimes you can face some issues/errors in connectivity with SQL Server instances from outside even after the above configurations. Please make sure:

    In SQL Server Configuration Manager, in the console pane, click on the SQL Server Services, make sure SQL Server Browser service is running.
    If you have assigned an SQL port other than 1433, you have to specify the port number in the server name at the time of the SQL connection. For example, cloude-desire-01, 1717 in my case.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we are going to install Microsoft SQL Server on windows machines step by step. In this tutorial, we are going to use SQL Server 2019 Developer edition and Windows Server 2019.

Pre-requisites
    Windows machine having 2 core and 4 GB RAM configuration

Download SQL Server 2019 Developer Edition
First, let’s download SQL Server installation media from the official website.

Step 1
Download installation media from this link.

Step 2
Run the downloaded file and you will see the below screen. Now select the third option – Download Media.

Step 3
Now you will see the below screen. Please select the language you prefer and select the ISO radio button to download the ISO file. In addition, select the download location of your choice. I will go with the default location. Now press the Download button.

Step 4
Now it will start downloading SQL Server installation media. It will take some time based on your internet connection speed.

Step 5
After successful download of installation media, you will see the below screen. Click the Close button.

Install SQL Server 2019 Developer Edition
Now that we have installation media, we can start the installation of the SQL Server. Let’s see how to install SQL Server step by step.

Step 1
Run install media file (ISO file) downloaded in above section by double-clicking on it. It will extract/mount all the contents in a new temporary drive.

Step 2
Once extraction is completed, double click on the setup.exe file and you will see the below screen. Click on the Installation option in the left panel and then click on New SQL Server stand-alone installation or add features to an existing installation option from the right panel.

Step 3
Now you will see the Product Key window. Select the Developer option from the dropdown and click on the Next button.

Step 4
Now you will see the License Terms window. Just select the checkbox and click on the Next button.

Step 5
Now you will see the Microsoft Update window. It is not compulsory to check for the latest updates but it is recommended. So, select the checkbox and click the Next button.

Step 6
Now it will check for updates and install them if any.

Step 7
After that, it will check some rules or prerequisites for the installation of SQL Server. Once all the rules passed, click on the Next button. Sometimes you may face an error at this stage. You can find some known errors at the end of this article.

Step 8
On the Feature Selection window, select features as shown in the below screenshot. You can also change the location for SQL Server instance installation but I will go with the default location. After feature selection please click the Next button.

        Image 1 – Features selection Image 2 - Features selection

Step 9
It will check some feature rules/prerequisites and then you will see the Instance Configuration screen. Here you can choose between Default Instance and Named Instance. Here I will go with Named Instance.

Note
Default Instance
When SQL Server is installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name. For example, HARDIK-PC.

Named Instance
A named instance is identified by the network name of the computer plus the instance you specify during the installation. The client must specify both the server name and the instance name when connecting. For example, HARDIK-PC/MSSQLSERVER.

Step 10
Next, you will see the Server Configuration window. In Service Accounts tab, select Automatic in Startup Type for SQL Server Agent, SQL Server Database Engine, and SQL Server Browser services.

In the Collation tab, select collation as per your preference.
“Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.” – Microsoft.

Step 11
Next, you will see the Database Engine Configuration window. In the Server Configuration tab, choose Mixed Mode in the authentication mode section and enter a strong password. In Specify SQL Server administrators section, your current windows user should already be added automatically. If not, click on Add Current User button.

In the Data Directories tab, specify locations for database files and backup files. By default, it saves all the files on a C drive but it is not recommended to store database files on an OS drive because if any OS-related issue occurs then we may lose our data. Therefore, I choose D drive on my local machine.

In the TempDB tab, there are configurations for the temporary database file(s). There are some best practices on how to configure temporary database files locations, the number of files, and their file sizes. Ideally, the number of the TempDB data files should match the number of logical processors. So I have a number of files to 2. If you are interested in deep dive into TempDB best practices, here is a very good article on it.

Next, in the MaxDOP tab, the maximum degree of parallelism (MAXDOP) is a server configuration option for running SQL Server on multiple CPUs. It controls the number of processors used to run a single statement in parallel plan execution. By default, the setup will suggest value based on the system configuration.

Next, in the Memory tab, we can configure how much memory SQL Server instance can consume. By default, the installation process will recommend you min and max memory allocation based on the system configuration on which it is going install. However, you can change it. Here you can find best practices for SQL Server memory configurations.

In the FILESTREAM tab, leave the checkbox unchecked because we are not going to enable this feature. FILESTREAM, in SQL Server, allows storing these large documents, images, or files onto the file system itself.

Click on the Next button.

Step 12
Next, the setup will check some feature configuration rules, and then the Ready to Install window will appear. This window shows the summary of all the features and configurations which we have done in the above steps. Once review the summary and click on the Install button.

Step 13
Now, the installation will start and it may take some time based on our configurations.

Step 14
After installation, it will show you the list of features and their installation status. If any error occurred, it will show here.

Congratulations! We have successfully installed SQL Server 2019 Developer edition on Windows machines. Next, you can install SQL Server Management Studio to connect SQL Server and query SQL databases. Please check my blog to – Step by step installation of SQL Server Management Studio.

HostForLIFEASP.NET SQL Server 2019 Hosting

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance. There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint.
 
ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.
 
Let’s take a look at the three options.
 
Trace Flag4199
In dealing with any trace flag, it is vital that you understand as to what the trace flag will enable or disable and how it pertains to your individual workloads. Some trace flags are benign, such as TF3226 which removes successful backup messages from the log, others can be quite intrusive and cause havoc. Make sure you review any documentation on the respective trace flag prior to implementation.
 
The official verbiage from Microsoft on trace flag 4199 is,
 
“Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs.
 
QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 being enabled.”
 
What this really implies is that if your database is at the most current compatibility level for your version of SQL server, then the query optimizer will utilize all of the QO hotfixes up to the release to market (RTM) version but nothing beyond the RTM version.
 
For example, if you are running SQL Server 2017 CU20 the database is set at the appropriate compatibility level (140), and TF4199 is disabled, the query optimizer will only utilize any hotfixes for it up to 2017 RTM. Any query optimizer hotfix that was delivered in subsequent cumulative updates will not be utilized.
 
If you enable trace flag 4199, this instructs SQL Server to implement any query optimizer hotfix that was delivered after the RTM version. Note that enabling this trace flag will be applied across all databases. It’s an all or nothing trace flag as most trace flags are.

Sometimes while handling large databases, we get stuck between Truncate and Delete statements. Each one has its pros and cons and it's a really crucial decision to decide which one to use. Before we go ahead and understand the answer to this question, let's first understand the differences between these two statements.

Delete
Syntax

Delete from MST_Team;

1. Delete is a DML command unlike truncate. Truncate is DDL command.
2. Delete statement deletes records row by row and maintains a log of each deleted row in the transaction log.
3. A row lock is applied on the table for each row getting deleted.
4. It can also be used with where clause to delete records based on matching conditions.
5. Identity retains its value even after deletion of records.
6. Delete can be used with indexed views.
7. Delete can also be used with foreign key references, records that are not interlinked with foreign key references will get deleted.
8. Delete uses more transactional space than a truncate statement.

Truncate
Syntax
Truncate table MST_Team;

1. Truncate is a DDL command.
2. Records in the whole table deleted at the same time. A table lock is applied while executing of the truncate statement.
3. No transactional log is maintained while deleting records with Truncate statement however page deallocation log is maintained.
4. The Identity of the column is set to its seed value.
5. Truncate can not be executed with foreign key references also it can not be used with indexed views.
6. No transactional log is maintained so Truncate is faster than Delete and also it uses less transactional space.
7. No where clause can be used, the data of the whole table gets deleted together. We can delete data partially with partitions.

From the above explanation of delete and truncate, we can easily conclude on the below scenarios.

Delete all records from a table: Which statement to use? Truncate/Delete
To delete all records from the table, Truncate is the best option, however, it also has its own limitations as mentioned below.

On the Truncate table the Identity column is set to it's seed value. If you don't want this to happen, then we can not use Truncate even if you want to delete all records.
No transaction log is maintained. If you want to maintain transactional log then we can not use Truncate.
To Truncate tables we need minimum ALTER Table rights on the table. Delete rights aren't sufficient to Truncate the table. Sometimes we may not want to give ALTER rights because of security reasons.
Even if a single record has a foreign key, the Truncate statement won't get executed.

We can use delete as well but it also has its own limitations, as mentioned below:

The Delete statement as mentioned above uses more transactional space.
The Delete statement is slow as it maintains all logs.

Delete a few records from the table: Which statement to use? Truncate/Delete
The answer to this question is very straight forward. It's the "DELETE" statement only, but hold on, there is some trick to delete partial records with Truncate.

We can delete partial data from the table with a Truncate statement using partitions. If logical partitions are applied on a table and if you want to delete a few partitions amongst all of them, then we can do the same.

We can import the .NET DLL or group of DLLs into SQL Server. Once the DLL is imported then, we can access the methods in the DLL inside the Stored Procedure, User-Defined function, or executed via TSQL. It is called CLR Assembly. In this article, we are going to explore how to create a custom CLR assembly and how to access it in the SQL Server.

CLR Assembly
An assembly is DLL files that are written by one of the managed code languages hosted by the Microsoft.NET Framework common language runtime (CLR) and it can be used in the stored procedures, triggers, user-defined functions, etc.
An assembly is a good option to expand the native functionality of the SQL server.

How to Create CLR Assembly
Create the Class Library using C#
Create Assembly from File
Create User-Defined Function for Access the Assembly
Invoke User-Defined Function

Create the Class Library using C#
Consider the below "DateConvertion" class which has the "DateConvert" function. It is used to convert date strings to the required format.
Both class and function are static and public.
I have used .NET Framework 4.5 and SQL server 2012. Because each Microsoft SQL Server is statically linked to a particular version of the CLR (not to a particular version of the .NET Framework).
SQL Server 2005, 2008, and 2008 R2 are linked to CLR version 2.0 which handles .NET Framework versions 2.0, 3.0, and 3.5, while SQL Server 2012 and 2014 are linked to CLR version 4.0 which handles .NET Framework versions 4.0, 4.5.x, 4.6.x, etc.
using System;
using System.Globalization;

namespace CLRDateConvertAssembly
{
    public static class DateConvertion
    {
        public static string DateConvert(string date, string format)
        {
            try
            {
                CultureInfo provider = new CultureInfo("en-US");

                string[] inputFormats = {
                "MM/dd/yyyy", "M/d/yyyy", "M/dd/yyyy","dd/MM/yyyy",
                "MM/d/yyyy", "M/d/yyyy h:mm:ss tt", "MM/d/yyyy h:mm:ss tt",
                "M/d/yyyy h:mm tt", "MM/dd/yyyy hh:mm:ss", "M/d/yyyy h:mm:ss",
                "M/d/yyyy hh:mm tt", "M/d/yyyy hh tt", "M/d/yyyy h:mm", "M/d/yyyy h:mm",
                "MM/dd/yyyy hh:mm", "M/dd/yyyy hh:mm",
                "MM-dd-yyyy", "M-d-yyyy", "M-dd-yyyy","dd-MM-yyyy",
                "MM-d-yyyy", "M-d-yyyy h:mm:ss tt", "MM-d-yyyy h:mm:ss tt",
                "M-d-yyyy h:mm tt", "MM-dd-yyyy hh:mm:ss", "M-d-yyyy h:mm:ss",
                "M-d-yyyy hh:mm tt", "M-d-yyyy hh tt", "M-d-yyyy h:mm", "M-d-yyyy h:mm",
                "MM-dd-yyyy hh:mm", "M-dd-yyyy hh:mm"};

                DateTime dt = DateTime.ParseExact(date, inputFormats, provider, DateTimeStyles.None);

                return dt.ToString(format);
            }
            catch
            {
                return string.Empty;
            }
        }
    }
}

Sign the project using an asymmetric key.
Right, Click the Project -> Properties.
Go to the "Signing" Tab.
Check the "Sign the assembly" check box.
Select the "New" option in the "strong name key file" drop-down.

Enter the key file name and uncheck the password option and click "OK".

Build the project and copy the "CLRDateConvertAssembly.dll" file into the required folder.

Create Assembly from File
To create an assembly, we need to follow the below steps. Execute steps 1,2,3 queries are in the "master" database and step 4 and 5 in our database.

Step 1
Create Asymmetric Key from Assembly File.

USE master;
GO
CREATE ASYMMETRIC KEY CLRDateConvertionKey FROM EXECUTABLE FILE = 'D:\GC\Blog\Content\Assembly\DLL\CLRDateConvertAssembly.dll';
GO

Step 2
Create SQL Server Login linked to the Asymmetric Key.
USE master;
GO
CREATE LOGIN CLRDateConvertionKeyLogin FROM ASYMMETRIC KEY CLRDateConvertionKey;
GO

Step 3
Grant UNSAFE assembly permission to the login created.
USE master;
GO
GRANT UNSAFE ASSEMBLY TO CLRDateConvertionKeyLogin;
GO

Step 4
Create a SQL Server database user for the SQL Server login created.
USE TestDB;
GO
CREATE USER CLRDateConvertionKeyLogin FOR LOGIN CLRDateConvertionKeyLogin;
GO

Step 5
Create CLR Assembly.
USE TestDB;
GO
CREATE ASSEMBLY DateConvertAssembly FROM 'D:\GC\Blog\Content\Assembly\DLL\CLRDateConvertAssembly.dll' WITH PERMISSION_SET = SAFE;
GO

When creating an assembly in the MS SQLServer database, you can specify one of three different levels of security.
SAFE
EXTERNAL_ACCESS
UNSAFE

SAFE is the default permission set and works for the majority of scenarios.

EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables.

UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.

Any one of the below conditions must be met for creating an EXTERNAL_ACCESS or UNSAFE assembly.

The assembly is a strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).
The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.

We have created the assembly with SAFE permission. So no need to disable the CLR Strict Security feature and enable the Database Trustworthy feature in the MS SQL Server.

Create User-Defined Function for Access the Assembly
Create the user-defined function for consuming the above assembly. I have created below "dbo.DateConvert" function.
CREATE FUNCTION dbo.DateConvert (@date NVARCHAR(100), @format NVARCHAR(100))
RETURNS NVARCHAR(100)
AS
     EXTERNAL NAME [DateConvertAssembly].[CLRDateConvertAssembly.DateConvertion].[DateConvert];
GO

Invoke User-Defined Function
Call the above user-defined function to consume the assembly for date conversion.
SELECT dbo.DateConvert ('10/01/2020', 'dd-MMM-yyyy') as [Date];

If you want to drop an assembly, run the below query.
Use TestDB;
GO
Drop ASSEMBLY DateConvertAssembly;
GO

I hope you have liked this article and know about CLR assembly in MS SQL Server.

HostForLIFEASP.NET SQL Server 2019 Hosting