SQL SERVER – SQL Server Encryption and Decryption with Script

Steps for encryption and decryption cell values in SQL Server.


Step 1: Create table and populate data.
Create a Master key(DMK-Database Master Key)

CREATE TABLE TestTable (Id INT, TextValue VARCHAR(50))
GO

INSERT INTO TestTable (Id, TextValue)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Teciners#123'
GO

The master key (DMK) in a database is encrypted by SQL server using its service master key (SMK).

SMK is created by a SQL Server instance when it starts up the very first time after installation. For any set of instances of SQL server, the SMK will always be different. So, the DMK in the database will be encrypted differently on each server.

Fetch one or all record from table conditionally.

WHAT WE WILL WE LEARN IN THIS POST?

After reading this post we must be able to fetch one or all record from the table conditionally. We will fetch one record based on the condition mentioned in where clause or all records when a particular value is to be retrieved. i.e., we can either select one record of our choice or all records when we choose some particular value. This is useful when we need to fetch single record or to display all records to the user. Either show selected record or all records.

HOW WE ARE GOING TO ACHIEVE THIS?

We will use most common, simple select statement and some variables along with Boolean Logic. Why Boolean Logic? Because we won't use case or if else statement.

Lets begin our excursion...

Split function in SQL

Unlike Split() function in C#, we don't have any pre-defined in SQL, that splits the string containing the delimiter. While during development, it is sometimes required the functionality of split function similar to that of C# in SQL too.

After a very long time, techiners back again with a new post that will explain the process of creating a function that split the string containing the delimiter, which accepts two parameters- one string and other delimiter.

The first parameter is the string that contains the delimiter and needs to be split across it.

So without much ado, lets start...

How to find nth highest salary

How to find nth highest salary?

At the time of interview a very common question that is asked to almost all the candidates is to find the 2nd highest salary. And very often too candidates are unable to answer it. To find highest salary is not a big deal. But to find second highest salary... need to think a bit. But again, not a difficult task.

SQL Query Performance Issue

SQL Query Performance Issue

SQL Query Performance

As a developer we need to take care a lot of performance of the system that we develop. Recently in one of my project I got a task to check the performance of the query. I tried with three different ways using joins each returning the same result. BUT...

Create a method that execute a SP irrespective of number of parameters.

We as a developer always use Stored Procedure for retrieving results from database. We write code to execute stored procedure and pass all the required parameters using Command Object. But writing all code including connection string, command object, AddWithValue() parameters and executing it every time when you have to execute a stored procedure with different parameters is a tedious task. As a developer, we must be lazy while writing our code which give rise to re-usability. This post will explain how to execute Stored Procedure irrespective of the number parameters where in we just have to pass command object and an empty DataSet object to retrieve the result.