This article was initially written in 2021, we try to make it done now.  The content is mainly based on the MS article Table-Valued Parameters with some understanding and explanation, and with some examples to demo the results.

Introduction
Table-valued parameters were introduced to SQL Server in 2008. Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data.

This is the structure of this article,
    Introduction
    A - Passing Multiple Rows in Previous Versions of SQL Server
    B - What Table-Parameters is
    C - Passing a user-defined table type to a Stored Procedure in SQL Server
    D - Passing a user-defined table type to a Stored Procedure from C# Code

A - Passing Multiple Rows in Previous Versions of SQL Server
Before table-valued parameters were introduced to SQL Server 2008, the options for passing multiple rows of data to a stored procedure or a parameterized SQL command were limited. A developer could choose from the following options for passing multiple rows to the server:

    Use a series of individual parameters to represent the values in multiple columns and rows of data.
    Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement.
    Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter.
    Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table.

The disadvantages of all methods above at least include one that the server side processing is necessary for them.

B - What Table-Parameters is
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. Table-valued parameters are strongly typed and their structure is automatically validated. The size of table-valued parameters is limited only by server memory.

There are several limitations to table-valued parameters:

• You cannot pass table-valued parameters to CLR user-defined functions.
• Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server does not maintain statistics on table-valued parameters.
• Table-valued parameters are read-only in Transact-SQL code. 
• You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

C - Passing a user-defined table type to a Stored Procedure in SQL Server
1. Creating Table-Valued Parameter Types
Table-valued parameters are based on strongly typed table structures that are defined by using Transact-SQL CREATE TYPE statements. You have to create a table type and define the structure in SQL Server before you can use table-valued parameters in your client applications. We use database Northwind.
Use Northwind

CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, CategoryName nvarchar(50) )

In the Microsoft SQL Server Management Studio, we can see the created type:
Database->Programmability->Types->User Define Table Types:

2. Creating Stored Procedures in SQL Server using the Table-valued Parameters Type
Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement.

Update
CREATE PROCEDURE usp_UpdateCategories
    (@tvpEditedCategories dbo.CategoryTableType READONLY)
AS
BEGIN
    SET NOCOUNT ON
    UPDATE dbo.Categories
    SET Categories.CategoryName = ec.CategoryName
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
    ON dbo.Categories.CategoryID = ec.CategoryID;
END

Note: that the READONLY keyword is required for declaring a table-valued parameter.

3. Run the Stored Procedure with Table-Valued Parameters (Transact-SQL)
Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement.

Table Categories --- Before:

Run the Stored Procedure with Table-Valued Parameters:

DECLARE @tvpUpdateCategories AS dbo.CategoryTableType
INSERT INTO @tvpUpdateCategories([CategoryID], [CategoryName]) VALUES(8,'SeaFood1')
EXEC  dbo.usp_UpdateCategories @tvpUpdateCategories

Table Categories --- After

D - Passing a Table-Valued Parameter to a Stored Procedure from C# Code
We will skip this part, you may see the detailed implementation from the bottom on Table-Valued Parameters.

Note [ref]:
Normally we provide DbType of SqlParameter for a normal parameter like varchar, nvarchar, int, and so on as in the following code.
SqlParameter sqlParam= new SqlParameter();
sqlParam.ParameterName = "@StudentName";
sqlParam.DbType = DbType.String;
sqlParam.Value = StudentName;

But in the case of a Table parameter, we do not need to provide a DbType as the parameter data type. We need to provide SqlType rather than DbType, such as
SqlParameter Parameter = new SqlParameter;
Parameter.ParameterName = "@PhoneBook";
Parameter.SqlDbType = SqlDbType.Structured;
Parameter.Value = PhoneTable;

HostForLIFEASP.NET SQL Server 2019 Hosting

Quick Tip
Remove CONVERT/CAST from your WHERE clauses and JOINS when comparing to variables of different data types. Set their data types to match your table definitions before using them as a filter. Optimizing your queries this way will greatly reduce the amount of CPU time, reads, and I/O generated in your queries and allow your code to take better advantage of indexes.

Example
We are going to create a very simple stored procedure called ConvertExample. In this procedure we will see two things. One, the first procedure we create will declare two variables as VARCHAR( MAX) data types, then in the WHERE clause it will convert a table column called Modified Date and compare the variables to that value. You’ll note using SET STATISTICS IO, TIME ON, this code takes considerably more CPU time compared to our second example that will CONVERT the variables first then compare it to a field without having to CONVERT in the WHERE clause.
USE [AdventureWorks2017]
GO
CREATE OR ALTER PROCEDURE [dbo].[ConvertExample]
(
       @fromDate AS VARCHAR(MAX),
       @toDate AS VARCHAR(MAX)
)
AS
BEGIN
SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]
  WHERE CONVERT(varchar(8), [ModifiedDate],112) BETWEEN @fromDate and @toDate
