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