Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4 5   вперед  Ctrl      все
 Вопрос на собеседовании  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
Чем отличается PRIMARY KEY от UNIQUE CONSTRAINT?
16 ноя 13, 20:15    [15141991]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
PRIMARY KEY не допускает NULL в своих полях,
а UNIQUE - допускает.
16 ноя 13, 20:18    [15141995]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
Exproment
Member

Откуда:
Сообщений: 416
iap, ну не только :) Концептуально это различные вещи - primary key есть первичный ключ, а unique constrain просто ограничение. primary key для sql server является уникальным идентификатором записи. Если нет primary key на таблице, сервер не может не может знать, какое поле на таблице идентифицирует запись.

*конечно на логическом, а не физическом уровнях. Вспомним про применение change trcking, который требует обязательного наличия primary key.
16 ноя 13, 20:42    [15142030]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
Exproment
iap, ну не только :) Концептуально это различные вещи - primary key есть первичный ключ, а unique constrain просто ограничение. primary key для sql server является уникальным идентификатором записи. Если нет primary key на таблице, сервер не может не может знать, какое поле на таблице идентифицирует запись.

*конечно на логическом, а не физическом уровнях. Вспомним про применение change trcking, который требует обязательного наличия primary key.
А если у нас есть кластерный индекс, но нет primary key то сервер тоже не может уникально идентифицировать запись?
16 ноя 13, 23:46    [15142348]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
Exproment
Member

Откуда:
Сообщений: 416
SandalTree, мне себя цитировать ?
Exproment
*конечно на логическом, а не физическом уровнях.

на физическом уровне database engine всегда может однозначно идентифицировать записать. Не зависимо от наличия кластеризованного индекса и независимо от каких либо ограничений уникальности(всегда есть RID). А вот на логическом - вы совершенно правы. О чем и говорит пример с change tracking.
16 ноя 13, 23:55    [15142370]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
Exproment
SandalTree, мне себя цитировать ?
Exproment
*конечно на логическом, а не физическом уровнях.

на физическом уровне database engine всегда может однозначно идентифицировать записать. Не зависимо от наличия кластеризованного индекса и независимо от каких либо ограничений уникальности(всегда есть RID). А вот на логическом - вы совершенно правы. О чем и говорит пример с change tracking.


1. Не всегда. RID создаётся только на неуникальном кластерном индексе и только в случае повторяющихся значений.

2. У меня вопрос непро уникальный кластерный индекс, а про Primary Key, который с ним различается.
17 ноя 13, 00:04    [15142387]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
Exproment
Member

Откуда:
Сообщений: 416
SandalTree,

1) Не придерайтесь к письму. :) Я дико сожалею что не написал "всегда есть возможность прописать RID у database engine"
2) Про кластерный индекс и физику говорить начали вы.

А свой вариант ответа на ваш вопрос я уже привел :) На мой взгляд различаются они чисто концептуально и сравнивать их нельзя.
17 ноя 13, 00:25    [15142454]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SandalTree
1. Не всегда. RID создаётся только на неуникальном кластерном индексе и только в случае повторяющихся значений.

2. У меня вопрос непро уникальный кластерный индекс, а про Primary Key, который с ним различается.


Всегда.
Storage Engine (SE) всегда может однозначно идентифицировать запись.
Картинка с другого сайта.

А вопрос, имхо, сильно зависит... от спрашивающего.

Если такой вопрос вас спрашивает знающий человек - возможно он хочет чтобы вы порассуждали на тему, чтобы в дальнейшем углубиться по спирали - такой метод использует известный в сообществе человек Thomas LaRock или Grant Fritchy. Это годный способ. Человек начинает отвечать на этот воппрос, например, что-то про индексы которые обеспечивают констрейнты, задается вопрос по индексам и далее по индукции/дедукции - раскручивают, это дает понимание собеседующему.

Если такой вопрос спрашивает "выскочка" - который нашел в справке определение того и другого и ждет от вас что вы обнаружите энциклопедические знания документации, и по пунктам перечислите отличия - то, имхо, можно смело разворачиваться и уходить. Лично я бы, вежливо, так и сделал - т.к. это указание на способы оценки специалистов не отражающие реальную ценность специалиста, как ее вижу я. А значит работать в таком коллективе я не смогу в любом случае, даже если выучу весь синтаксис XQuery =)

Как понять, кто перед тобой, если ты заранее не знаешь? Это можно сделать по реакции на свой ответ.
Если видно:
- высокомерие граничащее с тыканьем в грязь (обусловленное только тем, что собеседующий 10 минут назад прочитал раздел справки по которому тебя спрашивает, а-ля "в какой последовательности передаются параметры в функцию datediff")
- отсутствие наводящих и уточняющих вопросов с целью понять какой ты специалист
- ощущение что больше не тебя хотят спросить, а показать свою крутость
и прочие схожие мысли.