END
SET STATISTICS IO,TIME ON
GO

Now, turn on Actual Execution Plans and execute the procedure.
Execute dbo.[ConvertExample] '20110501','20110531'

Query Plan and Statistics IO, TIME results

This code generates a warning on our SELECT and generates 219ms CPU time. Also note the estimated number of rows 10,918. Now let’s rewrite the code by setting the variables to match the datatype of the field we want to filter on, Modified Date, which is a datetime.

--------------------------------------------------------------------------
--REWRITE Convert Variables Instead, and REMOVE CONVERT from WHERE Clause
-----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [dbo].[ConvertExample]
(
       @fromDate AS VARCHAR(MAX),
       @toDate AS VARCHAR(MAX)
)
AS
BEGIN
SET @fromDate= CONVERT(dateTime, @fromDate)
SET @toDate= CONVERT(dateTime, @toDate)
SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2017].[Sales].[SalesOrderDetail]
  WHERE [ModifiedDate] BETWEEN @fromDate and @toDate
END
-----------------------------------------------------------------------------
--RERUN The Proc
-----------------------------------------------------------------------------
Execute dbo.[ConvertExample] '20110501','20110531'

Query Plan and Statistics IO, TIME results for the second version.

Note the large difference in CPU time, it drops from 219ms to 15ms. You’ll also note the type conversion warning is gone from our SELECT statement in the plan and the estimated number of rows is now 356 instead of 10918. Lastly, we also now have a missing index warning—with the conversion in place the query optimizer was unable to identity the missing index.

Now for fun let’s add an index on Modified date using the code below. Then rerun the old procedure and compare it to the new procedure. You may be surprised on what you see.

USE [AdventureWorks2017]
GO
CREATE NONCLUSTERED INDEX [IDX_SalesOrderDetail_Modifed Date] ON [Sales].[SalesOrderDetail]
(
       [ModifiedDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
GO

Now, Recompile the first version of the procedure, then Execute and look at the plan. Then Recompile the 2nd version and Execute and compare. Here is the original run. Note it uses a Clustered Index Scan and bypasses our index.

Now let’s look at the new one. You’ll see that by removing the CONVERT for every row it compares, it now can take advantage of the index we created. You’ll also note there is now a Key Lookup, take a look at this blog to find out how you can further optimize this query by getting rid of the Key Lookup.

This was just a quick tip and reminder that whenever possible you should remove CONVERTS\CASTS from your WHERE clauses and set the variables to proper data types instead. This same logic applies to JOINS. Do this not only to reduce the CPU time, I/O and reads, but to also take advantage of your indexes.

HostForLIFEASP.NET SQL Server 2021 Hosting

In many situations, we need to create a database at runtime. A few years ago I worked on a project which was a multi tenant system where we create a new database for each tenant. For this task we followed a few steps like creatoing a blank database, executing schema script (tables, functions, stored procedures, views, triggers, etc), then executing master data. In this process there are 2 major issues we faced, one is it takes time to execute all this from application, as connection should be live while doing these steps, and exception handling and rollback.

Basically, we need to understand how a database gets created. Whenever we create a new database in SQL Server it creates a copy of model database from system databases. It means that whatever there in model database will get replicated to the newly created database, like whole schema and data inside all tables.

To resolve this problem we have a solution which is very easy and less effort to fulfill this requirement. In SQL Server we have 4 default DBs inside System Databases. We need to use model DB for this. You can see this in below image,

We can add table schema, stored procedure, views, functions, and so on. I added one table and one stored procedure in model database as shown below,

Now when you create a new database whether it is from SSMS or from an application, it will replicate model db.

Now you can see model schema got replicated to newly created Database.

Here you can see one table with data, and one stored procedure got created. This is what I tried from SSMS, now we can check with C# code. please refer below,

And same DB got created as below,

This way we can use model database to generate pre-defined schema and data.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, you will learn the SQL command categories and their sub-categories. SQL is an open-source data management system. The SQL query is used to retrieve and manipulate the data from the table. With the help of SQL command we can query, filter, sort, join, group and modify the data in the database.
SQL Commands

SQL commands are categorized into below 5 categories:

• DDL – Data Definition Language
• DQL – Data Query Language
• DML – Data Manipulation Language
• DCL – Data Control Language
• TCL - Transaction Control Language

DDL (Data Definition Language)
DDL or Data definition language is actually the definition or description of the database structure or schema, it won't change the data inside the database. Create, modify, and delete the database structures, but not the data. Only These commands are not done by all the users, who have access to the database via an application.

CREATE
Create the database or its object (ie table, index, view, function etc.).

CREATE DATABASE databasename

Example
CREATE DATABASE Student_data;

Syntax
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Example
CREATE TABLE Student (
   StudendId int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    Mark int
);

DROP
Drop command helps to delete the object from the database (ie table, index, view, function, etc.).
DROP object object_name

Example
DROP TABLE Student;

DROP DATABASE database_name

Example
DROP DATABASE Student_data;

ALTER
Alter command is helpful to change or modify the structure of the database or its object.
ALTER TABLE table_name
ADD column_name datatype

Example
ALTER TABLE Student
ADD Total int;

ALTER TABLE table_name
DROP COLUMN column_name

Example
​​​​​​​ALTER TABLE Student
DROP COLUMN Mark;

1) SQL Server / MS Access
ALTER TABLE table_name
ALTER COLUMN column_name datatype

Example
ALTER TABLE Student
ALTER COLUMN Total Varchar(255);

2) My SQL / Oracle (prior version 10G)
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Example
ALTER TABLE Student
MODIFY COLUMN Total Varchar(255);

