Funções matemáticas do Oracle 10g, do SQL Server 2005, do DB2 9 e do PostgreSQL 8.0.0

Função	Oracle 10g [a] [b]	SQL Server 2005 [c]	DB2 9.1 [d]	PostgreSQL 8.0.0
valor absoluto	abs(n)	abs(n)	abs(n) ou absval(n)	abs(x)
arco cosseno	acos(n)	acos(f)	acos(d)	acos(x)
arco seno	asin(n)	asin(f)	asin(d)	asin(x)
arco tangente de n	atan(n)	atan(f)	atan(d)	atan(x)
arco tangente de x/y	atan2(n, m)	atn2(f, f)	atan2(d, d)	atan2(x, y)
raiz cúbica	-	-	-	cbrt(dp)
menor inteiro não menor que o argumento	ceil(n)	ceiling(n)	ceil(n) ou ceiling(n)	ceil(dp ou numeric)
cosseno	cos(n)	cos(f)	cos(d)	cos(x)
cosseno hiperbólico	cosh(n)	-	cosh(d)	-
cotangente	-	cot(f)	cot(d)	cot(x)
radianos para graus	-	degrees(n)	degrees(d)	degrees(dp)
exponenciação	exp(n)	exp(f)	exp(d)	exp(dp ou numeric)
maior inteiro não maior que o argumento	floor(n)	floor(n)	floor(n)	floor(dp ou numeric)
logaritmo natural	ln(n)	log(f)	ln(d) ou log(d)	ln(dp ou numeric)
logaritmo, qualquer base	log(m, n)	-	-	log(b numeric, x numeric)
logaritmo, base 10	log(10, n)	log10(f)	log10(d)	log(dp ou numeric)
módulo (resto)	mod(m, n)	dividendo % divisor	mod(n, n)	mod(y, x)
constante π	-	pi()	radians(180)	pi()
potenciação	power(m, n)	power(f, y)	power(n, n)	pow(a dp, b dp) e pow(a numeric, b numeric)
graus para radianos	-	radians(n)	radians(d)	radians(dp)
número randômico	-	rand()	rand()	random()
arredondar para o inteiro mais próximo	round(n)	round(n,0)	round(n,0)	round(dp ou numeric)
arredondar para s casas decimais	round(n [,s integer])	round(n, s integer [,função])	round(n, s integer)	round(v numeric, s integer)
define a semente para as próximas chamadas a random()	-	rand(semente)	rand(semente)	setseed(dp)
sinal do argumento (-1, 0, +1)	sign(n)	sign(n)	sign(n)	sign(dp ou numeric)
seno	sin(n)	sin(f)	sin(d)	sin(x)
seno hiperbólico	sinh(n)	-	sinh(d)	-
raiz quadrada	sqrt(n)	sqrt(f)	sqrt(d)	sqrt(dp ou numeric)
tangente	tan(n)	tan(f)	tan(d)	tan(x)
tangente hiperbólica	tanh(n)	-	tanh(d)	-
trunca em direção ao zero	trunc(n)	-	trunc(n, 0)	trunc(dp ou numeric)
trunca com s casas decimais	trunc(n [,s integer])	-	trunc(n ,s integer)	trunc(v numeric, s integer)
Notas: a. Oracle 10g — As funções numéricas recebem entradas numéricas e retornam valores numéricos. A maior parte destas funções retornam valores com precisão de 38 dígitos decimais. As funções transcendentais cos, cosh, exp, ln, log, sin, sinh, sqrt, tan e tanh têm precisão de 36 dígitos decimais. As funções transcendentais acos, asin, atan e atan2 têm precisão de 30 dígitos decimais.Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 (N. do T.) b. Função transcendental — Em matemática, uma função que não pode ser expressa como uma combinação finita de operações algébricas de adição, subtração, multiplicação, divisão, elevar a uma potência ou extrair uma raiz. Os exemplos incluem as funções log x, sin x, cos x, ex e qualquer função contendo uma destas. Estas funções são expressas em termos algébricos apenas como séries infinitas. Em geral, o termo transcendental significa não-algébrico. transcendental function — Britannica Concise Encyclopedia c. SQL Server 2005 — As funções abs, ceiling, degrees, floor, power e radians retornam um valor que possui o mesmo tipo de dado da expressão numérica da entrada. As funções acos, asin, atan,cos, cot, exp, log, log10, sin, sqrt, square e tan recebem como argumento uma expressão de ponto flutuante, e retornam um valor de ponto flutuante. Todas as funções matemáticas, exceto rand, são funções determinísticas; retornam o mesmo resultado toda vez que são chamadas com o mesmo conjunto de valores de entrada. rand só é determinística quando é especificado o parâmetro semente. SQL Server 2005 Books Online — Mathematical Functions (Transact-SQL) d. DB2 9.1 — DB2 Version 9 for Linux, UNIX, and Windows — Supported functions and administrative SQL routines and views