В целом, имхо, вопрос сродни "чем отличается болт от шурупа" - вроде примерно похожи, но есть разница.
Ответ может лежать как в семантической (смысловой) области, так и в физической. Если это вопрос не для того чтобы завязать диалог, а "анкетный" вопрос - то он откровенно глупый, по крайней мере в такой формулировке. Т.к. "анкетный" вопрос все-таки, должен понимать "анкетный" ответ в той же плоскости. Например, можно было бы спросить "Для чего используется PK, а для чего UQ" - смысловой. Или "Как сервер реализует PK и как UQ" - физика.

Это вам задали на собеседовании или вы сами такие вопросы спрашиваете?
17 ноя 13, 01:08    [15142611]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
SomewhereSomehow
Это вам задали на собеседовании или вы сами такие вопросы спрашиваете?


Не спрашивали и сам не спрашиваю.

Просто думаю. Мне показалось что они очень похожи и знание различий может показать уровень специалиста.
Поэтому думаю что это неплохой вопрос для интрервью.

И я полностью согласен с вами. Иногда задают такие вопросы.... сразу понимаешь что ты слишком крут для этой позиции.
Думаю может подкрутить крутизну в обратную сторону.
17 ноя 13, 03:50    [15142894]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
Exproment
SandalTree,

1) Не придерайтесь к письму. :) Я дико сожалею что не написал "всегда есть возможность прописать RID у database engine"
2) Про кластерный индекс и физику говорить начали вы.

А свой вариант ответа на ваш вопрос я уже привел :) На мой взгляд различаются они чисто концептуально и сравнивать их нельзя.

Как понять кластерный индекс не зная "физики"?
Вы заговорили про RID, а я сказал что он вовсе не обязателен.
CREATE TABLE A(F1 Char(1) );
GO
INSERT INTO A(F1) VALUES ('A'), ('A')
GO

В этом примере RIDa нет и SE никак не сможет отличить одну запись от другой.

CREATE TABLE B(F1 Char(1) );
GO
CREATE CLUSTERED INDEX CLIX_B ON B(F1)
GO
INSERT INTO B(F1) VALUES ('A'), ('B')
GO

В этом примере RIDa тоже нет!


CREATE CLUSTERED INDEX CLIX_A ON A(F1)
GO
UPDATE  B SET F1 = 'A'  WHERE F1 = 'B'
GO
А вот тут RID ы появляются в обеих таблицах.
17 ноя 13, 04:19    [15142902]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
Кстати, ещё не все отличия перечислены.

Как миниму знаю вижу ещё два.
17 ноя 13, 04:20    [15142903]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
SandalTree
1. Не всегда. RID создаётся только на неуникальном кластерном индексе и только в случае повторяющихся значений.
Откуда сделан такой вывод?
В документации ведь написано:
http://technet.microsoft.com/en-us/library/hh213609.aspx
When a table is stored as a heap, individual rows are identified by reference to a row identifier (RID) consisting of the file number, data page number, and slot on the page.
Вы наверное путаете RID и uniqueifier.
http://msdn.microsoft.com/en-us/library/ms177484.aspx
If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes.
17 ноя 13, 07:51    [15142968]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
SandalTree
Exproment
SandalTree, мне себя цитировать ?
пропущено...

на физическом уровне database engine всегда может однозначно идентифицировать записать. Не зависимо от наличия кластеризованного индекса и независимо от каких либо ограничений уникальности(всегда есть RID). А вот на логическом - вы совершенно правы. О чем и говорит пример с change tracking.


1. Не всегда. RID создаётся только на неуникальном кластерном индексе и только в случае повторяющихся значений.

2. У меня вопрос непро уникальный кластерный индекс, а про Primary Key, который с ним различается.


Нет, RID есть всегда, он просто не всегда используется.
17 ноя 13, 09:52    [15143040]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос на собеседовании  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Storage Engine всегда может однозначно идентифицировать запись, даже если таблица куча и все значения в ней одинаковые или неуникальный кластерный индекс и все ключи одинаковые. "Только люди видят дубликаты" (c) Paul Randal выше на скриншоте. Я думаю достаточно авторитетный источник =)

Что касается механизма поддержки уникальности. Есть понятие - закладка. Закладка - это логический указатель на строку. Физически закладка может реализовываться по-разному.