3) Oracle 10G and later
ALTER TABLE table_name
MODIFY column_name datatype

Example
​​​​​​​ALTER TABLE Student
MODIFY Total Varchar(255);

TRUNCATE
Truncate command helps to remove all records from a table.
TRUNCATE TABLE  table_name

Example
TRUNCATE TABLE  Student;

COMMENT
Comment is helpful to add comments to the data dictionary."--" is used to comment the notes.
--(notes,examples)

Example
--select the student data
SELECT * FROM Student;

RENAME
Rename is helpful to rename an object existing in the database.
1) PostgreSQL
ALTER DATABASE "Old_DatabaseName" RENAME TO "New_DatabaseName";

Example
ALTER DATABASE "Student_data" RENAME TO "Employee_data";

2) MySQL
Example
SQL Command for Dump copy
mysqldump -u username -p"password" -R testDb > testDb.sql;

SQL Command for creating new DB
mysqladmin -u username -p"password" create testDB1;

SQL Command for Import
mysql -u username -p"password" testDb1 < testDb.sql;

Also for Unix, database names are case-sensitive

3) SQL Server
In SQL Server we can rename the database through server application, by right click the existing database and renaming it.

DQL (Data Query Language)
DQL or data query language is to perform the query on the data inside the schema or object (ie table, index, view, function, etc). With the help of DQL query we can get the data from the database to perform actions or operations like analysing the data.

SELECT
Select query on a table or tables to view the temporary table output from the database.
Select * from Table_Name;

Example
Select * from Student;

DML(Data Manipulation Language)
DML or Data Manipulation Language is to manipulate the data inside the database. With the help of DML commands, we can insert, delete, change the data inside the database.

INSERT
Insert command is helpful to insert the data into a table.

1) All the column names are mentioned in the insert statement.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)

Example
INSERT INTO Student (StudendId, FirstName, LastName)
VALUES (12345, "Sri", "Durga");

2) Column names do not need to mentioned in the query, Values should be given in the order according to the column.
INSERT INTO table_name
VALUES (value1, value2, value3, ...)

Example
INSERT INTO Student
VALUES (12345, "Anna", "Marry");

UPDATE
Update command is helpful to update the existing data in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition

Example
UPDATE Student
SET FirstName = "Peter" , LastName = "Scott"
WHERE StudentId=12345;

DELETE
Delete command helps to delete the records from a database table.

Syntax
DELETE FROM table_name WHERE condition;

Example
DELETE FROM Student WHERE StudentId=12345;

LOCK
Lock command is helpful to lock the table to control concurrency.
LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE

Example
LOCK TABLE Student IN SHARE MODE;

CALL
Call command is helping to Call a PL/SQL or JAVA subprogram.
EXEC SQL
 CALL GETEMPSVR (2, NULL)
END-EXEC

EXPLAIN PLAN
It describes the access path to the data.
EXPLAIN PLAN FOR
SELECT Column_name FROM table_name

Example
EXPLAIN PLAN FOR
SELECT last_name FROM Student;

SQL
This query explanation will be stored in the PLAN_TABLE table. We can then select the execution plan to review the queries.
DCL (Data Control Language)

