Hashing in SQL Server

Before we look into how to implement hashing in SQL Server, we need to understand the difference between hashing and encryption.

Encryption VS Hashing:

Encryption its a reversible operation i.e., you can decrypt the encrypted value with a key.
Hashing value cannot decrypted back to its original value.

HASHBYTES:

HASHBYTES function in SQL Server returns the hash value of a given input string.

this function uses following algorithms to hash the value.

1. MD2
2. MD4
3. MD5
4. SHA
5. SHA1
6. SHA2
7. SHA2_256(Applies to SQL Server 2012 to SQL Server 2014)
8. SHA2_512(Applies to SQL Server 2012 to SQL Server 2014)

this function returns type is Varbinary

syntax:

HASHBYTES ( '<algorithm>', { @input | 'input' } )

Example:

 DECLARE @HashThis nvarchar(4000);

 SET @HashThis = CONVERT(nvarchar(4000),'pranay1231232');

 SELECT HASHBYTES('SHA1', @HashThis);

 Result:

 0x52A99EAE34513AB695801F775D9F9FE2EEBBF3DB

we can use this function while storing the User Sensitive data.This is the much simple approach than TDE.

 

Splitting the Comma Separated String...

Following query is used to Split the Comma separated string into individual values.

This query can also be used to split any special character separated string.

Declare @P_text varchar(1000)

Set @P_text='1,2,3,4,5,6,7,8,9,10'

--select Charindex(',',@P_text)

--select PATINDEX('%,%',@P_text)

Declare @table as Table(Id Int)

Declare @x1 Varchar(100)

While CHARINDEX(',',@P_text) > 1

Begin

      set @x1=SUBSTRING(@P_text,0,PATINDEX('%,%',@P_text))

      Set @P_text=SUBSTRING(@P_text,PATINDEX('%,%',@P_text)+1,LEN(@P_text))

      Insert into @table(Id)

      select cast(@x1 as int)

End

Insert into @table

Select cast(@P_text as int)

select * from @table

Understanding System Databases

When we install SQL Server by default we will get 5 Databases.These can be seen under System Databases after you connect to SQL Server instance. The Default Databases are 

1. Master
2. Model
3. Msdb
4. Tempdb
5. Resourcedb

Resourcedb  is hidden database and it will not visible under system databases.

We will look at the functionality of each db individually.

Master : 

Master Database keeps the track of server installations and all other databases that are subsequently created.Master database has system catalogs that stores the information about system wide configurations such as 

•  endpoints
•  logins
•  Databases on current instance
•  Database files and usage
•  linked servers

Master database is critical to system.Operations such as creating another database,changing configuration values and modifying login accounts make modifications to Master database.

Model :

Model database is simply a template database, Every time we create a database SQL Server makes a copy of model to form the basis of the new database.

Msdb :

Msdb database is used by SQL Server Agent Service.Msdb stores the data related to following objects

• Jobs
• alerts
• log shipping
• policies
• database mail

Tempdb :

Tempdb is database is used as the work space. Tempdb is re created every time sql server restarted. It's used explicitly for temp tables created by users. Because Tempdb is re created, any objects that we create in it are lost the next time we restart the SQL Server instance.

Resourcedb :

The hidden mssqlsystemresourcedb is referred to as resource db. System objects such as system stored procedure and functions are stored here. Microsoft has created this database to allow fast and safe upgrades.

RowCount and Column Count For Each Table

Today, I got task to come up with the number of rows and number of columns for each table in given database.

This information is useful in defining the complexity while migrating database.

Below query is used to get such information.

Query:

SELECT A.NAME,COUNT(B.NAME) AS [NO.OFCOLUMNS],PT.ROW_COUNT --INTO #TEMP_COL

FROM SYS.TABLES A

JOIN SYS.COLUMNS B

ON A.OBJECT_ID=B.OBJECT_ID

JOIN SYS.DM_DB_PARTITION_STATS PT

ON A.OBJECT_ID=PT.OBJECT_ID

WHERE

PT.INDEX_ID < 2

GROUP BY A.NAME,PT.ROW_COUNT

Result:

Contained Database

Contained Database is a new feature of SQL Server 2012.

As per MS Contained Database is defined as:

“A Contained Database is database that is isolated from other databases and from the instance of SQL Server that hosts the Database. ”

In Simple words:

Now Database can store the User information/Credentials in Database it self instead of Instance level.

This feature is very useful when moving the database from one server to another server.

The containment setting of a database can be NONE,PARTIAL or FULL. 

SQL Server 2012 supports only NONE and PARTIAL Containment settings.

Requirements of Contained Databases:

1. Contained Databases need to enabled on instance
2. Contained Database need to be specified while connecting thru SSMS.

First we need to enable the contained databases at instance level. That we can do it in two ways.

USE master

GO

sp_configure 'show   advanced options', 1

GO

RECONFIGURE

GO

