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

In this article, you will see the Complete Reference - Table Scan, Index Scan, and Index Seek in MSSQL.

Background
While working with MS SQL database and SQL, a good knowledge of how the index works and how to use them to improve the SQL query performance is very important.

Prerequisites
Preliminary understanding is required about what the index is and how to create and drop an index.

Difference between Scan and Seek Operation

Table and Index Access
Now, let us understand each one, one by one, with practical examples.

Table Scan

• It is a very simple process. While performing table scan, the query engine starts from the physical beginning of the table and it goes through every row into the table. If a row matches with the criteria then it includes that into the result set.
• It is the fastest way to retrieve the data especially when there is quite a small table.
• For a small table, a query engine can load all the data in a one-shot but from a large table it is not possible i.e. more IO and more time will be required to process those large data.
• Generally, a full table scan is used when a query doesn’t have a WHERE clause i.e. all data.

For example, an Employee table with no index and the following query will use the Table scan.
SELECT * FROM Employee

Index Scan

• When you have a clustered index and your query needs all or most of the records (i.e. query without where or having clause) then it uses an index scan.
• Index scan works similar to the table scan during the query optimization process. The query optimizer takes look at the available index and chooses one of the best, based on JOINs and WHERE clauses.
• As the right index is being chosen, the SQL query processing engine will navigate the tree structure to the pointer of the data which matches the criteria and further extracts only the needed/required records.
• The key difference between Table Scan and Index Scan is that data is stored in the index tree, the query processor knows it when reaches the end of the current it is looking for. Then it can send the query or move on to the next range of data.
• An index scan is slightly faster than the Table scan but significantly slower than an Index.

For example, Employee table with clustered index and the following query will use the Index scan,

SELECT * FROM Employee

Index Seek

• When the search criterion matches the index well enough which can navigate directly to particular points into the data, this is known as the Index seek.
• The index seeks the fastest way to retrieve the data in the database.
• For example, the following query will use the Index seek which can be confirmed by checking the execution plan of the query
• The query optimizer can use an index directly to go to the 3rd employee id and fetch the data.

Query execution plan can show the same as it uses an index seek through created EmployeeId index,

SELECT name FROM Employee WHERE id=5

Difference between Table Scan, Index Scan and Index Seek

Now, I hope you understand the key important things about Table Scan, Index Scan, and Index Seek in MSSQL.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will see 'how to create a SQL Server Database backup'. Database backup using SQL Server Management Studio, SQL Server provides a large number of options to create backups. We will discuss here FULL BACKUP and DIFFERENTIAL BACKUP.
 
FULL BACKUP
The most common type of backup is the full backup. In this type of backup, it creates a full backup of your database and also includes the transaction logs into the backup.
 
We can create it by using SSMS (SQL Server Management Studio).
 
Let's see how.
 
Full Backup using T-SQL (Transact-SQL)
This will create a full (Complete) backup into the .bak file of the given database,
BACKUP DATABASE DotNet TO DISK='D:\DotNet\Backup\DotNet.BAK'
 
Full Backup using SSMS
Open your SSMS and select the desired database and right-click on the database name click on Tasks > Back Up,

Select Full as Backup Type, select Disk as the destination, and then click on Add button to add the directory where the backup will be stored on your disk.

Select the destination for the backup,

Click on ok and again ok on the next screen and the backup progress will be started.

This process will take some time that depends upon your database size.
 
DIFFERENTIAL BACKUP
Another option to create a backup is Differential backup. Differential Backup is the backup of the changes that have been made after a recent full/ complete database backup.
 
Let's see how.
 
Differential Backup using T-SQL (Transact-SQL)
This will create a differential backup with .DIF file extension of the given database,
 
BACKUP DATABASE DotNetKida TO DISK='D:\DotNet\Backup\DotNet.DIF' WITH DIFFERENTIAL
 
Differential Backup using SSMS
Open your SSMS and select the desired database and right-click on the database name click on Tasks > Back Up,

Select Differential as Backup Type, select Disk as the destination, and then click on Add button to add the directory where the backup will be stored on your disk.

Select the destination for the backup,

Click on ok and again ok on the next screen and the backup progress will be started.

A binary value or type in SQL Server is a series of bytes (known as a byte array in some programming languages). Just like char/varchar, there are fixed-length types, binary(1-8000), and variable-length ones, varbinary(1-8000) and varbinary(max).
Sometimes we store this type of data in SQL Tables and lose the source files. This type of data is stored in a binary format which is system formatted.
 
A PDF file is converted and stored in this format. And the text looks similar to this,
 "0x255044462D312E340A25C3A4C3BCC3B6C39F0A322030206F626A0A3C3C2F4C656E67746820332030
20522F46696C7465722F466C6174654465636F64653E3E0A73747265616D0A789C358CBD1240401083FB
7D8AD48A75B71CABD728551EC0F8291C43E3F5ED0D26453"
 
Now to recreate a file from this data is not something that can be created by simple copy-paste.
 
Here is the process which can be used to get the source file.
DECLARE @outPutPath varchar(50) = 'C:\ExtractedFiles'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max)
, @folderPath  varchar(max)

--Get Data into temp Table variable so that we can iterate over it
DECLARE @Doctable TABLE (id int identity(1,1), [FileName]  varchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable( [FileName],[Doc_Content])
Select [RecordID],[FileUpload] FROM  [dbo].[tbl_AuthorConferenceList]

--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable

WHILE @i >= 1
BEGIN

       SELECT
        @data = [Doc_Content],
        @fPath = @outPutPath +  '\' +[FileName] +'.pdf',
        @folderPath = @outPutPath
       FROM @Doctable WHERE id = @i

  --Create folder first

  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance created
  EXEC sp_OASetProperty @init, 'Type', 1;
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources

  print 'Document Generated at - '+  @fPath

--Reset the variables for next use
SELECT @data = NULL
, @init = NULL
, @fPath = NULL
, @folderPath = NULL
SET @i -= 1
END

Now you might end up getting compilation issues.
 
The reason is all the System Stored Procedures are unavailable.
To solve this we have to enable the Facets Property "OleAutomatedEnabled"

HostForLIFEASP.NET SQL Server 2019 Hosting