DCL or Data Control Language is to provide rights, permissions, and other controls of the database system.

GRANT
GRANT command is helpful to provide privileges to the database.
GRANT privileges_names ON object TO user

Example
GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name'@'localhost;
GRANT ALL ON Users TO 'Name'@'localhost;
GRANT SELECT  ON Users TO '*'@'localhost;

GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user

REVOKE
Revoke command is to withdraw the user’s access privileges given by using the GRANT command.

REVOKE privileges ON object FROM user

Example
REVOKE SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name'@'localhost;

REVOKE ALL ON Users TO 'Name'@'localhost;

REVOKE SELECT  ON Users TO '*'@'localhost;

REVOKE EXECUTE ON [ PROCEDURE | FUNCTION ] object FROM user
TCL (Transaction Control Language)

TCL or Transaction Control Language happens to a transaction in the database.

COMMIT
Commit command is to commit Transaction after insert or delete in the database.
Commit;

Example
DELETE from Student where Student_Id = 12345;
COMMIT;

ROLLBACK
Rollback command is to rollback a transaction in case of any error occurs.
Rollback;

Example
DELETE from Student where Student_Id = 12345;
ROLLBACK;

SAVEPOINT
Savepoint command is to Set a savepoint within a transaction. If transaction happens in big data, then for checking and rollup can't do it with all the data, to rollback the small part of the data we use savepoint query.

SAVEPOINT savepoint_name

SET TRANSACTION
Set command is to Specify the characteristics of the transaction.

SET TRANSACTION Access NAME transaction_name

In this article, let’s learn how to convert SQL Server data to JSON format. JSON format has become a standard way to represent data objects into strings. JSON format is commonly used in APIs to trasfer data from one application to other via APIs.

You can convert a SQL query results in JSON format in SQL Server by simply adding FOR JASON clause to the query. FOR JASON is used with PATH and AUTO
SELECT name, surname
FROM emp
FOR JSON AUTO;

Here is a simple SQL query on Northwind database that returns 10 orders from the Orders table.
SELECT TOP (10) [OrderID]
      ,[OrderDate]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]

The output in SSMS looks like this.

Now, let’s add FOR JASON PATH clause at the end of the SQL query.
SELECT TOP (10) [OrderID]
      ,[OrderDate]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]
  FOR JSON PATH;

The new output looks like this that is a JSON object.
[{"OrderID":10248,"OrderDate":"1996-07-04T00:00:00","ShipName":"Vins et alcools Chevalier","ShipAddress":"59 rue de l'Abbaye","ShipCity":"Reims","ShipPostalCode":"51100","ShipCountry":"France"},{"OrderID":10249,"OrderDate":"1996-07-05T00:00:00","ShipName":"Toms Spezialitäten","ShipAddress":"Luisenstr. 48","ShipCity":"Münster","ShipPostalCode":"44087","ShipCountry":"Germany"},{"OrderID":10250,"OrderDate":"1996-07-08T00:00:00","ShipName":"Hanari Carnes","ShipAddress":"Rua do Paço, 67","ShipCity":"Rio de Janeiro","ShipPostalCode":"05454-876","ShipCountry":"Brazil"},{"OrderID":10251,"OrderDate":"1996-07-08T00:00:00","ShipName":"Victuailles en stock","ShipAddress":"2, rue du Commerce","ShipCity":"Lyon","ShipPostalCode":"69004","ShipCountry":"France"},{"OrderID":10252,"OrderDate":"1996-07-09T00:00:00","ShipName":"Suprêmes délices","ShipAddress":"Boulevard Tirou, 255","ShipCity":"Charleroi","ShipPostalCode":"B-6000","ShipCountry":"Belgium"},{"OrderID":10253,"OrderDate":"1996-07-10T00:00:00","ShipName":"Hanari Carnes","ShipAddress":"Rua do Paço, 67","ShipCity":"Rio de Janeiro","ShipPostalCode":"05454-876","ShipCountry":"Brazil"},{"OrderID":10254,"OrderDate":"1996-07-11T00:00:00","ShipName":"Chop-suey Chinese","ShipAddress":"Hauptstr. 31","ShipCity":"Bern","ShipPostalCode":"3012","ShipCountry":"Switzerland"},{"OrderID":10255,"OrderDate":"1996-07-12T00:00:00","ShipName":"Richter Supermarkt","ShipAddress":"Starenweg 5","ShipCity":"Genève","ShipPostalCode":"1204","ShipCountry":"Switzerland"},{"OrderID":10256,"OrderDate":"1996-07-15T00:00:00","ShipName":"Wellington Importadora","ShipAddress":"Rua do Mercado, 12","ShipCity":"Resende","ShipPostalCode":"08737-363","ShipCountry":"Brazil"},{"OrderID":10257,"OrderDate":"1996-07-16T00:00:00","ShipName":"HILARION-Abastos","ShipAddress":"Carrera 22 con Ave. Carlos Soublette #8-35","ShipCity":"San Cristóbal","ShipPostalCode":"5022","ShipCountry":"Venezuela"}]

