Triggers
Triggers are a special type of procedure that are fired automatically when a query is executed on a table or a view. Triggers provide a powerful way of control how action queries modify data in your database. With the triggers you can enforce design rules, implement business logic and prevent data inconsistency with a flexibility that cant be duplicated any other way. This article with code snippet explains how to write create, update, and delete triggers in SQL.

Trigger Creation
The CREATE TRIGGER statement provides for two types of triggers: AFTER triggers and INSTEAD OF triggers. Both types of triggers can be defined to fire for an insert, update, or delete operation. If an action query has an AFTER trigger, the trigger fires after the successful completion of action query. If an action query has an INSETEAD OF trigger the trigger is fired instead of the action query. In other words the action query is never executed.
CREATE TRIGGER trigger_name ON {table_name|view_name}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE] As sql_statements

FOR is same as AFTER but it is for backward compatibility.

Each trigger is associated with the table or view names in the ON clause. Although each trigger is associated with a single table or view, a single table can have many number of AFTER triggers. Since two or more triggers on table can cause confusion to manage and to debug however its better to place all the related code in one trigger for each action. A view can't have AFTER triggers.

CREATE TRIGGER Shashi_INSERT_UPDATEON Shashi AFTER INSERT,UPDATEASUPDATE ShashiSET ln_name = UPPER(ln_name) WHERE Shashi_id in (SELECT Shashi_id from Inserted)

The CREATE TRIGGER statement in the above example defines an AFTER trigger for the Authors table. In this case the trigger fires after an insert or update operation is performed on the table. If you closely observe in the trigger body we have used a sub query and a table named Inserted in from clause, this is a special table that's created by SQL Server during an insert operation. It contains the rows that are being inserted into the table. This table exists while the trigger is executing, you can only refer to it in the trigger code. In addition to the inserted table you have one more table i.e. deleted which contains the information about the rows deleted. These tables are called Magic tables.

An AFTER trigger fires after the action query is executed. If the action query causes an error, the AFTER trigger never fires. AFTER triggers can be used to enforce referential integrity.

An INSTEAD of trigger can be associated with a table or view. However INSTEAD OF triggers are used most often to provide better control of updatable views.

INSTEAD OF trigger is executed instead of the action query that causes it to fire. Because the action query is never executed, the trigger typically contains code that performs the operation. Each table or view can have only one INSTEAD OF trigger for each type of action.

How to delete or Change a Trigger
To change the definition of a trigger you can use ALTER TRIGGER or else to drop trigger use DROP TRIGGER.

The syntax of the DROP triggers statement.
DROP TRIGGER trigger_name [,...]

The syntax of the ALTER TRIGGER statement
ALTER TRIGGER trigger_nameON {table_name|view_name}[WITH ENCRYPTION]{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]As sql_statements

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

If you are a database administrator and facing issues while reconnecting log files to MDF files, read this blog. We have shared some easy ways to reconnect the log file to the MDF file.

Methods to Reconnect Log File to MDF File
Here in this blog, I am going to discuss two simple methods to reconnect SQL Log file to MDF database files. The methods are:

    SQL Server Management Studio
    Reconnect Log File without the Transaction File

Let’s explain both methods separately.
SQL Server Management Studio (SSMS) to Reconnect Log File to MDF File
    Login to your computer and search for SQL Server Management Studio.
    Connect to the database either by selecting Windows or Server Authentication.

    Right-click on the Database option and choose Attach. It will lead you to the attached database wizard.
    Now, click on the Add button.
    Here, from the list of database files, select the MDF file you wish to connect with the log file and click on the OK button.
    Click on the message “transaction log file not found” and click OK.
    Finally, reconnect the Log file to the MDF file whether it is connected properly or not.

This is how you can easily reconnect LDF files with the MDF database files.
Reconnect Log File without the Transaction File

