SQL Server TRY_CONVERT returns an error instead of NULL

Can this be happened? The functions TRY_CONVERT and TRY_CAST have been designed for handling errors and they should return NULL when there is an error.

But, it is not always true.

What is the reason? Although it returns NULL when an error occurred with the conversion, it does not happen if the conversion is not a permittable. The above code tries to convert an integer into an unique identifier that is not permitted, hence it fails with an error. 

Calculate the average value against a large table - SQL Server Brain Basher of the Week #068

Let's talk about another Interview Question related to SQL Server development. This is about one of the aggregate functions that is AVG. Here is the question;

You have a table called SalesOrderHeader and it has millions of records. It has a column called Freight and you need to the average of it. You need to make sure that only 10% of records is used for calculating the average.

What would be the best way? The standard answer is, write a query to get 10% of records using TOP and calculate the average.

SELECT AVG(t.Freight) AverageFreight
FROM (SELECT TOP 10 PERCENT Freight
 FROM Sales.SalesOrderHeader) t

But this might not give you the accurate average as you consider only first set of records. It will be more accurate if you pick records randomly and then calculate the average. You may add ORDER BY to your statement with NEW_ID function.

SELECT AVG(t.Freight) AverageFreight
FROM (SELECT TOP 10 PERCENT Freight
 FROM Sales.SalesOrderHeader
 ORDER BY NEWID()) t

Second method gives the most accurate value as it picks records randomly. However the cost is high with the statement. There is another way to achieve the same. It is using TABLESAMPLE operator.

SELECT AVG(Freight) AverageFreight
FROM Sales.SalesOrderHeader
TABLESAMPLE (10 PERCENT)

Here is the comparison between both methods. Notice the total cost.

As you see, TABLESAMPLE gives better performance than the first method.

Since it picks records randomly, the average it returns different at each execution. If you need to same value for all your executions, REPEATABLE option has to be used with repeat_seed. 

SELECT AVG(Freight) AverageFreight
FROM Sales.SalesOrderHeader
TABLESAMPLE (10 PERCENT) REPEATABLE (1)

You will get the same average as long as the repeat_seed is same.

SQL Server - All Pooled Connections in use? How many Pooled Connections are there?

You may continuously experience the following error with your application;

Unhandled Exception: System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

You may rarely see sudden crash in your application. You may see that SQL Server suddenly hangs forever until you restart the service. There could be many reasons for this and one of the reasons could be related to the Connection Pool.

What is Connection Pooling? How does it work and what limitations are there with it? How can I check whether there are pooled connections? These are the things you should know if you experience an issue related to it.

Let's understand Connection Pooling

Establishing a connection goes through several steps that need resources and has a cost. If an application continuously uses the SQL Server Database by opening and closing the connection, it is better to keep the connection without discarding and reuse when required without re-creating for each an every call. This is purely for improving the performance. When the application first time tries to make the connection, it creates the Connection Pool. Then the connection is added to the pool and once the connection is closed, it will be kept for a certain time period for reusing for new requests come for the same Connection Pool. This connection is called as Pooled Connection. If the Pooled Connection is being used and a new request is received for the same Connection Pool, then a new Pooled Connection is created in the same pool and used. Connection Pool is based on several keys in connection string and they are used for identifying the right Connection Pool to get a connection. If the request comes from a different connection string, it creates a new Connection Pool and maintains Pooled Connection in it.

By default, maximum number of Pooled Connections can be maintained in a Connection Pool is 100. If there are 100 Pooled Connections in the pool and all are being used, and another request comes for the same pool, you will experience one of above mentioned issue.

How can I see the number of Pooled Connections in Connection Pool?

There are multiple ways of seeing this. What I use is a simple TSQL as below;

xxx

This gives you connections and number of Pooled Connections (NumberOfConnections). There is no specific threshold for determining whether the number shows is good or bad. However, if you see ALWAYS see a number above 50, that might indicate an issue.

Why I see a higher number of Pooled Connections?

There can be two main reasons for seeing a larger number. One is, obviously, if you have many number of concurrent users working with your database using the same connection string. Second is, your application does not close the connection used and opens again. It will add a new Pooled Connection to the pool. Remember, opened connections cannot be used until they are closed (There can be exceptions, see the below example).

See the below C#.Net code;

int x = 1;
SqlConnection con;
SqlCommand cmd;
string sql;