Now, you can use this same return value from SQL query in your application to read JSON objects in your code.

Using the same method, you can convert a SQL Server Table to JSON by using a SELECT * or SELECT column names query on the entire table. The following SQL query converts all rows of a SQL Server table to a JSON string.
SELECT [OrderID]
      ,[OrderDate]
      ,[ShipName]
      ,[ShipAddress]
      ,[ShipCity]
      ,[ShipPostalCode]
      ,[ShipCountry]
  FROM [Northwind].[dbo].[Orders]
  FOR JSON PATH;

Here is a detailed article on JSON in SQL Server with various options.

HostForLIFEASP.NET SQL Server 2019 Hosting

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SQL Server Integration Services (SSIS) expressions can help make SSIS packages more dynamic.

In general, expressions in SSIS is a combination of literals, functions, operators that yields a single data value.

An expression can be composed of a single value (“abc”) or a variable (@[User::FilePath]) or a function (GETDATE()), or it can be more complex and contains some conditionals (CASE statement) or some mathematical operations or some string manipulation.

Here I am describing how we can convert the datetime into string with SSIS expression.

There are multiple ways to convert date to string. The two ways are listed below.
(DT_WSTR, 4)(YEAR(@[User::PositionDateToProcess])) + "-"+
(DT_WSTR, 2)(MONTH(@[User::PositionDateToProcess])) +"-"+
(DT_WSTR, 2)(DAY(@[User::PositionDateToProcess]))

2. SUBSTRING((DT_STR,30, 1252) GETDATE(), 1, 10)

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will learn about how to create a new database with the help of an existing database in same Microsoft SQL Server through the help of Procedure in Local Database.

In this article, I explain the process to create the procedure by two methods.

    HardCode
    Dynamically

The below code is of Stored Procedure for creating New Copy Database with New Name in the same server,
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_post_new_database]
     (
   @DatabaseName varchar(100) = 'CopyDatabase',  --Name of New Database which we going to create
   @CopyDatabase varchar(100) = 'OriginalDatabase'    --Name of Database
)
AS
BEGIN
DECLARE  @BackupDataFileNameAndPath VARCHAR(MAX)=''
DECLARE  @OpenDataFileNameAndPath VARCHAR(MAX)=''
DECLARE  @PathBackUpDatabase VARCHAR(MAX) = 'D:\db\'  -- We also make it dynamic ,for dynamic we need pass parameter to Our Store Procedure
DECLARE  @OpenPathDatabase VARCHAR(MAX) = 'D:/db/' -- We also make it dynamic ,for dynamic we need pass parameter to Our Store Procedure
DECLARE  @fileExtenion_mdf VARCHAR(MAX)=''
DECLARE  @fileExtenion_ldf VARCHAR(MAX)=''
DECLARE  @Open_ldf VARCHAR(MAX)=''
SET @BackupDataFileNameAndPath = @PathBackUpDatabase+@DatabaseName+'.bak'  --for dynamic  | for HardCode --> 'D:\db\CopyDatabase.bak'
--Open BackUpdataBase
SET @OpenDataFileNameAndPath = @OpenPathDatabase+@DatabaseName+'.bak'   --for dynamic
SET @fileExtenion_mdf=@PathBackUpDatabase+@DatabaseName+'_Data.mdf' --for dynamic   | for HardCode -->  'D:\db\CopyDatabase_Data.mdf'
SET @fileExtenion_ldf=@PathBackUpDatabase+@DatabaseName+'_Log.ldf'  --for dynamic   | for HardCode -->  'D:\db\CopyDatabase_Data_Log.ldf'
SET @Open_ldf=@CopyDatabase+'_Log'  --for dynamic
--BackUp Our database which name of Our New Created Database
BACKUP DATABASE @CopyDatabase  -- <- for dynamic | for HardCode -->  BACKUP DATABASE 'OriginalDatabase'
   TO DISK =@BackupDataFileNameAndPath    -- <- for dynamic | for HardCode -->  TO DISK = 'D:\db\CopyDatabase.bak'
   WITH FORMAT,
      MEDIANAME = 'MyServerDatabaseBackups',
      NAME = 'Full Backup of My  OriginalDatabase';
  --Used for View the Name Of .mdf and .ldf which are in CopyDatabase.bak
   RESTORE FILELISTONLY
   FROM disk =@OpenDataFileNameAndPath;    -- <- for dynamic | for HardCode -->  FROM disk ='D:/db/CopyDatabase.bak';