Step 1
Specify the DB name and the .MDF file location using the Create Database with Attach option script given below,
USE [master]
Go
CREATE DATABASE [Tester] ON
(FileName = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\Tester.mdf’)
For Attach
GO

Step 2
Once the script is executed, a message will display saying a new transaction log file was created.
Thanks!

HostForLIFEASP.NET SQL Server 2019 Hosting

Have you forgotten your password for the system administrator (SA) account in SQL Server? Do you want to reset SA password in SQL Server? If you are unable to recall it, Don’t panic! Here in this article, we are providing you with free methods to reset the System Administrator (SA) password in SQL Server.

Use Single User Mode
Make sure the Microsoft SQL Server Management System must be installed on your system before using the Single-User mode. Given steps can be followed,

Enter Windows key+R in the administrator mode.

Type the command net stop MSSQLSERVER and then click the Enter button to stop the SQL instance from running.

Now, restart the SQL Server in the Single-User mode by using this command: net start MSSQLSERVER /m”SQLCMD” and then hit the Enter button.

Here, connect to the SQL Server by entering the command sqlcmd and click on Enter button.

Create user credentials (user name and password) using T-SQL commands. CREATE LOGIN name WITH PASSWORD=’password’. Here, “name” specifies the account name and “password” specifies the new password.

Now, type SP_ADDSRVROLEMEMBER name,‘SYSADMIN’ command to add this recently created user to the System Administrator role using T-SQL command.

Here, you need to exit the SQLCMD command line by typing the command exit and clicking on the Enter button.

Now open Microsoft SQL Server Management Studio using the SQL Server Authentication and login with the recently created user by providing the user name and password and click on Connect button.

Here in the Object Explore section, expand the Security tab>Login and right-click on the SA and click on Properties.

Now in the Login Properties windows, provide the new credentials and click on OK and close it.
This is how you can easily reset SA password in SQL Server effortlessly. I hope this helps!

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will cover how to bring a database online from the suspect mode in an SQL server. Along with that, we also explain the meaning of SQL database in suspect mode and the reasons for this issue. So, let us start the article.

First, let us understand what SQL suspect mode means.

When the SQL database shows the suspect mode instead of online, it resembles that the database has started to recover the corrupted file but is not finished yet. There are various reasons for this issue. Below we suggested a few common reasons for the error message.

Why Does This Error Occur?
The SQL database goes to suspect mode due to many reasons. However, some reasons are more prevalent than others. Therefore, before you bring the database online from the suspect mode in the SQL server, we discuss some common causes for the SQL server issue.

    Abrupt shutdown of the SQL server leads to various issues. SQL Suspect mode is also one of those problems.
    When the database is unable to access the location of the log files and other important files, it shows the SQL server in suspect mode.
    If the files required by the database are opened in any other third-party application or by antivirus software, you will also experience the same issue.
    Insufficient disk space is another main reason for SQL databases being in suspect mode.
    Server crash sent your SQL database in Suspect mode.
    Sometimes, the SQL server database file corruption also causes this issue.

The above are a few scenarios when your SQL database is in suspect mode. We will explain an effective method to fix the problem.
How to Bring Database Online from Suspect Mode in SQL Server?

You have understood what are the main reasons for your database to be in suspect mode. Now, it is time to learn how to recover SQL database from suspect mode. We discuss the most efficient and easy solution to fix the DBMS issue. To perform the steps you need the SSMS (SQL Server Management Studio).

Perform the below steps to repair database in suspect mode in SQL Server

First, launch the SSMS and connect it to the database.

Now, choose New Query and set the database to the emergency mode by passing the following command.
EXEC sp_resetstatus ‘db_name’;

ALTER DATABASE db_name SET EMERGENCY

After that, give the below command for Consistency Check. It helps you to identify whether the database files are corrupted or not.
DBCC CHECKDB (‘database_name’)