while (x < 1000)
{
        con = new SqlConnection("Server=.;Database=AdventureWorks2014;Integrated Security=SSPI;");
    con.Open();
    sql = "BEGIN TRAN; SELECT * FROM Production.Product WHERE ProductID = " + x.ToString();
    cmd = new SqlCommand(sql, con);
    cmd.ExecuteNonQuery();
    Console.WriteLine(x.ToString());
    x = x + 1;
}

As you see, I executes the database code many times. Once executed, it will iterate the loop making multiple connections in the same pool and at a certain stage, an exception is thrown;

And if I check my TSQL code at this time, I will see that number of Pooled Connection as 100 for the application session.

Once the pool is filled with 100 connections, it cannot add another connection to the pool. Did you notice that code has created through 238 connections? This indicates that some connections have been reused even though they are not closed. However, this is something you need to always check if you get this error. If you see that code does not close the connection, it is better to check all codes and add closing code, making sure that the connection can be reused and no connection pool related errors.

Can I create connection without adding them to Connection Pool?

Yes, it is possible though it is not recommended. If you add Pooling=False to the connection string, it will not add to connections to the pool but you may experience some performance issue.

ODBC Scalar Functions in SQL Server

Do you know that you can call set of ODBC Scalar functions inside SQL Server? Yes, it is possible and they can be used with ad-hoc statements, stored procedures and functions.

There are different types of functions and they are categorized as String, Numeric, Date time, System Functions, and Data type conversion. You can see the list at https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-e-scalar-functions.

When you call an ODBC function, you need to make sure it is encased with curly brackets and started with fn. Arguments that are required for the function can be mentioned with parenthesis.

Here are some examples;

SELECT {fn ASCII ('A')} As Column1;
SELECT {fn Now()} As Column1;
SELECT 'Hello' + {fn Space(10)} + 'World' As Column1;
SELECT {fn USER()} As Column1;

Which protocol has been used for my SQL Server connection?

SQL Server uses 3 protocols to make the communication between client and the server. Initially there were 4 protocols but now it supports only 3: Shared Memory, Named Pipes and TCP/IP. We can enable/disable these protocols from server-end and change the priority order from client-end. Now, how do I know which protocol has been used for my connection?

We can easily see this by using sys.dm_exec_connection dynamic management view. It shows all current connection along with the used protocol. The net_transport is the one that shows it.

Here is a sample code. The first connection 54, was made without specifying anything additional when connecting, hence it has used Shared Memory. This protocol is used when it is enabled and connection made using the same machine that hosts the SQL Server. The second connection 56 has been established using Named Pipes because I forced to use Named Pipes for my connection.

How can I force the protocol when connecting via SSMS? It is simple. When connecting, if you use lpc: as the prefix for the server name, it uses Shared Memory. If you use np:, then it uses Named Piped.

Adding a Hash column using HASHBYTES based on all columns to all tables

We use either Checksum or Hashbytes for generating a value for finding changes of records when need to transfer records from one source to another and changes cannot be identified at the source end. This is specially used in data warehousing. I have written two posts on it, you can read them for getting an idea on it;

Incremental ETL: How to identify changes for fact data when no support from the source

Ways of incrementally loading data to a data warehouse

I had to implement similar with another database but it was not at the design stage. The database is already developed and being used, and it was over 1TB. The requirement was, adding a column that has Hash based on all existing columns. Since there were more than 300 tables, it was not practical to open the Design of the table and add the column. The only option I had was, form a dynamic query that alters the table by adding the column. I had to consider few things with the implementation;

• All tables should be altered.
• All columns should be used for generating the Hash.
• Tables that have records must set Hash immediately.
• Null must be replaced with blank because Hashbytes does not accept nulls.

Considering all, I wrote the following code for altering tables. You may use the same if you have the same requirement;

-- Getting table names into a table
-- A temporary table or table variable can be used for this
SELECT ROW_NUMBER() OVER(ORDER BY NofRecords) Id, TableName, TableId, NofRecords 
INTO dbo.TableNames
FROM
 (
 SELECT t.name TableName, t.object_id TableId, SUM(p.rows) NofRecords 
 FROM sys.partitions p
  INNER JOIN sys.tables t
   ON p.object_id = t.object_id
 WHERE p.index_id < 2 AND t.type = 'U'
 GROUP BY t.name, t.object_id) AS t;