sp_configure 'CONTAINED   DATABASE AUTHENTICATION', 1

GO

RECONFIGURE

GO

sp_configure 'show   advanced options', 0

GO

RECONFIGURE

GO

Second way is to do it from SSMS.

From ssms right click on sqlserver instance then Click Adavnced Page then set the Enable Contained Database property to TRUE.

Once its enabled at instance level, we can create the database by stating Containment

When creating a database Database from SSMS, we need to mention the containment type property value to “Partial”  as shown in below image .

Once Database is created with Partial containment we need to create users in it. These users authenticated by Database rather than Instance.

USE [Contained_Demo]

GO

CREATE USER   [User_Demo] WITH   PASSWORD=N'demo1234',

DEFAULT_SCHEMA=[dbo]

GO

Now add the user to db_owner role

USE [Contained_Demo]

GO

ALTER ROLE   [db_owner] ADD   MEMBER [User_Demo]

let’s try to connect to the database using the user account created above, which is expected to be authenticated by the database:

Ohh..its throws an error. As mentioned in the above we need to mention the Database name in connection string.

In connection properties we need to mention the Database name 

When sucessfully authenticated by DB, User will see only DB to which user has access..

Advantages of Contained Database:

1. Easy to move database from one server to another.
2. Authentication is done by DB rather than Instance
3. contained Database users can be windows/Sql authenticated.

Limitations:

1. Partially Contained databases cannot use CDC,CT,Replication.

OFFSET Window Functions

OFFSET  window functions allow you to return value from a row that is at certain offset from current row.

SQL Server 2012 supports four OFFSET functions.

SQL Server 2012 supports four OFFSET functions.

1. LAG()
2. LEAD()
3. FIRST_VALUE()
4. LAST_VALUE()

The LAG() and LEAD() functions support window partition and window order clause. LAG function looks before the current row and LEAD function looks ahead. These functions accepts three arguments. The first argument to the function is the element you want to return,the second argument is the offset,third argument is the default value to return in case there is no row t the requested offset(NULL If not specified).

Example:

select s.SalesOrderID,s.SalesOrderDetailID,

LAG(s.SalesOrderDetailID) over(partition by salesorderid order by SalesOrderDetailID) LagValue,

LEAD(s.SalesOrderDetailID) over(partition by salesorderid order by SalesOrderDetailID) LeadValue

from Sales.SalesOrderDetail s

where salesorderid in (43659)

Because we did not mentioned the offset,the function assumed 1 default. In other words LAG returned the Value immediately previous sales order ID and LEAD immediately next.Also,because we did not mention the third argument,NULL was assumed by default when there was no previous or next row.

FIRST_VALUE() and LAST_VALUE() functions allow you to return an element from the first and last rows in the windows frame.

Example:

SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,

FIRST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) FirststValue,

LAST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LastValue

FROM Sales.SalesOrderDetail s

WHERE SalesOrderID IN (43659,43670)

OUTPUT Clause

Normally , Modification statement(Insert/Delete/Update) would not return any output except modifying the data. In some scenarios,being able to get data back from the modified rows can be useful. For example ,think advantages of Update statement returning  old and new values of the updated columns. This can be useful in Auditing ,trobleshooting, etc.,

SQL Server supports this capability thru OUTPUT clause

The OUPUT clause will return the requested attributes from the modified rows as result sets like SELECT statement does. IF you want to insert this result into a table add an INTO clause with target table name.

The OUTPUT clause can be used with INSERT,DELETE and UPDATE Statements.

INSERT with OUTPUT:

Following example shows the OUTPUT clause with INSERT statement.

Use AdventureWorks2012

Create table personinfo

(

EntityID Int,

Personname varchar(1000)

)

Insert into personinfo(EntityID,Personname)

OUTPUT inserted.EntityID,inserted.Personname

select BusinessEntityID, CONCAT(FirstName,MiddleName,LastName) as Personname from person.Person

where BusinessEntityID < 100

If you want insert this result set into a table then following query will be helpful

Declare @newrows table(EntityID Int,

Personname varchar(1000)

)

Insert into personinfo(EntityID,Personname)

OUTPUT inserted.EntityID,inserted.Personname

into @newrows

select BusinessEntityID, CONCAT(FirstName,MiddleName,LastName) as Personname from person.Person

where BusinessEntityID > 100 and  BusinessEntityID < 200

select * from @newrows

DELETE with OUTPUT:

Below query shows the OUTPUT clause with DELETE statement.

Delete from personinfo

Output deleted.EntityID,

          deleted.Personname

where EntityID < 100

UPDATE with OUTPUT:

Below query shows the OUTPUT clause with an UPDATE statement.

update personinfo set EntityID += 1--its equal to entityid=entityid+1

output

       inserted.EntityID as newentityid,

       deleted.EntityID as oldentityid

where EntityID < 150

OUTPUT Clause can be used with MERGE statement as well. We will see that MERGE related article.