반응형
SQL Server GUID 정렬 알고리즘입니다. 왜죠?
고유 식별자 문제
고유 식별자를 기본 키와 일부 테이블의 일부 null 가능 열로 광범위하게 사용하는 기존 데이터베이스가 있습니다(불행히도!테이블에 의미 있는 정렬을 제공하는 다른 열이 없기 때문에 이러한 테이블에서 실행되는 일부 보고서가 이러한 고유 식별자를 정렬하는 상황을 발견했습니다(반어적이지 않습니까!).항목이 삽입되었지만 삽입되지 않은 순서대로 항목을 표시하도록 정렬하는 것이 목적이었습니다.NewSequentialId()따라서 시간 낭비입니다.
정렬 알고리즘에 대한 사실
어쨌든 SQL Server가 끝 5번째 바이트 그룹(6바이트)에서 시작하여 3번째 바이트 그룹(2바이트)의 순서를 오른쪽 왼쪽에서 왼쪽으로 반대로 1번째 바이트 그룹(4바이트)으로 이동하는 바이트 그룹을 기준으로 고유 식별자를 정렬하는 것을 고려하면,
내 질문
이런 종류의 도움이 되는 실제 생활 상황이 있는지 궁금합니다.
SQL Server는 내부에 고유 식별자를 저장하여 이러한 엉터리 정렬 알고리즘을 사용하는 이유에 대한 통찰력을 제공합니까?
참조:
Alberto Ferrari의 SQL Server GUID 종류 발견
예
아래 데이터가 있는 고유 식별자 열에서 주문 기준을 사용하면 고유 식별자가 아래와 같이 정렬됩니다.
아래 데이터는 오름차순으로 정렬되며 가장 높은 정렬 선호도는 5번째 바이트 그룹에서 1번째 바이트 그룹(뒤로)입니다.
-- 1st byte group of 4 bytes sorted in the reverse (left-to-right) order below --
01000000-0000-0000-0000-000000000000
10000000-0000-0000-0000-000000000000
00010000-0000-0000-0000-000000000000
00100000-0000-0000-0000-000000000000
00000100-0000-0000-0000-000000000000
00001000-0000-0000-0000-000000000000
00000001-0000-0000-0000-000000000000
00000010-0000-0000-0000-000000000000
-- 2nd byte group of 2 bytes sorted in the reverse (left-to-right) order below --
00000000-0100-0000-0000-000000000000
00000000-1000-0000-0000-000000000000
00000000-0001-0000-0000-000000000000
00000000-0010-0000-0000-000000000000
-- 3rd byte group of 2 bytes sorted in the reverse (left-to-right) order below --
00000000-0000-0100-0000-000000000000
00000000-0000-1000-0000-000000000000
00000000-0000-0001-0000-000000000000
00000000-0000-0010-0000-000000000000
-- 4th byte group of 2 bytes sorted in the straight (right-to-left) order below --
00000000-0000-0000-0001-000000000000
00000000-0000-0000-0010-000000000000
00000000-0000-0000-0100-000000000000
00000000-0000-0000-1000-000000000000
-- 5th byte group of 6 bytes sorted in the straight (right-to-left) order below --
00000000-0000-0000-0000-000000000001
00000000-0000-0000-0000-000000000010
00000000-0000-0000-0000-000000000100
00000000-0000-0000-0000-000000001000
00000000-0000-0000-0000-000000010000
00000000-0000-0000-0000-000000100000
00000000-0000-0000-0000-000001000000
00000000-0000-0000-0000-000010000000
00000000-0000-0000-0000-000100000000
00000000-0000-0000-0000-001000000000
00000000-0000-0000-0000-010000000000
00000000-0000-0000-0000-100000000000
코드:
알베르토의 코드는 정렬이 개별 비트가 아닌 바이트에 있음을 나타내기 위해 확장되었습니다.
With Test_UIDs As (-- 0 1 2 3 4 5 6 7 8 9 A B C D E F
Select ID = 1, UID = cast ('00000000-0000-0000-0000-100000000000' as uniqueidentifier)
Union Select ID = 2, UID = cast ('00000000-0000-0000-0000-010000000000' as uniqueidentifier)
Union Select ID = 3, UID = cast ('00000000-0000-0000-0000-001000000000' as uniqueidentifier)
Union Select ID = 4, UID = cast ('00000000-0000-0000-0000-000100000000' as uniqueidentifier)
Union Select ID = 5, UID = cast ('00000000-0000-0000-0000-000010000000' as uniqueidentifier)
Union Select ID = 6, UID = cast ('00000000-0000-0000-0000-000001000000' as uniqueidentifier)
Union Select ID = 7, UID = cast ('00000000-0000-0000-0000-000000100000' as uniqueidentifier)
Union Select ID = 8, UID = cast ('00000000-0000-0000-0000-000000010000' as uniqueidentifier)
Union Select ID = 9, UID = cast ('00000000-0000-0000-0000-000000001000' as uniqueidentifier)
Union Select ID = 10, UID = cast ('00000000-0000-0000-0000-000000000100' as uniqueidentifier)
Union Select ID = 11, UID = cast ('00000000-0000-0000-0000-000000000010' as uniqueidentifier)
Union Select ID = 12, UID = cast ('00000000-0000-0000-0000-000000000001' as uniqueidentifier)
Union Select ID = 13, UID = cast ('00000000-0000-0000-0001-000000000000' as uniqueidentifier)
Union Select ID = 14, UID = cast ('00000000-0000-0000-0010-000000000000' as uniqueidentifier)
Union Select ID = 15, UID = cast ('00000000-0000-0000-0100-000000000000' as uniqueidentifier)
Union Select ID = 16, UID = cast ('00000000-0000-0000-1000-000000000000' as uniqueidentifier)
Union Select ID = 17, UID = cast ('00000000-0000-0001-0000-000000000000' as uniqueidentifier)
Union Select ID = 18, UID = cast ('00000000-0000-0010-0000-000000000000' as uniqueidentifier)
Union Select ID = 19, UID = cast ('00000000-0000-0100-0000-000000000000' as uniqueidentifier)
Union Select ID = 20, UID = cast ('00000000-0000-1000-0000-000000000000' as uniqueidentifier)
Union Select ID = 21, UID = cast ('00000000-0001-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 22, UID = cast ('00000000-0010-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 23, UID = cast ('00000000-0100-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 24, UID = cast ('00000000-1000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 25, UID = cast ('00000001-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 26, UID = cast ('00000010-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 27, UID = cast ('00000100-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 28, UID = cast ('00001000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 29, UID = cast ('00010000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 30, UID = cast ('00100000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 31, UID = cast ('01000000-0000-0000-0000-000000000000' as uniqueidentifier)
Union Select ID = 32, UID = cast ('10000000-0000-0000-0000-000000000000' as uniqueidentifier)
)
Select * From Test_UIDs Order By UID, ID
이 알고리즘은 다음 SQL Server 사용자에 의해 문서화되어 있습니다.SQL Server 2005에서 GUID는 어떻게 비교됩니까?여기에 인용합니다(몇 년 후에 영원히 사라질 수도 있는 오래된 기사이기 때문에).
일반적으로 동일성 비교는 고유 식별자 값을 사용하는 것이 매우 의미가 있습니다.그러나 일반적인 순서를 지정해야 하는 경우 잘못된 데이터 유형을 볼 수 있으므로 다양한 정수 유형을 고려해야 합니다.
신중하게 생각한 후에 고유 식별자 열을 주문하기로 결정하면 반환되는 내용에 놀랄 수 있습니다.
다음 두 가지 고유 식별자 값이 주어지면,
@g1= '5566BEE-B3A0-4BF5-81A7-86FF976E763F' @g2 = '8DD5BCA5-6ABE-4F73-B4B7-393AE6BBB849'
많은 사람들은 @g1이 @g2보다 작다고 생각합니다. 5566B 이후로.EE'는 '8DD5BCA5'보다 확실히 작습니다.그러나 SQL Server 2005에서는 고유 식별자 값을 비교하지 않습니다.
비교는 바이트 "그룹" 내에서 오른쪽에서 왼쪽으로, 왼쪽에서 오른쪽으로 바이트 "그룹"을 살펴봄으로써 이루어집니다.바이트 그룹은 '-' 문자로 구분됩니다.좀 더 기술적으로 말하면 바이트 {10~15}, 다음으로 {8-9}, 다음으로 {6-7}, 다음으로 {4-5}, 마지막으로 {0~3}을(를) 살펴봅니다.
이 구체적인 예에서는 '86FF976E763F'와 '393'을 비교하는 것으로 시작합니다.AE6BBB849'입니다.즉시 우리는 @g2가 실제로 @g1보다 크다는 것을 알 수 있습니다.
.NET 언어에서 GUID 값은 SQL Server와 기본 정렬 순서가 다릅니다.SQL Server 비교 시맨틱을 사용하여 어레이 또는 Guid 목록을 주문해야 하는 경우 SQL Server 시맨틱과 일치하는 방식으로 IComparable을 구현하는 대신 SqlGuid의 어레이 또는 목록을 사용할 수 있습니다.
또한 정렬은 바이트 그룹 엔디언을 따릅니다(여기: 전역적으로 고유한 식별자 참조).그룹 10-15와 8-9는 빅 엔디안(위키백과 기사의 데이터 4에 해당)으로 저장되므로 빅 엔디안으로 비교됩니다.다른 그룹은 리틀 엔디안을 사용하여 비교됩니다.
받아들여진 답이 조금 애매하다고 생각하는 사람들을 위한 특별한 서비스.코드 자체가 설명합니다. 마법의 부분은 다음과 같습니다.
System.Guid g
g.ToByteArray();
int[] m_byteOrder = new int[16] // 16 Bytes = 128 Bit
{10, 11, 12, 13, 14, 15, 8, 9, 6, 7, 4, 5, 0, 1, 2, 3};
public int Compare(Guid x, Guid y)
{
byte byte1, byte2;
//Swap to the correct order to be compared
for (int i = 0; i < NUM_BYTES_IN_GUID; i++)
{
byte1 = x.ToByteArray()[m_byteOrder[i]];
byte2 = y.ToByteArray()[m_byteOrder[i]];
if (byte1 != byte2)
return (byte1 < byte2) ? (int)EComparison.LT : (int)EComparison.GT;
} // Next i
return (int)EComparison.EQ;
}
전체 코드:
namespace BlueMine.Data
{
public class SqlGuid
: System.IComparable
, System.IComparable<SqlGuid>
, System.Collections.Generic.IComparer<SqlGuid>
, System.IEquatable<SqlGuid>
{
private const int NUM_BYTES_IN_GUID = 16;
// Comparison orders.
private static readonly int[] m_byteOrder = new int[16] // 16 Bytes = 128 Bit
{10, 11, 12, 13, 14, 15, 8, 9, 6, 7, 4, 5, 0, 1, 2, 3};
private byte[] m_bytes; // the SqlGuid is null if m_value is null
public SqlGuid(byte[] guidBytes)
{
if (guidBytes == null || guidBytes.Length != NUM_BYTES_IN_GUID)
throw new System.ArgumentException("Invalid array size");
m_bytes = new byte[NUM_BYTES_IN_GUID];
guidBytes.CopyTo(m_bytes, 0);
}
public SqlGuid(System.Guid g)
{
m_bytes = g.ToByteArray();
}
public byte[] ToByteArray()
{
byte[] ret = new byte[NUM_BYTES_IN_GUID];
m_bytes.CopyTo(ret, 0);
return ret;
}
int CompareTo(object obj)
{
if (obj == null)
return 1; // https://msdn.microsoft.com/en-us/library/system.icomparable.compareto(v=vs.110).aspx
System.Type t = obj.GetType();
if (object.ReferenceEquals(t, typeof(System.DBNull)))
return 1;
if (object.ReferenceEquals(t, typeof(SqlGuid)))
{
SqlGuid ui = (SqlGuid)obj;
return this.Compare(this, ui);
} // End if (object.ReferenceEquals(t, typeof(UInt128)))
return 1;
} // End Function CompareTo(object obj)
int System.IComparable.CompareTo(object obj)
{
return this.CompareTo(obj);
}
int CompareTo(SqlGuid other)
{
return this.Compare(this, other);
}
int System.IComparable<SqlGuid>.CompareTo(SqlGuid other)
{
return this.Compare(this, other);
}
enum EComparison : int
{
LT = -1, // itemA precedes itemB in the sort order.
EQ = 0, // itemA occurs in the same position as itemB in the sort order.
GT = 1 // itemA follows itemB in the sort order.
}
public int Compare(SqlGuid x, SqlGuid y)
{
byte byte1, byte2;
//Swap to the correct order to be compared
for (int i = 0; i < NUM_BYTES_IN_GUID; i++)
{
byte1 = x.m_bytes[m_byteOrder[i]];
byte2 = y.m_bytes[m_byteOrder[i]];
if (byte1 != byte2)
return (byte1 < byte2) ? (int)EComparison.LT : (int)EComparison.GT;
} // Next i
return (int)EComparison.EQ;
}
int System.Collections.Generic.IComparer<SqlGuid>.Compare(SqlGuid x, SqlGuid y)
{
return this.Compare(x, y);
}
public bool Equals(SqlGuid other)
{
return Compare(this, other) == 0;
}
bool System.IEquatable<SqlGuid>.Equals(SqlGuid other)
{
return this.Equals(other);
}
}
}
여기 다른 접근법이 있습니다.GUID는 SQL Server에서 발생하는 것처럼 일반 문자열을 비교할 준비가 되어 있습니다.이것은 Javascript이지만 어떤 언어로도 변환하기 매우 쉽습니다.
function guidForComparison(guid) {
/*
character positions:
11111111112222222222333333
012345678901234567890123456789012345
00000000-0000-0000-0000-000000000000
byte positions:
111111111111
00112233 4455 6677 8899 001122334455
*/
return guid.substr(24, 12) +
guid.substr(19, 4) +
guid.substr(16, 2) +
guid.substr(14, 2) +
guid.substr(11, 2) +
guid.substr(9, 2) +
guid.substr(6, 2) +
guid.substr(4, 2) +
guid.substr(2, 2) +
guid.substr(0, 2);
};
언급URL : https://stackoverflow.com/questions/7810602/sql-server-guid-sort-algorithm-why
반응형
'programing' 카테고리의 다른 글
| Oracle: where 절에서 날짜 및 시간별로 필터링하는 방법 (0) | 2023.07.15 |
|---|---|
| java.sql.SQL 예외:사용된 결과 집합 (0) | 2023.07.10 |
| 실제 숫자인 문자열 값을 기준으로 Mongo 정렬 (0) | 2023.07.10 |
| '시스템'입니다.웹. 보안.SqlMembershipProvider'에는 스키마 버전 '1'과(와) 호환되는 데이터베이스 스키마가 필요합니다. (0) | 2023.07.10 |
| 마지막 태그 이후 모든 Git 커밋 가져오기 (0) | 2023.07.10 |