[MS SQL Server] - UPDATE com incremento

O script abaixo realiza uma tarefa interessante. Através dele é possível atualizar as linhas de uma tabela (ou um conjunto de linhas, caso um filtro seja aplicado) com valores incrementais (o valor inicial e o incremento podem ser definidos).

-- Criando tabela temporária para testes
CREATE TABLE #tmp (id int primary key identity(1,1), counter int default 0)
GO

-- Inserindo dados na tabela

INSERT INTO #tmp DEFAULT VALUES
GO 10

-- Veja como está a tabela

SELECT * FROM #tmp

-- PREENCHENDO O CAMPO counter  COM VALORES INCREMENTAIS

DECLARE @counter int
SET @counter = 0

UPDATE #tmp
SET @counter = counter = @counter + 1
-- Veja novamente a tabela com o campo counter preenchido
SELECT * FROM #tmp

Este script pode ser utilizado para popular qualquer campo que apresente um caráter sequencial, independente do incremento a ser utilizado.

Através dele, pode-se evitar o uso de Cursores e laços While ao fazer atualizações grandes em tabelas. 

Referencia:

http://www.sqlserverdicas.com/2011/11/update-com-incremento.html

[MS SQL Server] - Exclusão de colunas com valor default

Ao excluir uma coluna com valor default atribuído em uma tabela, o seguinte erro é disparado pelo SQL Server:

The object 'DF__Contato__ativo__6166761E' is dependent on column 'Ativo'.

Isso acontece porque antes de excluir uma coluna de uma tabela, é necessário certificar-se que ela não é referenciada por nenhuma chave ou restrição e também não tenha um valor default associado a ela.

Para resolver este problema, pode-se utilizar a stored procedure de sistema sp_unbindefault . Veja o exemplo abaixo:

-- Criando o valor default para o banco de dados
CREATE DEFAULT DF_Ativo AS 0
GO

-- Criando tabela sem o valor default associado
CREATE TABLE Contato
(
id int identity primary key
,nome varchar(50) not null
,ativo bit
)

-- Associando o valor default à coluna Ativo da tabela
EXEC sp_bindefault 'DF_Ativo','Contato.ativo'

-- Removendo o valor default da coluna Ativo
EXEC sp_unbindefault 'Contato.Ativo'

-- Excluindo coluna Ativo
ALTER TABLE Contato
DROP COLUMN Ativo

-- Verificando se a coluna foi excluída
SELECT * FROM Contato

O código acima retorna as seguintes mensagens:

Default bound to column.
Default unbound from table column.

E o comando SELECT retorna o seguinte resultado:

id nome
----------- --------------------------------------------------

Entretanto, esta procedure funciona apenas se o valor default foi associado através da stored procedure sp_bindefault. Quando um valor default é atribuído durante a criação da tabela ou através do comando ALTER TABLE, a procedure sp_unbindefault retorna uma mensagem de erro como abaixo:

Cannot unbind from 'Contato.Ativo'. Use ALTER TABLE DROP CONSTRAINT.

Seria fácil executar o comando ALTER se soubéssemos o nome da restrição gerada automaticamente pelo SQL Server.

Assim, para facilitar esta tarefa implementei o script abaixo. Ele remove a restrição de default utilizando apenas o nome da tabela e o da coluna. 
Para utilizá-lo, basta configurar as variáveis @nomeTabela e @nomeColuna, conforme indicado:

DECLARE @nomeTabela VARCHAR(50)
DECLARE @nomeColuna VARCHAR(50)

/* Configure aqui */

   SET @nomeTabela = 'Contato'
   SET @nomeColuna = 'Ativo'

/* Fim das configurações */

DECLARE @command NVARCHAR(1000)

SELECT
    @command='ALTER TABLE '+@NomeTabela+' DROP CONSTRAINT '+D.name
FROM
    sysobjects D
        inner join sysobjects T on
           T.id = D.parent_obj
        inner join syscolumns C on
           C.id = T.id AND cdefault = D.id
WHERE
    T.name = @nomeTabela
    AND C.name = @NomeColuna
    AND D.xtype = 'D'

exec sp_executesql @command

Este script utiliza as views de sistema sysobjects e syscolumns para encontrar o nome da restrição que atribui o valor default à coluna. Com este valor, o comando de exclusão da restrição é gerado e executado.

Referencia:
http://www.sqlserverdicas.com/2010/08/em-bancos-de-dados-sql-server-as.html