desabilitar CONSTRAINT tabelas

SELECT ‘ALTER TABLE ‘ + TABLE_NAME + ‘ NOCHECK CONSTRAINT ALL’ FROM INFORMATION_SCHEMA.TABLES

SELECT ‘ALTER TABLE ‘ + TABLE_NAME + ‘ CHECK CONSTRAINT ALL’ FROM INFORMATION_SCHEMA.TABLES

Em tempo: podemo usar também essa SP:
CREATE PROCEDURE DeleteAllData
AS
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
EXEC sp_MSFOREACHTABLE ‘SELECT * FROM ?’
GO

Alterar o collate do SQL Server

ALTER DATABASE [base] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [base] COLLATE Latin1_General_CI_AS
ALTER DATABASE [base] SET MULTI_USER

Problemas com criação de um Diagrama (database diagrams) no sql2005

 

erro = “Database diagram support objects cannot be installed because this database does not have a valid owner”

No SQL Server Management Studio verifique:

 

1. Right Click no seu database, vá em “properties”

2. Acesse a opção “Options” em “Select a page”

3. Veja se a combo “Compatibility Level” está em “SQL Server 2005(90)”

 

Se tudo estiver correto mude sua autorização para “sa” com o comando abaixo:

 
ALTER AUTHORIZATION ON DATABASE::[Nome do Database] TO sa

Fazendo um SELECT RANDÔMICO

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Thanks Tim

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Funções Do Sql Server

Funções de agrupamento:

AVG() = Retorna o valor médio de uma coluna específica
BINARY_CHECKSUM() = O valor do BINARY_CHECKSUM computado sobre uma linha ou uma tabela ou sobre uma lista de expressões. BINARY CHECKSUM é usada para detectar alterações em uma linha ou uma tabela. 
CHECKSUM() = O valor de CHECKSUM computado sobre uma linha ou uma tabela, ou sobre uma lista de expressões. CHECKSUM é usada para construir índices de hash. 
CHECKSUM_AGG() = O valor de CHECKSUM de um grupo. Valores nulos são ignorados.
COUNT() = Retorna o número de linhas 
COUNT_BIG() = igual ao COUNT mas o COUNT_BIG sempre retorna um tipo de dados bigint.
MAX() = Retorna o valor máximo de uma coluna específica
MIN() = Retorna o valor mínimo de uma coluna específica
SUM() = Retorna a soma de uma coluna específica
STDEV() = Desvio padrão de todos os valores
STDEVP() = Desvio padrão da população
VAR() = Variância estatistica de todos os valores
VARP() = Variância estatística de todos os valores da população

Funções de manipulação de strings:

ASCII(string) = pega o valor em ASCII da string
CHAR(integer) = troca inteiro do ASCII em um caráter 
LEN(string) = Identifica o comprimento de uma expressão em caracteres 
LOWER(string) = converte uma string uppercase para lowercase.
LTRIM(string) = remove os espaços em branco
PATINDEX(posicao, expressao) = devolve a posicão de uma string dentro de um texto. Se não encontrar, retorna zero.
REPLICATE(string, integer) = Repete N vezes um caractere especificado
REVERSE(string) = retorna o inverso de uma expressao
RTRIM (string) = remove os espaços em branco à direita de uma string
SPACE(integer) = que retorna o número de espaços em branco informados no parâmetro 
STUFF(string texto, X, Y, string texto_a_inserir) = apaga da string “texto” os y caracteres a partir da posição x e os substitui por “texto_a_inserir”
SUBSTRING(string texto, posicao_inicial, tamanho) = retorna uma string com o comprimento definido em “tamanho” extraida da string “texto”, a partir da “posicao_inicial”
UPPER(string) = retorna string em maiusculas

