Plataforma SQL Server 2012

De Qknow
Ir para: navegação, pesquisa
SQLServer2012.jpeg


Introdução

Embora não seja o propósito desta base de conhecimento compartilhar informações de outras plataformas e produtos, é fato que muitas das fontes de dados para o QlikView ou QlikSense partem de sistemas de gerenciamento de banco de dados de mercado, como SQL Server, Oracle, PostgreSQL, MySQL, DB2, entre tantos outros. Longe de ser uma base completa de instruções e comandos de cada uma destas plataformas, este artigo se concentra em apresentar alguns pontos relevantes no trabalho com o SQL Server 2012/2014 utilizado como plataforma dos dados para o QlikView, QlikSense ou Qlik NPrinting. Desta maneira, cabem algumas observações sobre o produto de banco de dados da Microsoft.

Services and Components

O SQL 2012 é composto de uma série de serviços para diferentes finalidades, incluindo:

  • SQL Server Engine: Responsável por atendimento as requisições transacionais. Cada instância do SQL instalado em um equipamento Windows Server contará com uma versão deste serviço.
  • Analysis Services: É o serviço responsável pelo OLAP do SQL e tratamento de requisições analíticas onde boa parte do serviço de dataware housing é baseada.
  • Integration Services: Sucessor do MS DTS, permite criar fluxos de importação e exportação de dados entre diferentes plataformas. É o ETL do BI tradicional.
  • Report Services: Serviço responsável por disponibilizar relatórios aos usuários finais baseado em web services. Concorrente mais direto do QlikView NPrinting.
  • Master Data Services: Fornece ferramental para tratamento de dados mapeados ou referência a dados externos. É utilizado como hub entre conjuntos de dados.
  • StreamInsight: Destinado a soluções que necessitam de alto poder de processamento, especialmente para eventos de grande velocidade.
  • Data Mining: Serviço de mineração dos dados responsável por aplicar regras que identificam padrões através de um engine de inferências.
  • Full-text Search: Presente desde as primeiras versões, permite buscas textuais avançadas dentro do banco relacional, incluindo sofisticadas pesquisas semânticas.
  • PowerPivot: Destinado aos usuários de negócio, profissionais de informática ou Power Users, um dos concorrentes indiretos do QlikView/QlikSense.
  • Replication: Serviço destinado a copiar dados entre plataformas de maneira constante, movendo informações dispersas para um repositório central ou vice-versa.
  • Data Quality Services: Serviço que se utiliza de uma base de "conhecimento" sobre os dados para garantir uma melhor qualidade das informações.
  • Power View: Permite uma rápida visualização dos dados por usuários finais com uma ferramenta de fácil entendimento e uso.

SQL Server Instances

Diferentes instâncias podem ser instaladas em um único equipamento por diferentes motivos, incluindo a necessidade de equipe de administração segregada por base de dados, diferentes níveis de SLA (Acordos de Níveis de Serviço) para cada tipo de base de dados, questões de compatibilidade com versões anteriores ou mesmo edições instaladas, além de características de collation (conjunto de caracteres disponíveis) de aplicações que dependam das configurações do SGBD (mais especificamente da tempdb).

Por questões de compatibilidade com as versões anteriores, a instância padrão do SQL é ainda denominada default instance, enquanto as demais instâncias são chamadas de instâncias nomeadas. No entanto, nenhuma default instance precisa existir antes que uma instância nomeada seja instalada. Além disso, muitos serviços são instalados uma única vez, como no caso do Integration Services, capaz de atender múltiplas instâncias com uma única instalação. Adicionalmente, as ferramentas de gerenciamento e desenvolvimento são capazes de trabalhar com todas as instâncias instaladas em um mesmo servidor, dispensando a necessidade de novas instalações.

Nota:  Tanto o collation quanto o nome das instâncias alteram as instruções (comandos) de conexão do QlikView/Sense ao SQL Server.

Editions

