Hierarchyid é um tipo de dados de tamanho variável. Deve-se hierarchyid para representar posição em uma hierarquia. Uma coluna de tipo hierarchyid não representa automaticamente uma árvore. Depende de o aplicativo gerar e atribuir valores hierarchyidde tal modo que a relação desejada entre as linhas seja refletida nos valores.
Um valor do tipo de dados hierarchyid representa uma posição em uma hierarquia de árvore. Os valores para hierarchyid têm as seguintes propriedades:
- Extremamente compacto O número médio de bits necessários para representar um nó em uma árvore com n nós depende da média de fanout (o número médio de filhos de um nó). Para fanouts pequenos (0-7), o tamanho é de aproximadamente 6*logAn bits, onde A é o fanout médio. Um nó em uma hierarquia organizacional de 100.000 pessoas com um fanout médio de 6 níveis usa cerca de 38 bits. Isso é arredondado para 40 bits, ou 5 bytes, para armazenamento.
- A comparação está na ordem de profundidade Dado dois valores hierarchyid a e b, a
- Suporte a inserções e exclusões arbitrárias Usando o método GetDescendant, é sempre possível gerar um irmão à direita de qualquer nó determinado, à esquerda de qualquer nó determinado, ou entre dois irmãos. A propriedade de comparação é mantida quando um número arbitrário de nós é inserido ou excluído da hierarquia. A maioria das inserções e exclusões preserva a propriedade de densidade. Porém, inserções entre dois nós produzirão valores hierarchyid com uma representação ligeiramente menos compacta.
- A codificação usada no tipo hierarchyid se limita a 892 bytes. Por conseguinte, nós que têm muitos níveis em sua representação para se ajustarem a 892 bytes não podem ser representados pelo tipo hierarchyid.
O tipo hierarchyid está disponível a clientes CLR como o tipo de dados SqlHierarchyId. O tipo hierarchyid codifica informações logicamente sobre um único nó em uma árvore de hierarquia codificando o caminho da raiz da árvore para o nó. Esse caminho é representado logicamente como uma seqüência de rótulos de nós de todos os filhos visitados depois da raiz. Uma barra inicia a representação e um caminho que visita apenas a raiz é representado por uma barra única. Para níveis abaixo da raiz, cada rótulo é codificado como uma seqüência de inteiros separados por pontos. A comparação entre filhos é executada comparando-se as seqüências de inteiros separados por pontos na ordem alfabética. Cada nível é seguido por uma barra. Portanto, uma barra separa os pais de seus filhos. Por exemplo, são caminhos de hierarchyid válidos, respectivamente, de comprimentos 1, 2, 2, 3 e 3 níveis:
- /
- /1/
- /0.3.-7/
- /1/3/
- /0.1/0.2/
Podem ser inseridos nós em qualquer local. Nós inseridos depois de /1/2/, mas antes de /1/3/ podem ser representados como /1/2.5/. Nós inseridos antes de 0 têm a representação lógica como um número negativo. Por exemplo, um nó que vem antes de /1/1/ pode ser representado como /1/-1/. Nós não podem ter zeros à esquerda. Por exemplo, /1/1.1/ são válidos, mas /1/1.01/ não são válidos. Para evitar erros, insira nós usando o método GetDescendant.
O tipo de dados hierarchyid pode ser convertido em outros tipos de dados, como segue:
- Use o método ToString () para converter o valor de hierarchyid para a representação lógica como tipo de dados nvarchar(4000).
- Use Read () e Write () para converter hierarchyid para varbinary.
- Não há suporte para conversão de hierarchyid para XML. Para transmitir parâmetros hierarchyid por SOAP, primeiro converta-os em cadeias de caracteres. Uma consulta com a cláusula FOR XML falhará em uma tabela com hierarchyid, a menos que a coluna seja, primeiro, convertida para um tipo de dados de caractere.
Quando um banco de dados é atualizado para SQL Server 2008, os novo assembly e o tipo de dados hierarchyid são instalados automaticamente. Regras do supervisor de atualização detectam qualquer tipo de usuário ou assemblies com nomes conflitantes. O supervisor de atualização aconselhará renomear quaisquer assemblies conflitantes, bem como qualquer tipo de conflito, ou usar nomes de duas partes no código para fazer referência ao tipo de usuário preexistente.
Se uma atualização de banco de dados detectar um assembly de usuário com nome conflitante, ele o renomeará automaticamente e o colocará no banco de dados em modo de suspeição.
Se um tipo de usuário com nome conflitante existir durante a atualização, nenhuma etapa especial será efetuada. Depois da atualização, existirão ambos os tipos de usuário, antigo e novo. O tipo de usuário só estará disponível através de nomes de duas partes.
Podem ser usadas colunas do tipo hierarchyid em qualquer tabela replicada. Os requisitos para seu aplicativo dependem de a replicação ser unidirecional ou bidirecional e das versões de SQL Server utilizadas.
Replicação unidirecional inclui replicação de instantâneo, replicação transacional e replicação de mesclagem, nas quais não são feitas alterações no Assinante. O modo de funcionamento das colunas hierachyid com replicação unidirecional depende da versão de SQL Server executada pelo Assinante.
- Um Editor SQL Server 2008 pode replicar colunas hierachyid para um Assinante SQL Server 2008 sem qualquer consideração especial.
- Um Publicador do SQL Server 2008 deve converter colunas hierarchyid para replicá-las para um Assinante que está executando SQL Server Compact 3.5 SP1 ou uma versão anterior do SQL Server. O SQL Server Compact 3.5 SP1 e versões anteriores do SQL Server não oferecem suporte para colunas hierarchyid. Ainda que esteja usando uma dessas versões, você poderá replicar dados para um Assinante. Para tanto, você deve definir uma opção de esquema ou o nível de compatibilidade de publicação (para replicação de mesclagem) a que a coluna pode ser convertida para um tipo de dados compatível. Para obter mais informações, consulte Usando várias versões do SQL Server em uma topologia de replicação.
Vamos fazer um exemplo e ver na prática como usar esse novo recurso.
Use AdventureWorksLT
Go
Create Schema HumanResources
Go
CREATE TABLE HumanResources.EmployeeDemo
(
OrgNode HIERARCHYID,
EmployeeID INT,
LoginID VARCHAR(100),
Title VARCHAR(200),
HireDate DATETIME
)
Go
CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo
ON HumanResources.EmployeeDemo (OrgNode,EmployeeID)
Go
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (hierarchyid::GetRoot(), 1,'adventure-works\scott', 'CEO', '3/11/05') ;
Go
DECLARE @Manager hierarchyid
SELECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo;
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-works\Mark',
'CTO', '4/05/07')
Go
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeDemo;
Select @FirstChild = @Manager.GetDescendant(NULL,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-works\ravi',
'Director Marketing', '4/08/07')
Go
DECLARE @Manager hierarchyid
SELECT @Manager = CAST('/1/' AS hierarchyid)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(NULL, NULL),45,
'adventure-works\Ben','Application Developer', '6/11/07') ;
Go
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = CAST('/1/' AS hierarchyid)
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild, NULL),55,
'adventure-works\Laura','Trainee Developer', '6/11/07') ;
Go
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = CAST('/2/' AS hierarchyid)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(NULL, NULL),551,
'adventure-works\frank','Trainee Sales Exec.', '12/11/07') ;
Go
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = CAST('/2/' AS hierarchyid)
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild, NULL),531,
'adventure-works\vijay','Manager Industrial Sales', '12/09/06') ;
Go
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
DECLARE @SecondChild hierarchyid
SELECT @Manager = CAST('/2/' AS hierarchyid)
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
SELECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543,
'adventure-works\james','Manager Consumer Sales', '12/04/06') ;
CREATE PROCEDURE AddEmployee(@ManagerID hierarchyid, @EmpID int,
@LogID varchar(100), @JobTitle as varchar(200), @JoiningDate datetime)
AS
BEGIN
DECLARE @LastChild hierarchyid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo
WHERE OrgNode = @ManagerID
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate)
COMMIT
END ;
SELECT * FROM HumanResources.EmployeeDemo
Nenhum comentário:
Postar um comentário