MSSQL: How to read an UTF-8 file (XML or JSON)?

It is not sosimple to read an UTF-8 file.

Solution:
1. Read file as binary (BLOB)
then
2. Cast content to XML (also for JSON and other formats!!!)
then
3. Cast to the NVARCHAR(MAX)

For example:

SELECT Content = CAST(CAST(Content AS XML) AS NVARCHAR(MAX)) -- !!! Double CAST because of UTF-8 source
FROM OPENROWSET(BULK N'... Path to my file ...', SINGLE_BLOB) AS T(Content)

Then you can save this Content directly to the Variable:

DECLARE @Content NVARCHAR(MAX) = (
    SELECT Content = CAST(CAST(Content AS XML) AS NVARCHAR(MAX)) -- !!! Double CAST because of UTF-8 source
    FROM OPENROWSET(BULK N'... Constant path to my file ...', SINGLE_BLOB) AS T(Content)
)

If path to yout file is a constant, the it works.
But if you want to put this path as parameter, then will be necessary to use dynamic SQL:

DECLARE @FilePath NVARCHAR(260) = N'... Parametrized path to my file ...';
DECLARE @ContentTable TABLE (Content VARCHAR(MAX));
INSERT @ContentTable
EXEC ('
	SELECT Content = CAST(CAST(Content AS XML) AS NVARCHAR(MAX)) -- !!! Double CAST because of UTF-8 source
	FROM OPENROWSET(BULK N''' + @FilePath + ''', SINGLE_BLOB) AS T(Content)
');
DECLARE @Content NVARCHAR(MAX) = (SELECT Content FROM @ContentTable);

How to add the last version of LocalDB to Azure Devops Pipeline

How to add the last version of LocalDB to Azure Devops Pipeline

1. Add [Command line] Step to Pipeline and name it "Upgrade LocalDB"
2. Add this commands to the new step:

SqlLocalDB stop
SqlLocalDB delete
choco upgrade sqllocaldb
SqlLocalDB create
SqlLocalDB start
SqlLocalDB share MSSQLLocalDB MSSQLLocalDB
SqlLocalDB info MSSQLLocalDB
SQLCMD.EXE -S (localdb)\MSSQLLocalDB -Q "SELECT @@VERSION;"

Known issues:
In some cases the .NET Code cannot connect the LocalDB.
If LocalDB is used in .NET Core Services, then add EnableRetryOnFailure to UseSqlServer call

Reduce SSMS startup time

To reduce the SQL Server Management Studio (SSMS) startup time do following:

1. Edit Shortcut tu start SSMS and append this startup parameter: -nosplash
2. Open system Internet options, go to the Advanced tab and uncheck "Check for server certificate revocation" - this change reuqires system restart.

After that both changes startup time (by me) is 10 times shorter

Additionaly you can reduce clicks by predefine other startup parameters.
For example for localhost, trusted connection and USE of MyDatabase:

..../SSMS.exe -S . -E -d MyDatabase -nosplash

I also set this option:
SSMS Menu / Tools / Option / Environmen / Startup / At statrup = "Open Object Explorer and query window"

Google Chrome - Managed by your organization - how to remove?

In some organisations your Web Browser is limited by your organisation.
For example can be deactivated Sync, or switched off geo-location, or disabled installation of some extensions.
you can check it like there: support.google.com: is_chrome_managed?
There are two places in Windows Registry where these policies could be set:

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Google\Chrome\
HKEY_CURRENT_USER\SOFTWARE\Policies\Google\Chrome\

To remove these Registry keys and start browser again can does not help. Mach better is to set values. For example like there:
DefaultGeolocationSetting = 3
PasswordManagerEnabled = 1
SyncDisabled = 0
BuiltInDnsClientEnabled = 1

VMware and Chrome - damaged or washed out colors or transparent backgrounds

Sometimes in the Google Crome in VMware Virtual Machine the colors are damaged or washed out or backgrounds are transparent.
For example correct:

and incorrect (background looks to be transparent):

How to solve it?
I know three methods:
1. Switch off 3D Graphics Acceleration in VMware Virtual Machine Settings (you need first shutdown the VM:

2. The worst solution: In Chrome Flags change flag "force color profile" to "scRGB linear (HDR where available)"
(write in address line chrome://flags/ and then search for "force color profile"):

3. My preferred: In Chrome Flags change flag "Choose ANGLE graphics backend" to "OpenGL"
(write in address line chrome://flags/ and then search for "ANGLE"):

MSSQL - How to clear all caches?

Script to clear all caches? (For example for performance debug)

/*
https://yabele.blogspot.com/2017/07/mssql-how-to-clear-all-caches.html
*/
SET NOCOUNT ON;
DECLARE @StartedAt DATETIME2 = GETDATE();
CHECKPOINT; 
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');
-- Query
PRINT '    Elapsed time: ' + CAST(DATEDIFF(ms, @StartedAt, GETDATE()) AS VarChar(20)) + ' ms';

MSSQL - T-SQL equivalent of VBA VAL() function

There the text of scalar-value function to extract numbers from string and convert to integer:

IF OBJECT_ID(N'dbo.fn_extract_digits') IS NOT NULL
  DROP FUNCTION dbo.fn_extract_digits;
GO
 
CREATE FUNCTION dbo.fn_extract_digits (@str VARCHAR(MAX))
/*
https://yabele.blogspot.com/2017/07/mssql-t-sql-equivalent-of-vba-val.html
*/
RETURNS INT
AS
BEGIN
  DECLARE
    @newstr VARCHAR(MAX) = '',
    @i INT = 1,
    @chr CHAR(1);
 
  WHILE @i <= LEN(@str) BEGIN
    SET @chr = SUBSTRING(@str, @i, 1);
    IF @chr BETWEEN '0' AND '9'
      SET @newstr = @newstr + @chr;
    SET @i = @i + 1;
  END;
 
  RETURN CAST(@newstr AS INT);
END
/*
DECLARE @str VARCHAR(MAX) = '1-23-456';
SELECT Digits = dbo.fn_extract_digits(@str);*/
;