Category: Data Design

Here is a short example. Let’s say we have this sample JSON output. We would like to split it on “}” characters;

DECLARE @json NVARCHAR(MAX) = '[{
  "text": "Books",
  "nodes": [{
	"text": "Programming Books",
	"rackNumber": "1",
	"moreInfo": [{
			"text": "C# book",
			"price": "$49.99"
		  },
		  {
			"text": "React book",
			"price": "$40.99"
		  }
		]},
		{
	"text": "Database Books",
	"rackNumber": "2",
	"moreInfo": [{
			"text": "SQL Server 2022",
			"price": "$52.99"
  		  },
		  {
			"text": "Maria database",
			"price": "$20.99"
		  }
		]}
	]
}]
'

SQL Server has some handy functions, STRING_Split, to split and to re-create, STRING_AGG. I am going to demo it here;

----split strings to make changes
IF OBJECT_ID('tempdb..#ADDF') IS NOT NULL DROP TABLE #ADDF
;WITH CTE AS
 (
	SELECT value  from STRING_Split(@json, '}')
 )
 SELECT * INTO #ADDF FROM CTE;
 //show me the JSON. The string should be split in two parts
 SELECT * FROM #ADDF

 //TODO..do whatever you want to do with the string JSON array

--return modified string. You should see a single line JSON output here 
SELECT STRING_AGG(value, '}') AS jsonBack
FROM #ADDF;

Happy coding.

SQL Server has a pretty good support for JSON. If there is a special character in JSON, then it will throw this error;

JSON path is not properly formatted. Unexpected character ‘#’ is found at position 2

To produce this, here is an example

DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'

-- Print the current JSON
PRINT @data

-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @data=
 JSON_MODIFY(
  JSON_MODIFY(@data,'$.Contractor', JSON_VALUE(@data,'$.#Name')),
  '$.#Name',
  NULL
 )
-- Print the new JSON
PRINT @data

I didn’t find any built-in support to handle these characters. The workaround I found is to simply replace special characters. Here is how;

DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'
DECLARE @cleanData NVARCHAR(50) = REPLACE(@data, '#Name', 'Name');
-- Print the current JSON
PRINT @data

-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @cleanData=
 JSON_MODIFY(
  JSON_MODIFY(@cleanData,'$.Contractor', JSON_VALUE(@cleanData,'$.Name')),
  '$.Name',
  NULL
 )
-- Print the new JSON
PRINT @cleanData

And the output is;

{“#Name”:”Shahzad”}
{“Contractor”:”Shahzad”}

References

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?source=recommendations&view=sql-server-ver16

Database Guides

Lets take a look at two queries using CURSORS, the first one will use the FORWARD_ONLY type cursor, and the second will use the FAST_FORWARD type cursor. These two types sound very similar, but perform quite differently.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;
 
SET @PeoplePhoneCursor = CURSOR FORWARD_ONLY FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;
 
OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
     PRINT ISNULL(@firstName, '') + ' ' +
           ISNULL(@middleName, '') + ' ' +
           ISNULL(@lastName, '')  +
           ' Phone: ' + ISNULL(@phone, '') ;
     FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

Now for the FAST_FORWARD CURSOR example. Notice only one line has changed, that’s the line that says “SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR”.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;
 
-- HERE IS WHERE WE SET THE CURSOR TO BE FAST_FORWARD
SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;
 
OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
     PRINT ISNULL(@firstName, '') + ' ' +
           ISNULL(@middleName, '') + ' ' +
           ISNULL(@lastName, '')  +
           ' Phone: ' + ISNULL(@phone, '') ;
     FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

The FORWARD_ONLY CURSOR takes 4 times the time as the FAST FORWARD CURSOR, and the number continues to widen as the number of times the cursor loops is executed.

FAST FORWARD CURSORS are usually the fastest option with SQL Server. There may be cases where another option may work better, but the FAST FORWARD CURSOR is a good place to start if you must use a CURSOR.

The best practice is to avoid cursor but sometime they are inevitable. The alternatives could be temp tables, while loops etc.