What is a trigger in sql server?

Trigger is a special kind of stored procedure which will get executed on events like insert, delete or update operation happens on a specific object of the database.

Types of Trigger

  1. Insert Trigger
  2. Delete Trigger
  3. Update Trigger
  4. Combination of above Triggers
What is a trigger in sql server? was last modified: October 1st, 2017 by Justin Antony

ACID property in Sql Server

ACID property

Atomicity : If a particular part of transaction fails then the entire transaction should fail.
Consistency : All the constraints and data integrity rules have to be followed. Transaction should make the database from one valid state to another. There should not be any violation of defined rules of the database.
Isolation : Every Transaction should be independent and Isolated completely from one another. One Transaction should not interfere with another transaction.
Durability : Once a transaction is completed it should remain permanent and should not get affected by any power outage or any disk failures.

ACID property in Sql Server was last modified: October 1st, 2017 by Justin Antony

Find active lock on the system in sql server

Active lock sql server

IF we want to know which locks are active in the sql server we can use the following statement

EXEC SP_LOCK
EXEC master..SPDBLOCKS 'Database Name'

This will give the SP and Database Id and the object along with its Mode and Status.

Find active lock on the system in sql server was last modified: October 1st, 2017 by Justin Antony

Arithmetic overflow error converting float to data type numeric

The error occur in the following scenario

select cast(cast(11177777777777771.111555555555333333 as float) as decimal(15,5))
select cast(cast(11177777777777771.111555555555333333 as float) as decimal(15,5))

Modify the range value to accommodate the result

select cast(cast(11177777777777771.111555555555333333 as float) as decimal(25,5))
select cast(cast(11177777777777771.111555555555333333 as float) as decimal(25,5))
Arithmetic overflow error converting float to data type numeric was last modified: January 10th, 2017 by Justin Antony

Perform bulk backup operation using SSIS

Perform bulk backup operation using SSIS

Follow the following steps in SSIS for the bulk backup operation.
Steps:
1. Add a For each loop container on the SSIS Package
2. Add Database backup component to the Loop container
3. Configure the For Each loop for objects in SQL Database ( This will loop through all the database in the particular server)
4. Pass the Database Name to the “Database Backup component” using Expression
5. Provide the backup location — You can concatenate DateandTime with the backup file name to avoid overwriting.

Perform bulk backup operation using SSIS was last modified: February 21st, 2017 by Justin Antony