Following script should make it clear in regards to Question 1.

declare @i int = 0;
declare @temp table( id int, latin varchar(1) collate SQL_Latin1_General_CP1_CI_AS, jap varchar(1) collate Japanese_90_CI_AS )
while (@i < 256)
begin
insert into @temp 
select @i, char(@i)  collate SQL_Latin1_General_CP1_CI_AS ,char(@i)  collate Japanese_90_CI_AS  

set @i = @i+1;
end

select * from @temp
order by latin

select * from @temp
order by jap

2. Collation is the rules on how to sort have a read on this. If your columns are nvarchars instead of varchars answer becomes different.

3. Any answer would be opinion based, most of companies above don't use single type of database (graph, bigdata, etc.), I never had a need to use anything but SQL_Latin1_General_CP1_CI_AS. (I never had to work out of Europe ether)

4. If you are using nvarchars = you are using unicode and unicode is like Chuck Norris - covers everything (twice).

Following script should make it clear in regards to Question 1.

declare @i int = 0;
declare @temp table( id int, latin varchar(1) collate SQL_Latin1_General_CP1_CI_AS, jap varchar(1) collate Japanese_90_CI_AS )
while (@i < 256)
begin
insert into @temp 
select @i, char(@i)  collate SQL_Latin1_General_CP1_CI_AS ,char(@i)  collate Japanese_90_CI_AS  

set @i = @i+1;
end

select * from @temp
order by latin

select * from @temp
order by jap

2. Collation is the rules on how to sort have a read on this. Script above should show you how sort changes have a look how it matches to UCA.

3. Any answer would be opinion based, most of companies above don't use single type of database (graph, bigdata, etc.), I never had a need to use anything but SQL_Latin1_General_CP1_CI_AS. (I never had to work out of Europe ether)

4. If you are using nvarchars = you are using unicode and unicode is like Chuck Norris - covers everything (twice).

Following script should make it clear in regards to Question 1.

declare @i int = 0;
declare @temp table( id int, latin varchar(1) collate SQL_Latin1_General_CP1_CI_AS, jap varchar(1) collate Japanese_90_CI_AS )
while (@i < 256)
begin
insert into @temp 
select @i, char(@i)  collate SQL_Latin1_General_CP1_CI_AS ,char(@i)  collate Japanese_90_CI_AS  

set @i = @i+1;
end

select * from @temp
order by latin

select * from @temp
order by jap

Following script should make it clear in regards to Question 1.

declare @i int = 0;
declare @temp table( id int, latin varchar(1) collate SQL_Latin1_General_CP1_CI_AS, jap varchar(1) collate Japanese_90_CI_AS )
while (@i < 256)
begin
insert into @temp 
select @i, char(@i)  collate SQL_Latin1_General_CP1_CI_AS ,char(@i)  collate Japanese_90_CI_AS  

set @i = @i+1;
end

select * from @temp
order by latin

select * from @temp
order by jap

2. Collation is the rules on how to sort have a read on this. If your columns are nvarchars instead of varchars answer becomes different.

3. Any answer would be opinion based, most of companies above don't use single type of database (graph, bigdata, etc.), I never had a need to use anything but SQL_Latin1_General_CP1_CI_AS. (I never had to work out of Europe ether)

4. If you are using nvarchars = you are using unicode and unicode is like Chuck Norris - covers everything (twice).