-- Restore the files for BackupDataBase.
RESTORE DATABASE @DatabaseName
   FROM disk=@OpenDataFileNameAndPath   -- <- for dynamic | for HardCode -->  FROM disk='D:/db/CopyDatabase.bak'
   WITH RECOVERY,
   -- Below two line Move .mdf and .ldf file from  CopyDatabase.bak and restore in Your Server with
   --Our Given new Database name and Your Desire location in You local Disk
   MOVE @CopyDatabase TO @fileExtenion_mdf,  -- <- for dynamic | for HardCode -->    MOVE 'OriginalDatabase' TO 'D:\db\CopyDatabase_Data.mdf' ,
   MOVE @Open_ldf TO @fileExtenion_ldf;    -- <- for dynamic | for HardCode -->     MOVE 'OriginalDatabase_Log' TO 'D:\db\CopyDatabase_Data_Log.ldf' ;
   --For Delete .bak File From Disk
   --Create BackUp Device "sp_addumpdevice" Procedure set Path of Our CopyDatabase.bak file for delete
   EXEC sp_addumpdevice 'disk', @CopyDatabase, @BackupDataFileNameAndPath ;   -- <- for dynamic | for HardCode -->  EXEC sp_addumpdevice 'disk', 'OriginalDatabase','D:\db\CopyDatabase.bak' ;
   --"sp_dropdevice" Procedure  delete CopyDatabase.bak file from our Local Disk because we already restore new created database in Server
   EXEC sp_dropdevice @CopyDatabase, 'delfile' ;    -- <- for dynamic | for HardCode -->  EXEC sp_dropdevice 'OriginalDatabase', 'delfile' ;
END
GO

HostForLIFEASP.NET SQL Server 2019 Hosting

When dealing with NULLs, most beginners think it has some value, but it means unknown.

This article will explore what's NULL is and SQL's NULL conditional operators.

These operators are IS NULL and IS NOT NULL.

What is NULL?
It is used to specify that a particular data value does not exist.

That's why you'll hear from other SQL developers or administrators; NULL represents a missing or unknown value. And from that statement, I think it can be easily remembered and understood.

Moreover, the ANSI behavior for NULL values any expression containing a NULL value is NULL.

Let's see some examples below.
Examples

1. Let's try to add a number to a NULL value.
SELECT (NULL + 1) [Sum of NULL and 1] -- NULL

Output

2. Let's try to concatenate a string to a NULL value.
SELECT (NULL + 'C# Corner Rocks') [Concat NULL to a word] -- NULL

IS NULL and IS NOT NULL
I still remember learning TSQL when I wanted to check a column for NULL. I used the equal (=) or the not (<>) operator.

Let's see an example below.
SELECT * FROM [tblSomething] WHERE name = NULL
SELECT * FROM [tblSomething] WHERE name <> NULL

Note: the example code above won't be behaving as expected. You won't see any results!

Checking for NULL values is impossible with comparison operators such as =, < or <>.

It is because this is not the default behavior of it.

That's why instead of using these equal and or not operators, we can use IS NULL or IS NOT NULL instead.

Let's see an example below.

DROP TABLE IF EXISTS temp.dbo.#tempFruits;

CREATE TABLE #tempFruits
(Id INT IDENTITY,
 [Name] NVARCHAR(10) NOT NULL,
 [Description] NVARCHAR(50) NULL);

 INSERT INTO #tempFruits (Name, Description)
 VALUES
 ('Apple', 'The domesticated tree Malus'),
 ('Kiwifruit', NULL),
 ('Pineapple', NULL),
 ('Papaya', 'Also called papaw or pawpaw'),
 ('Cherry', NULL)

 SELECT * FROM #tempFruits WHERE [Description] = NULL -- 0 result
 SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 results

 SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 0 result
 SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 results

As you can see, in our code sample above, we have combined the equal and the not operator with their NULL operators' counterparts.

Let's try to see the differences.

First, instead of = operator, we used the IS NULL operator to check whether a column is NULL.
SELECT * FROM #tempFruits WHERE [Description] = NULL -- 0 result
SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 results

Output

Second, instead of the <> operator, we used the IS NOT NULL operator to check whether a column is not NULL.
SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 0 result
SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 results