Funções de manipulação de data/hora:

nomes das partes de data:

Year = yy, yyyy
Quarter = qualquer, que
Month = mm, m
Dayofyear = dy, y
Day = dd, d
Week = wk, ww
Hour = hh
Minute = mi, n
Second = ss, s
Millisecond = ms

DATEADD (parte, numero, data) = adiciona um valor a parte de uma data
DATEDIFF (parte, data inicial, data final) = subtrai a data inicial da data final, indicando o resultado na unidade definida em “parte”
GETDATE() = retorna a data atual do sistema
DATENAME (parte, data) = retorna o nome da parte de uma data
DATEPART(parte, data) = retorna a parte de uma data

Funções de Sistema:

CAST(expressao as datatype) = converte uma expressao no datatype informado
COL_LENGTH(nome_da_tabela, nome_da_coluna) = retorna o tamanho da coluna
COL_NAME(id_da_tabela, id_da_coluna) = retorna o nome da coluna
DATALENGTH(expressao) = retorna o numero de bytes usados para armazenar a expressao
DB_ID(nome_do_banco) = retorna o ID do banco informado
DB_NAME(id_do_banco) = retorna o nome do banco
HOST_ID() = retorna a ID da estação que está acessando o SQL Server
HOST_NAME() = retorna o nome da estação que está acessando o SQL Server
IDENT_INCR(nome_da_tabela_ou_view) = retorna o valor incrementado
IDENT_SEED(tabela_ou_view) = retorna o valor inicial da coluna
INDEX_COL(nome_da_tabela, indice_id, chave_id) = retorna o nome da coluna que participa do índice
ISNULL(expressao, valor) = se a expressao for null, troca pelo valor especificado
ISNUMERIC(expressao) = retorna 1 se a expressao for numerica e 0 se não for
NEWID() = retorna um novo valor do tipo uniqueidentifier
NULLIF(expressao_1, expressao_2) = retorna nulo se as duas expressoes forem equivalentes. Se não forem, retorna a primeira expressao.
OBJECT_ID(nome_do_objeto) = retorna o ID de um objeto, a partir do nome fornecido
OBJECT_NAME(ID_do_objeto) = retorna o nome do objeto, a partir do ID fornecido
PARSENAME(objeto, parte) = retorna a parte do nome de um objeto, desde que tenha sido qualificado 
STATS_DATE(tabela_id, indice_id) = retorna a data em que as estatísticas do índice foram atualizadas
SUSER_SID(nome_do_usuario) = retorna o ID do usuario informado
SUSER_NAME(usuario_id) = retorna o id do usuário no servidor. O argumento é opcional.
SUSER_SNAME(id_do_usuario) = retorna o nome do usuario informado. Se nenhum ID de usuario for passado para a função, retorna o nome do usuario logado
USER_ID(nome_do_usuario) = retorna o ID do usuario informado para o BD em uso
USER_NAME(id_do_usuario) = retorna o usuario conectado ao BD

Funções Matematicas:

ABS(numero) = retorna o valor absoluto do numero
ACOS(float) = retorna o arco-coseno do numero informado
ASIN(float) = retorna o arco-seno do numero informado
ATAN(float) = retorna o arco-tangente do numero informado
ATN2(Float expressao_1, float expressao_2) = Arco-tangente do valor definido pela divisão da primeira expressão pela segunda
CEILING(numero) = retorna o menor inteiro que seja maior ou igual ao numero informado
COS(float) = retorna o coseno do numero informado
COT(float) = retorna o cotangente do numero informado
DEGREES(numero) = converte radianos para graus
EXP(float) = retorna o exponencial de um numero especificado
FLOOR(numero) = retorna o maior inteiro que seja menor ou igual ao numero informado 
LOG(float) = retorna o logaritmo natural do numero informado
LOG10(float) = retorna o logaritmo base 10 do numero informado
PI() = retorna o valor de PI 3.1415926535897931.
POWER(numero, potencia) = retorna o valor elevado à potencia informada
RADIANS(numero) = converte graus para radianos
RAND(expressao) = um número aleatório entre 0 e 1. Expressão é opcional e será usada como semente da cadeia pseudo-aleatória
ROUND(numero, precisao, arredonda_ou_trancar) = arredonda ou tranca o numero fornecido de acordo com a precisao informada. Se o terceiro parametro não for passado para a funçao, o numero é arredondado. Se quiser que o numero seja truncado, deve-se fornecer o valor 1
SIGN(numero) = retorna sinal positivo, negativo ou zero do numero
SIN(float) = retorna o seno do angulo especificado
SQRT(float) = retorna a raiz quadrada de um numero
TAN(float) = retorna a tangente de um numero informado
SQUARE(float) = retorna o quadrado de um numero