Enable Single-User Mode and execute the below command to roll back the last transaction.
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Take the backup of your database items to prevent data loss. give the following command to repair and rebuild the SQL database lost missing rows.
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

After that, deactivate the Single-User mode and enable the Multi-User Mode.
ALTER DATABASE database_name SET MULTI_USER

After performing the above steps, anyone can easily restore their corrupted database files and repair database in suspect mode in SQL server.

I hope after reading this article you will get a satisfactory solution to recover SQL database from suspect mode. We explained what are the main reasons behind the database issue and also explained a simple yet effective manual method. However, if the problem originates due to corrupted database files, we suggest you opt for any advanced professional approach.

HostForLIFEASP.NET SQL Server 2019 Hosting

Don’t waste your time in anger, regrets, worries, and grudges. Life is too short to be unhappy. One of the most popular and important questions that people often ask in complex database performance checks is how to measure the execution time of a stored procedure when it contains many statements. Well, honestly, the solution is very straightforward. Today we will discuss the execution time of stored procedures.

It is very easy to measure the execution time of a stored procedure. It is not necessary to add up the entire execution time of the stored procedure. Before running the stored procedure, just run the following command and you will see the last line in the message section with the time required to run the execution plan for the stored procedures.

SET STATISTICS TIME ON
EXEC YourSPName

The output will look like this.
SQL Server Execution Times: CPU time = 450 ms, Elapsed time = 1150 ms.
SQL Server Execution Times: CPU Time = 50 ms, Elapsed Time = 100 ms.
SQL Server Execution Times: CPU time = 1450 ms, Elapsed time = 1950 ms.
SQL Server Execution Times: CPU time = 2150 ms, Elapsed time = 3250 ms.

If your stored procedure has three statements, the first three represent the execution time of a single query. However, the last line represents the addition or commutative time for all the query statements together.

I hope you like this.

Thanks.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this tutorial, I will tell you about How to search a string entire database on SQL Server.

--USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'Class VIII'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

For example, I will search the above mentioned string then the sample output is as follows:

HostForLIFEASP.NET SQL Server 2019 Hosting

ANSI_NULLS define the comparison rule for NULL values in SQL Server.
 
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name.
 
Example

But when SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name.
 
Example

HostForLIFEASP.NET SQL Server 2019 Hosting

Introduction
Hope you're familiar with the concepts of what's a primary key in a relational database management system like SQL Server. But, you need more details to better understand it, or you need some review or refresher for yourself, you have come to the right place.

This article will discuss a primary key, entity integrity, primary key constraint, composite keys, and the benefits of a primary key.

Let's get started.

What's A Primary Key?
A primary key is a logical concept where a column or field uniquely identifies each record inside a database table. Moreover, it has multiple attributes: it must not be null, and it must be unique.

Easy to remember, right? But if you're curious, we'll test and see what will happen when we try to pass a NULL and duplicate value in a primary column in the latter part of the article.
What's Entity Integrity?

Entity integrity is a rule for practical database construction, and this practice is widely used. It is a process of enforcing the primary key for each table in a database.

Therefore, it is implemented to uniquely identify each row inside the table by specifying a primary key inside our database table.
What's a Primary Key Constraint?

A primary key constraint is a restriction that basically ensures entity integrity.

Note: Unique constraint also ensures entity integrity.
Benefits of Primary Key and Entity Integrity

Proper usage and selection of a primary key and maintaining entity integrity prevent duplicate records and other issues that would indirectly compromise the integrity of the database.

Things to Remember About Primary Keys
    A primary key column cannot have NULL values.
    A primary key column cannot have duplicate values.
    A table can have only one primary key constraint.
    When multiple columns are used as primary keys, they are called composite keys.
    It is good to remember that a primary key is the default clustered index if a clustered index on the table does not exist.

Composite Keys
Now, you might ask why I see multiple primary keys in one table? Those are called composite keys.

Composite keys use two or more fields from a table to create a unique value.

