SQL Server Functions
There are two kinds of Functions in SQL Server. A Function in SQL Server is a named set of SQL explanations that can be called and executed as a solitary unit. The function can perform estimations, control information, and return scalar or table-based results.

1. Scalar function
A Function capability returns a solitary worth, like a whole number, float, or string. Scalar capabilities are utilized inside SQL articulations, such as SELECT or WHERE statements, to perform computations or alter information.

Here is an illustration of a scalar Function that returns the length of a string,
CREATE FUNCTION fnStringLength (@str VARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @length INT
    SET @length = LEN(@str)
    RETURN @length
END

2. Table-valued functions
A table-valued function returns a table as its result set. Table-valued functions are often used to encapsulate complex queries and simplify their use in other parts of the database.

Here is an example of a table-valued function that returns a table of employees who have a salary greater than a specified amount,
CREATE FUNCTION fnGetHighSalaryEmployees (@salary FLOAT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Employees WHERE Salary > @salary
)

Functions can be created using the CREATE FUNCTION statement and called using the SELECT statement or within other SQL statements. It's important to note that functions can significantly impact performance, so it's essential to use them judiciously and test their performance in real-world scenarios.

HostForLIFEASP.NET SQL Server 2019 Hosting

Do you find it difficult to manage and organize your SQL Server databases? Are you constantly struggling to navigate through a sea of tables, views, and stored procedures? If so, you're not alone. Many developers and database administrators face this same problem every day, and it can be frustrating and time-consuming.

But what if there was a solution to this problem? What if there was a way to simplify database management and make it easier to access the data you need? That's where schemas come in.

In this article, we'll explain what a schema is in SQL Server and how it can help solve your database management woes. We'll delve into the benefits of using schemas and show you how to create and manage them in your SQL Server environment.

With our step-by-step guide, you'll learn how to use schemas to organize your database objects into logical groups, improve security by controlling access to specific schemas, and simplify database maintenance by reducing complexity. So, if you're ready to take your SQL Server skills to the next level and tackle the problem of database management head-on, let's dive in.

A schema in a SQL database is a collection of logical data structures. The schema is owned by a database user and has the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object. In other words, schemas are very similar to separate namespaces or containers that are used to store database objects.

Security permissions can be applied to schemas; hence, schemas are essential for separating and protecting database objects based on user access rights. It improves flexibility for security-related administration of the database.
User schema separation

Before SQL Server, database object owners and users were the same things, and database objects (table, index, view, and so on) were owned by the user. In other words, database objects were directly linked to the user, and the user could not delete them without removing the database object that was associated with the user.

In SQL Server, a schema separation is introduced; now, the database object is no longer owned by a user, group, or role. The schema can be owned by the user, group, or role. The schema can have multiple owners. The schema ownership is transferrable. Database objects are created within the schema. Now the user can be dropped off without dropping off the database object owned by the user. But the schema cannot be deleted if it contains a database object.

The following are the advantages of user schema separation:

    The schema ownership is transferrable.
    Database objects can be moved among the schemas.
    A single schema can be shared among multiple users.
    A user can be dropped without dropping the database objects associated with the user.
    Provides more control of access and level of access.

Default schema

The default schema is the first schema searched when resolving object names. The user can be defined within the default schema. Using the "SCHEMA_NAME" function, we can determine the default schema for the database.

The schema can be the default for the user by defining DEFAULT_SCHEMA with CREATE USER or ALTER USER. If no default schema is defined, then SQL will assume "DBO" as the default schema. Note that no default schema is associated with a user if the user is authenticated as a member of the group in the Windows operating system. In this case, a new schema will be created, and the name will be the same as the user name.

Advantages of using Schema

• Act as object protection tool: A schema can be a very effective object projection tool combined with the appropriate level of user permissions. A DBA can maintain control access to an object, which would be crucial.
• Managing a logical group of database objects within a database: Schemas allow database objects to be organized into a logical group. This would be advantageous when multiple teams are working on the same database application, and the design team wants to maintain the integrity of the database tables.
• Easy to maintain the database: A schema allows a logical grouping of the database objects. The schema can help us when the database object name is the same but falls into a different logical group.

Other Advantages

• A single schema can be shared among multiple databases and database users.
• A database user can be dropped without dropping database objects.
• Manipulation of and access to the object is now complex and more secure. The schema acts as an additional layer of security.
• Database objects can be moved among schemas.
• The ownership of schemas is transferable.

Example of a Schema in SQL
Let's say we have a database that contains information about a company's employees, departments, and projects. To organize this information, we can create separate schemas for each of these categories.

For example, we can create a schema called "employees" to store tables related to employee information, such as employee names, job titles, and salaries. We can also create a schema called "departments" to store tables related to department information, such as department names and locations. Finally, we can create a schema called "projects" to store tables related to project information, such as project names, budgets, and timelines.

By creating separate schemas for each category, we can easily manage and access the information we need without having to navigate through a large and complex database. This also helps to improve security by restricting access to certain schemas based on user roles and permissions.

To create a schema in SQL Server, we can use the following syntax:
CREATE SCHEMA schema_name

For example, to create a schema called "employees", we can use the following query:
CREATE SCHEMA employees

We can then create tables within the schema using the following syntax:
CREATE TABLE schema_name.table_name

For example, to create a table called "employee_info" within the "employees" schema, we can use the following query:
CREATE TABLE employees.employee_info (
   employee_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   job_title VARCHAR(50),
   salary DECIMAL(10,2)
);

This creates a table within the "employees" schema that stores information about employee IDs, first names, last names, job titles, and salaries.

A schema in SQL Server is a way to organize database objects such as tables, views, and stored procedures into logical groups. By creating separate schemas for different categories of information, we can easily manage and access the data we need, improve security, and simplify database maintenance.

HostForLIFEASP.NET SQL Server 2019 Hosting

The apply keyword was introduced mainly for selecting data by combining a select query with a table-valued function, which is nothing but a select query on another table (or the result of any business logic in the function). To understand it better, let's call our main table the left table and the second table (in the table-valued function) the right table.

Let's start by creating sample data. We will have two tables, Employee and Department. Here, our Employee table will be the left table, and the Department table will be the right one. Each employee can only belong to one department.

Our intent will be to join these two tables to get the required data. One solution that immediately comes to mind is using a LEFT, RIGHT, or INNER JOIN, which will depend on our requirements. Let's see the results with the use of JOINS.

APPLY works the same way as the co-related subquery, with the fact that the select query on which the application is used, in other words, the right table, will be executed for every record of the main table or the left table, that is nothing but how the co-related sub query works. The same results can be obtained using the apply keyword. It can be applied in either of the following two ways:

    CROSS APPLY- Works the same as the INNER JOIN on two queries.
    OUTER APPLY- Works the same as the LEFT JOIN on two queries.

Let's change the queries using these two apply forms and see the results.

As we can see above, CROSS APPLY gives the same result as the INNER JOIN, and OUTER APPLY gives the same result as the LEFT OUTER JOIN. The difference with the JOIN is that APPLY results in the execution of the select statement of the Department query for each record of the Employee record (the same as that of a co-related sub-query).

Next, suppose we were using the co-related subquery. But we need to view the rest of the columns of the second table. In other words, the Department table. Can we do that? Unless we add some twist to the query, it doesn't seem to be. But this can be easily done with the APPLY keyword. Add the name of the columns we want to view in the select statement of the Department, and we are done. Let's change our queries and see the results:

Another possible and extensive use of APPLY is with the table-valued function. We create a simple function that returns Department details by Id. Next, we replace our select statement for Department with a call to the user-defined function. See the query below:

So, depending on the requirements, we can add or remove the columns' names in the function call's SELECT statement. To summarize, we can use the apply keyword as.

    A co-related subquery with the advantage of selecting multiple columns.
    A join with the table-valued user-defined function to select multiple columns from the second table.

So this was about the use of the apply keyword.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will see how to dynamically get a database List using C#.

Step 1. Used Namespaces
using System.Web.Configuration;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

Step 2. Referenced DLL

Step 3. Connection String:
<add name="<KEY>" connectionString="Data Source=<SERVERNAME>;Initial Catalog=<DATABASE>;Integrated Security=True" />

Step 4. Used to get Connection String from Web.config file.
public static string GetConnectionStringFromWebConfigByName(string name)
{
    return WebConfigurationManager.ConnectionStrings[name].ConnectionString;
}

Step 5. Usage
DatabaseCollection databases =  SQLDatabaseClass .DbCollection(connectionString);

Here you can get the collection of a database as a List.

Database database =  SQLDatabaseClass.SinglDatabase(connectionString);

Here you can get a single database by its name.

Step 6. Creating a Connection String for server connection.
public static SqlConnection Connecection(string connectionString)
{
    SqlConnection con = new SqlConnection(connectionString);
    return con;
}

Step 7. Create a Server connection using a Connection string.
public static ServerConnection GetServerConnection(string connectionString)
{
    ServerConnection serverCon = new ServerConnection(Connecection(connectionString));
    serverCon.Connect();
    return serverCon;
}

Step 8. Creating Server Object.
public static Server GetServer(string connectionString)
{
    Server server = new Server(GetServerConnection(connectionString));
    return server;
}

Step 9. Getting Database collection from the SQL Server:
public static DatabaseCollection DbCollection(string connectionString)
{
    Server server = GetServer(connectionString);
    return server.Databases;
}

Step 10. Getting a specific database from the collection of databases in the SQL Server:
public static Database SinglDatabase(string connectionString, string databaseName)
{
    return GetServer(connectionString).Databases[databaseName];
}

Code Snippet
class SQLDatabaseClass
{
    #region Database

    public static DatabaseCollection DbCollection(string connectionString)
    {
        Server server = GetServer(connectionString);
        return server.Databases;
    }

public static Database SinglDatabase(string connectionString, string Name)
{
    return GetServer(connectionString).Databases[databaseName];
}

#endregion

public static Server GetServer(string connectionString)
{
    Server server = new Server(GetServerConnection(connectionString));
    return server;
}

public static ServerConnection GetServerConnection(string connectionString)
{
    ServerConnection serverCon = new ServerConnection(Connecection(connectionString));
    serverCon.Connect();
    return serverCon;
}

public static ServerConnection GetServerConnectionByLogin(bool isWindows, string serverName)
    {
        ServerConnection serverCon = new ServerConnection();
        serverCon.LoginSecure = isWindows;
        serverCon.ServerInstance = serverName;
        serverCon.Connect();
        return serverCon;
    }

    public static SqlConnection Connecection(string connectionString)
    {
        SqlConnection con = new SqlConnection(connectionString);

        return con;
    }
}

Thanks for reading this article. I hope you have a nice day.

HostForLIFEASP.NET SQL Server 2019 Hosting

If you work with a SQL Server database, I'm sure you have heard of .mdf and .ldf files. When you install a new SQL Server database server on a machine, and create a database, these files are created on a hard drive. In this blog, let's learn about mdf and ldf files.

MDF - Main Database File
    It contains all the main information of the database that is part of the server.
    It plays a crucial role in information storage.

Note - All the successful queries go in MDF.

LDF - Log Database File
    It stores information related to transaction logs for the main data file.
    It stores changes related to CRUD Insert, Delete, and Update.

Note - All the unsuccessful or stuck queries go in LDF.

IMP Image Note
The below image shows the .mdf and .ldf files of EmployeeDB Database.

Difference between MDF and LDF

HostForLIFEASP.NET SQL Server 2019 Hosting

Common Table Expression (CTE) in SQL offers a more readable form of a derived table. A Common Table Expression is an expression that returns a temporary result set. This result set is similar to a hybrid Derived Table. The resultset can be declared once and referenced multiple times in a query. It does not require any extra effort to declare it. CTE is more powerful than the derived table. It can self-reflect, and we can also use CTE multiple times in the same query. Mainly, CTE improves readability and makes it easy to maintain complex queries. CTE can be used for selects, and DML (Insert, Update, and Delete) statements.

Common Structure of CTE 

;WITH CTE_name [ ( column_name [,...n] ) ]
AS
(
query_definition
)
select * from CTE_name;

The Common Table Expression is created using the WITH statement followed by the CTE name and List of Columns (specifying a column is optional). After the "AS," the information used to populate the returning columns begins. The CTE is then followed by a select calling it. Always start CTE with a semi-colon.

Example

Step 1. Create a query 

The following is a sample of creating two tables, EmployeeMasters and DepartmentMasters, and inserting some default values into them.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartmentMasters]') AND type in (N'U'))
DROP TABLE [dbo].[DepartmentMasters]
CREATE TABLE [dbo].[DepartmentMasters](
[DepartmentId] [int] IDENTITY(1,1) NOT NULL,
[DepartmentCode] [varchar](50) NULL,
[DepartmentName] [varchar](50) NULL,
CONSTRAINT [PK_DepartmentMasters] PRIMARY KEY CLUSTERED
(
[DepartmentId] 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
SET ANSI_PADDING OFF
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMasters]') AND type in (N'U'))
DROP TABLE [dbo].[EmployeeMasters]
CREATE TABLE [dbo].[EmployeeMasters](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeCode] [varchar](50) NULL,
[DepartmentId] [int] NULL,
CONSTRAINT [PK_EmployeeMasters] PRIMARY KEY CLUSTERED
[EmployeeId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[EmployeeMasters] WITH CHECK ADD CONSTRAINT [FK_EmployeeMaster_DepartmentMaster] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[DepartmentMasters] ([DepartmentId])
GO
ALTER TABLE [dbo].[EmployeeMasters] CHECK CONSTRAINT [FK_EmployeeMaster_DepartmentMaster]
GO
INSERT INTO DepartmentMasters VALUES
('Eaxm', 'Examination'),
('Staff', 'Exam Staff')
INSERT INTO EmployeeMasters VALUES
('Peter','D0093',1),
('Scott','D0094',1),
('Mike','D0095',1),
('Friedrich','D0096',2),
('Kenneth','D0097',2)

Step 2. Writing CTE Query

The following is a sample use of a CTE Query.

;WITH emp_detail(EmployeeName,EmployeeCode,DepartmentCode)
AS
(
SELECT e.EmployeeCode,e.EmployeeName,d.DepartmentCode FROM EmployeeMasters e JOIN DepartmentMasters d ON e.DepartmentId=d.DepartmentId
)
SELECT * FROM emp_detail;

When to Use CTE in SQL?

CTE offers the same functionality as a View (ideal for one-off usages). CTE provides the following four advantages.

• Create a recursive query. 
• Alternative from a view when the general use of an idea is not required, a case in which you do not have to store the definition in metadata.
• Enable grouping by a column derived from a scalar subselect or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement. 

CIt can not use with CTE

• The clauses like ORDER BY, INTO, OPTION clause with query hints, FOR XML, and BROWSE cannot be used in the CTE query definition.
•  "SELECT DISTINCT," GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries "are not allowed in the CTE query definition of a recursive member.
•  A CTE can be self-referencing and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
• Specifying more than one "WITH" clause in a CTE is prohibited. For example, if a CTE query definition contains a subquery, then that subquery cannot have a nested WITH clause to define other CTE. 

Summary
CTE provides a more readable and usable approach to derived tables. CTE is not materialized into a work table (temporary table). CTEs are not a replacement for temporary Tables. The scope of the CTE is limited to the first SELECT statement only.

HostForLIFEASP.NET SQL Server 2019 Hosting

Simplify complex joins and subqueries using SQL Server Common Table Expressions or CTEs. It also provides a way to query hierarchical data. This article provides a complete overview of CTEs, types of CTEs, benefits, drawbacks, and how to use them with SQL Server.

A Common Table Expression or CTE is a short-lived named result set created from an easy SELECT statement employed in a subsequent SELECT statement. Every SQL CTE is sort of a named query, the result of which is kept in a very virtual table (a CTE) which is referenced later within the main query.

Syntax
WITH cte_expression_name[ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

To view the CTE results, use a select query with the name of the CTE expression.
Select [Column1,Column2,Column3,Column4...] from cte_expression_name

OR

SELECT * FROM cte_expression_name

Types of CTE(Common Table Expression)
There are 2 types of CTEs,
    Recursive CTE.
    Non-Recursive CTE.

Recursive CTE
A Recursive common table expression (CTE) could be a CTE that references itself. By doing so, the CTE repeatedly executes, and returns subsets of information, till it returns the whole result set.

A recursive CTE is beneficial in querying ranked data adore organization charts wherever one worker reports to a manager or multi-level bill of materials once a product consists of many components, and every component itself additionally consists of many different components.

We'll see how to use a CTE to create a simple recursive query that displays row numbers from 1 to 10.

First, he declared an integer variable as 'RowNo', set the default value to 1, and created our first CTE query as the expression name 'ROW_CTE'. This CTE first displays the default row number, then uses union ALL to increment the row number by 1 until the row number reaches the incremented value of 10. To view the results, use the query of your choice to view the CTE results.
Declare @RowNo int =1;
;with ROW_CTE as
   (
      SELECT @RowNo as ROWNO
        UNION ALL
      SELECT  ROWNO+1
  FROM  ROW_CTE
  WHERE RowNo < 10
    )

SELECT * FROM ROW_CTE

Output
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Non-Recursive CTE
Non-Recursive CTEs are easy in which the CTE doesn’t use any recursion, or repeated processing in of a sub-routine. We will create an easy Non-Recursive CTE to show the row variety from 1 to 10.

As in keeping with the CTE Syntax, every CTE question will begin with a "With" observed with the aid of using the CTE Expression call with a column list.

Here we had been the usage of only one column as ROWNO. Next is the Query part, right here we write our pick-out question to be executed for our CTE. After developing our CTE question to run the CTE uses the pick out an announcement with the CTE Expression call.
;with ROW_CTE(ROWNO) as
   (
     SELECT
  ROW_NUMBER() OVER(ORDER BY name ASC) AS ROWNO
FROM table_name
WHERE id <= 10
    )

SELECT * FROM ROW_CTE

Output:
ROWNO
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10

Nested CTE (Common Table Expression)
WITH First_CTE
   AS (SELECT 1 EmpId, 'Peter James' Name)

  ,Second_CTE
   AS (SELECT EmpID, Name, 'London' State FROM First_CTE)

SELECT *   FROM Second_CTE

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, you will learn how to format dates in SQL Server using the format function. In this article, we will learn how to format dates using Format function in SQL Server.
Built-in function in SQL Server to get the DateTime value in a specific format

By using some built-in function in SQL Server we can get the DateTime value in a specific format.

For example,
GETDATE()

It returns server DateTime in “YYYY-MM-DD HH:mm:ss.fff” format.
SELECT GETDATE() AS [GETDATE()]
Result:-2022-06-09 12:28:37.787

GETUTCDATE()
It returns DateTime in GMT.
SELECT GETUTCDATE() AS [GETDATE()];
Result:-2022-06-09 07:10:54.350

SYSDATETIME()
It returns the server’s DateTime
SELECT SYSDATETIME() AS [GETDATE()];
Result:2022-06-09 12:41:46.8713228

SYSDATETIMEOFFSET()
It returns the server’s DateTime with time zone in which SQL Server instance is running.
SELECT SYSDATETIMEOFFSET() AS [GETDATE()];
Result:2022-06-09 12:42:15.7936382 +05:30

SYSUTCDATETIME()
It returns server DateTime in GMT.
SELECT SYSUTCDATETIME() AS [GETDATE()];
Result:2022-06-09 07:12:54.4664815

CURRENT_TIMESTAMP
It returns current DateTime of the server.
SELECT CURRENT_TIMESTAMP AS [GETDATE()];
Result:2022-06-09 12:43:40.650

After the CONVERT function, SQL Server added a function (FORMAT) to handle date formatting, giving us a new way to format dates in SQL Server.

To format the date and time data types from a date column (Date, DateTime, etc. Data type) in a table or a variant such as GETDATE(), use the FORMAT function.
Date Format with FORMAT Function

We have many ways to format dates as given below

DD/MM/YYYY
SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date;
Result:09/06/2022

DD/MM/YYYY, HH:MM:SS
SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date;
Result:09/06/2022, 04:56:44

DDDD,MMMM,YYYY
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date;
Result:Thursday, June, 2022

MMM DD YYYY
SELECT FORMAT (getdate(), 'MMM dd yyyy') as date;
Result:Jun 09 2022

MM.DD.YY
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.09.22

MM-DD-YY
SELECT FORMAT (getdate(), 'MM-dd-yy') as date;
Result:06-09-22

HH:MM:SS TT
SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date;
Result:05:17:37 PM

MM/DD/YYYY (Standard: USA)
SELECT FORMAT (getdate(), 'd','us') as date;
Result:06/09/2022

YYYY-MM-DD HH:MM:SS TT
SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date;
Result:2022-06-09 05:18:55 PM

YYYY.MM.DD HH:MM:SS T
SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date;
Result:2022.06.09 05:19:53 P

DDDD,MMM,YYYY in Spanish
SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date;
Result:jueves, junio, 2022

DDDD DD, MMMM,YYYY in Japanese

SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date;
Result:木曜日 09, 6月, 2022

Date Format with Culture
We can get regional formatting by using the culture option as shown below:

English-USA
SELECT FORMAT (getdate(), 'd', 'en-US') as date;
Result:6/10/2022

French-France
SELECT FORMAT (getdate(), 'd', 'fr-FR') as date;
Result:10/06/2022

French - Belgium
SELECT FORMAT (getdate(), 'd', 'fr-BE') as date;
Result:10-06-22

French - Canada
SELECT FORMAT (getdate(), 'd', 'fr-CA') as date;
Result:2022-06-10

Danish - Denmark
SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.10.22

Dari - Afghanistan
SELECT FORMAT (getdate(), 'd', 'prs-AF') as date;
Result:1401/3/20

Simplified Chinese
SELECT FORMAT (getdate(), 'd', 'zh-CN') as date;
Result:2022/6/10

Divehi - Maldives
SELECT FORMAT (getdate(), 'd', 'dv-MV') as date;
Result:10/06/22

Bosnian Latin
SELECT FORMAT (getdate(), 'd', 'bs-Latn-BA') as date;
Result:10. 6. 2022.


isiXhosa / Xhosa - South Africa
SELECT FORMAT (getdate(), 'd', 'xh-ZA') as date;
Result:2022-06-10

Hungarian - Hungary
SELECT FORMAT (getdate(), 'd', 'hu-HU') as date;
Result:2022. 06. 10.

Spanish - Bolivia
SELECT FORMAT (getdate(), 'd', 'es-bo') as date;
Result:10/6/2022

Here is a list of all CultureInfo codes along with country names and language.

As you saw above, we have used a lot of options for date and time formatting, which are detailed below,

• hh - this is the hour from 01-12
• HH - this is the hour from 00-23
• mm - this is the minute from 00-59
• ss - this is the second from 00-59
• dd - this is day of month from 01-31
• dddd - this is the day spelled out
• MM - this is the month number from 01-12
• MMM - month name abbreviated
• MMMM - this is the month spelled out
• yy - this is the year with two digits
• yyyy - this is the year with four digits
• tt - this shows either AM or PM
• d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
• us - this shows the date using the US culture which is MM/DD/YYYY

HostForLIFEASP.NET SQL Server 2019 Hosting

For development or testing purposes, SQL users often need to restore the same database on the same PC or server with a different name. In this tutorial, I am going to explain the detailed steps to restore the same database with different names in SQL Server.

We advise against attaching or restoring databases from unauthorized or untrusted sources for security reasons. These databases may contain malicious code that can alter the physical database structure or schema, run unwanted T-SQL code, or cause problems.

This tutorial will show you various ways to restore the same database with different names in SQL Server.
Method 1 - Using SQL Server Management Studio (SSMS)

Step 1
Open SQL Server Management Studio (SSMS) and connect to the SQL Server Instance.

Step 2
In Object Explorer, right-click Databases and select "Restore Database...".

Step 3
The Restore Database window will appear on the screen. On the General page, use the Source section to specify the source and location of the backup set to be restored. Now, select the Device option and click the Browse (...) button.

Step 3a
Select backup devices window will appear on the screen. Click the Add button to select one or more backup device(s) for the Backup media box.

Step 3b
Locate and select the SQL database backup file(s) (.bak) that you want to restore, and click the OK button to proceed.

Step 3c
After adding the desired backup file to the Backup media list box, click on the OK button to proceed.

Step 4
In the Destination section, the Databases box is automatically populated with the name of the database to be restored. Now, you need to rename the destination database, to do so enter the new name in the Database box. And, leave the default values as it is in the "Restore to" box and "Backup sets to restore" grid.

For example, here I have renamed the Destination Database to "CSHARPCORNER_Backup".

Step 5
Going to the Files page, proceed with the steps to specify the new location or name of the database files (Data and Log files).
    The file names in our case are "CSHARPCORNER_Backup.mdf" and "CSHARPCORNER_Backup_log.ldf," respectively.

Note:

• When you rename the destination database, SSMS itself renames both the data and log files to "Restore As". I remember that in earlier versions of SSMS you had to change manually the names of the files, otherwise, they would conflict with existing files.
• To prevent conflicts, avoid placing new database files in the same directory as the existing database. If necessary, modify the directory name. However, make sure the drive you are using has enough space.

Step 6
Proceeding with the steps again, go to the Options page. Here, you need to make sure that the following options are checked. And, click the OK button.
    "Overwrite the existing database (WITH REPLACE)" under the Restore options section.
    "Take tail-log backup before restore" under the Tail-log backup section. (optional)

Congratulations! the "Database 'CSHARPCORNER_Backup' restored successfully" message shows that our backup database has been successfully restored.

Method 2 - Using Transact-SQL (T-SQL)
With the help of T-SQL, users can also restore the database with a different database name. Follow the below steps to proceed.

Step 1
Optionally, determine the logical and physical names of the files in the backup set that contains the full database backup that you want to restore. This statement returns a list of the database and log files contained in the backup set. The basic syntax is as follows:
RESTORE FILELISTONLY FROM DISK = <Backup_file_location>

Step 2
To restore a full database backup, use the RESTORE DATABASE statement. Use the MOVE option to relocate each of the database files (.mdf & .ldf) and to avoid conflicts with existing files. Because data and log files are restored to their original locations by default. Use the following basic T-SQL syntax to restore the database to a new location and a new name.
RESTORE DATABASE [NEW_DATABASE_NAME]
FROM DISK = N'<BACKUP_FILE_PATH/BACKUP_FILE_NAME.BAK>'
[ WITH
{
    [ **RECOVERY** | NORECOVERY ]
    [ , ] [ FILE = { *backup_set_file_number* | @*backup_set_file_number* } ]
    [ , ] MOVE '*Logical_File_Name_In_Backup*' TO '*Operating_System_File_Name*' [ ,...*n* ]
}

Example
Execute the following T-SQL queries to restore the database with the same name on the same PC or server. Follow the below steps to proceed.

Step 1
Determine the logical and physical names of the files in the backup set by executing the following query.
RESTORE FILELISTONLY FROM DISK = N'F:\FinalDev\Database Backups\CSHARPCORNER\CSHARPCORNER.bak'

Step 2
Once you have obtained the logical and physical names of the database files, execute the below query to restore the same database with a different name.
RESTORE DATABASE [CSHARPCORNER_Backup] FROM DISK = N'F:\FinalDev\Database Backups\CSHARPCORNER\CSHARPCORNER.bak'
WITH FILE = 1,
MOVE N'CSHARPCORNER' TO N'F:\FinalDev\Microsoft SQL Server\DATA\CSHARPCORNER_Backup.mdf',
MOVE N'CSHARPCORNER_log' TO N'F:\FinalDev\Microsoft SQL Server\DATA\CSHARPCORNER_Backup_log.ldf',
NOUNLOAD, REPLACE, STATS = 5
GO

Problems associated with restoring the database

Both methods of restoring the database with the new name are effective and efficient. However, these techniques are not without drawbacks, and you may encounter difficulties such as the following:

    Invalid file format.
    Invalid source file path.
    Incorrect data and log file name.
    Inconsistency errors in the database.
    Insufficient disk space to restore the database.
    Inadequate SQL permissions to run the T-SQL statement.
    To restore an encrypted database, you must have the certificate or asymmetric key that was used to encrypt it. Otherwise, you cannot restore the database without a certificate or asymmetric key.

Note
Apart from these issues, incorrect implementation of a single step to restore a database can corrupt the database and result in potential data loss. Additionally, you may encounter the "SQL database restore failed, database in use" error message.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this blog, we learn how to calculate credit debit Transactions like banking report using SQL Sever.

Step 1:  Create Table valued function for calculate credit, debit amount with total balance
USE [SqlBank]

CREATE   FUNCTION [dbo].[FNGetTransaction](@CID BIGINT)
RETURNS @Tab_TRansaction TABLE (id BIGINT ,CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200)
, CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime,Remarks varchar(max) , IsmailSend int)
AS
BEGIN

DECLARE @TempAC_ID BIGINT;

SET @TempAC_ID = (SELECT TOP 1 A.AC_ID FROM Tbl_Account A join Tbl_Cust
  C ON A.CID=C.CID WHERE c.CID=@CID)

DECLARE @Tbl_Tran Table
(id BIGINT,
CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200),
 CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime  ,
Remarks varchar(max)  , IsmailSend int
)

INSERT INTO @Tbl_Tran(id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate,Remarks,IsmailSend)
SELECT TR.TR_ID, CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END CreditAmt,
CASE WHEN tr.TR_CrDrType ='Dr' THEN tr.TR_Amt ELSE 0 END DebitAmt ,Tr.TR_Type,tr.TR_Date,Tr.AC_ID ,
 CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END - CASE WHEN tr.TR_CrDrType ='Dr'
 THEN tr.TR_Amt ELSE 0 END  Balance,
 Tr.TR_CrDrType  ,C.CName ,Acc.AC_NO ,C.CAddress ,C.CMObile,C.CEmail ,Acc.AC_OpDate ,
 Tr.Remarks , Tr.IsmailSend

FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc with(nolock) ON acc.AC_ID=Tr.AC_ID
      join Tbl_Cust C with(nolock) ON C.CID=Acc.CID
WHERE Acc.CID=@CID;

WITH Tbl_CTE_Tran
as
(
SELECT T2.id,T2.CreditAmt,T2.DebitAmt,SUM(T1.CreditAmt-T1.DebitAmt) Balance,
T2.Tr_Type,T2.TranDate,T2.AC_ID
,T2.TType,T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate,t2.Remarks,t2.IsmailSend FROM @Tbl_Tran T1
join @Tbl_Tran T2 on T1.id<=T2.id WHERE T2.AC_ID=@TempAC_ID
GROUP BY T2.id,T2.CreditAmt,T2.DebitAmt,T2.Tr_Type,T2.TranDate,T2.AC_ID,T2.TType,
T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate  ,t2.Remarks ,t2.IsmailSend
)

INSERT INTO @Tab_TRansaction (id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks ,IsmailSend
 )
SELECT id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType  ,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks,IsmailSend
FROM Tbl_CTE_Tran  with(nolock)
WHERE AC_ID=@TempAC_ID

RETURN
END

Step 2: Create Procedure & Call above function in Procedure

USE [SqlBank]

CREATE PROC [dbo].[PROC_TRansaction]
(
@TR_ID int=null output,
@CID bigint=null,
@TR_Amt decimal(18,2)=null,
@AC_ID bigint =null,
@Flag varchar(100)=null,
@AC_No bigint=null,
@Remarks varchar(max)=null,
@MTR_ID int=null output,
@Balance decimal(18,2)=null output
)
AS
BEGIN
DECLARE @TempTRAmount decimal(18,2)
DECLARE @Temp_ACID bigint
DECLARE @Tran_ScopID bigint;
DECLARE @Tran_ID bigint;
DECLARE @MMTR_ID bigint;

BEGIN TRAN Tbl_Transaction_Tran
   BEGIN  TRY
      IF(@Flag = 'Tran')
      BEGIN

IF EXISTS(SELECT 1 FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc
with(nolock) ON acc.AC_ID=Tr.AC_ID WHERE Acc.CID=@CID)
BEGIN
 SELECT  a.id id ,a.DebitAmt,a.CreditAmt,a.Balance
 ,a.Tr_Type, isnull(Format(a.TranDate,'dd-MMM-yyyy HH:mm'),'') TranDate, NCHAR(8377) Rupees ,a.TType,a.Remarks
   FROM dbo.FNGetTransaction(@CID) a
--      JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
--GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType
END
ELSE
BEGIN
Select 'No Transaction summary found...?' OpMsg
END
    END
ELSE IF(@Flag = 'IN')
    BEGIN
    SET @Temp_ACID = (SELECT Top 1 A.AC_ID  FROM Tbl_Account A with(nolock)
     Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE A.AC_No=@AC_No)
    DECLARE @SenderName varchar(max)
        SET @SenderName = (SELECT Top 1 c.CName  FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE c.CID=@CID)
    DECLARE @ReciverName varchar(max)
       SET @ReciverName = (SELECT Top 1 c.CName FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID
       WHERE A.AC_No=@AC_No)
SET @TempTRAmount = (
 SELECT TOP 1 ISNULL(SUM(b.balance),0) Balance
   FROM dbo.FNGetTransaction(@CID) a
JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id
GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType ORDER BY a.id desc)
if(@TR_Amt > @TempTRAmount)
BEGIN
Select 'Insuffitient Balance' as msg
END
ELSE
  BEGIN
  Declare @FixScratchAmt decimal(18,2)=500;
  --if not exists (select 1 from Tbl_Transaction Where TR_Date=CURRENT_TIMESTAMP and Ref_TranACC=@AC_ID)
  --begin
  Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Online - Transfer To - '+ @ReciverName + ' '+Cast(@Ac_NO as varchar(max))+' ',
  ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@AC_ID,'Dr','Tran-' +CAST(@AC_ID as varchar(max)),0,'S',0,@Remarks)
  set @Tran_ID = @@IDENTITY;
  set @TR_ID= @Tran_ID;
  set @Tran_ScopID= SCOPE_IDENTITY();
  Set @Balance = (SELECT TOP 1 BALANCE FROM dbo.FNGetTransaction(@CID) order by id desc)
  if(@TR_Amt >= @FixScratchAmt)
  begin
   Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Cash Back From S Bank7 ',10,CURRENT_TIMESTAMP,@AC_ID,'Cr',0,1,'R',0,'Cash back from Sbank7. Pay & win more cash back ')
  END

Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Recived From ' + @SenderName + ' Tran - '+Cast(@Tran_ScopID as varchar(max))+'-'+
  CAST(@AC_ID as varchar(max)),ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@Temp_ACID,'Cr','Tran-'
  +Cast(@Tran_ScopID as varchar(max))+'-'+ CAST(@AC_ID as varchar(max)),0,'R',0,@Remarks)
  set @MMTR_ID = @@IDENTITY;
  set @MTR_ID = @MMTR_ID;
    END
    END
