보미네

unique index를 string에 주고 싶다. 

그럼 code table에 허용하는 범위가 아니므로 collation을 지원하는 collation으로 바꾸어 준다. 

쉽게 이야기 하면 a 와 A 를 구분 못하는 korean_wansung_ci_as와 같은 이슈이다. 

DROP TABLE IF EXISTS dbo.unicode_string_test
 CREATE TABLE dbo.unicode_string_test (
     string NVARCHAR(10)
 )
 -- 1 : U+0031
 -- 2 : U+0032
 -- ー : U+30FC
 -- ヽ : U+30FD
 INSERT dbo.unicode_string_test SELECT N'111'
 INSERT dbo.unicode_string_test SELECT N'11ー'
 INSERT dbo.unicode_string_test SELECT N'11ヽ'
 INSERT dbo.unicode_string_test SELECT N'1ーー'
 INSERT dbo.unicode_string_test SELECT N'1ヽヽ'
 INSERT dbo.unicode_string_test SELECT N'ーーー'
 INSERT dbo.unicode_string_test SELECT N'222'
 INSERT dbo.unicode_string_test SELECT N'22ー'
 INSERT dbo.unicode_string_test SELECT N'22ヽ'
 INSERT dbo.unicode_string_test SELECT N'2ーー'
 INSERT dbo.unicode_string_test SELECT N'2ヽヽ'
 INSERT dbo.unicode_string_test SELECT N'ヽヽヽ'
 
 SELECT * FROM dbo.unicode_string_test WHERE string = N'111'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'11ー'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'1ーー'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'1ーヽ'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'ーーー'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'222'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'22ヽ'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'2ヽヽ'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'2ーヽ'
 SELECT * FROM dbo.unicode_string_test WHERE string = N'ヽヽヽ'
go




alter table unicode_string_test 
alter column string NVARCHAR(10) COLLATE Korean_Wansung_BIN2
go

create unique nonclustered index nc_unicode_string_test on unicode_string_test (string)
go

select * from dbo.unicode_string_test where string = N'111'
go

SELECT name, description FROM sys.fn_helpcollations() where name like '%korea%'

SELECT * FROM dbo.unicode_string_test where string = N'111' collate Korean_Wansung_BIN2 
go

-- 각 지점의 위도 경도 구하기 (아래 사이트에서 구함, kakao api 이용해도 가능)
-- https://tablog.neocities.org/keywordposition
-- 검색한 위도 경도를 이용해 서울역에서 용인두산위브까지의 거리 SQL서버로 구하기
-- Parse 는 대소문자를 구분하고, STGeomFromText 에서 SRID를 4236으로 고정한 값으로 계산함 
-- https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/parse-geography-data-type?view=sql-server-ver16
-- https://learn.microsoft.com/ko-kr/sql/t-sql/spatial-geometry/stgeomfromtext-geometry-data-type?view=sql-server-ver16
-- POINT는 점으로 계산, LINESTRING 은 선분으로 계산 WKT 표현임, 모든 데이터베이스에 이런거 다 지원함

DECLARE @g geography;  
DECLARE @h geography;  
SET @g = geography::Parse('POINT(126.9720686318752 37.55593878839654)'); 
SET @h = geography::Parse('POINT(127.15001085373893 37.28340610861977)'); 
SELECT @g.STDistance(@h);  --this is the distance in meters
GO

DECLARE @g geography;  
DECLARE @h geography;  
SET @g = geography::STGeomFromText('POINT(126.9720686318752 37.55593878839654)', 4326); 
SET @h = geography::STGeomFromText('POINT(127.15001085373893 37.28340610861977)', 4326); 
SELECT @g.STDistance(@h);  --this is the distance in meters
GO

-- SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/troubleshoot-performance-narrow-vs-wide-plans

• SQL Server chooses a wide plan update when the following criteria are met at the same time:
  • The impacted number of rows is greater than 250.
  • The memory of leaf index is at least 1/1000 of the max server memory setting.
• Wide plans boost performance at the expense of consuming extra memory.
• If the expected query plan isn't used, it may be due to stale statistics (not reporting correct data size), max server memory setting, or other unrelated issues like parameter-sensitive plans.
• The duration of UPDATE statements using a wide plan depends on several factors, and in some cases, it may take longer than narrow plans.
• Trace flag 8790 will force a wide plan; trace flag 2338 will force a narrow plan.