Therefore, it guarantees uniqueness only when combined columns, but they don't guarantee uniqueness individually.

Creating A Primary Key on A Table
Let's show how we can define a column as a primary key.
CREATE TABLE EMPLOYEE(
[Id] int NOT NULL IDENTITY(1,1),
[FirstName] nvarchar(50),
[LastName] nvarchar(50),
[Age] int,
PRIMARY KEY (Id))

In the code sample above, we have seen that using the PRIMARY KEY then passing the column Id, we have defined the primary key of the EMPLOYEE table.

Let's try to see the result below.

Add a Primary Key on A Table
Let's recreate the table from the previous example, but we will not create the primary key.
We will create a primary key after we have created the table.
Let's try to see the code sample below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL IDENTITY(1,1),
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO
BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Just a note, if you have removed the IDENTITY (1,1), everything will still be good and have no errors because it's an auto-incrementing column.

Although these two are used together, there's no requirement when defining a primary key column that it needs to be an identity column.

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO
BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Again, another note, if we have forgotten the NOT NULL that makes the Id column nullable, it will give you an error.

You'll probably see an error like this
    "Cannot define a PRIMARY KEY constraint on nullable column in table 'EMPLOYEE.'"

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int, -- NOT NULL REMOVED FOR YOU TO SEE THE ERROR MESSAGE
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO
BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Let's try to see the result below.

Delete Primary Key on A Table
In this section, let's try to recreate the table again, but after creating the table, let's make a primary key with the name of [PK_ON_EMPLOYEE_TABLE].
The reason for giving the primary key a custom name is so we won't have a hard time knowing its name when we need to drop the primary key.

Let's try to see the example below.

PRINT 'STEP 0. DROP EMPLOYEE TABLE IF EXISTS'

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    PRINT 'STEP 0.1 DROPPING EMPLOYEE TABLE'
    DROP TABLE EMPLOYEE;
END
GO

PRINT 'STEP 1. CREATE THE TABLE'

BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL IDENTITY(1,1),
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
PRINT 'STEP 1.1 EMPLOYEE TABLE CREATED'
END
GO

BEGIN
PRINT 'STEP 2. EMPLOYEE TABLE ADDING PRIMARY KEY [PK_ON_EMPLOYEE_TABLE]'
ALTER TABLE EMPLOYEE ADD CONSTRAINT [PK_ON_EMPLOYEE_TABLE] PRIMARY KEY(Id);
END
GO

BEGIN
PRINT 'STEP 3. EMPLOYEE TABLE REMOVING THE PRIMARY KEY'
ALTER TABLE EMPLOYEE
DROP CONSTRAINT [PK_ON_EMPLOYEE_TABLE];
END

Inserting NULL values into Primary Key Column
This obviously will show an error because we're violating the primary key constraint.
Still, we'll see how the SQL Server will react when inserting NULL values out of curiosity.

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN

CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO

BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id) ;
END
GO

BEGIN
--LET'S INSERT NULL value and expect an error
INSERT INTO [dbo].[EMPLOYEE] ([Id],[FirstName],[LastName],[Age])
VALUES
(NULL, 'Jin', 'Necesario', 100)
END
GO

Let's try to see the result below.

Inserting Duplicate Values into Primary Key Column
Again, this will obviously show an error because we're violating the primary key constraint. Still, we'll see how the SQL Server will react when inserting duplicate values out of curiosity.

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN

CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO

BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id) ;
END
GO

BEGIN
--LET'S INSERT SAME Id value of 1 and expect an error
INSERT INTO [dbo].[EMPLOYEE] ([Id],[FirstName],[LastName],[Age])
VALUES
(1, 'Jin', 'Necesario', 100),
(2, 'Vincent','Necesario', 100),
(1, 'Jin Vincent','Necesario', 100)
END
GO

Let's try to see the result below.

HostForLIFEASP.NET SQL Server 2019 Hosting