SQL Server 2012 está disponível em diferentes edições, cada uma com uma capacidade e usabilidade com diversos custos e resultados, incluindo:

  • Parallel Data Warehouse: Comercializado como um appliance, é destinado a grandes volumes de dados e não é comercializado como o padrão de licenças de software.
  • Enterprise Edition: A edição mais completa contendo todas as características de um banco relacional como também estrutura para montagem de serviços OLAP.
  • Business Intelligence: Adiciona os recursos de BI à versão Standard sem que seja necessária aquisição de uma edição Enterprise.
  • Standard: Versão confiável e robusta dos principais e necessários recursos de um gerenciador de banco de dados relacional, sem características de BI.
  • Express: Edição gratuita para pequenas aplicações de portais Web e aplicativos baseados em servidores de dados. Recursos limitados mas suficientes para pequenos projetos.
  • Compact: Edição gratuita para aplicações individuais (sem conectividade) ou para aplicações conectadas por dispositivos móveis.
  • Developer Edition: Com os mesmos recursos de uma edição Standard, mas destinado ao ambiente de desenvolvimento, segregado do ambiente de produção.
  • Web: Edição robusta para aplicações e portais baseados na Web, contendo características de segurança e baixo custo de aquisição.
  • SQL Azure: Serviço de banco de dados em nuvem da Microsoft, onde os recursos de hardware e administração do software são abstraídos da instituição.



Connecting From Client and Applications

Antes que uma ferramenta ou aplicação como o QlikView ou QlikSense possa se conectar ao Microsoft SQL Server é preciso determinar o meio pelo qual a conexão será realizada. O protocolo mais próximo da camada do SQL é chamado de TDS (Tabular Data Stream), que deve ser transportado por um protocolo de nível mais baixo e próximo da camada de rede, tipicamente TCP/IP, Named Pipes ou Shared Memory.

Do lado das aplicações QlikView ou QlikSense as conexões são estabelecidas por meio de bibliotecas como ODBC, OLEDB ou JDBC. O conhecido padrão ODBC é responsável por omitir da aplicação as características singulares do banco de dados evitando que o cliente tenha que conhecer os aspectos específicos de cada plataforma. As instruções emitidas pela aplicação são traduzidas pelo ODBC de acordo com a plataforma em uso. No padrão OLEDB, originalmente concebido para trabalhar no modo OLE (Object Linking and Embedding), os comandos do cliente não são transformados. Ao invés, são enviados nativamente para a plataforma de banco selecionada, o que torna esse método mais rápido e ágil.

SQLServerConnect.PNG

Independente da biblioteca cliente em uso (ODBC, JDBC ou OLEDB), a camada chamada de SNAC (SQL Server Native Access Component) é responsável por traduzir os comandos para o Engine e de volta para a aplicação utilizando o protocolo no padrão TDS. Somente a partir deste ponto a chamada para rede (se for o caso) é feita.

Todas as chamadas encaminhadas são recebidas pela camada chamada de Endpoints. As conexões para os Endpoints são realizadas tipicamente sobre o protocolo TCP/IP ou Named Pipes. Se a ferramenta ou aplicação estiver sendo executada no servidor onde o SQL está instalado, então um protocolo especial chamado Shared Memory pode ser utilizado. O SQL SO (Operation System) abstrai os recursos de hardware do Engine do SQL Server, alocando e liberando os recursos necessários.

Para projetos que envolvem BI, o instalador do SQL adiciona modelos (templates) ao Visual Studio 2010 se este estiver presente no mesmo equipamento onde o servidor for instalado. Estes templates são utilizados para os serviços do Analysis Services, Integration Services e Report Services. Os modelos são chamados de SSDT (SQL Server Data Tools) e utilizam o Visual Studio como plataforma. Caso o Visual Studio não esteja presente durante a instalação do servidor SQL, uma edição "partner" é instalada pelo programa setup. Estes são os produtos mais diretamente concorrentes do QlikView ou QlikSense, ainda que estejamos falando de soluções de BI tradicionais e não de produtos de descoberta de dados.

No QlikView ou QlikSense, o comando Connect configura a conexão para um banco de dados por meio de OLEDB ou ODBC, o que for especificado na instrução. O primeiro passo é especificar o nome da fone de dados e o tipo de conexão. No entanto, não é preciso decorar a sintaxe da instrução connect na medida em que o assistente de conexão se encarrega de inserir o comando com todos os parâmetros necessários.

A sintaxe do comando Connect é apresentada a seguir:

ODBC  connect to caracter-conexão [ ( info_acesso ) ]
OLEDB connect to caracter-conexão [ ( info_acesso ) ]

Considere ainda as variações que podem ocorrer em virtude da conectividade 32 ou 64 bits do sistema operacional, driver do banco de dados e instrução connect pretendida. O comando Connect32 tem a mesma aplicação do connect, mas força o sistema de 64 bits a usar um provedor de ODBC/OLEDB de 32 bits. Já o comando Connect64 tem a mesma aplicação do connect, mas força o uso de um provedor de 64 bits.

Janelas 	QlikView 	Comando 	Resultado
64 bits 	64 bits 	connect 	usa um ODBC de 64 bits
64 bits 	64 bits 	connect32 	usa um ODBC de 32 bits
64 bits 	64 bits 	connect64 	usa um ODBC de 64 bits
64 bits 	32 bits 	connect 	usa um ODBC de 32 bits
64 bits 	32 bits 	connect32 	usa um ODBC de 32 bits
64 bits 	32 bits 	connect64 	usa um ODBC de 64 bits
32 bits 	32 bits 	connect 	usa um ODBC de 32 bits
32 bits 	32 bits 	connect32 	usa um ODBC de 32 bits
32 bits 	32 bits 	connect64 	causa erro de script

A seguir um exemplo de conexão para a plataforma SQL Server:

OLEDB CONNECT32 TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;
                   Initial Catalog=TSQL2012;Data Source=192.168.63.134;Use Procedure for Prepare=1;Auto Translate=True;
                   Packet Size=4096;Workstation ID=WIN-E15SL7MRRKR;Use Encryption for Data=False;Tag with column collation when possible=False]
                   (XPassword is IEGOZCNOULZEXZcGGLIWC);



Administration Tools

Para administração do SQL 2012 existem várias ferramentas disponíveis, iniciando pelo SSMS (Management Studio) que habilita o administrador a conectar a múltiplas instâncias do SQL Server, realizar consultas, criar e manter usuários, tabelas, visões e procedures, além de invocar outras ferramentas a partir do próprio SSMS.

O SQL Server Configuration Manager possui novas características que inclui o gerenciamento dos serviços (iniciar, parar ou interromper temporariamente), além de definição dos protocolos de conectividade do servidor, porta, alias e tipos de Endpoints disponíveis. Pode especificar também os protocolos que serão utilizados para conexão com o servidor pelo lado do cliente.

Um equívoco comum é alterar a conta de execução dos serviços listados no SSCM a partir dos serviços do sistema operacional Windows, como feito nas versões 2000 do SQL Server. No 2012, as alterações de credenciais dos serviços devem ser modificadas pelo SSCM, sob pena de não incorporar as permissões e controles necessários a correta execução do serviço para a conta especificada. Estas configurações não ocorrem automaticamente quando a troca é realizada pelo Services do Control Painel do Windows Server.

Portas e Listeners são também configurados pelo SSCM, sob os quais existem configurações específicas para cada tipo de protocolo a ser utilizado, incluindo a porta de escuta do serviço SQL. Duas seções estão disponíveis para configurações de clientes 32 e 64 bits. Por exemplo, o SSMS é uma aplicação 32 bits, mesmo quando executado sobre plataforma 64.

Na criação de Alias o SSCM permite ocultar dos clientes as características específicas de cada servidor, evitando que o cliente tenha que informar um grande conjunto de parâmetros para conexão ao Server. Essa abordagem permite também que alterações possam ser feitas nos servidores sem que haja problemas de conectividade dos clientes, já que as configurações de conexão podem ser alteradas para um determinado Alias.

Outras ferramentas igualmente úteis na administração do SQL incluem o SQL Profiler, capaz de capturar as instruções e comandos emitidos pelas aplicações clientes ao Engine do SQL, bem como ao Analysis Services, o Database Engine Tuning Advisor, destinado a criação de índices com base na análise dados coletados, o Master Data Services Configuration Manager, para configuração dos serviços de Data Services, Reporting Services, para geração de relatórios, Data Quality Services, para configurar e gerenciar projetos de base de conhecimento sobre dados, SQL Server Error and Usage Reporting, capaz de configurar alertas aos administradores do SQL visando automação de tarefas e aspectos do produto, o PowerShell Provider para consultar o SQL via linha de comando e, por último, o SQL DMO, responsável pelas bibliotecas de administração da ferramenta (API).

SQL Server Resources for QlikView/QlikSense

Creating Views

Views são criadas pela emissão do comando CREATE VIEW seguido do nome a ser atribuído a view. Estas são reflexo das tabelas que a originam e repetirão os nomes das colunas das tabelas de origem se nenhum alias (apelido usando o mesmo comando As do QlikView ou QlikSense) for utilizado. Alias são obrigatórios em views para colunas calculadas e eventuais duplicidades de nomes de colunas oriundos tipicamente de joins (INNER JOIN, LEFT JOIN, RIGHT JOIN) de mais de uma tabela. Views podem ser ligadas ao schema das tabelas que a originam pelo acréscimo do comando SCHEMABINDING. Nesta situação, a(s) tabela(s) de origem da view não podem ser excluídas sem antes a própria view ser modificada ou excluída. Alterações nas tabelas são permitidas desde que não afetem a definição da view ligada pelo parâmetro SCHEMABINDING.

Views podem referenciar tabelas de múltiplos bancos de dados desde que o usuário tenha permissão de acesso as tabelas relacionadas. No entanto, se o parâmetro SCHEMABINDING for utilizado, todos os objetos referenciados pela view devem estar contidos no mesmo database. A seguir um exemplo de View criada no SQL Server que pode ser lida pelo QlikView ou QlikSense omitindo questões de ligação ou regras de banco de dados não aplicadas ao negócio ou propósito do painel.

  CREATE VIEW dbo.GetProducctsInfo
          WITH SCHEMABINDING
               AS
               SELECT pc.categoryname, pp.unitprice, pp.productname from Production.Categories as pc
                      INNER JOIN Production.Products as pp
                             ON pp.categoryid = pc.categoryid;

O uso da instrução SCHEMABINDING vai garantir que modificações nas tabelas que dão origem a View não ocorram em detrimento as instruções SQL Select utilizadas dentro do QlikView ou QlikSense. Isso significa uma garantia adicional de que as modificações nas tabelas não afetarão as views utilizadas nas cargas de dados.

Colunas calculadas na View ou computadas com funções (MAX, COUNT, SUM, MIN, etc.) não permitem modificações. Igualmente, colunas geradas por operadores do tipo UNION, UNION ALL, CROSSJOIN, EXCEPT e INTERSEC não são atualizáveis pela View. Estas restrições se aplicam a quaisquer instruções SELECT especificadas na View, inclusive subqueries.

A alteração de Views ocorre pelo comando ALTER VIEW, o que mantém as configurações de permissão inalteradas. Ademais, objetos dependentes não precisam ser excluídos e recriados, diferente do que ocorreria em uma exclusão e recriação da View. Views indexadas podem ser alteradas com a mesma instrução, mas os índices correspondentes são excluídos automaticamente, diferente de outros objetos persistidos. A exclusão de Views ocorre pela emissão do comando DROP VIEW.

A criação de Views indexadas aceleram o processo de consulta permitindo que o Engine faça uso dos índices que compõe a View. Múltiplos índices podem ser criados em uma INDEXED VIEW, mas necessariamente o primeiro deles deve ser do tipo UNIQUE CLUSTERED. Além disso, outras opções de ambiente determinadas pela instrução SET necessitam ser configuradas para que os índices em Views operem adequadamente, incluindo como ON os seguintes conjuntos: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YELDS_NULL, QUOTED_IDENTIFIER. Além destes, deve ser configurado para OFF o parâmetro NUMERIC_ROUDABORT.

  CREATE VIEW dbo.GetProducctsInfo
          WITH SCHEMABINDING
               AS
               SELECT pc.categoryname, pp.unitprice, pp.productname from Production.Categories as pc
                      INNER JOIN Production.Products as pp
                             ON pp.categoryid = pc.categoryid;

  CREATE UNIQUE CLUSTERED INDEX idxGetProducts
               ON dbo.GetProducctsInfo (categoryid, productid);

Além das configurações dos parâmetros via SET, é necessário que o resultado das instruções SQL definidas para uma INDEXED VIEW seja determinístico. Ou seja, a partir dos mesmos parâmetros de entrada os valores do SELECT da View devem retornar o mesmo resultado se executado inúmeras vezes. Por exemplo, a função DATEADD é determinística, pois a partir de uma entrada específica o resultado será o mesmo em todas as execuções, diferente da função GETDATE(), em que para cada execução um valor diferente é retornado. Pra Views indexadas o parâmetro SCHEMABINDING é obrigatório.

Nota:  A função GETDATE() no SQL Server é equivalente a função NOW() do QlikView/QlikSense.

Outras condições precisam estar satisfeitas para a criação e uso de Views indexadas, dentre elas, que não contenha as seguintes instruções na definição da View: COUNT(*), ROWSET, OUTER joins, self-joins, TOP, ORDER BY, MIN, MAX, UNION, EXCEPT, INTERSECT TABLESAMPLE, CUBE, ROLLUP, GROUPING SETS, PIVOT, UNPIVOT, OFFSET. Ademais, a View não deve referenciar colunas do tipo float, text, ntext ou image.

A lista de Views em um banco de dados pode ser obtida pela instrução T-SQL SELECT em uma View de sistema (SYS.VIEWS). O conteúdo de uma View pode ser consultado (desde que não criptografada) pela função OBJECT_DEFINITION. Neste caso, é necessário fornecer o número do objeto (OBJECT ID) que pode ser obtido na sys.views. As antigas versões do SQL Server introduziram a procedure SP_HELPTEXT para obter o conteúdo de um objeto. Por questões de compatibilidade, a instrução ainda está disponível no SQL 2012. Já a dependência de um objeto em relação aos demais pode ser consultado na View de sistema sql_expression_dependencies.

SELECT * FROM sys.views;

SELECT OBJECT_DEFINITION (1925581898);

SP_HELPTEXT vw_Products;

SELECT * FROM sys.sql_expression_dependencies WHERE referenced_entity_name = 'Products';

Deve-se evitar o uso do asterisco na definição de Views, uma vez que a inclusão ou exclusão de uma ou mais colunas na(s) tabela(s) base não são refletidas na View automaticamente. No caso de inclusões, o resultado é simplesmente a não visualização da nova coluna, enquanto na exclusão, o resultado é uma mensagem de erro correspondente a desatualização da View em relação a(s) tabela(s) bases. Para que uma View definida com a instrução * possa refletir a(s) tabela(s) base, é necessário atualizar a definição da View com o comando ALTER VIEW ou pela execução da procedure sp_refreshview.

Query Data by Using SELECT

Basic SQL

A seleção de dados de uma plataforma como ORACLE, DB2, SQL Server, POSTGRE, MySQL, entre outras, é realizada pela execução do comando SELECT e suas variações. Diferente de arquivos persistidos em disco como planilhas eletrônicas, arquivos textuais (.TXT) e arquivos delimitados por algum caractere, bancos de dados necessitam de uma linguagem padrão de conversação para retornar ao QlikView as informações solicitadas pelo usuário em forma de linhas e colunas.

A sintaxe básica de um comando SELECT é apresentada a seguir, embora possa depender do driver de conexão em uso. Ou seja, dependendo do banco de dados utilizado é possível que haja mais parâmetros do que aqueles apresentados a seguir. Alguns destes, como o parâmetro TOP podem não estar disponíveis em todos os tipos de conexões. Recomenda-se consultar o manual de instruções SELECT do banco de dados em uso para maiores detalhes sobre os comandos disponíveis e suas variações.

SELECT [ALL | DISTINCT | DISTINCTROW | TOP n [PERCENT] ] 
	* lista_de_campos
FROM lista_de_tabelas
[WHERE critério ]
[ORDER BY listadecampos [ASC | DESC] ]
[ (INNER | LEFT | RIGHT | FULL) JOIN nome_da_tabela ON
	nome_de_campo_da_tabela_from = nome_de_campo_da_tabela_join ]

Os exemplos a seguir apresentam as formas mais comuns de combinação dos parâmetros apresentados na sintaxe do comando SELECT. Observe que para execução destas instruções uma conexão prévia ao banco de dados deve ser estabelecida.

Exemplo 01:
SELECT * FROM Categorias;	  // Leitura de todas as colunas (*) da tabela Categorias

Exemplo 02:
SELECT 	[ID da Categoria],        // Somente os campos ID da Categoria e 
	[Nome da Categoria]       // Nome da Categoria serão lidos da tabela
		FROM Categorias;  // Categoria 

Exemplo 03:
SELECT	[Detalhes do Pedido].[ID do Pedido],
	[Detalhes do Pedido].[Preço Unitário],
	[Detalhes do Pedido].Quantidade as Resultado
		FROM [Detalhes do Pedido], Pedidos
		WHERE Pedidos.[ID do Pedido] = [Detalhes do Pedido].[ID do Pedido]
		ORDER BY [Detalhes do Pedido].[ID do Pedido];

O exemplo 03 apresenta a leitura dos campos Id do Pedido, Preço Unitário e Quantidade da tabela Detalhes do Pedido (FROM) desde que haja uma ligação entre as tabelas Pedidos e Detalhes do Pedido (WHERE), mantida pela existência do campo Id do Pedido em ambas as tabelas. O resultado é ainda ordenado pela coluna Id do Pedido (ORDER BY). O uso de colchetes delimita os campos e/ou tabelas que tenham espaços em seu nome. Já a cláusula AS, utilizada no campo Quantidade, é utilizada para trocar o nome do cabeçalho do campo para Resultado. Observe que os campos são precedidos pelo nome da tabela separados por um sinal de ponto, no formato tabela.campo.

A função RANK estabelece uma lista numerada de acordo com o critério de priorização fornecido e campo a ser considerado. Os parâmetros internos (PARTITION BY e ORDER BY) visam agrupar o ranking pelo campo informado e ordená-lo de acordo com a necessidade, respectivamente. A função RANK funciona como um campo, criando uma relação de valores ordenados. Caso haja empates, estes serão classificados com o mesmo número e o seqüenciador pulará para o próximo número após a quantidade de empates existentes.

Top Records

SELECT TOP 10 orderid, productid, unitprice, qty,
	RANK() OVER (ORDER BY unitprice, qty DESC) FROM Sales.OrderDetails;

10269	33	2.00	60	1
10382	33	2.00	60	1
10414	33	2.00	50	3
10410	33	2.00	49	4
10252	33	2.00	25	5
10271	33	2.00	24	6
10273	33	2.00	20	7
10415	33	2.00	20	7
10454	33	2.00	20	7
10473	33	2.00	12	10

Para recuperar um número X de linhas de uma tabela ou View é possível utilizar o argumento TOP da instrução SELECT. Este pode ser especificado como um número fixo de linhas ou utilizar o parâmetro PERCENT para obter um percentual das linhas. No caso dos últimos registros promoverem um empate, a opção WITH TIES incluirá os valores iguais aos da última posição. WITH TIES só opera com a presença da instrução ORDER BY.

SELECT TOP 10 PERCENT * FROM Sales.Orders;

SELECT TOP 10 WITH TIES shipcity, sum(freight) FROM Sales.Orders GROUP BY shipcity  ORDER BY shipcity;

SELECT TOP 12 WITH TIES productname, unitprice FROM Production.Products ORDER BY unitprice;

Além do comando TOP (proprietário do SQL Server), o 2012 introduziu o comando FETCH como extensão do comando ORDER BY, que permite obter um número de linhas em um intervalo, bastante útil para paginação de dados.

SELECT * FROM Sales.OrderDetails
	ORDER BY unitprice
		OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;

SELECT * FROM Sales.EmpOrders
	ORDER BY empid
		OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY;


XML Output

Quando uma instrução SELECT é executada em um SQL Server, o resultado padrão de retorno é um tipo de dado chamado resultset. No entanto, é possível modular para XML o retorno da instrução adicionando ao final do comando o parâmetro FOR XML, que contém quatro variações. O primeiro deles, RAW, determina que cada linha do resultset seja retornando em uma linha do XML.

SELECT [categoryname], [productname], [unitprice] FROM [Production].[Categories] PC
       INNER JOIN [Production].[Products] PP ON PC.categoryid = PP.categoryid
               WHERE PP.categoryid = 1
                        FOR XML RAW;

<row categoryname="Beverages" productname="Product HHYDP" unitprice="1.8000"/>
<row categoryname="Beverages" productname="Product RECZE" unitprice="1.9000"/>
<row categoryname="Beverages" productname="Product QOGNU" unitprice="0.4500"/>
<row categoryname="Beverages" productname="Product SWNJY" unitprice="1.4000"/>
<row categoryname="Beverages" productname="Product NEVTJ" unitprice="1.8000"/>
<row categoryname="Beverages" productname="Product QDOMO" unitprice="26.3500"/>

O segundo tipo de emissão de resultado em formato XML envolve o parâmetro FOR XML AUTO. Este modelo utiliza algoritmos internos para criar um XML com hierarquias. A mesma (anterior) instrução executada com este parâmetro cria uma chave geral para o campo categoryname contendo o item "Beverages".

<PC categoryname="Beverages">
	<PP productname="Product HHYDP" unitprice="1.8000"/>
	<PP productname="Product RECZE" unitprice="1.9000"/>
	<PP productname="Product QOGNU" unitprice="0.4500"/>
	<PP productname="Product SWNJY" unitprice="1.4000"/>
	<PP productname="Product NEVTJ" unitprice="1.8000"/>
	<PP productname="Product QDOMO" unitprice="26.3500"/>
	<PP productname="Product LSOFL" unitprice="1.8000"/>
	<PP productname="Product ZZZHR" unitprice="4.6000"/>
	<PP productname="Product XLXQF" unitprice="1.4000"/>
	<PP productname="Product TOONT" unitprice="1.5000"/>
	<PP productname="Product BWRLG" unitprice="0.7750"/>
	<PP productname="Product JYGFE" unitprice="1.8000"/>
</PC>

O último dos parâmetros, PATH, faz com que o XML seja gerado com uma linha para cada registro do resultset. Porém, diferente do XML RAW, as linhas serão incluídas dentro das tags <ROW> </ROW>, ao invés de serem atributos da tag <ROW> como ocorre no XML RAW.

<row><categoryname>Beverages</categoryname><productname>Product HHYDP</productname><unitprice>1.8000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product RECZE</productname><unitprice>1.9000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product QOGNU</productname><unitprice>0.4500</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product SWNJY</productname><unitprice>1.4000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product NEVTJ</productname><unitprice>1.8000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product QDOMO</productname><unitprice>26.3500</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product LSOFL</productname><unitprice>1.8000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product ZZZHR</productname><unitprice>4.6000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product XLXQF</productname><unitprice>1.4000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product TOONT</productname><unitprice>1.5000</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product BWRLG</productname><unitprice>0.7750</unitprice></row>
<row><categoryname>Beverages</categoryname><productname>Product JYGFE</productname><unitprice>1.8000</unitprice></row>

Sub Queries

CTE é a sigla utilizada para COMMUM TABLE EXPRESSION, uma representação em forma de tabela de um resultset definido por uma instrução SELECT que pode ser utilizado em outras operações de consulta bem como para operações de INSERT, UPDATE e DELETE. CTEs também podem ser definidas em Views. Outra possibilidade é fazer uso da definição da CTE dentro dela própria, chamando-a recursivamente. Uma CTE não pode ser usada para definir outra CTE. Ou seja, não é possível utilizar dois comandos WITH em uma mesma definição de CTE, mesmo pensando em uma sub-query de CTEs.

Adicionalmente, CTEs não podem conter instruções do tipo ORDER BY (salvo quando utilizado com TOP ou FOR XML), INTO e Hints no comando OPTION. Ao utilizar a instrução ORDER BY, é necessário combinar com o comando TOP ou FOR XML. O número de colunas retornado pela instrução que define o CTE deve ser o mesmo número da instrução WITH. As colunas recuperadas devem ter nomes únicos, o que pode ser ajustado ao se definir os nomes de cada uma delas no próprio comando WITH.

Exemplo 1:
WITH tmpSalesFrance
	AS
	(
		SELECT * FROM Sales.FranceCustomers WHERE city = 'Paris'
	)
	SELECT * FROM tmpSalesFrance;
Exemplo 2:
WITH tmpSalesFrance (xmlResult)
	AS
	(
		SELECT companyname, city FROM Sales.FranceCustomers WHERE city = 'Paris' 
			ORDER BY contactname FOR XML AUTO
	)
	SELECT * FROM tmpSalesFrance;
Exemplo 3:
WITH CompanySales (CompanyName, City, Region)
	AS
	(
		SELECT [companyname], [city], [region] FROM [Sales].[MexicoCustomers]
		UNION 
		SELECT [companyname], [city], [region] FROM [Sales].[FranceCustomers]
	)
	SELECT * FROM CompanySales ORDER BY Region


Aggregate Queries

Apesar da imensa capacidade do QlikView e QlikSense de trabalhar com cálculos In-Memory, há condições em que a quantidade de registros versos o detalhamento desejado permitem ações de aceleração do desempenho agregando valores antes que estes sejam carregados no QlikView/QlikSense. Esta característica no SQL Server permite que os dados possam ser agrupados em um nível de detalhamento esperado pelo dashboard para um aplicativo Qlik mais ágil e que consuma menos recursos computacionais como memória e processador.

A instrução GROUP BY é responsável por agregar os valores calculados por funções em uma ou mais colunas selecionadas a partir de uma operação SELECT. Em alguns casos, o comando GROUP BY pode ser combinado com a instrução ROLLUP para produzir sub-totais e/ou totais. Nestas ocasiões, valores NULL são inseridos nas colunas agregadas visando representar os totalizadores. Por vezes, é possível que haja valores NULL em colunas agregadas que confundem-se com valores NULL dos totais e sub-totais. Por isso, é possível utilizar a função GROUPING (campo(s)) para definir se a linha apresentada é resultante de um sub-total ou não. Valores zerados indicam valores reais, não totalizadores, enquanto 1 (um) representa um totalizador.

	SELECT [categoryname], SUM([unitprice]), GROUPING([categoryname]) 
		FROM [Production].[Categories] PC
			INNER JOIN [Production].[Products] PP ON PP.categoryid = PC.categoryid
				GROUP BY [categoryname] WITH ROLLUP

Beverages		45.575		0
Condiments		27.675		0
Confections		32.708		0
Dairy Products		28.73		0
Grains/Cereals		14.175		0
Meat/Poultry		32.404		0
Produce			16.185		0
Seafood			24.819		0
NULL			222.271		1




Assuntos Relacionados


Outros Assuntos


Envelope01.jpg
Procurando Algo? Fale Conosco!

Voltar | Página Principal