Função de conversão:

CONVERT(tipo(tamanho), expressão, estilo) = converte a expressão para o tipo de dado. O tamanho e o estilo são opcionais

SQL SERVER não traz NULL em um INNER JOIN

Se não está retornando nada, relaxe as restrições. Como vc não tem nenhum WHERE, só lhe resta relaxar os JOINs. O tipo de JOIN que você está fazendo não traz os dados caso a tabela filha não tenha a relação com a pai (tenha um NULL aí no meio). Mude os INNER JOIN para LEFT JOIN e verifique se não aparecem NULLs no resultado.

O fato é que se tem NULL num campo usado para fazer INNER JOIN, esse registro não será trazido. O INNER JOIN é uma operação que combina os registros de duas tabelas, sendo que se ele não existir em uma das tabelas (referência NULL), não constará no resultado. 

A mudança para LEFT modifica o JOIN para um OUTER, que adiciona o registro ao resultado mesmo que ele não conste numa das tabelas relacionadas (no caso, usando o LEFT, vai trazer mesmo que não conste na tabela filha).

Variáveis tipo TABLE

Definição

Variável tipo TABLE nada mais é do que um tipo especial de variável que pode ser utilizada para armazenamento temporário de dados, de maneira similar a tabelas temporárias.

Vantagens

·         Devido à sua utilização estritamente local, tabelas criadas a partir de variáveis tipo TABLE não consomem recursos para controle de bloqueios;
·         A manipulação de dados em variáveis tipo TABLE é mais eficiente porque essas operações são minimamente logadas (um ROLLBACK após um INSERT não tem efeito em variáveis tipo TABLE);
·         Em função do seu escopo local, procedures que se utilizam de variáveis tipo TABLE estão sujeitas a um número menor de recompilações quando comparadas às tabelas temporárias.

Desvantagens

·         A vida útil de uma tabela criada a partir de uma variável tipo TABLE está limitada ao batch e/ou procedure onde é utilizada;
·         Com variáveis tipo TABLE não é permitido:
§         Criação de índices não-cluster,
§         Criação de constraints CHECK, DEFAULT;
§         Criação e/ou atualização de estatísticas;
§         Alteração da estrutura da tabela;
§         Uma variável tipo TABLE não pode ser o destino de INSERT EXEC ou SELECT INTO
§         Uma variável tipo TABLE só pode ser referenciada por um comando SP_EXECUTESQL se a variável for criada.

Utilização

Variáveis tipo TABLE são uma ótima opção para armazenamento transitório de dados, em substituição às tabelas temporárias. Exemplos de utilização:

·         Para criar uma variável tipo TABLE

declare @tab TABLE (cod_cliente int, qtde_pedidos int)

·         Manipulando dados numa variável tipo tabela

declare @tab TABLE (cliente varchar(10), qtde_pedidos int)
insert into @tab
   select customerId, qtde_pedidos=count(*)
   from orders
   where customerId like (‘v%’)
   group by customerId
select * from @tab
update @tab set qtde_pedidos = qtde_pedidos + 1
delete from @tab
———————————————————————
(3 row(s) affected)
cliente    qtde_pedidos
———- ————
VAFFE      11
VICTE      10

VINET      5
(3 row(s) affected)
(3 row(s) affected)
(3 row(s) affected)

·         Executando Join com tabela criada à partir de uma variável tipo TABLE

