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.

WITH RESULT SETS

SQL Server 2012 introduces new clause WITH RESULT SETS which allows us to redefine the Column Names and Data types of columns being returned from from Stored Procedure.

Limitations of WITH RESULT  SETS:

1. WITH RESULT SETS can only be used with Execute statement
2. WITH RESULT SETS cannot be used with INSERT Statement.
3.  Cannot change the number of columns returned by Result sets.

Example:

Creating a Stored Procedure:

Create proc Example_Withresultsets

as

begin

       Select top 10 concat(Firstname,'.',Middlename,'.',LastName) as PersonName,BusinessEntityID as EntityID from person.Person

      

end

/* Execute Stored Procedure*/

Exec Example_Withresultsets

/* Executing Stored Procedure with result sets clause*/

Exec Example_Withresultsets

with result sets

((

PersonFullName Varchar(100),

BusinessEntityID int

))

In the above image you can find that the column names are changed second resut set and this result is achieved by using the WITH RESULT SETS clause along with Execute statement.

THROW In SQL Server 2012

THROW is a newly introduced key word in SQL Server 2012 which used to throw the error from catch block. It’s a new enhancement in Error Handling in SQL Server.

It used as the same way as RAISEERROR() function in SQL Server 2008.

Example with Raiseerror:

BEGIN TRY

       SELECT 1/0

END TRY

BEGIN CATCH

DECLARE

   @ErMessage NVARCHAR(2048),

   @ErSeverity INT,

   @ErState INT

 SELECT

   @ErMessage = ERROR_MESSAGE(),

   @ErSeverity = ERROR_SEVERITY(),

   @ErState = ERROR_STATE()

 RAISERROR (@ErMessage,

             @ErSeverity,

             @ErState )

END CATCH

(0 row(s) affected)

Msg 50000, Level 16, State 1, Line 15

Divide by zero error encountered.

Example with THROW:

BEGIN TRY

       SELECT 1/0

END TRY

BEGIN CATCH

       THROW

END CATCH

(0 row(s) affected)

Msg 8134, Level 16, State 1, Line 2

Divide by zero error encountered.

OFFSET FETCH Clause In SQL Server 2012

OFFSET FETCH Clasue gives us an option to select the range of records from table.

This feature is very useful in implementing the paging in front end applications.

Syntax:

ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n][<offset_fetch>]

OFFSET : Specifies the number of row to skip.

FETCH  (First/Next)  <No.of Rows>  ROWS ONLY : Specifies the next  number of rows to be returned.

Limitations of OFFSET FETCH:

1. OFFSET FETCH can only be Used with ORDER BY Clause.
2. TOP caluse cannot be used with OFFSET FETCH caluse.
3. OFFSET FETCH rowcount be expression,parameter,constant.

Examples:

Skips the first 10 rows and returns rest of the rows from person table.

select Firstname+' '+LAstName as NAME,BusinessEntityID from person.person ORDER BY BusinessEntityID OFFSET 10 ROWS

Skipping the First 10 rows and fetching the next 5 rows

select Firstname+' '+LAstName as NAME,BusinessEntityID from person.person ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 5 ROWS only

Passing the Rowcounts as parameters

Declare @Startposition int=15,@nextset int=10

select Firstname+' '+LAstName as NAME,BusinessEntityID from person.person ORDER BY BusinessEntityID OFFSET @Startposition ROWS FETCH NEXT @nextset ROWS ONLY

CONCAT in SQL Server 2012

CONCAT is one of the new string function which introduced in SQL server 2012.

CONCAT:

CONCAT function takes  ‘n’ number of arguments and concatenates them into single string.

Syntax:

CONCAT ( value1, value2………ValueN)

Properties of CONCAT Function:

1. It takes minimum two values else it will throws an error
2.  All Values are implicitly converted to String type
3.  If all values are NULL then it returns an empty string of Varchar(1)  

Example:

SELECT CONCAT ( 'Today Is',07, '/', 10,' Of July' ) AS Result;

And output is:

SELECT CONCAT ( 'Today Is', datepart(dd,getdate()),'/',datepart(mm,getdate()) ) AS Result;

New Logical Functions in SQL Server 2012

Following are the New Logical functions which are introduced in SQL Server 2012

a.      CHOOSE

b.      IIF

Now we look at the each function in detail.

CHOOSE:

    Returns the item at a specified index from a list.

    Syntax:

     CHOOSE ( index, val_1, val_2 [, val_n ] )

    Where Val_1,Val_2…are the list of item of any datatype and index specifies which Item to be returned from list.

    Example:

    Select CHOOSE(2,'Pranay','P','April') as Result;

    And result would be

    

    

   Now we look at the another example

  USE AdventureWorks2012;

  GO

  SELECT ProductCategoryID, CHOOSE (ProductCategoryID, 'A','B','C','D','E') AS Expression1

  FROM Production.ProductCategory;

  

In previous version of SQL sever, to get the same result set as above we need to use the case statement.

SELECT CASE PRODUCTCATEGORYID

          WHEN 1 THEN 'A'

          WHEN 2 THEN 'B'

          WHEN 3 THEN 'C'

          WHEN 4 THEN 'D'

          END AS EXPRESSION1,PRODUCTCATEGORYID

          FROM PRODUCTION.PRODUCTCATEGORY

the result of above query will be same as CHOOSE function’s result set.

We cannot use the Comparison statement in CHOOSE statemet and CHOOSE cannot be used in the WHERE Clause.

IIF:

IIF Function behaves exactly same as IIF funtion in .NET or in any other programming language.

It returns one of the two two values depending on the boolean expression.

Syntax:

  IIF ( boolean_expression, true_value, false_value )

Example:

 SELECT IIF ( 10 >20, 'TRUE', 'FALSE' ) AS Result;

SELECT IIF ( 10 < 20, 'TRUE', 'FALSE' ) AS Result;