IF(@@TRANCOUNT > 0)
  BEGIN
  COmmit tran Tbl_Transaction_Tran
  END
END TRY
BEGIN CATCH
IF(@@TRANCOUNT > 0)
            BEGIN
            ROLLBACK TRAN Tbl_Transaction_Tran
            END
            DECLARE @USERID varchar(max),@ERRORLINE varchar(max)
            ,@ERRORMESSAGE varchar(max),@ERRORPROCEDURE varchar(500),@ERRORSEVERITY varchar(max)
            ,@ERRORSTATE varchar(max), @ErroFrm varchar(max)

            SELECT @USERID = SUSER_SNAME(),@ERRORLINE=ERROR_LINE(),@ERRORMESSAGE=ERROR_MESSAGE(),
                   @ERRORPROCEDURE=ERROR_PROCEDURE(),@ERRORSEVERITY=ERROR_SEVERITY(),
                  @ERRORSTATE= ERROR_STATE() ,@ErroFrm = 'Backend'

        EXEC Proc_ERRORLOG @USERID,@ERRORLINE,@ERRORMESSAGE,@ERRORPROCEDURE,@ERRORSEVERITY,@ERRORSTATE,0,@ErroFrm
END CATCH
END

Step 3: Execute Procedure to Check Report
exec [dbo].[PROC_TRansaction]

@CID =2,@Flag='Tran'

I hope it works!

HostForLIFEASP.NET SQL Server 2019 Hosting