Sql-server – SQL Server: localhost vs MSSQLLocalDB

See my comment above. I used Process Monitor to trace the registry access and found it was getting an Access Denied while trying to write:

3:16:40.8405491 PM sqlservr.exe 8756 RegCreateKey HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer ACCESS DENIED Desired Access: Write

I got around this by opening the Registry Editor and giving Everyone full access to HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer. Then I changed the default location and removed the Everyone access once it was done. This made the following extra keys that weren't there before:

DefaultData

DefaultLog

BackupDirectory

You can convert the version string to XML and then extract the four parts replacing no value (nulls) with a 0. Put the version back together and compare to your original table.

-- Sample table
declare @T table
(
  Title varchar(20),
  Version varchar(10)
);

-- Sample data
insert into @T(Title, Version) values
('SQL Server', '13'),
('SQL Server', '13.0.0'),
('SQL Server', '13.0.0.0'),
('SQL Server', '13.0.0.175'),
('Visual Studio', '13'),
('Visual Studio', '13.0.0'),
('Visual Studio', '13.0.0.0'),
('Visual Studio', '13.0.0.175');

-- Get the required versions
select *
from @T as T1
where exists (
             select *
             from @T as T2
               -- Convert version to XML
               cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

               -- Extract the values from the XML and build a new version string with 0 instead of null.
               cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                     isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                     isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
             where  T1.Title = T2.Title and
                    T1.Version = V.Version
             );

-- Or if you want to delete from your original table ...
delete from @T
where not exists (
                 select *
                 from @T as T2
                   -- Convert version to XML
                   cross apply (select cast(replace(T2.Version, '.', '<X/>') as xml)) as X(Value)

                   -- Extract the values from the XML and build a new version string with 0 instead of null.
                   cross apply (select X.Value.value('text()[1]', 'varchar(10)')+'.'+
                                         isnull(X.Value.value('text()[2]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[3]', 'varchar(10)'), '0')+'.'+
                                         isnull(X.Value.value('text()[4]', 'varchar(10)'), '0')) as V(Version)
                 where  [@T].Title = T2.Title and
                        [@T].Version = V.Version
                 );

select *
from @T;