Output

SET ANSI_NULLS ON or OFF
Hopefully, now you understand that by default, you can't use the equality operators when dealing with NULLs and instead use IS NULL or IS NOT NULL operators.

However, this default ANSI behavior can be turned off by adding this line of instruction SET ANSI_NULLS OFF before executing the previous entire code.

Let's add the SET ANSI_NULLS OFF on our previous example and see the output.

SET ANSI_NULLS OFF; -- LET'S TURN OFF THE DEFAULT ANSI BEHAVIOR!

DROP TABLE IF EXISTS temp.dbo.#tempFruits;

CREATE TABLE #tempFruits
(Id INT IDENTITY,
 [Name] NVARCHAR(10) NOT NULL,
 [Description] NVARCHAR(50) NULL);

 INSERT INTO #tempFruits (Name, Description)
 VALUES
 ('Apple', 'The domesticated tree Malus'),
 ('Kiwifruit', NULL),
 ('Pineapple', NULL),
 ('Papaya', 'Also called papaw or pawpaw'),
 ('Cherry', NULL)

 SELECT * FROM #tempFruits WHERE [Description] = NULL -- 3 result
 SELECT * FROM #tempFruits WHERE [Description] IS NULL -- 3 result

 SELECT * FROM #tempFruits WHERE [Description] <> NULL -- 2 result
 SELECT * FROM #tempFruits WHERE [Description] IS NOT NULL -- 2 result

Output

As you can see from the output, when you turn set ANSI_DEFAULT off, the equality operators will eventually be acceptable and work as expected.

So, in incase you experienced this, there's a higher chance that the ANSI_DEFAULT is turned off.
Avoid Changing Default ANSI_NULLS

Turning off the ANSI_NULLS option is not recommended unless you're just trying something or making some quick fixes.

Moreover, it is safer to use the IS NULL and IS NOT NULL operators for the NULL value comparison because it will work in all cases irrespective of the ANSI_NULLS option setting.

HostForLIFEASP.NET SQL Server 2019 Hosting

If you're one of those confused about the difference between the CAST and CONVERT functions, you have come to the right place.

That's why in this article, we'll explore the CAST and CONVERT functions of SQL Server and see their difference.

OK, let's get started then.

What is the SQL CAST function?
Basically, this function converts data from one data type to another. For example, it can convert numeric data into character or string data.
Things to Remember about SQL Cast Function

    Transforms the expression data from one data type to another.
    The transformation lasts only for the life of the query.
    It is an ANSI standard (ANSI SQL-92 compliant), portable to other DBMS.
    So if you're comfortable with the CAST function, you'll be able to use it on other SQL-related databases.
    If in case the conversion fails, it will return an error. Otherwise, it returns the converted value.
    Doesn't have a parameter that accepts optional style for formatting. That's why you can use the SQL FORMAT function.

Syntax
CAST([Expression] as DataType (length))

    [Expression] – defines the valid expression argument.
    DataType – the target data type for conversion.
    Length (optional) – the specified length of the target data type.

Examples

Example 1
Let's try to convert a string to DateTime data type.
--Let's declare a type
DECLARE  @YOUR_BIRTHDAY NVARCHAR(10) = '01/29/2000';

--SYNTAX: CAST([Expression] AS DataType)
SELECT @YOUR_BIRTHDAY AS [Data To Convert],
CAST(@YOUR_BIRTHDAY AS DATETIME) AS [BirthDate - Data Converted];

Output

Example 2
Let's add two or more random numbers and convert them into money data type.
--Let's declare two numbers
DECLARE @NUM1 INT = 100, @NUM2 BIGINT = 854775808

--Let's add them together and convert to money and format a bit so it would look like a genuine $. :-)
SELECT FORMAT(CAST ((@NUM1 + @NUM2) AS MONEY), '$ #,##0.00') [Sum];

Example 3
Let's convert two random char data type to int data type and multiply it together.

--Let's declare 2 char with random number assigned to it
DECLARE @char1 CHAR(3) = '100', @char2 CHAR(3) = '569';

--Let's multiply the two char data-type and see the product of those two numbers.
SELECT CAST(@char1 AS INT) * CAST(@char2  AS INT) [Product of Two Numbers]

Output

What is SQL CONVERT function?
This function converts an expression from one data type to another, just like the CAST function. Another good thing about the SQL CONVERT function is that it gives you the option to format your converted data. That's why we can say that it is more powerful compared to the CAST function.
Things to Remember about SQL Convert Function

    A non-ANSI SQL-92 compliant function converts the expression data type to another data type with a formatting option.
    This function is specific to Microsoft's TSQL and will not be portable to other DBMS.