Например:
  • Таблица-куча - FileID:PageID:SlotID (то, что общепринято называть RID)
  • Таблица-кластеризованная RowStore - Clustered Index Key : [Uniqueifier] (унификатор, добавляется если кластерный индекс не уникален)
  • Таблица-кластеризованная ColumnStore - RowgroupID:TypleID (для кластерных колоночных индексов в 2014, идентификатор группы строк + порядковый номер строки, нигде не хранится - вычисляется)
  • Таблица-кластеризованная ColumnStore часть Delta Store - CSILOCATOR (для кластерных колоночных индексов в 2014, для внутренней структуры Delta Store , которая поддерживает изменения добавляется псевдо колонка CSILOCATOR суррогатный ключ, по сути просто порядковый номер)

    Закладки нужны серверу для того, чтобы поддерживать изменения. Даже если никаких других индексов нет, простое написание запроса delete from t, происходит следующим образом.

    + Создать БД

    --Create DB
    create database bmtest;
    go
    use bmtest;
    go
    



    Куча
    + Куча

    --Heap
    CREATE TABLE A(F1 Char(1) );
    GO
    INSERT INTO A(F1) VALUES ('A'), ('B');
    GO
    select *, sys.fn_PhysLocFormatter(%%physloc%%) as PhysicalLocationFormatted , %%physloc%% as PhysicalLocation from A;
    go
    set showplan_xml on;
    go
    delete from a;
    go
    set showplan_xml off;
    go
    
    -- Ckeck leaf level nonclustered index
    create nonclustered index ix_f1 on a(f1);
    go
    select *, sys.fn_PhysLocFormatter(%%physloc%%) as PhysicalLocation from A with(index(ix_f1));
    go
    dbcc page('bmtest',1,94,3) with tableresults;
    



    Любой запрос на изменение имеет две части, так называемые read cursor и write cursor. В части read cursor сервер определяет, какие строки нужно изменить, для этих строк он собирает закладки, эти закладки после передаются в часть write cursor. Которая выполняет изменения на основных данных и дополнительных (если есть другие индексы которые затрагивает модификация).

    Картинка с другого сайта.

    Обратите внимание в плане на вывод псевдо колонки Bmk1000. Это как раз и есть закладка.
    В данном случае, таблица куча, а потому закладка это просто физическое расположение строки.

    Картинка с другого сайта.

    Можно создать не кластерный индекс и посмотреть, что лежит на его листовых страницах, в качестве закладки (вторая часть скрипта, подставьте свои номера страниц если будете пробовать сами).

    Картинка с другого сайта.

    Видно, что используется HEAP RID key, который совпадает с физическим положением строки в кластерном индексе (предыдущий рисунок).

    Неуникальный кластерный индекс
    Тот же набор экспериментов для неуникального кластерного индекса.
    + Неуникальный кластерный индекс
     --Clustered Index
    CREATE CLUSTERED INDEX CLIX_B ON A(F1);
    go
    set showplan_xml on;
    go
    delete from a;
    go
    set showplan_xml off;
    go
    
    -- Ckeck leaf level nonclustered index
    select *, sys.fn_PhysLocFormatter(%%physloc%%) as PhysicalLocation from A with(index(ix_f1));
    go
    dbcc page('bmtest',1,121,3) with tableresults;
    


    Картинка с другого сайта.

    Обратите внимание, теперь, в качестве закладки в write cursor передается ключ кластерного индекса + унификатор (т.к. мы сделали неуникальный кластреный индекс). Второй интересный момент, поскольку на предыдущем шаге мы создали некластреный индекс, то для поиска строк оптимизатор решил использовать его, и значение закладки взял именно из некластерного индекса.

    Можно посмотреть, что хранится на листовом уровне некластерного индекса теперь:

    Картинка с другого сайта.

    Видно, что теперь нет RID-а, но добавился унификатор, причем, что интересно, поскольку значения A,B у нас фактически разные, унификатор один и тот же.

    Уникальный кластерный индекс
    + Уникальный кластерный индекс
    --Unique Clustered Index
    CREATE UNIQUE CLUSTERED INDEX CLIX_B ON A(F1) WITH (DROP_EXISTING = ON);
    go
    set showplan_xml on;
    go
    delete from a;
    go
    set showplan_xml off;
    go
    
    -- Ckeck leaf level nonclustered index
    select *, sys.fn_PhysLocFormatter(%%physloc%%) as PhysicalLocation from A with(index(ix_f1));
    go
    dbcc page('bmtest',1,144,3) with tableresults;
    


    Теперь, поскольку индекс уникальный, в качестве закладки, используется просто ключ кластерного индекса:

    Картинка с другого сайта.

    Картинка с другого сайта.

    Для колоночных индексов уже не буду расписывать, в докладе есть на видео, кому интересно могут посмотреть.
    Таким образом, с помощью закладок, сервер всегда точно знает, с какой именно строкой он работает.

    + Удалим БД

    -- Drop DB
    use master;
    go
    drop database bmtest;
    go
    

  • 17 ноя 13, 13:33    [15143366]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104751
    PK - всегда один единственный
    UQ - может быть сколько угодно
    17 ноя 13, 15:01    [15143643]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47144
    Glory
    PK - всегда один единственный
    Не всегда. Его вообще может не быть. Увы.
    17 ноя 13, 15:03    [15143650]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104751
    iap
    Glory
    PK - всегда один единственный
    Не всегда. Его вообще может не быть. Увы.

    В смысле ? Вы можете создать больше одного ПК ?
    17 ноя 13, 15:05    [15143654]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47144
    Glory
    iap
    пропущено...
    Не всегда. Его вообще может не быть. Увы.

    В смысле ? Вы можете создать больше одного ПК ?
    Могу вообще не создавать.
    И не будет "одного-единственного"...
    17 ноя 13, 15:07    [15143658]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104751
    iap
    Glory
    пропущено...

    В смысле ? Вы можете создать больше одного ПК ?
    Могу вообще не создавать.
    И не будет "одного-единственного"...

    Словосочетание "один единственный" означает, что создать его можно в единственном экземпляре.
    В отличии от уникальных.
    17 ноя 13, 15:10    [15143670]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47144
    В общем, я предполагал, что точнее сказать
    "можно создать не более одного PK и сколько угодно UQ".
    Ничего, кроме этого.
    17 ноя 13, 15:14    [15143684]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    SandalTree
    Member

    Откуда: Перехлёсток восьми батог
    Сообщений: 28146
    Ruuu
    SandalTree
    1. Не всегда. RID создаётся только на неуникальном кластерном индексе и только в случае повторяющихся значений.
    Откуда сделан такой вывод?
    В документации ведь написано:
    http://technet.microsoft.com/en-us/library/hh213609.aspx
    When a table is stored as a heap, individual rows are identified by reference to a row identifier (RID) consisting of the file number, data page number, and slot on the page.
    Вы наверное путаете RID и uniqueifier.
    http://msdn.microsoft.com/en-us/library/ms177484.aspx
    If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes.
    Вы правы. Я их попутал.

    В любом случае RID это как домашний адрес, он нигде не хранится, а по нему вас найти можно, но если ты его не знаешь...

    Спасибо за поправку.
    17 ноя 13, 19:08    [15144342]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    SandalTree
    Member

    Откуда: Перехлёсток восьми батог
    Сообщений: 28146
    SomewhereSomehow
    Можно создать не кластерный индекс и посмотреть, что лежит на его листовых страницах, в качестве закладки (вторая часть скрипта, подставьте свои номера страниц если будете пробовать сами).

    Картинка с другого сайта.

    Видно, что используется HEAP RID key, который совпадает с физическим положением строки в кластерном индексе (предыдущий рисунок).

    HEAP RID key используется индексом, но в реальности-то он не хранится.
    Нету его на странице данных.
    17 ноя 13, 19:47    [15144474]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    SandalTree
    Member

    Откуда: Перехлёсток восьми батог
    Сообщений: 28146
    iap
    Glory
    PK - всегда один единственный
    Не всегда. Его вообще может не быть. Увы.
    Полагаю Гаря хотел сказать так:
    автор
    UQ - может быть сколько угодно
    PK - может быть только один
    Вот уже три отличия нашли.
    Добавлю от себя четвёртое:
    автор
    При создании PK по дефолту создаётся кластерный индекс, а при создании UQ нет.
    Всего 4 отличия. Кто-то знает/видит ещё?
    17 ноя 13, 19:51    [15144492]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    SandalTree
    Member

    Откуда: Перехлёсток восьми батог
    Сообщений: 28146
    iap
    В общем, я предполагал, что точнее сказать
    "можно создать не более одного PK и сколько угодно UQ".
    Ничего, кроме этого.
    Все правильно поняли
    17 ноя 13, 19:52    [15144499]     Ответить | Цитировать Сообщить модератору
     Re: Вопрос на собеседовании  [new]
    Сон Веры Павловны
    Member

    Откуда:
    Сообщений: 6202
    SandalTree
    Добавлю от себя четвёртое:
    автор
    При создании PK по дефолту создаётся кластерный индекс, а при создании UQ нет.

    Только если ранее не был объявлен/создан другой кластерный индекс:
    use tempdb
    go
    create table dbo.T (
      n int unique clustered,
      k int primary key
    )
    select type_desc from sys.indexes where object_id=OBJECT_ID(N'dbo.T') and is_primary_key=1
    

    type_desc
    -------------------
    NONCLUSTERED
    17 ноя 13, 20:10    [15144548]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2 3 4 5   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить