Retornando uma consulta SQL em XML

Depois de algum tempo sem postar, estou de volta. Estive um pouco ocupado e em busca de novos conhecimentos. Mas chega de ficar falando besteira e vamos ao que realmente interessa.

Estive pesquisando e encontrei uma maneira de gerar a saída de uma consulta no formato XML utilizando uma consulta em SQL.

SELECT

Clientes.ID,

Pedidos.Descricao,

Clientes.Nome

FROM

Clientes,

Pedidos

WHERE

Clientes.Nome LIKE ‘%Alexandre%’

ORDER BY

Pedidos.ID

FOR XML AUTO

A consulta acima exibirá como resultado (em formato XML) os clientes que contenham ‘Alexandre’ no nome ordenando pelo(s) pedido(s) existente(s).

A cláusula AUTO cria uma hierarquia dos elementos retornados automaticamente. Ex.: Existe o cliente ‘Alexandre’ e o mesmo possue dois pedidos, neste caso o retorno do XML será similar a este:

<Clientes ID=”1″ Nome=”Alexandre”>

<Pedidos Descricao=”Pedido Pendrive 4GB”/>

<Pedidos Descricao=”Pedido Televisor 29pol”/>

</Clientes>

Os nós de pedidos estão entre os clientes, criando assim uma hierarquia.

Provavelmente continuarei este artigo posteriormente, porque existe outras maneiras de utilizar esta técnica. Até a próxima.

Usando o CHARINDEX em SQL

O CHARINDEX é uma função em SQL que retorna a primeira posição de uma sequencia de caracteres em uma determinada expressão. A primeira posição de contagem é 1.

Sintaxe:

CHARINDEX(expressao1, expressao2 [, posicao_inicial])

expressao1: É onde contém a sequencia de caracteres a ser encontrado.
expressao2: É onde contém a sequencia de caracteres que será feito a busca. Geralmente, é usado uma coluna de um campo.
posicao_inicial: É um parâmetro opcional, que deve-se colocar a posição inicial da busca, caso omitido, será iniciado do zero.

Segue abaixo a sintaxe:

SELECT

Nome,

CHARINDEX(‘papel’, Nome) AS Posicao

FROM

Produto

Na situação acima a busca será iniciada na primeira posição do campo ‘Nome’ da tabela ‘Produto’, porque o último parâmetro que indica a posição inicial de busca foi omitido.

Como saber as características de um objeto em SQL

A linguagem SQL possui uma função que exibe informações sobre todos os objetos existentes na base de dados.

Sintaxe:

OBJECTPROPERTY(id, propriedade)

Argumentos:

id -> É a identificação do objeto na base de dados, ou seja, uma identificação única e que pode ser encontrada na tabela sysobjects. Lembrando que este argumente DEVE ser do tipo inteiro.
propriedade -> É onde insere a expressão que se deseja obter sobre o objeto.

Segue abaixo todas as propriedades existentes no SQL Server:

Property name	Object type	Description and values returned
CnstIsClustKey	Constraint	A primary key with a clustered index. 1 = True0 = False
CnstIsColumn	Constraint	COLUMN constraint. 1 = True0 = False
CnstIsDeleteCascade	Constraint	A foreign key constraint with the ON DELETE CASCADE option.
CnstIsDisabled	Constraint	Disabled constraint. 1 = True0 = False
CnstIsNonclustKey	Constraint	A primary key with a nonclustered index. 1 = True0 = False
CnstIsNotTrusted	Constraint	Constraint was enabled without checking existing rows, so constraint may not hold for all rows. 1 = True0 = False
CnstIsNotRepl	Constraint	The constraint is defined with the NOT FOR REPLICATION keywords.
CnstIsUpdateCascade	Constraint	A foreign key constraint with the ON UPDATE CASCADE option.
ExecIsAfterTrigger	Trigger	AFTER trigger.
ExecIsAnsiNullsOn	Procedure, Trigger, View	The setting of ANSI_NULLS at creation time. 1 = True0 = False
ExecIsDeleteTrigger	Trigger	DELETE trigger. 1 = True0 = False
ExecIsFirstDeleteTrigger	Trigger	The first trigger fired when a DELETE is executed against the table.
ExecIsFirstInsertTrigger	Trigger	The first trigger fired when an INSERT is executed against the table.
ExecIsFirstUpdateTrigger	Trigger	The first trigger fired when an UPDATE is executed against the table.
ExecIsInsertTrigger	Trigger	INSERT trigger. 1 = True0 = False
ExecIsInsteadOfTrigger	Trigger	INSTEAD OF trigger.
ExecIsLastDeleteTrigger	Trigger	The last trigger fired when a DELETE is executed against the table.
ExecIsLastInsertTrigger	Trigger	The last trigger fired when an INSERT is executed against the table.
ExecIsLastUpdateTrigger	Trigger	The last trigger fired when an UPDATE is executed against the table.
ExecIsQuotedIdentOn	Procedure, Trigger, View	The setting of QUOTED_IDENTIFIER at creation time. 1 = True0 = False
ExecIsStartup	Procedure	Startup procedure. 1 = True0 = False
ExecIsTriggerDisabled	Trigger	Disabled trigger. 1 = True0 = False
ExecIsUpdateTrigger	Trigger	UPDATE trigger. 1 = True0 = False
HasAfterTrigger	Table, View	Table or view has an AFTER trigger. 1 = True0 = False
HasInsertTrigger	Table, View	Table or view has an INSERT trigger. 1 = True0 = False
HasInsteadOfTrigger	Table, View	Table or view has an INSTEAD OF trigger. 1 = True0 = False
HasUpdateTrigger	Table, View	Table or view has an UPDATE trigger. 1 = True0 = False
IsAnsiNullsOn	Function, Procedure, Table, Trigger, View	Specifies that the ANSI NULLS option setting for the table is ON, meaning all comparisons against a null value evaluate to UNKNOWN. This setting applies to all expressions in the table definition, including computed columns and constraints, for as long as the table exists. 1 = ON0 = OFF
IsCheckCnst	Any	CHECK constraint. 1 = True0 = False
IsConstraint	Any	Constraint. 1 = True0 = False
IsDefault	Any	Bound default. 1 = True0 = False
IsDefaultCnst	Any	DEFAULT constraint. 1 = True0 = False
IsDeterministic	Function, View	The determinism property of the function. Applies only to scalar- and table-valued functions. 1 = Deterministic0 = Not DeterministicNULL = Not a scalar- or table-valued function, or invalid object ID.
IsExecuted	Any	Specifies how this object can be executed (view, procedure, or trigger). 1 = True0 = False
IsExtendedProc	Any	Extended procedure. 1 = True0 = False
IsForeignKey	Any	FOREIGN KEY constraint. 1 = True0 = False
IsIndexed	Table, View	A table or view with an index.
IsIndexable	Table, View	A table or view on which an index may be created.
IsInlineFunction	Function	Inline function. 1 = Inline function0 = Not inline functionNULL = Not a function, or invalid object ID.
IsMSShipped	Any	An object created during installation of Microsoft® SQL Server™ 2000. 1 = True0 = False
IsPrimaryKey	Any	PRIMARY KEY constraint. 1 = True0 = False
IsProcedure	Any	Procedure. 1 = True0 = False
IsQuotedIdentOn	Function, Procedure, Table, Trigger, View	Specifies that the quoted identifier setting for the table is ON, meaning double quotation marks delimit identifiers in all expressions involved in the table definition. 1 = ON0 = OFF
IsReplProc	Any	Replication procedure. 1 = True0 = False
IsRule	Any	Bound rule. 1 = True0 = False
IsScalarFunction	Function	Scalar-valued function. 1 = Scalar-valued0 = Table-valuedNULL = Not a function, or invalid object ID.
IsSchemaBound	Function, View	A schema bound function or view created with SCHEMABINDING. 1 = Schema-bound0 = Not schema-boundNULL = Not a function or a view, or invalid object ID.
IsSystemTable	Table	System table. 1 = True0 = False
IsTable	Table	Table. 1 = True0 = False
IsTableFunction	Function	Table-valued function. 1 = Table-valued0 = Scalar-valuedNULL = Not a function, or invalid object ID.
IsTrigger	Any	Trigger. 1 = True0 = False
IsUniqueCnst	Any	UNIQUE constraint. 1 = True0 = False
IsUserTable	Table	User-defined table. 1 = True0 = False
IsView	View	View. 1 = True0 = False
OwnerId	Any	Owner of the object. Nonnull = The database user ID of the object owner.NULL = Invalid input.
TableDeleteTrigger	Table	Table has a DELETE trigger. >1 = ID of first trigger with given type.
TableDeleteTriggerCount	Table	The table has the specified number of DELETE triggers. >1 = ID of first trigger with given type.NULL = Invalid input.
TableFullTextBackground…	Table	The table has full-text background update index enabled. 1 = TRUE0 = FALSE
TableFulltextCatalogId	Table	The ID of the full-text catalog in which the full-text index data for the table resides. Nonzero = Full-text catalog ID, associated with the unique index that identifies the rows in a full-text indexed table.0 = Table is not full-text indexed.
TableFullTextChangeTr…	Table	The table has full-text change-tracking enabled. 1 = TRUE0 = FALSE
TableFulltextKeyColumn	Table	The ID of the column associated with the single-column unique index that is participating in the full-text index definition. 0 = Table is not full-text indexed.
TableFullTextPopulate…	Table	0 = No population1 = Full population2 = Incremental population
TableHasActiveFulltext…	Tables	The table has an active full-text index. 1 = True0 = False
TableHasCheckCnst	Table	The table has a CHECK constraint. 1 = True0 = False
TableHasClustIndex	Table	The table has a clustered index. 1 = True0 = False
TableHasDefaultCnst	Table	The table has a DEFAULT constraint. 1 = True0 = False
TableHasDeleteTrigger	Table	The table has a DELETE trigger. 1 = True0 = False
TableHasForeignKey	Table	The table has a FOREIGN KEY constraint. 1 = True0 = False
TableHasForeignRef	Table	Table is referenced by a FOREIGN KEY constraint. 1 = True0 = False
TableHasIdentity	Table	The table has an identity column. 1 = True0 = False
TableHasIndex	Table	The table has an index of any type. 1 = True0 = False
TableHasInsertTrigger	Table	The object has an Insert trigger. 1 = True0 = FalseNULL = Invalid input.
TableHasNonclustIndex	Table	The table has a nonclustered index. 1 = True0 = False
TableHasPrimaryKey	Table	The table has a primary key. 1 = True0 = False
TableHasRowGuidCol	Table	The table has a ROWGUIDCOL for a uniqueidentifier column. 1 = True0 = False
TableHasTextImage	Table	The table has a text column. 1 = True0 = False
TableHasTimestamp	Table	The table has a timestamp column. 1 = True0 = False
TableHasUniqueCnst	Table	The table has a UNIQUE constraint. 1 = True0 = False
TableHasUpdateTrigger	Table	The object has an Update trigger. 1 = True0 = False
TableInsertTrigger	Table	The table has an INSERT trigger. >1 = ID of first trigger with given type.
TableInsertTriggerCount	Table	The table has the specified number of INSERT triggers. >1 = ID of first trigger with given type.
TableIsFake	Table	The table is not real. It is materialized internally on demand by SQL Server. 1 = True0 = False
TableIsPinned	Table	The table is pinned to be held in the data cache. 1 = True0 = False
TableTextInRowLimit	Table	The maximum bytes allowed for text in row, or 0 if text in row option is not set.
TableUpdateTrigger	Table	The table has an UPDATE trigger. >1 = ID of first trigger with given type.
TableUpdateTriggerCount	Table	The table has the specified number of UPDATE triggers. >1 = ID of first trigger with given type.

Nota: Tabela retirada do site MSDN

A propriedade IsIndexable pode consumir significantes recursos de processamento.
A propriedade TableHasActiveFulltextIndex retornará 1 (Verdadeiro) quando no mínimo uma coluna da tabela é adicionada com indíce.

Vamos aos exemplos:

A. Para descobrir se um determinado objeto (Veiculo) é uma VIEW, basta executar a seguinte instrução

IF OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘ISVIEW’) = 1
PRINT ‘Veiculo é uma VIEW’
ELSE
PRINT ‘Veiculo não é uma VIEW’

Observe que utilizei a função OBJECT_ID, esta função retorna a identificação do objeto passado como parâmetro, que deve ser o nome do objeto na base de dados. Foi utilizado esta função para não haver a necessidade de descobrir a identificação do objeto.

Caso a necessidade seja outra, como por exemplo, saber se o objeto é uma tabela, basta substituir a palavra-chave ISVIEW por ISTABLE. Como no exemplo abaixo:

IF OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘ISTABLE’) = 1
PRINT ‘Veiculo é uma TABLE’
ELSE
PRINT ‘Veiculo não é uma TABLE’

B. Saber se uma determinada tabela possui um campo do tipo TEXT, retornará 1 quando for verdadeiro e 0 quando for falso. Uma variável do tipo booleana.

SELECT OBJECTPROPERTY(OBJECT_ID(‘Cliente’), ‘TableHasTextImage’)

C. Saber se uma determinada tabela possui uma chave-primária

SELECT OBJECTPROPERTY(OBJECT_ID(‘Fornecedor’), ‘TableHasPrimaryKey’)

D. Saber se uma determinada tabela possui uma TRIGGER SQL que remove (DELETE) registros

SELECT OBJECTPROPERTY(OBJECT_ID(‘Cliente’), ‘TableDeleteTrigger’)

E. Descobrir qual o proprietário de um determinado objeto, Ex.: PROCEDURE, TRIGGER, TABLE, etc.

SELECT OBJECTPROPERTY(OBJECT_ID(‘Veiculo’), ‘OwnerId’)

Esta função retorna a identificação do proprietário do objeto na base de dados ou NULL caso passado o parâmetro incorretamente.

Finalizo este artigo por aqui, deixe seu comentário, críticas e/ou sugestões.

A diferença entre DATALENGTH e LEN em SQL

A função DATALENGTH tem o objetivo retornar o número de bytes usados para representar uma determinada expressão. Esta função é geralmente utilizada em variáveis do tipo VARCHAR, VARBINARY, TEXT, IMAGE, NVARCHAR e NTEXT que são tipos de dados que podem armazenar grandes quantidades de dados.

Muitos confundem a função DATALENGTH com a função LEN, que são totalmente diferentes, podendo retornar os mesmos valores. A função LEN tem como intuito retornar a quantidade de caracteres existentes em uma determinada expressão.

SELECT

LEN(CPF) AS CPF

FROM

Cliente

A instrução acima mostrará a quantidade de caracteres existentes no campo ‘CPF’ da tabela ‘Cliente’.

SELECT

DATALENGTH(CPF) AS CPF

FROM

Cliente

Já esta outra consulta informa o número de bytes que o campo de um determinado registro está ocupando na base de dados. Quando o campo é NULL, ou seja, nulo em português, significa que o campo não tem valor nenhum e o retorno da função DATALENGTH é nulo enquanto o retorno da função LEN é 0, indicando que não possue nenhum caracter na expressão.

Utilizando a claúsula TOP em SQL

Já expliquei aqui no blog como selecionar uma porcentagem de registros, mas não expliquei como funciona a claúsula TOP sem o PERCENT (indicativo de porcentagem).

O TOP sem o PERCENT retorna os primeiros registros conforme o número especificado após a clausula.

SELECT TOP 50

*

FROM

Cliente

A instrução acima irá informar os primeiros 50 clientes retornados na consulta. Muitos utilizam o TOP quando necessitam retornar o maior / menor registro de uma tabela, criando uma instrução similar a esta com um TOP 1 e ordenando por decrescente / ascendente para se obter o resultado. Há uma outra forma de se obter o mesmo resultado consumindo menos recursos, basta substituir pelo MAX / MIN, mas isto ficará para um próximo artigo.

Usando operadores lógicos em SQL

Toda linguagem de programação possue operadores lógicos, que são elementos pré-definidos. Vou explicar neste artigo como utilizar os principais operados lógicos existentes: AND, OR e NOT.

Os operadores AND e OR são operadores que necessitam de dois elementos para concluir a instrução, já o NOT é do tipo unário, precisando de apenas um elemento.

O operador AND é utilizado sempre quando necessita de que todas (duas ou mais) as condições estabelecidas sejam verdadeiras. Vejamos um exemplo que informa veículos:

SELECT

Fabricante,

Nome,

Modelo,

Ano,

Cilindrada

FROM

Veiculos

WHERE

Fabricante = ‘Chevrolet’

AND

Modelo = 2006

AND

Ano = 2005

Notem que a instrução acima utiliza dois operadores AND, isto faz com que a instrução inteira seja verdadeira, ou não retornará o registro.

Já o operador lógico OR é utilizado quando pelo menos uma das condições estabelecidas forem satisfeitas.

SELECT

Fabricante,

Nome,

Modelo,

Ano,

Cilindrada

FROM

Veiculos

WHERE

Fabricante = ‘Fiat’

OR

Ano = 2005

No caso acima, será retornado os veículos (Veiculos) que foram fabricados no ano de 2005 ou que o fabricante seja ‘Fiat’.

E o por último, o operador NOT, que faz a oposição da condição.

SELECT

Fabricante,

Nome,

Modelo,

Ano,

Cilindrada

FROM

Veiculos

WHERE

NOT

Ano = 2004

Esta instrução exibirá todos os registros que NÃO foram fabricados em 2004.

Unir duas tabelas em SQL

O operador UNION permite a combinação de mais de dois resultados de uma instrução SQL em um único resultado de consulta. Os resultados combinados no operador UNION devem ter a mesma estrutura, ou seja, a mesma quantidade e tipos de campos devem coincidir em todas as consultas.

Segue abaixo a sintaxe da utilização do operador UNION:

INSTRUCAO_SELECT

UNION [ALL]

INSTRUCAO_SELECT

Para demonstrar como se utiliza o operador UNION, segue abaixo uma instrução de criação de uma tabela de clientes referente à pessoas físicas (ClienteFisico) e outra contendo as informações de clientes referente à pessoas jurídicas, com campos diferentes é claro (se não, não haveria a necessidade de criação de uma segunda tabela de diferenciação).

CREATE TABLE dbo.ClienteFisico
(

ID bigint NOT NULL,

Nome varchar(64) NOT NULL,

RG varchar(16) NOT NULL,

Nascimento datetime NOT NULL

) ON [PRIMARY]

CREATE TABLE dbo.ClienteJuridico
(

ID bigint NOT NULL,

NomeFantasia varchar(64) NOT NULL,

CNPJ bigint NOT NULL,

IE varchar(16) NOT NULL

) ON [PRIMARY]

Após a criação das tabelas acima pode-se unir ambas em um único resultado de consulta.

SELECT

ID,

Nome

FROM

ClienteFisico

UNION

SELECT

ID,

NomeFantasia

FROM

ClienteJuridico

A consulta acima irá retornar todos os clientes físicos e jurídicos existentes nas respectivas tabelas (ClienteFisico e ClienteJuridico)

SELECT

ID,

Nome,

RG

FROM

ClienteFisico

UNION

SELECT

ID,

NomeFantasia,

CNPJ

FROM

ClienteJuridico

Esta consulta não irá funcionar, ocorrerá um erro, porque os tipos não correspondem. O campo RG é do tipo VARCHAR que contém até 16 caracteres enquanto o campo CNPJ é do tipo CHAR que contém 14 caracteres. Este problema é fácil de ser resolvido, basta converter o resultado de um dos campos para que os dois tenham o mesmo tipo.

SELECT

ID,

Nome,

RG

FROM

ClienteFisico

UNION

SELECT

ID,

NomeFantasia,

CONVERT(VARCHAR(16), CNPJ) AS CNPJ

FROM

ClienteJuridico

Acima é demonstrado a conversão feita no campo CNPJ atribuindo um alias (codinome) ao campo, caso não seja feito, irá mostrar o campo sem nome no título do resultado.

Existe um operador que pode ser utilizado com o UNION, o ALL. A consulta com o operador ALL indica que NÃO será eliminado nenhuma duplicidade que ocorrer entre as tabelas, ou seja, mostrará tudo que existir nas tabelas que estão sendo unidas com resultado. Se omitir o ALL, como padrão, o SQL remove linhas dos resultados que duplicaram.

SELECT

ID,

Nome

FROM

ClienteFisico

UNION ALL

SELECT

ID,

NomeFantasia

FROM

ClienteJuridico

É claro que não irá servir para muita coisa o operador ALL na consulta acima, porque não existe nenhum cliente físico que possua o mesmo nome de um cliente jurídico (pelo menos que eu conheça).

Formatando datas em SQL

Geralmente é preciso formatar a data que é retornada em uma instrução SQL, de uma maneira que poderia gerar uma lógica um pouco complicada e complexa. Com este intuito, a linguagem SQL possui uma função (e muitas outras) de formatação de datas.

Uma consulta que achei muito útil é a de poder capturar o dia do ano. Veja a instrução a seguir:

SELECT

DATEPART(dy, GETDATE()) AS ‘Dia do Ano’

Existe outro tipo de formatação que retorna o quadrimestre da data que for passada como parâmetro, observe:

SELECT

DATEPART(qq, ‘2007-10-19’) AS ‘Quadrimestre do Ano’

Passando esta data como parâmetro irá mostrar 4.

SELECT

DATEPART(dw, GETDATE()) AS ‘Dia da Seman’

O exemplo acima irá retornar o dia da semana correspondente de uma determinada data.

Possuem outros tipos de formatação e que podem ser úteis algum dia.

Usando REPLICATE em SQL

Hoje irei demonstrar como se utiliza a instrução REPLICATE no SQL. Como o próprio nome diz é uma replicação de algo, ou seja, copia o conteúdo de um determinado campo para outro. O propósito desta instrução é permitir que não haja a necessidade do desenvolvedor fazer outro filtro de um mesmo campo em uma consulta, consumindo recursos adicionais de hardware.

SELECT

REPLICATE(Nome, 2)

FROM

Cliente

ORDER BY

Nome

A instrução acima irá retornar todos os nomes dos clientes existentes na tabela ‘Cliente’ e duplicá-los no mesmo campo.

Vocês devem estar pensando, qual a finalidade disto? É realmente não achei muito útil até observar um exemplo. Observe a seguinte situação, tem um cliente que possui um telefone com um determinado número e um fax (eu sei, quase ninguém utiliza mais isso) com o mesmo número do telefone, pronto, é replicado a informação do telefone para o fax.

SELECT

Nome,

Telefone AS Telefone,

REPLICATE(Telefone, 1) AS Fax

FROM

Cliente

ORDER BY

Nome

Como podem ver a instrução acima copia o conteúdo do ‘Telefone’ e atribui ao ‘Fax’.

Como descobrir a quantidade máxima de conexões simultâneas no SQL

Atualmente tenho pesquisado em como descobrir a quantidade de conexões simultâneas permitidas pelo servidor que está localizado o MS SQL Server.

A sintaxe abaixo é muito simples e retorna quantidade de conexões simultâneas permitidas (a quantidade retornada não necessáriamente corresponde a quantidade de acessos atuais).

SELECT @@MAX_CONNECTIONS

Quando fiz o teste o resultado foi 32767, andei pesquisando sobre isto e acho que deve ser a configuração padrão do SQL Server.

Até a próxima.