[MS SQL Server] - Conflitos de COLLATION em operações de comparação

Em bancos de dados SQL Server, as Collations são utilizadas para definir a maneira como os dados do tipo string (nchar, nvarchar, e ntext) são armazenados.

Em resumo, uma collation define:

• A codificação (Character Set) utilizada para armazenar os caracteres não-Unicode.
• O algoritmo de ordenação utilizado para ordenar o retorno das consultas

O SQL Server permite que seja configurada uma collation default para o servidor,atribuindo-a a todos os campos cuja collation não é explicitamente definida durante a criação.

Além disso, as collations podem ser definidas em diferentes granularidades. É possível definir collations para bancos de dados, tabelas, campos de tabelas e variáveis do SQL Server.

Entretanto, o uso de diversas collations em uma mesma base de dados pode causar problemas ao desenvolvedor, pois algumas collations não são compatíveis entre si. Um problema comum ao escrever consultas envolvendo comparação de campos de texto com collations diferentes é o confilto nessa operação de igualdade.

"Cannot resolve collation conflict for equal to operation"

Uma solução para este problema é indicar ao processador de consultas qual collation deverá ser utilizada para realizar a comparação entre os dois valores. Para isso, deve-se utilizar o comandoCOLLATE. Além disso, pode-se passar como argumento a palavra-chave DATABASE_DEFAULT, que retorna a collation definida para o banco de dados em uso. Veja o exemplo abaixo:

SELECT userName FROM Usuarios
INNER JOIN Clientes ON 
Clientes.userName COLLATE DATABASE_DEFAULT
= Usuarios.userName  COLLATE DATABASE_DEFAULT

No exemplo, foi feita a junção entre as tabelas "Usuarios" e "Clientes", utilizando-se o campo userName, presente em ambas as tabelas. Supõe-se que este campo possua collations diferentes e conflitantes nestas tabelas. O comando COLLATE resolve o conflito na operação de igualdade do JOIN, utilizando a collation padrão do banco.

Referências:
Comando COLLATE (MSDN)

http://www.sqlserverdicas.com/2010/08/em-bancos-de-dados-sql-server-as.html

[MS SQL Server] - Executando várias vezes um mesmo script

Em certas ocasiões, é necessário executar várias vezes uma mesma sequência de comandos no SQL Server (batch). Essa tarefa pode ser muito trabalhosa quando o número de repetições atinge grandes valores.

Se os comandos forem sempre os mesmos e não houver mudança de parâmetros, pode-se utilizar o comando GO seguido pelo número de vezes que o comando será executado, respeitando a sintaxe abaixo:

GO <número_de_execuções>

O comando GO não é um comando do SQL Server, mas sim uma instrução interpretada pelas interfaces de gerenciamento do SQL Server (SQL Server Management Studio, sqlcmd e osql).

Esse comando pode ser especialmente útil para popular bases de teste.

Exemplo

No exemplo abaixo, criamos uma tabela com um campo inteiro e inserimos 15 registros com valores aleatórios:

CREATE TABLE #tblExemplo (number DECIMAL)
GO

INSERT INTO #tblExemplo VALUES(RAND() * 100)
GO 15

SELECT * FROM #tblExemplo

DROP TABLE #tblExemplo
GO

Pode-se observar o resultado do script na imagem abaixo:

Referências 
GO (Transact-SQL)

[MS SQL Server] - Cursores - exemplo básico de utilização

Cursores são estruturas da linguagem T-SQL que permitem o processamento das linhas retornadas por uma consulta (SELECT), através de estruturas complexas de programação, como repetições ou  comandos condicionais. 

O exemplo básico de cursor consiste em uma repetição (loop) onde um mesmo conjunto de comandos é executado para todas as linhas do retorno de uma consulta. 
No exemplo abaixo, utiliza-se um cursor para executar a chamada da procedure sp_helptext para cada procedure de um banco de dados. As procedures foram obtidas através de uma consulta na viewROUTINES do Schema INFORMATION SCHEMA. 

DECLARE @schemaName VARCHAR(30)
    , @procName VARCHAR(30)
    , @fullName VARCHAR(60)

-- Cursor para percorrer os nomes dos objetos
DECLARE cursor_objects CURSOR FOR
    SELECT
          ROUTINE_SCHEMA
        , ROUTINE_NAME
    FROM
        INFORMATION_SCHEMA.ROUTINES
    WHERE
        ROUTINE_TYPE = 'PROCEDURE'

-- Abrindo Cursor para leitura
OPEN cursor_objects

-- Lendo a próxima linha
FETCH NEXT FROM cursor_objects INTO @schemaName, @procName

-- Percorrendo linhas do cursor (enquanto houverem)
WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @fullName = @schemaName + '.' + @procName

    EXEC sp_helptext @fullName

    -- Lendo a próxima linha
    FETCH NEXT FROM cursor_objects INTO @schemaName, @procName
END

-- Fechando Cursor para leitura
CLOSE cursor_objects

-- Desalocando o cursor
DEALLOCATE cursor_objects 

Algumas considerações:

• Um cursor deve estar sempre associado a uma consulta, especificada ao declarar o cursor.

• O comando FETCH popula as variáveis recebidas como parâmetro com os valores da próxima linha da consulta a ser lida. O número de variáveis passadas como parâmetro deve ser igual ao número de colunas retornadas na consulta associada ao cursor.

• A variável global @@FETCH_STATUS retorna o resultado da última operação FETCH executada por um cursor na conexão.
  O status 0 significa que o comando FETCH retornou uma linha, qualquer outro resultado significa que não houve linha retornada.

• Cursores são estruturas relativamente lentas se comparadas ao desempenho de consultas do banco. O uso descuidado dessa ferramenta pode causar sérios problemas de performance.
  

Referencia:

http://www.sqlserverdicas.com/2010/12/cursores-exemplo-basico-de-utilizacao.html

[MS SQL Server] - Inserindo o resultado de uma consulta em uma tabela já existente

No artigo anterior, verificamos como inserir os resultados de uma consulta em uma nova tabela utilizando a cláusula INTO do comando SELECT. Agora, vamos explorar como inserir o resultado de uma consulta em uma tabela já existente.

O comando INSERT possui uma sintaxe alternativa que auxilia a execução desta tarefa. Veja abaixo: 

INSERT INTO tabelaDestino

SELECT campo1, campo2, ... , campoN
FROM tabelaOrigem
WHERE condicionais

Utilizando a tabela Funcionarios do artigo anterior, criaremos 

CREATE TABLE Funcionarios
(
    idFuncionario INT IDENTITY (1,1) PRIMARY KEY
    , nome VARCHAR(100) NOT NULL
    , dataAdmissao DATETIME NOT NULL
    , dataDemissao DATETIME NULL
)

-- Inserindo dados fictícios para testes
INSERT INTO Funcionarios VALUES
    ('Evandro', '2001-07-03', NULL)
    ,('José','2002-02-09',NULL)
    ,('Ronaldo','1995-09-25','2001-05-03')
    ,('Márcia','2003-03-22','2004-06-04')
    ,('Régis','1997-09-25','2004-08-07')
    ,('Renata','2008-02-27',NULL)
    ,('Mariana','2009-01-15',NULL)

Em seguida, criaremos a tabela para armazenar os funcionários demitidos (FuncionariosDemitidos), com a mesma estrutura da tabela Funcionarios

-- Criando a tabela de Funcionários demitidos com a mesma estrutura original
CREATE TABLE FuncionariosDemitidos
(
    idFuncionario INT 
    , nome VARCHAR(100) NOT NULL
    , dataAdmissao DATETIME NOT NULL
    , dataDemissao DATETIME NULL
)

Para preencher a nova tabela, basta utilizar o comando INSERT com a sintaxe indicada:

-- Inserindo os dados dos funcionários demitidos
INSERT INTO FuncionariosDemitidos      
    SELECT *
    FROM Funcionarios
    WHERE
        dataDemissao is not null

Algumas consideraçõe sobre este procedimento:

• Ao contrário do comando SELECT com a cláusula INTO, o comando INSERT não cria a tabela tabelaDestino. Ou seja, a tabela tabelaDestino precisa obrigatoriamente existir.
• Evite utilizar o caracter curinga * para definir os campos a serem inseridos na tabelatabelaOrigem, principalmente quando esta possuir campos com auto-numeração (IDENTITY)

Para maiores informações sobre o comando INSERT, consulte no MSDN:
INSERT (T-SQL)
http://www.sqlserverdicas.com/2011/01/inserindo-o-resultado-de-uma-consulta_25.html

[MS SQL Server] - Alterando o Schema de uma tabela

O uso de schemas é uma boa prática para organizar a estrutura e as permissões de um banco de dados SQL Server.

Todos os objetos pertencentes a um Schema herdam suas permissões (desde que não existam permissões explícitas para cada objeto). Além disso, cada objeto pertence a um, e somente um, Schema.

Apesar do schema fazer parte da identificação de todo objeto, o simples renomeio de uma tavela  não possibilita associá-la a outro Schema.

Para realizar esta tarefa, deve-se utilizar a cláusula TRANSFER do comando ALTER SCHEMA.

Veja a sintaxe abaixo:

ALTER SCHEMA nome_do_schema TRANSFER outro_schema.nome_da_tabela; 

Note que o uso desta cláusula permite que a tabela nome_data_tabela seja "importada" para o Schema nome_do_schema.

No exemplo abaixo, 2 Schemas são criados (Marketing e RecursosHumanos). Em seguida, cria-se a a tabela Funcionario no Schema Marketing. Suponhamos que esta seja uma associação equivocada, e que a tabela Funcionario deva pertencer ao Schema RecursosHumanos.

-- Criando Schema para a área de Marketing
CREATE SCHEMA Marketing
GO

-- Criando Schema para a área de RecursosHumanos
CREATE SCHEMA RecursosHumanos
GO

-- Criando tabela Funcionario no Schema Marketing
CREATE TABLE Marketing.Funcionario
(
id int identity(1,1) PRIMARY KEY,
nome VARCHAR(100)
)
GO

/* Alterando o Schema da tabela Funcionario. 
(DE: Marketing PARA: RecursosHumanos) */
ALTER SCHEMA RecursosHumanos TRANSFER Marketing.Funcionario
GO

Observações:

• Tome cuidado ao alterar nomes ou schemas de objetos de um banco de dados. Tenha sempre em mente que este objeto pode ser referenciado por aplicações ou mesmo por outros objetos existente no banco de dados. Caso isso aconteça, estas referências serão inválidas.

[MS SQL Server] - Desabilitando restrições ao inserir muitos registros

O uso de restrições (CONSTRAINTS) nas tabelas de um banco de dados é extremamente recomendável para garantir a integridade dos dados armazenados.

Entretanto, quando os registros são inseridos ou alterados em tabelas que possuam contraints, o SQL Server precisará validar todas as constraints para cada cada alteração realizada, o que diminui a performance destas operações.

Em situações comuns de uso do banco de dados, esse impacto não é percebido, principalmente se o banco de dados foi implementado seguindo as boas práticas. Por outro lado, em uma carga de dados, ou em qualquer situação onde seja necessário realizar muitas inserções ou alterações de registros, essa verificação adicional pode impactar severamente na perfomance destas operações.

Para contornar esta situação, é possível desabilitar a verificação de constraints, desde que se tenha a garantia de que os dados a serem inseridos/atualizados estão em conformidade com as restrições do banco.

A cláusula NOCHECK do comando ALTER TABLE desabilita estas verificações.

Sintaxe

ALTER TABLE NomeDaTabela
       NOCHECK CONSTRAINT NomeDaConstraint

Exemplo

ALTER TABLE Cliente
       NOCHECK CONSTRAINT fk_cliente_x_empresa

ReferênciaALTER TABLE (Transact-SQL)

http://www.sqlserverdicas.com/2010/08/desabilitando-restricoes-ao-inserir.html

[MS SQL Server] - Inserindo o resultado de uma consulta em uma nova tabela

Esta é uma tarefa simples, muito utilizada para fazer backup de tabelas ou extrair dados segundo um filtro.

Se a tabela ainda não existir, pode-se utilizar a cláusula INTO do comando SELECT para inserir os resultados em uma nova tabela com as mesmas colunas definidas no retorno do comando SELECT.

Para exemplificar, criaremos uma tabela simples para simular o cadastro de funcionários de uma empresa:

CREATE TABLE Funcionarios
(
    idFuncionario INT IDENTITY (1,1) PRIMARY KEY
    , nome VARCHAR(100) NOT NULL
    , dataAdmissao DATETIME NOT NULL
    , dataDemissao DATETIME NULL
)

-- Inserindo dados fictícios para testes
INSERT INTO Funcionarios VALUES
    ('Evandro', '2001-07-03', NULL)
    ,('José','2002-02-09',NULL)
    ,('Ronaldo','1995-09-25','2001-05-03')
    ,('Márcia','2003-03-22','2004-06-04')
    ,('Régis','1997-09-25','2004-08-07')
    ,('Renata','2008-02-27',NULL)
    ,('Mariana','2009-01-15',NULL)

Veja abaixo como o comando SELECT (com a cláusula INTO) pode ser utilizado para extrair os funcionários demitidos de uma empresa (Data de demissão preenchida) e armazená-los em uma nova tabela FuncionariosDemitidos: 

-- Criando tabela com os funcionários demitidos
SELECT * 
INTO FuncionariosDemitidos 
FROM Funcionarios
WHERE 
    dataDemissao is not null 

Observe o resultado de uma consulta sobre a nova tabela FuncionariosDemitidos:

 

Observe que apenas as linhas com a coluna DataDemissao preenchida foram inseridas na nova tabela.

Além disso, o nome dos campos criados na nova tabela são idênticos aos da tabela original.

Para maiores sobre a cláusula INTO do comando SELECT, consulte no MSDN: 
INTO Clause (Transact-SQL)

Referencia:

http://www.sqlserverdicas.com/2011/01/inserindo-o-resultado-de-uma-consulta.html

[ORACLE] - Formatação de Datas em texto no Oracle

Bom pessoal, segue abaixo formatação de datas para exemplo com os links para pesquisa quando necessário:

SELECT to_char(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') AS data_e_hora_inteira,
       to_char(SYSDATE, 'HH24:MI:SS') AS hora_inteira,
       to_char(SYSDATE, 'HH') AS hora_h12,
       to_char(SYSDATE, 'WW') AS SEMANA,
       to_char(SYSDATE, 'W') AS SEMANA1,
       to_char(SYSDATE, 'IW') AS SEMANA2,
       to_char(SYSDATE, 'Day', 'nls_language =''BRAZILIAN PORTUGUESE''') AS nome_dia,
       to_char(SYSDATE, 'Month', 'nls_language =''BRAZILIAN PORTUGUESE''') AS nome_mes,
       to_char(SYSDATE, 'YEAR', 'nls_language =''BRAZILIAN PORTUGUESE''') AS nome_ano,
       to_char(SYSDATE, 'DD', 'nls_date_language = PORTUGUESE') AS dia,
       to_char(SYSDATE, 'MM', 'nls_date_language = PORTUGUESE') AS mes,
       to_char(SYSDATE, 'YYYY', 'nls_date_language = PORTUGUESE') AS ano,
       to_char(SYSDATE, 'HH24') AS hora_h24,
       to_char(SYSDATE, 'MI') AS minuto,
       to_char(SYSDATE, 'SS') AS segundo,
       to_char(SYSDATE,
               ('DAY, dd "de" FMMONTH "de" YYYY'),
               'nls_date_language = PORTUGUESE') AS data_literal,
       to_char(SYSDATE,
               ('DAY, dd "," FMMONTH "," YYYY'),
               'nls_date_language = AMERICAN') AS data_literal_americana,
       to_char(SYSDATE,
               'yyyy-MON-dd, FMDAY',
               'nls_date_language = AMERICAN') data_padrao_americano,
       to_char(SYSDATE,
               'FMDAY , dd/MM/yyyy',
               'nls_date_language = PORTUGUESE') data_padrao_brasil,
       sessiontimezone AS timezone_da_sessao,
       current_date AS data_formato_timezone
FROM   dual;

Tabelas de parâmetros

Parâmetros	Descrição
YEAR	Ano (Ex: dois mil e onze, twenty eleven)
YYYY  YYY  YY  Y	Ano (Ex: 2011)  Ano (Ex: 011)  Ano (Ex: 11)  Ano (Ex: 1)
Q	Quadrimestre (1,2,3,4)
MM	Mês (Ex: 10)
MON	Abreviatura do nome do Mês (Ex: OUT)
MONTH	Nome do Mês (Ex: Outubro)
RM	Mês em números romanos (Ex: X)
WW	Semana do Ano de 1 a 53
W	Semana do mês de 1 a 5
D	Dia da semana de 1 a 7 (1 = Domingo até 7=Sábado)
DAY	Nome do dia da semana (Ex: Sabádo)
DD	Dia do Mês de 1 a 31
DDD	Dia do Ano de 1 a 366
DY	Abreviatura do dia da semana (Ex: SÁB)
HH	Hora de 1 a 12
HH12	Hora de 1 a 12
HH24	Hora de 1 a 24
MI	Minutos
SS	Segundos
SSSSS	Milésimos

Fonte:

http://ss64.com/ora/syntax-fmt.html

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html