declare @tab TABLE (cliente varchar(10), qtde_pedidos int)
insert into @tab
   select customerId, qtde_pedidos=count(*)
   from orders
   where customerId like (‘v%’)
   group by customerId
select *
from @tab t
inner join
    customers c
on t.cliente = c.customerId
———————————————————————(3 row(s) affected)
cliente    razao_social                            
———- —————————————-
VAFFE      Vaffeljernet
VICTE      Victuailles en stock
VINET      Vins et alcools Chevalier
(3 row(s) affected)

Considerações Finais

Diferentemente do que muitos pensam, variáveis tipo TABLE também consomem recursos do TempDB – na verdade tanto tabelas temporárias quanto variáveis tipo TABLE serão criadas em memória para pequeno volume de dados. O diferencial das tabelas temporárias é o log reduzido, o número baixo de recompilações e o ganho de performance com a ausência do controle de bloqueios.

Conclusão

Variáveis tipo TABLE são uma versão “light” das equivalentes tabelas temporárias e devem ser utilizadas no dia-a-dia para armazenamento temporário de pequenos volumes de dados.

Artigo escrito por: Paulo Ribeiro (psribeiro@hotmail.com) é Microsoft MCDBA e membro da equipe editorial da SQL Magazine.

operadores EXCEPT e INTERSECT – sql2005

O EXCEPT e o INTERSECT são operadores novos no SQL SERVER. Eles nos permitem encontrar registros que são comuns em dois sets de dados (tablelas, views, etc) ou registros em um set que não esteja no outro.

EXCEPT

O Operador EXCEPT pode ser usado como alternativas ao operador AND e ao comando NOT EXISTS.

Exemplo 1

Vamos retornar os produtos que tem um preço unitário superior a 18 com o estoque menor que 20 unidades.

Com AND NOT

Select * from products
where unitprice > 18 AND NOT unitsinStock <20

Com EXCEPT

Select * from products where unitprice > 18
EXCEPT
Select * from products
where unitsinStock < 20

Exemplo 2

Agora, vamos listar o id dos produtos que não tem pedidos feitos.

Com NOT EXISTS

select prod.productid from products as prod
where not exists
(select ord.productid from [order Details] as ORD
         where ord.productid = prod.productid )

Com EXCEPT

select prod.productid from products as prod
EXCEPT
select ord.productid from [order Details] as ORD

INTERSECT

O operador INTERSECT é muito similar ao INNER JOIN.

Suponha-se que temos a tabela Orders e uma outra tabela similar chamada ImportedOrders, e queremos saber quais os pedidos da tabela ImportedOrders estão duplicados na tabela Orders.

Com INNER JOIN

Select ord.orderid, ord.customerid from orders as ord
inner join importedOrders as iord on ord.orderid = iord.orderid and ord.customerid = iord.customerid

Com INTERSECT

select orderid, customerid  from orders
intersect
select orderid, customerid  from importedorders

Script para dividir uma coluna em duas colunas

Script para separa dados de 1 coluna em 2 outros colunas distintas:

CREATE TABLE EMP_Demo
    (EMP_PAY VARCHAR(20),
                EMP_NAME      VARCHAR(20),
                PAY_SCALE     VARCHAR(20));
INSERT INTO EMP_DEMO(EMP_PAY)
        VALUES    (‘ALPESH,7009′)
INSERT INTO EMP_DEMO(EMP_PAY)
        VALUES    (‘KRUTI,9909′)
INSERT INTO EMP_DEMO(EMP_PAY)
        VALUES    (‘TANMAY,16000.7′)
INSERT INTO EMP_DEMO(EMP_PAY)
        VALUES    (‘NESHA,6060.8′)
INSERT INTO EMP_DEMO(EMP_PAY)
        VALUES    (‘DEVANG,14000′)
UPDATE EMP_Demo
    SET EMP_NAME = LEFT(EMP_PAY,CHARINDEX(‘,’,EMP_PAY)-1)
UPDATE EMP_Demo
    SET PAY_SCALE = RIGHT(EMP_PAY,LEN(EMP_PAY)-CHARINDEX(‘,’,EMP_PAY))
SELECT *
    FROM EMP_Demo
DROP TABLE EMP_Demo
GO