Syntax
CONVERT (DataType, (length), expression, style)

    DataType – target data type to which you want to convert.
    Length – the specified length of the target type.
    Expression – the target data type for conversion.
    Style – optional integer value to specify the style format of the output. Cool right?

Example
Let's create a temp table, insert the data of this year's Chinese New Year, convert it into VARCHAR, and pass some formatting options.
DROP  TABLE IF EXISTS temp.dbo.#CONVERT_FUNCTION_STYLES

CREATE TABLE #CONVERT_FUNCTION_STYLES
(Id int,
 [Name] varchar(10),
 [ConvertedDate] varchar(20))

 DECLARE @CHINISE_NEWYEAR DATETIME = '02/01/2022 12:00:00 AM';

 INSERT INTO #CONVERT_FUNCTION_STYLES
 (Id, Name, [ConvertedDate])
 VALUES
 (1, 'Default', CONVERT(VARCHAR,@CHINISE_NEWYEAR, 100)),
 (2, 'US',      CONVERT(VARCHAR, @CHINISE_NEWYEAR, 101)),
 (3, 'ANSI',    CONVERT(VARCHAR, @CHINISE_NEWYEAR, 102)),
 (4, 'German',  CONVERT(VARCHAR, @CHINISE_NEWYEAR, 104)),
 (4, 'Japan',  CONVERT(VARCHAR, @CHINISE_NEWYEAR, 111));

 SELECT @CHINISE_NEWYEAR [Date and Time To Be Converted],
       Name [Country],
       ConvertedDate [Country Standard Converted Date]

 FROM #CONVERT_FUNCTION_STYLES;

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

If you are a developer then surely you must have used JSON (JavaScript Object Notation) but if not, then don’t worry, you'll use it sooner rather than later. JSON is a kind of ecosystem that is most popular in various areas for exchanging data. If you talk about charting solutions, AJAX, Mobile services, or any 3rd party integration then generally JSON is the first choice of the developers.

Nowadays most of the NoSQL databases like Microsoft Azure Document DB, MongoDB, etc. also use JSON ecosystem and some of them are based on JSON.

As it is such a popular growing system, why not in SQL SERVER?

In SQL SERVER 2016 JSON was introduced. This was step or bridge between NON-relational databases and relational databases by Microsoft SQL SERVER.

SQL Server 2016 provides the following capabilities when you are using JSON

    Parse JSON by relation query
    Insert & update JSON using the query
    Store JSON in the database

If you see it then conceptually it is similar to an XML data type which you might use in SQL SERVER.

The good thing in SQL SERVER 2016 for JSON is there is no Native data type. This will help in migration from any NoSQL to SQL SERVER.

SQL Server provides bidirectional JSON formatting which you can utilize in various ways. Suppose data is coming from the external source in the JSON format then you can parse it and store it in table structure (if required) in another case external source requires data in JSON format while data in SQL SERVER in tabular format so both the purpose can easily solve with SQL SERVER’s JSON feature.

Now, let’s jump directly to the practical to check JSON capabilities in SQL SERVER

1) FOR JSON AUTO
It is similar to FOR XML AUTO. It will return JSON object of selected column where column name is treated as a Key or in other words we can say it will format the query result in JSON.

when you run the above command the result will be like as shown in the below figure.

2) FOR JSON PATH
It’s exactly like JSON auto, the only difference is instead of SQL SERVER we have full control over the format. JSON Auto takes predefined column schema while with JSON path we can create a complex object.

For example, we are using the AdventureWorks Sales order table and joining that with the product table to get a sub-node. As you see below the image we have added a Root node as well. This root Node can be added in JSON auto as well if required.

Now, when you run the above query we can get complex JSON object as follows

3) IsJSON function
By the name, it is clear that this is a validating function.
To cross-check whether the provided string is a valid JSON or not we can run ISJSON.

4) JSON_VALUE
By the name, it is clear that if you want to get the value of the particular key of JSON then you can use this beautiful function which is JSON_VALUE.

5) OPENJSON function
This is a very beautiful function that you can use to parse external schema. Suppose, you got a JSON string from a mobile service that you will directly pass to SQL Server, and the SQL SERVER stored procedure will do the rest of the operation to parse it. The parsing and other operation can be easily handled by OPENJSON. The only tweak here is that it required database compatibility level 130 which you need to do (if not compatible with level 130)

There are many other interesting things which we will cover later.

HostForLIFEASP.NET SQL Server 2019 Hosting