-- Adding a clustered index
-- This is not required if the nof tables is low
CREATE CLUSTERED INDEX IX_TableNames ON dbo.TableNames (Id);
GO

DECLARE @Id int = 1;
DECLARE @LastId int = 0;
DECLARE @TableName varchar(500)
DECLARE @TableId int
DECLARE @Sql varchar(max)
DECLARE @Columns varchar(8000)

SELECT @LastId = COUNT(*) FROM dbo.TableNames;

-- Iterate through all tables
WHILE (@Id <= @LastId)
BEGIN

 SELECT @TableName = TableName, @TableId = TableId FROM dbo.TableNames WHERE Id = @Id;
 
 SET @Sql = 'ALTER TABLE dbo.' + @TableName;
 SET @Sql += ' ADD ';
 SET @Sql += ' MIG_HashKey AS HASHBYTES(''MD5'', ';
 
 -- get all columns, convert them to varchar
 -- and replace null with blank value
 SELECT @Columns = STUFF((SELECT '+ IsNull(Convert(varchar(4000), ' + name + '),'''')' FROM sys.columns WHERE object_id = @TableId FOR XML PATH ('')), 1, 2, '');

 SET @Sql += @Columns;
 SET @Sql += ') ';

 -- Execute the code
 BEGIN TRY
  EXEC ( @sql);
 END TRY
 BEGIN CATCH

  PRINT ERROR_MESSAGE()
  PRINT @Sql;
 END CATCH

 SET @Sql = '';
 SET @Columns = ''
 SET @Id += 1; 
END
GO

How to get the total row count of all SQL Server tables

I had a requirement for getting the record count of all tables in one of client databases that had many tables with over 10 millions records. There are many ways of getting this, hence explored some to find the most efficient way. I analyzed many techniques using various approaches. Here are some of the ways I used and time it took for producing the result;

1. Using sys.partitions Catalog View - 1 second
2. Using SELECT COUNT(*) with sp_MSforeachtable - 10 minutes
3. Using sys.indexes and dm_db_partition_stats - 1 seconds

One thing we need to remember is, the database we have to work with can have tables with different structures. One can have a heap and another can have clustered structure. Not only that, if we use Dynamic Management Objects or objects that depend on Statistics, we may not get the accurate output. However, 1st option worked well for me, here is the code I wrote for getting result;

SELECT ROW_NUMBER() OVER(ORDER BY NofRecords) Id, TableName, TableId, NofRecords 
--INTO dbo.TableNames
FROM
 (
 SELECT t.name TableName, t.object_id TableId, SUM(p.rows) NofRecords 
 FROM sys.partitions p
  INNER JOIN sys.tables t
   ON p.object_id = t.object_id
 WHERE p.index_id < 2 AND t.type = 'U'
 GROUP BY t.name, t.object_id) AS t;

Get all SQL Server tables that have IDENTITY enabled

Here is a useful script. If you need to find out tables that have Identity property enabled, you can simply query the sys.tables Catalog View combining with OBJECTPROPERTY function.

USE AdventureWorks2014;
GO

SELECT SCHEMA_NAME(schema_id) + '.' + name TableName 
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasIdentity') = 1;

Remember, this OBJECTPROPERTY function can be used to check many properties related to SQL Server objects. See this MSDN page for more details on it;

https://msdn.microsoft.com/en-us/library/ms176105.aspx

CHECK constraints accepts values that evaluate to UNKNOWN

Few days back, I wrote a post titled as SQL Server Default and Rule objects - Should not I use them now? that discussed two objects that are deprecated that can be used for enforcing the data integrity. I received a question based on it, related CHECK Constraint.

CHECK Constraint limits the values for columns based on the condition added. It can be set with a column, or it can be set for the entire record by adding it to the table. If you are adding CHECK Constraint for enforcing data integrity, you need to remember how it works.

CHECK Constraint works with any Boolean Expression that can return True, False or Unknown. If the value is False, it will be rejected and if the value if True, it will be accepted. However, if the value is Unknown, then it accepts it without rejecting. Therefore, you need to be very careful with the condition you write because, if the condition returns NULL, then it will be treated as True.

You can understand it by looking at the following code;

USE tempdb;
GO

CREATE TABLE dbo.Student
(
 StudentId int primary key
 , Name varchar(100) NOT NULL
 , Marks int NULL
 , Credit int NOT NULL
 , Constraint ck_Student_Marks_Credit CHECK (Marks + Credit > 100)
);
GO

-- This record can be inserted
INSERT INTO dbo.Student 
 VALUES (1, 'Dinesh', 60, 55);

-- This record cannot be inserted
INSERT INTO dbo.Student 
 VALUES (2, 'Yeshan', 40, 40);

-- This record CAN BE INSERTED
INSERT INTO dbo.Student 
 VALUES (3, 'Priyankara', null, 60);

SQL Server Default and Rule objects - Should not I use them now?

In order to make sure that the database contains high quality data, we ensure data integrity with our data that refers to the consistency and accuracy of data stored. There are different types of data integrity that can be enforced at different levels of solutions. Among these types, we have three types called Domain, Entity and Referential Integrity that are specific to database level for enforcing data integrity.

For enforcing Domain Integrity, SQL Server has given two types of objects called Default and Rule. We have been using these objects for handling Domain Integrity but now it is not recommended to use these for enforcing Domain Integrity.

Let's try to understand what these objects first and see the usage. Default object can be used for creating an object that holds a default value and it can be bound to a column of the table. Rule is same as Default and it creates an object for maintaining rules for columns. See below code as an example.

USE tempdb;
GO

-- creating default object
CREATE DEFAULT CreditLimitDefault AS 10000;
GO

-- creating a sample table
CREATE TABLE dbo.Customer
(
 CustomerId int PRIMARY KEY
 , LastName varchar(50) NOT NULL
 , CreditLimit decimal(16,4) NOT NULL
);
GO

-- Binding the default to a column
-- The object can be bound to many tables
EXEC sp_bindefault 'CreditLimitDefault', 'dbo.Customer.CreditLimit';
GO

-- creating rule object
CREATE RULE CreditLimitRule AS @CreditLimit > 9000;
GO

-- Binding the rule to a column
-- The object can be bound to many tables
EXEC sp_bindrule 'CreditLimitRule', 'dbo.Customer.CreditLimit';

As you see, above code creates two objects, CreditLimitDefault and CreditLimitRule that are Default and Rule objects. These objects can be assigned to any column in any table.

As I mentioned above, it is not recommended to use them now as they are deprecated. It is recommended to use Default and Check constraints instead.

Read more on CREATE DEFAULT at: https://msdn.microsoft.com/en-us/library/ms173565.aspx

Read more on CREATE RULE at: https://msdn.microsoft.com/en-us/library/ms188064.aspx

How to hide SysStartTime and SysEndEtime columns in Temporal Tables

Temporal table was introduced with SQL Server 2016 and it is designed to capture and store changes of data in tables. In other words, similar to Change Data Capture (CDC), Change Tracking (CT), Temporal table maintains the history with changed details.

Temporal table needs two additional columns called SysStartTime and SysEndTime. Once they are added, they can be seen with the table just like other columns and will be appeared with SELECT * statement. Although it is not recommended to write SELECT * type of query against tables, unfortunately it can still be seen with many application and the database I had to analyze today had similar codes in almost all areas in the application. I had to make two tables as Temporal Tables and I had to make sure that it does not break the existing application.

Fortunately, SQL Server has given a solution for handling it. I was able to alter the table and make it as a Temporal Table without making changes to any statement written in the application while making sure that SELECT * does not return newly added SysStartTime and SysEndTime columns.

If you use, HIDDEN keyword when creating the Temporal Table, it makes sure that these two columns are not appeared when SELECT * is performed. However, columns can be explicitly mentioned in the SELECT if required.

-- changing existing table by adding columns
ALTER TABLE dbo.Customer  
ADD  ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN 
  CONSTRAINT DF_SysStartTime 
  DEFAULT CONVERT(datetime2 (0), '2017-02-24 00:00:00')
 , ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN
  CONSTRAINT DF_SysEndTime 
  DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59')
 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);  
GO

-- turning versioning on
ALTER TABLE dbo.Customer
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));  
GO

-- Checking records  
SELECT * FROM dbo.Customer;  
SELECT *, ValidFrom, ValidTo FROM dbo.Customer;
SELECT * FROM dbo.CustomerHistory;  

SQL Server Date, Datetime and Datetime2 - What should I use when I get the value as a string?

When we have to store a datatime value (or date), in most cases, application accepts the value as a datetime and send the value to SQL Server as a datetime value. However, if the value is sent as a string (Example, CSV upload), then what should be the best way of formatting the value and how we can convert it to datatime without making any mistake?

It is always recommended to use ISO 8601 standard when exchanging datatime values. The standard describes the way of passing a datetime value, generally it is YYYY-MM-DDTHH:MM:SS.sss. You can read more on this my post: Best way to pass datetime values to SQL Server – SS SLUG Dec 2013 – Brain Bashers - Demo III.

With SQL Server 2016, the default string format for dates is YYYY-MM-DD. If you pass the value with this format, regardless of the Current Language set, SQL Server will accurately read the value. However, this does not work with all datetime data types as expected. Have a look on the following code;

SET LANGUAGE English;

DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';

SELECT DATENAME(mm, @Date) As WithEnglish;
SELECT DATENAME(mm, @Datetime) As WithEnglish;
SELECT DATENAME(mm, @Datetime2)As WithEnglish;
GO


SET LANGUAGE German;

DECLARE @Date date
DECLARE @Datetime datetime
DECLARE @Datetime2 datetime2

SET @Date = '2017-05-06';
SET @Datetime = '2017-05-06';
SET @Datetime2 = '2017-05-06';


SELECT DATENAME(mm, @Date) As WithGerman;
SELECT DATENAME(mm, @Datetime) As WithGerman;
SELECT DATENAME(mm, @Datetime2) As WithGerman;

As you see, Datetime data type convert happens based on the language set but Date and Datetime2 data types are accurately interpreted regardless of the language set. This is something you have to remember. If you expect datetime values as string and settings related to the session can be changed, then it is always better to use either Date or Datetime2.

If you need to make sure that date is properly getting interpreted regardless of the settings (language), then stick into ISO 8601. If you change the values of variable as below, you will get the same month: May for both English and German.

SET @Date = '2017-05-06T00:00:00';
SET @Datetime = '2017-05-06T00:00:00';
SET @Datetime2 = '2017-05-06T00:00:00';

Note that ISO 8601 accepts a value like 24:00:00 for time for midnight but SQL Server does not support it.

SQL Server Sequence suddenly starts with -2147483648

Have you faced this? Assume that you have created a Sequence object for generating values sequentially for tables which data type is set as int, and suddenly it shows the next number as -2147483648.

See below code;

-- creating sequence
CREATE SEQUENCE [dbo].[OrderSequence]
AS int
START WITH 1 
INCREMENT BY 1 
MAXVALUE 2 
CYCLE
GO

-- requesting numbers three times
SELECT (NEXT VALUE FOR   dbo.OrderSequence) AS OrderSequence
GO 3

What could be the reason? If you analyze the code written above, you can easily find the issue. I have not used MINVALUE property when creating the Sequence, hence it takes the lowest value of the data type set for the Sequence, which is -2147483648 for int data type. You may experience the same, if so, check and see whether the MINVALUE has been set or not.

Can we access the SQL Server temporary table created in different database?

Temporary tables are nothing new and we have been using this for long time. There are two types of temporary tables; Local that starts with single pound sign (#) and Global that starts with double pound signs (##). Local Temporary Tables are limited to the connection created and will be discarded automatically when the connection is disconnected. Global Temporary Tables are global to the instance and it can be accessed by the anyone connected the instance. It will be dropped automatically when the last referenced connection is dropped.

Now the question is, when a Local Temporary Table is created, can I access it in another database?

Answer is yes and no. See the code below.

USE Sales;
GO

CREATE TABLE #TempTable
(
 Id int
);
GO

-- This works without any issue
SELECT * FROM #TempTable;

The created table can be access without any issue because access is done in the same database using the same connection. If we try the SELECT in another database with different window (different connection);

USE Sales;
GO

-- This will throw "Invalid object name '#TempTable'." error.
SELECT * FROM #TempTable;

You will see an error as above. However if I try to access the table from the same connection but different database;

--USE Sales;
--GO

--CREATE TABLE #TempTable
--(
-- Id int
--);
--GO

--SELECT * FROM #TempTable;

-- Same first connection but different database
USE AdventureWorks2014;
GO

-- This will work
SELECT * FROM #TempTable;

As you see, it is possible. Remember, Local Temporary Tables are limited to the connection, not to the database created, hence the created table can be accessed within any database as long as the connection is same.

Changing Schema of SQL Server objects

I had a requirement today to change the schema of set of tables to new schema but I did not find a direct method to change the schema of all objects using a single statement. The ALTER SCHEMA supports transferring one object from one schema to another but it cannot be executed against multiple tables.

USE AdventureWorks2014;
GO

-- transferring Person table from Person Schema to Sales
-- Once executed, tables becomes Sales.Person
ALTER SCHEMA Sales TRANSFER Person.Person;

Therefore I wrote a simple code for transferring multiple tables (of course code can be changed for addressing any object type) and thought to share it because you may look for something similar if you have the same need.

USE AdventureWorks2014;
GO


DECLARE @TableNames TABLE (Id int identity(1,1) PRIMARY KEY, Name varchar(500));
DECLARE @Messages TABLE (Id int identity(1,1) PRIMARY KEY, Message varchar(1000));

DECLARE @TableName varchar(500);
DECLARE @TableId int = 1
DECLARE @NewSchema varchar(20) = 'Sales';
DECLARE @OldSchema varchar(20) = 'Production';
DECLARE @Statement varchar(500)

-- table all table names needed
INSERT INTO @TableNames
 (Name)
SELECT s.name + '.' + t.name TableName
FROM sys.tables t
 INNER JOIN sys.schemas s
  ON t.schema_id = s.schema_id
WHERE s.name = @OldSchema
 AND t.type = 'U';

-- making the ALTER SCHEMA statement for all tables
-- and execute them using EXEC
WHILE EXISTS (SELECT * FROM @TableNames WHERE Id = @TableId)
BEGIN
 SELECT @TableName = Name FROM @TableNames WHERE Id = @TableId;
 SET @Statement = 'ALTER SCHEMA ' + @NewSchema + ' TRANSFER ' + @TableName;
 BEGIN TRY

  EXEC (@Statement);
  -- record success message
  INSERT INTO @Messages (Message) VALUES ('Successfully transfered ' + @TableName + ' to ' + @NewSchema);
 END TRY
 BEGIN CATCH
  
  -- record the error
  INSERT INTO @Messages (Message) VALUES ('Transfer unsuccessful: ' + @TableName + ' [' + ERROR_MESSAGE());
 END CATCH

 SET @TableId += 1;
END

-- checking the output
SELECT * FROM @Messages;

Splitting values in a string variable and inserting values as rows - II - STRING_SPLIT function

Yesterday I wrong a post on splitting string values using an Extended Function. The biggest issue with it was, unavailability in Azure SQL Database. However, we have a new function to achieve it and it works in both SQL Server 2016 and Azure SQL Database.

This is how it works;

DECLARE @String nvarchar(4000) = 'SQL,Business Intelligence,Azure';

SELECT *
FROM STRING_SPLIT(@String, ',');

No more complex queries for splitting values :).

Splitting values in a string variable and inserting values as rows - I

Challenges are interesting and finding various ways to solve is a thrilling adventure.

One of the codes I had to write today was, splitting a string value received from an ASP.Net application and inserting them into a table. There are many ways of splitting a string values (or sometime, converting columns into rows) but this was bit different. The values I receive from the application are something like;

"SQL Business_Intelligence Azure"

"Personal Fun_time Crazy_Stuff"

Now how can I convert them into individual values (as rows) and insert them into a table?

There is a useful extended stored procedure that allows us to split values in a string considering space as the separator. It is xp_sscanf. It has some limitations but it can be useful in some scenario.

You can read more on it at: https://msdn.microsoft.com/en-us/library/ms181431.aspx

Here is the function I wrote for splitting values;

USE tempdb;
GO

CREATE OR ALTER FUNCTION dbo.SplitString (@String nvarchar (4000))
RETURNS @Tags TABLE
(
 Tag nvarchar(200)
)
AS
BEGIN

 DECLARE @Tag1 nvarchar(200)
   , @Tag2 nvarchar(200)
   , @Tag3 nvarchar(200)
   , @Tag4 nvarchar(200)
   , @Tag5 nvarchar(200)
   , @Tag6 nvarchar(200)
   , @Tag7 nvarchar(200)
   , @Tag8 nvarchar(200)
   , @Tag9 nvarchar(200)
   , @Tag10 nvarchar(200)

 EXEC xp_sscanf @String, '%s %s %s %s %s %s %s %s %s %s',   
  @Tag1 OUTPUT, @Tag2 OUTPUT, @Tag3 OUTPUT, @Tag4 OUTPUT, @Tag5 OUTPUT
  , @Tag6 OUTPUT, @Tag7 OUTPUT, @Tag8 OUTPUT, @Tag9 OUTPUT, @Tag10 OUTPUT;  

 INSERT INTO @Tags
  (Tag)
 SELECT T.*
 FROM (
  SELECT REPLACE(@Tag1, '_', ' ') AS NewTag
  UNION ALL
  SELECT REPLACE(@Tag2, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag3, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag4, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag5, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag6, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag7, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag8, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag9, '_', ' ')
  UNION ALL
  SELECT REPLACE(@Tag10, '_', ' ')) AS T
 WHERE T.NewTag IS NOT NULL;

 RETURN;
END
GO

And this is how I can use it;

USE tempdb;
GO

IF OBJECT_ID('dbo.Tags') IS NOT NULL
 DROP TABLE dbo.Tags;

CREATE TABLE dbo.Tags
(
 TagId int identity(1,1) primary key
 , Tag nvarchar(200)
);
GO

DECLARE @String nvarchar(4000) = 'SQL Business_Intelligence Azure';

INSERT INTO dbo.Tags
 (Tag)
SELECT Tag
FROM dbo.SplitString (@String);

SELECT * FROM dbo.Tags;

As you see with the second script, I can simply pass the string received, get them split and insert to the required table.

Is this working in Azure SQL Database?
Unfortunately, it does not work in Azure SQL Database as Azure SQL does not support Extended Stored Procedure. However, good news is, SQL Server 2016 has a new function that can be used with both SQL Server 2016 and Azure SQL Database. Here is a sample code for it.

SQL Server - Adding an Authenticator when encrypting data

Among multiple methods given for securing data stored in the SQL Server database, even though the latest one which is Always Encrypted is available, we still use Keys. Passphrases and Certificates for encrypting data. When keys such as Symmetric Keys or Asymmetric Keys, or Passphrases are used for encrypting data, an additional parameter can be supplied which is called Authenticator. Since I recently used this for one of my database solutions, thought to make a note on it.

What is the usage of Authenticator? Why we should use it. Let's take an example and understand with it.

The following code creates a database and a table that holds Customers. The Security Code of the customer will be encrypted.

-- Create a database
CREATE DATABASE Sales;
GO

-- Connect with newly cerated database
USE Sales;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO 

-- Create a certificate for protecting the our key 
CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';
GO

-- Create the key for encrypting data
-- Note that the created certificate protects it.
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = AES_128  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

-- Creating a table that holds customers
-- Note the Securiry Code Column, it is varbinary 
-- because code will be encrypted and stored
CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) PRIMARY KEY
 , Name varchar(100) NOT NULL
 , SecurityCode varbinary(256) NOT NULL
);
GO

Let's insert some records.

OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ;
GO

INSERT INTO dbo.Customer
 (Name, SecurityCode)
VALUES
 ('Dinesh', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ'))

INSERT INTO dbo.Customer
 (Name, SecurityCode)
VALUES
 ('Yeshan', ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3'))

Once inserted, data will be looked like below;

And if I try to retrieve records, I need to decrypt encrypted values;

So far so good. Now let's try understand the usage of Authenticator. Assume that Yeshan needs to access some Securables that can be accessed only by Dinesh through an application. For that, all Yeshan needs is, log in to the application using Dinesh's Security Code. Since he does not know Dinesh's Security Code, one way of accessing the application using Dinesh's account is, replacing the Dinesh's code with his code. Let's see whether it is possible.

The following code updates Dinesh's account with Yeshan's code. And as you see, it gets updated and now Yeshan can use Dinesh account as he knows the code.

This should not be allowed and even if it is possible, what if we make sure that encrypted code cannot be replaced like that. That is what we can do with the Authenticator.

Look at the following code. It passes two additional values for encrypting. The third one which is 1 says that this has an Authenticator. The forth parameter is the data from which to derive an Authenticator.

OPEN SYMMETRIC KEY SalesKey DECRYPTION BY CERTIFICATE SalesCertificate ;
GO

-- Update Security codes with CustomerId as the Authenticator
UPDATE dbo.Customer
 SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS005XZ', 1, Convert(varbinary(256), CustomerId))
WHERE CustomerId = 1;

UPDATE dbo.Customer
 SET SecurityCode = ENCRYPTBYKEY(KEY_GUID('SalesKey'), 'CUS02ANX3', 1, Convert(varbinary(256), CustomerId))
WHERE CustomerId = 2;

Just like the previous code, values are encrypted now. However, if Yeshan tried to do the same, see the result;

As you see, even though Dinesh's code has been replaced with Yeshan's code, when try to decrypt value of Dinesh, it results null because Authenticator is different. This is the usage of the Authenticator.

Note that we used CustomerId as the Authenticator but you can use something else, something uncommon as the Authenticator to make it more secured and avoid malicious activities like this.

Incorrect syntax near 'TRIPLE_DES'. - SQL Server throws an error when try to use algorithms

Assume that you use SQL Server 2016 and trying to create a Symmetric Key or Asymmetric Key for encrypting data. If you try use an algorithm like TRIPLE_DES, you will get the mentioned error;

Msg 102, Level 15, State 1, Line 20

Incorrect syntax near 'TRIPLE_DES'.

Here is a sample code for seeing this error;

-- Create a database
CREATE DATABASE Sales;
GO

-- Connect with newly cerated database
USE Sales;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO 

-- Create a certificate for protecting the our key 
CREATE CERTIFICATE SalesCertificate WITH SUBJECT = 'Sales Certificate';
GO

-- Create the key for encrypting data
-- Note that the created certificate protects it.
-- However, this will throw an error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Now, what is the reason for this. The reason for this is, this algorithm is deprecated in SQL Server 2016. Not only that, All Algorithms except AES_128, AES_192, and AES_256 are deprecated in SQL Server 2016. 

What if you still need one of these deprecated algorithms? Yes, it is possible, but you need to downgrade the database by changing the Compatibility Level 120 or below.

This code shows the way of doing it.

USE master;
GO

-- Change the compatibility level to 120
ALTER DATABASE Sales
SET COMPATIBILITY_LEVEL = 120;

-- And check again
USE Sales;
GO

-- This will work now without any error
CREATE SYMMETRIC KEY SalesKey
WITH ALGORITHM = TRIPLE_DES  
ENCRYPTION BY CERTIFICATE SalesCertificate;
GO

Even though you can use the algorithm after changing the Compatibility Level, remember lower Compatibility Level might not let you to use all functionalities available with SQL Server 2016. Therefore, if possible, use allowed algorithms only.

SQL Server - Adding Code Snippet and Using Existing Code Snippet

Once I wrote a post on SQL Server Template Explorer that describes available TSQL templates and how they can be used. Just like templates, we have been given some ready-made Code Snippets that help us to construct the statements easily. Not only that, it allows us to add our own snippets using adding Code Snippet Manager.

First of all, let's see how we can use existing code snippets. Assume that you need to create a SQL Login and you cannot remember the syntax. What you can do is;

    1. Either select Insert snippet... context menu in the Query Window or press Ctrl+K and Ctrl+X  (Press Ctrl and hold, and then press K and X).

    

    2. Select Login folder and then select Create SQL Authentication Login.

    

    3. Change the code as you need.

    

Note that, like adding codes using Template Explorer, you do not get another interface for changing values. Values need to be manually changed.

If you need to add your own code snippet, you can take copy of an existing one, change as you want and save with your own name. Assume that you need to add Azure Firewall Setting as a code snippet. If so, here are the steps;

    1. Open the SQL Server Code Snippet folder. If you have selected the default location when installing SQL Server, the path would be C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SQL\Snippets\1033.

    2. Create a folder called Azure (or name as you need).

    3. Update the SnippetsIndex.xml. Add the following node to the file. This node is for the newly created folder. (** Note that this file cannot be modified if you have not opened the editor as Administrator. If change this using Notepad, open the Notepad as an Administrator and then open the file for modifictions).

    4. Take a copy of existing snippet and place in Azure folder. I have taken Create SQL Authentication Login.snippet and renamed as Create Azure Server Level Firewall Rule.

    5. Open Create Azure Server Level Firewall Rule file (Open as an Administrator) and modify -->