#sql-server #sql-server-2005 #sql-server-2008 #sorting
#sql-сервер #sql-server-2005 #sql-server-2008 #сортировка
Вопрос:
Проблема с уникальными идентификаторами
У нас есть существующая база данных, которая широко использует uniqueidentifiers (к сожалению!) как в качестве первичных ключей, так и в качестве некоторых обнуляемых столбцов некоторых таблиц. Мы столкнулись с ситуацией, когда некоторые отчеты, выполняемые в этих таблицах, сортируются по этим уникальным идентификаторам, потому что в таблице нет другого столбца, который давал бы осмысленную сортировку (разве это не иронично!). Целью было отсортировать так, чтобы элементы отображались в том порядке, в котором они были вставлены, но они не были вставлены с помощью NewSequentialId() — следовательно, пустая трата времени.
Факт об алгоритме сортировки
В любом случае, учитывая, что SQL Server сортирует уникальные идентификаторы на основе групп байтов, начиная с конечной 5-й группы байтов (6 байт) и продвигаясь к 1-й группе байтов (4 байта), меняя порядок в 3-й группе байтов (2 байта) справа налево на лево-правый,
Мой вопрос
Мне было любопытно узнать, есть ли какая-нибудь реальная жизненная ситуация, в которой подобное вообще помогает.
Как SQL Server хранит uniqueidentifier внутри, что может дать представление о том, почему у него такой странный алгоритм сортировки?
Ссылка:
Открытие Альберто Феррари сортировки идентификаторов SQL Server GUID
Пример
Уникальные идентификаторы сортируются, как показано ниже, когда вы используете Order By в столбце uniqueidentifier, содержащем приведенные ниже данные.
Пожалуйста, обратите внимание, что приведенные ниже данные отсортированы по возрастанию, и наивысшее предпочтение отдается сортировке от 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
Комментарии:
1. Вот обновленная ссылка на блог, упомянутый в вопросе: sqlbi.com/blog/alberto/2007/08/31 /…
Ответ №1:
Алгоритм описан ребятами из SQL Server здесь: Как сравниваются идентификаторы GUID в SQL Server 2005?Я цитирую здесь (поскольку это старая статья, которая может исчезнуть навсегда через несколько лет)
В общем, сравнения на равенство имеют большой смысл при использовании значений uniqueidentifier. Однако, если вам потребуется общий порядок, возможно, вы смотрите на неправильный тип данных и вместо этого должны рассмотреть различные целочисленные типы.
Если после тщательного обдумывания вы решите сделать заказ по столбцу uniqueidentifier, вы можете быть удивлены тем, что получите в ответ.
Учитывая эти два значения uniqueidentifier:
@g1= ‘55666BEE-B3A0-4BF5-81A7-86FF976E763F’ @g2 = ‘8DD5BCA5-6ABE-4F73-B4B7-393AE6BBB849’
Многие люди думают, что @g1 меньше, чем @g2, поскольку ‘55666BEE’, безусловно, меньше, чем ‘8DD5BCA5’. Однако SQL Server 2005 сравнивает значения uniqueidentifier не таким образом.
Сравнение производится путем просмотра «групп» байтов справа налево и слева направо внутри «группы» байтов. Группа байтов — это то, что разделено символом ‘-‘. Более технически, сначала мы рассматриваем байты {10-15}, затем {8-9}, затем {6-7}, затем {4-5} и, наконец, {от 0 до 3}.
В этом конкретном примере мы бы начали с сравнения ’86FF976E763F’ с ‘393AE6BBB849’. Сразу же мы видим, что @g2 действительно больше, чем @g1.
Обратите внимание, что в языках .NET значения Guid имеют другой порядок сортировки по умолчанию, чем в SQL Server. Если вы обнаружите необходимость упорядочить массив или список идентификаторов Guid, используя семантику сравнения SQL Server, вы можете использовать вместо этого массив или список SqlGuid, который реализует IComparable способом, который согласуется с семантикой SQL Server.
Кроме того, сортировка выполняется по порядку байтовых групп (смотрите здесь: Глобально уникальный идентификатор). Группы 10-15 и 8-9 хранятся в формате big endian (соответствующем Data4 в статье Википедии), поэтому они сравниваются в формате big endian. Другие группы сравниваются с использованием строчного порядкового номера.
Ответ №2:
Специальный сервис для тех, кто считает, что принятый ответ немного расплывчатый. Код говорит сам за себя; волшебные части — это:
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);
}
}
}
Ответ №3:
Здесь другой подход. Идентификатор 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);
};
Комментарии:
1. разве некоторые из них не сортируются в обратном порядке?