Lição 3: Aplicando funções de rank

 

As funções de rank são utilizadas para prover uma simples visão analítica como também uma ordenação estatística ou segmentação.

Classificando dados

Há quatro funções de rank disponíveis no SQL Server, são elas: ROW_NUMBER, RANK, DENSE_RANK E NTILE.

ROW_NUMBER retorna um número de 1 a n baseado na ordenação definida pelo usuário. Caso haja valores duplicados o ROW_NUMBER irá atribuir uma nova numeração, ignorando a repetição.

SELECT P.FirstName
            ,P.LastName
            ,ROW_NUMBER() OVER(ORDER BY S.SalesYTD DESC) AS ‘RowNumber’
            ,S.SalesYTD, A.PostalCode
FROM  Sales.SalesPerson S INNER JOIN Person.Person P
           ON S.BusinessEntityID = P.BusinessEntityID
       INNER JOIN Person.BusinessEntityAddress BA ON P.BusinessEntityID = BA.BusinessEntityID
        INNER JOIN Person.Address A ON A.AddressID = BA.AddressID
WHERE S.TerritoryID IS NOT NULL

Você pode utilizar o ROW_NUMBER com um agrupamento para que a numeração seja feita de acordo com os grupos. Para conseguir esse resultado você precisará utilizar a cláusula opcional PARTITION BY.

SELECT P.FirstName
,P.LastName
,S.TerritoryID,ROW_NUMBER() OVER(PARTITION BY S.TerritoryID ORDER BY S.SalesYTD DESC) AS ‘RowNumber’
,S.SalesYTD, A.PostalCode
FROM Sales.SalesPerson S INNER JOIN Person.Person P
ON S.BusinessEntityID = P.BusinessEntityID
INNER JOIN Person.BusinessEntityAddress BA ON P.BusinessEntityID = BA.BusinessEntityID
INNER JOIN Person.Address A ON A.AddressID = BA.AddressID
WHERE S.TerritoryID IS NOT NULL

Se você quiser numerar o resultado em camadas você precisará utilizar o RANK, caso não haja camadas o RANK produzirá o mesmo resultado do ROW_NUMBER.

Caso haja camadas o RANK irá repetir a mesma numeração para cada linha, deixando uma lacuna na sequência numérica correspondente ao número de linhas da camada.

SELECT A.ProductID
        ,B.Name
        ,A.LocationID
        ,A.Quantity
        ,RANK() OVER(PARTITION BY A.LocationID ORDER BY a.Quantity DESC) AS ‘Rank’
FROM Production.ProductInventory A INNER JOIN  Production.Product B
            ON A.ProductID = B.ProductID
ORDER BY B.Name

SELECT A.ProductID
,B.Name
,A.LocationID
,A.Quantity
,RANK() OVER(PARTITION BY A.LocationID ORDER BY a.Quantity DESC) AS ‘Rank’
FROM Production.ProductInventory A INNER JOIN Production.Product B
ON A.ProductID = B.ProductID
ORDER BY A.LocationID,‘Rank’

Caso não queira lacunas na numeração você pode utilizar a função DENSE_RANK. DENSE_RANK atribui o mesmo valor para cada valor duplicado mas não deixa lacuna na sequência numérica.

SELECT A.ProductID
,B.Name
,A.LocationID
,A.Quantity
,DENSE_RANK() OVER(PARTITION BY A.LocationID ORDER BY a.Quantity DESC) AS ‘DenseRank’
FROM Production.ProductInventory A INNER JOIN Production.Product B
ON A.ProductID = B.ProductID
ORDER BY A.LocationID,‘DenseRank’

 

NTILE divide o resultado da consulta em grupo, por exemplo , se você quiser  dividir um resultado  em quatro grupos com o aproximadamente o mesmo número de linhas em cada grupo, você pode utilizar o NTILE(4).

SELECT P.FirstName
            ,P.LastName
            ,NTILE(4) OVER(ORDER BY S.SalesYTD DESC) AS ‘Grupo’
            ,S.SalesYTD, S.TerritoryID
FROM  Sales.SalesPerson S INNER JOIN Person.Person P
            ON S.BusinessEntityID = P.BusinessEntityID
        INNER JOIN Person.BusinessEntityAddress BA ON P.BusinessEntityID = BA.BusinessEntityID
       INNER JOIN Person.Address A ON A.AddressID = BA.AddressID
WHERE S.TerritoryID IS NOT NULL

 

SELECT P.FirstName
,P.LastName
,NTILE(2) OVER(PARTITION BY S.TerritoryID ORDER BY S.SalesYTD DESC) AS ‘Grupo’
,S.SalesYTD, S.TerritoryID
FROM Sales.SalesPerson S INNER JOIN Person.Person P
ON S.BusinessEntityID = P.BusinessEntityID
          INNER JOIN Person.BusinessEntityAddress BA  ON   P.BusinessEntityID = BA.BusinessEntityID
          INNER JOIN Person.Address A ON A.AddressID = BA.AddressID
WHERE S.TerritoryID IS NOT NULL                                                     ORDER BY S.TerritoryID, ‘Grupo’

 

Dica para o exame

Você precisa entender a diferença entre os quatro funções de rank e quando produzem o mesmo resultado.

 

Quick Check

1 – Qual a diferença entre RANK e DENSE_RANK?

2 – Quando o ROW_NUMBER, RANK e DENSE_RANK produzem o mesmo resultado?

Respostas:

1 – RANK atribui o mesmo valor para as camadas porém deixa lacunas na sequência numérica. DENSE_RANK atribui o mesmo valor para as camadas e não deixa lacunas na sequência.

2-ROW_NUMBER, RANK e DENSE_RANK produzem o mesmo resultado quando as colunas ordenadas não possuem valores duplicados.

Lição 2: Implementando SubConsultas

 

As subconsultas são úteis para construir consultas complexas e para obter resultados que antes pareciam impossíveis.

Existem dois tipos de subconsultas:

Correlacionada:  é uma consulta que depende e faz referência às colunas da consulta externa a qual está contida.

Não correlacionada: é uma consulta independente da consulta externa na qual está contida.

Ambos os tipos de subconsultas podem retornar uma ou mais linhas. Quando o retorno é escalar, somente um valor,  pode ser definido em qualquer lugar de dentro do SELECT onde um ou zero valores são esperados, quando o retorno é múltiplo a subconsulta pode ser utilizada em onde o resultset é esperado.

 

Consultas não correlacionadas

O principal objetivo de consultas não correlacionadas é permitir a escrita de códigos mais dinâmicos. Por exemplo, você precisa listar os produtos onde o nome da categoria inicia com a letra C, porém você desconhece todas as subcategorias necessárias para especificar na cláusula where, veja o exemplo abaixo:

SELECT P.Name, P.Color
FROM Production.Product P
     INNER JOIN Production.ProductSubcategory S
       ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE S.ProductCategoryID IN( SELECT C.ProductCategoryID       FROM Production.ProductCategory C
WHERE Name LIKE ‘C%’)

Outro exemplo, supondo que você gostaria de retornar todos os produtos onde o preço de lista está acima da média geral, ao invés de obter o valor e armazenar em uma variável, você pode fazer como no exemplo abaixo:

 

SELECT A.ProductID, A.Name, A.ListPrice
FROM Production.Product A
WHERE A.ListPrice> (SELECT AVG(B.ListPrice)
                    FROM Production.Product B)

 

Tabelas derivadas 

Ao invés de armazenar dados em tabelas temporárias para fazer joins e retornar um determinado conjunto de resultados, você pode utilizar as tabelas derivadas.

Tabelas derivadas são consultas realizadas dentro da clausula from de uma consulta externa, é necessário que todos os campos calculados sejam nomeados, a consulta deve estar contida entre parênteses e possuir um apelido.

Veja o exemplo abaixo:

 

SELECT B.BusinessEntityID, B.JobTitle, A.QtdeLinhas
FROM (SELECT E.JobTitle, COUNT(*) QtdeLinhas
      FROM HumanResources.Employee E

      GROUP BY JobTitle) A

INNER JOIN HumanResources.Employee B on A.JobTitle=B.JobTitle

O príncipal benefício de uma tabela derivada é o fato de que o resultado está armazenado inteiramente na memória, resultando em um ganho de desempenho.

Agrupamentos

Você pode realizar agrupamentos dentro de tabelas derivadas e combiná-los externamente.

Como no exemplo abaixo:

SELECT SH3.SalesPersonID,SH3.OrderDate, SH3.DailyTotal, SUM(SH4.DailyTotal) RunningTotal
FROM    (SELECT SH1.SalesPersonID, SH1.OrderDate, SUM(SH1.TotalDue) DailyTotal
                FROM Sales.SalesOrderHeader SH1
                WHERE SH1.SalesPersonID  IS NOT NULL
                GROUP BY SH1.SalesPersonID, SH1.OrderDate) SH3
INNER JOIN  (SELECT SH2.SalesPersonID, SH2.OrderDate, SUM(SH2.TotalDue) DailyTotal
                FROM Sales.SalesOrderHeader SH2
                WHERE SH2.SalesPersonID  IS NOT NULL
                GROUP BY SH2.SalesPersonID, SH2.OrderDate)SH4
ON SH3.SalesPersonID = SH4.SalesPersonID
    AND SH3.OrderDate>SH4.OrderDate
GROUP BY SH3.SalesPersonID, SH3.OrderDate, SH3.DailyTotal
ORDER BY SH3.SalesPersonID, SH3.OrderDate

As tabelas derivadas foram combinadas para retornar todos os pedidos de um vendedor que possuir mais de um pedido por dia. O join por SalesPersonID (vendedor) assegura que você esta acumulando linhas do mesmo vendedor. O join sem igualdade determina que somente os pedidos do vendedor onde data do pedido é anterior à data do pedido dentro do resultset serão retornados.

 

Consultas correlacionadas

As consultas correlacionadas dependem de valores da consulta externa. No exemplo abaixo será listado o Id do Produto e o preço de lista, porém será retornado apenas os produtos que já foram vendidos anteriormente.

SELECT a.ProductID, a.ListPrice FROM Production.Product A WHERE EXISTS (SELECT 1 FROM Sales.SalesOrderDetail B                         WHERE B.ProductID=A.ProductID)

Usando um Join ou uma cláusula where será retornado o produto para cada vez que ele foi vendido, já o comando EXISTS provém melhor desempenho, pois procura será por uma única ocorrência na tabela.

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Lição 1: Construindo consultas recursivas com CTE

 

O conceito de CTE é muito similar a tabelas derivadas, porém com CTEs você pode cruzar com outros resultados para resolver um ou mais problemas, executando consultas recursivas.

 

Expressão de Tabela Comum (Common Table Expressions)

 

Sintaxe:

WITH – Cláusula onde contém o comando válido de select.

Select – Consulta que será realizada utilizando a CTE.

 

WITH EmpTitle AS

(SELECT JobTitle, COUNT(*) NumTitles

FROM HumanResources.Employee

GROUP BY JobTitle)

 

SELECT b.BusinessEntityID, b.JobTitle, a.NumTitles

FROM EmpTitle a INNER JOIN HumanResources.Employee b ON b.JobTitle = a.JobTitle

GO

 

Uma CTE recursiva expande a definição de uma expressão de tabela e consiste em duas partes:

- Uma consulta âncora, que é a fonte da recursividade, juntamente com a declaração “UNION ALL” e uma segunda consulta.

- Uma consulta externa que referencia a rotina e especifica o numero de níveis de recursão.

Por exemplo, a consulta abaixo permite que você especifique um employee e retorna  os niveis da gerencia acima desse empregado na organização, com um limite de 25 niveis de organização.

 

DECLARE @EmployeeToGetOrgFor INT=126;

WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel)

AS (SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName, E.JobTitle, 0

       FROM HumanResources.Employee e INNER JOIN Person.Person AS p

           ON P.BusinessEntityID = E.BusinessEntityID

       WHERE E.BusinessEntityID = @EmployeeToGetOrgFor

  UNION ALL

      SELECT E.BusinessEntityID, E.OrganizationNode, P.FirstName, P.LastName,     E.JobTitle, RecursionLevel+1

    FROM HumanResources.Employee E INNER JOIN EMP_cte

           ON E.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1)

       INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID)

 

SELECT EMP_cte.RecursionLevel, EMP_cte.BusinessEntityID, EMP_cte.FirstName, EMP_cte.LastName,   EMP_cte.OrganizationNode.ToString() AS OrganizationNode,

P.FirstName AS ‘ManagerFirstName’, P.LastName AS ‘ManagerLastName’

FROM EMP_cte INNER JOIN HumanResources.Employee E

                         ON EMP_cte.OrganizationNode.GetAncestor(1) = E.OrganizationNode

                      INNER JOIN Person.Person P ON P.BusinessEntityID = E.BusinessEntityID

ORDER BY RecursionLevel,EMP_cte.OrganizationNode.ToString()

OPTION (MAXRECURSION 25);

 

 

A primeira consulta com a cláusula WITH define o resultado ancora. A segunda consulta executada recursivamente até o nível máximo de recursão contra a consulta âncora.

A recursividade é realizada pelo INNER JOIN na CTE:

INNER JOIN EMP_cte

A consulta externa é então utilizada para retornar o resultado da operação recursiva enquanto dados adicionais são necessários. A cláusula “OPTION” especifica o número máximo de níveis de recursão serão permitidos.

Dica para o exame:

Caso haver um bloco de comando antes da palavra chave “WITH” utilizar “;” para finalizar o bloco de comando anterior.

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Lição 2: Declarativa de Integridade de dados

 

Validação de dados é uma das rotinas mais comum de um desenvolvedor.

Validação de dados

Há duas formas de validar a integridade dos dados, usando a integridade de dados declarativa ou integridade de dados procedural.

Integridade de dados declarativa é um conjunto de regras que são aplicadas a uma tabela e suas colunas utilizando o comando ALTER TABLE e CREATE TABLE. Essas regras são conhecidas como constraints.

A integridade de dados procedural é implementada através de procedures com validações de dados ou de triggers que checam os dados.

Como implementar a Integridade de dados

Há cinco tipos de constraints, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE e DEFAULT.

PRIMARY KEY e UNIQUE constraints

Uma coluna ou uma combinação de colunas criam um identificador único para uma linha, que é reforçada pela criação de um índice único e não aceita duplicidade. Devido a isso primary key e unique constraints possuem a mesma limitação de tamanho como uma chave ou índice, ou seja, não pode haver mais de 16 colunas ou 900 bytes de dados.

Quando nada é especificado o índice criado é clustered e o índice para um unique é do tipo non-clustered. Você pode alterar esse comportamento especificando o tipo de index, veja o exemplo:

–Primary key as clustered index

ALTER TABLE MyTable

ADD PRIMARY KEY (MyTableId)

–Primary key as a nonclustered index

ALTER TABLE MyTable

ADD PRIMARY KEY NONCLUSTERED (MyTableId)

FOREIGN KEY constraints

 

FOREIGN KEY constraints

Foreign key constraint identifica uma coluna ou combinação de colunas que devem existir na mesma tabela ou em outra tabela. A foreign key constraint gerencia a integridade referencial entre duas tabelas ou apenas uma. Para implementar uma foreign key constraint você deve seguir essas regras:

- As colunas referenciadas devem ter o mesmo tipo de dados nos dois locais.

- As colunas referenciadas devem possuir um índice único.

Devido ao índice único, as colunas da foreign key devem respeitar a mesma limitação da primary key.

Quando ocorre uma violação de integridade referencial devido a um delete ou update a linha em questão retorna uma mensagem de erro e desfaz o comando que provocou o erro. Você pode definir qual ação o SQL Server deve realizar nesses casos, veja abaixo:

- NO ACTION – Comportamento padrão do SQL Server, desfaz a ação e retorna um erro.

- SET NULL – Altera os valores referenciados para NULL, não retorna erro.

- SET DEFAULT – Altera os valores referenciados para o valor default da coluna, não retorna erro.

- CASCADE – Exclui linhas referenciadas em um comando delete (ON DELETE) e atualiza linhas referenciadas em um comando update (ON UPDATE), não retorna erro.

Veja como implantar essas ações:

 

CREATE TABLE Customers (

        CustomerID INT PRIMARY KEY );

CREATE TABLE Orders (

        OrderID INT PRIMARY KEY

        ,CustomerID INT NULL

               REFERENCES Customers

                  ON DELETE SET NULL

                  ON UPDATE CASCADE );

 

CHECK CONSTRAINTS

Check constraints são uma série de regras que validam o dado antes de incluir na coluna. Veja algumas vantagens:

- São de fácil implementação.

- São verificadas automaticamente.

- Podem melhorar a performance.

Veja o script de uma simples check constraint que verifica se o preço do produto não é negativo:

ALTER TABLE Products

ADD CHECK (Price>=0.0);

Essa simplicidade é uma grande vantagem sobre as triggers, porém há algumas desvantagens:

- As mensagens de erro são geradas automaticamente e não podem ser substituida.

- Uma check constraint não pode “enxergar” o valor anterior da coluna.

Você pode enriquecer a check constraint utilizando funções de usuário, utilizando T-SQL ou ainda utilizar funções CLR (tanto em C# quanto em VB)

CHECK E FOREIGN KEY CONSTRAINTS VS. QUERY PERFORMANCE

Check e foreign key podem melhorar a performance, pois ambos são regras que o optimizer pode usá-los para criar planos de execução mais eficientes, o código abaixo é um simples exemplo de como isso acontece:

CREATE TABLE Customers (

                CustomerID INT PRIMARY KEY );

CREATE TABLE Orders (

               OrderID INT PRIMARY KEY

               ,CustomerID INT NOT NULL

                    CONSTRAINT FK_OrdersCustomers

                            REFERENCES Customers(CustomerID) );

Para entedermos melhor o que o SQL Server faz quando executa a consulta selecione a opçao Include Actual Execution Plan ou pressione Ctrl+M.

SELECT o.* FROM Orders AS o

WHERE

         EXISTS (SELECT * FROM Customers AS c

             WHERE c.CustomerID = o.CustomerID);

Perceba pelo plano de execução que a tabela Customers não foi acessada, apenas a tabela Orders, isto porque o optimizer sabe ela que não é necessaria para o operado EXISTS porque a foreign key constraint requer que todos os pedidos (orders) possuem referência com um cliente (customer), que é o que a cláusura where verifica.

 

 

Agora desabiliete a foreign key:

ALTER TABLE Orders NOCHECK CONSTRAINT FK_OrdersCustomers;

Execute a mesma consulta novamente e perceba que nesse caso o optimizer executa o operador EXISTS. Devido a foreign key constraint estar desabilitada o SQL Server não pôde ter certeza que todos os pedidos possuem clientes válidos. Em tabelas grandes isso pode fazer uma grande diferença.

Para habilitar novamente a utilize o seguinte código:

ALTER TABLE Orders CHECK CONSTRAINT FK_OrdersCustomers;

ALTER TABLE Orders

     WITH CHECK

       CHECK CONSTRAINT FK_OrdersCustomers

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Lição 1: Trabalhando com Tabelas e Tipos de Dados

 

Modelar, criar e manter tabelas é uma das funções mais importantes de um desenvolvedor de banco de dados.

Tipos de Dados

Antes de criar uma tabela você precisa entender as diferenças entre os tipos de dados que você pode utilizar em uma coluna. Há dois tipos diferentes de tipos de dados no Sql Server:

• Tipos de dados de sistema.
• Tipos de dados de usuários (User-Defined types – UDTs) ou Sql Common Language Runtime (CLR).

Cadeias de Caracteres

Existem alguns tipos de dados string no Sql Server, são eles: char, varchar, nchar, nvarchar, text, ntext.

A diferença entre char e varchar (assim como nchar e nvarchar) é fato do char possuir tamanho fixo e varchar tamanho variável, ou seja char sempre aloca o espaço suficiente em disco para armazenar o tamanho declarado e varchar armazena apenas o tamanho do dado inserido. A vantagem de utilizar char é que as atualizações feitas na coluna nunca requer o deslocamento da linha pois os dados inseridos sempre utilizam o mesmo tamanho, em contra partida varchar aloca muito menos espaço do que char.

Para os tipos de dados char e varchar a collation é utilizada para especificar o code page (conjunto de caracteres) do conteúdo da coluna. A collation também é utilizada para definir como ordenar e comparar os dados armazenados. A nomenclatura da collation especifica algumas características:

• CI – Case Insensitive
• CS – Case Sensitive
• AI – Accent Insensitive
• AS – Accent Sensitive

Nchar e nvarchar armazenam caracteres utilizando o Unicode universal code page (UCS-2). 

 

Tipos numéricos exatos

Os números numéricos exatos são inteiros ou decimais, produzem o mesmo resultados exceto pelo processamento ou pela magnetude (tamanho) dos números.

 

Tipos numéricos aproximados

O sql server suporta dois tipos numéricos com pontos flutuantes o real e o float, assim como o decimal, ambos aceitam parâmetros. Os parâmetros definem o numero de bits que será utilizado para armazenar o dado.

 

Manipulando Data e Hora

A tabela abaixo possui um breve resumo sobre os tipos de dados de data e hora.

É necessário entender as diferenças entre smalldatetime e datetime pois há dois grandes problemas, um deles é que a hora e dia são armazenados juntos e isso precisa ser levado em consideração, veja o exemplo a seguir:

SELECT SalesOrderID

             ,CustomerID

             ,OrderDate

FROM Sales.SalesOrderHeader

WHERE OrderDate=’20080818′

A Consulta apenas retorna pedidos realizados no dia 18/08/2008 exatamente as 00:00:00. Para resolver esse problema você precisa especificar o horário na consulta, como nos exemplos abaixo:

–Query #1

SELECT SalesOrderID ,CustomerID ,OrderDate

FROM Sales.SalesOrderHeader

WHERE OrderDate BETWEEN ’2008-08-18T00:00:00′ and ’2008-08-18T23:59:59.997′

 

–Query #2

SELECT SalesOrderID ,CustomerID ,OrderDate

FROM Sales.SalesOrderHeader

WHERE OrderDate>=’20080818′ and OrderDate <’20080819′

 

Ambas consultas produzem o mesmo resultado, porém considere utilizar o segundo exemplo pois o mesmo atende tanto datetime quanto datetime2.

O tipo de dados time aceita um parâmetro para especificar a precisão ou número de decimais por fração de segundo. Quando o parâmetro não é especificado a precisao é de 7 casas decimais.

 

Tabelas Básicas

No Sql Server há três tipos de variações de tabelas: permanentes, temporárias locais e temporárias globais, além dessa variações temos os tipos de tabelas e parâmetros da tabela.

 

Criando uma tabela

Antes de criar uma tabela é necessario especificar o schema. Schema é similar a namespace em diversas linguagens de programação, o Sql Server possui os schemas sys para objetos de sistema e dbo para novos objetos.

CREATE SCHEMA Sales;

GO 

CREATE TABLE Sales.Customers(

CustomerId INT NOT NULL,

Name NVARCHAR(50) NOT NULL);

Tabelas também podem ser criadas a partir de uma query utilizando o comando select into:

SELECT * INTO Sales.Customers_Copy from Sales.Customers

 

Nomenclatura de colunas e tabelas

Ambos, tabelas e colunas, são identificadores e precisam obedecer algumas regras de nomenclatura:

Identificadores Padrão (Standard)

• O primeiro caracteres deve ser letra ou underscore (_).
• Caracteres seguintes podem ser dígitos, @, $, # e _.
• Não deve ser uma palavra reservada do T-SQL.
• Espaços em branco não são aceitos.

 

Identificadores Delimitados

As mesmas caracteristas do Standard, com exceto que voce pode criar nomes com espaço em banco utilizando as aspas (“”) ou colchetes ([]). Precisa estar setado como ON a propriedade QUOTED_IDENTIFIER.

 

Criando a tabela

Agora vamos criar uma tabela, veja o exemplo:

CREATE TABLE HR.Employees(

EmployeeID INT IDENTITY(1000,2) NOT NULL

,FirstName NVARCHAR(50) NOT NULL

,LastName NVARCHAR(50) NOT NULL

,PhoneNumber VARCHAR(15) NULL

,BirthDate DATE NOT NULL );

Esse código cria a tabela Employees com cinco colunas. Podemos especificar o schema que será utilizado, nesse caso HR.

As colunas são definidas utilizando três propriedades básicas: nome da

coluna, tipo de dado e nullabilidade (se a coluna aceita null o ou não).

 

Nomenclatura

Ao escolher a nomenclatura de tabelas e colunas é importante seguir o padrão da organização ou algumas diretrizes. São elas:

• Utilize PascalCasing (também conhecido como Camel Casing).
• Evite abreviações.
• Longos nomes que usuários entendem são preferíveis a nomes curtos que podem não ser entendidos.

 

Escolhendo Tipos de dados

A escolha do tipo de dado certo para cada coluna é muito importante, para ajudá-lo segue algumas diretrizes que você pode seguir:

• Sempre utilize um tipo de dado que utilizar o mínimo de espaço em disco.
• Mudar o tipo de dados posterior à criação da tabela pode ser muito custoso, não utilize um tipo de dado se o mesmo não atende as necessidades da aplicação.
• Sempre que possível utilize tipos de dados com tamanho variável, como nvarchar e varchar.
• Um dos poucos casos em que o char e nchar são indicados é quando o valor da coluna requer alteração constante, nesse caso o custo de mover a linha com tamanho variável é maior do que a com tamanho fixo.
• Evite o uso dos tipos datetime e smalldatetime, pois ambos ocupam mais espaço em disco e provem menos precisão do que os tipos datetime2, date e time.
• Utilize varchar (Max), nvarchar (Max) e varbinary (Max) ao invés de text, ntext e image, pois poderão ser descontinuados em futuras versões.
• Use o tipo de dado rowversion ao invés de timestamp, pois poderá ser descontinuado em futuras versões.
• Somente Utilize varchar (max), nvarchar(max) e varbinary(max) e xml, quando não há como especificar a quantidade de caracteres a ser utilizada, pois esses campos não podem ser utilizados para índices.
• Use float e real apenas quando a precisão do decimal for insuficiente.

 

Nulo ou Não Nulo?

Você nunca deve utilizar valores no lugar do nulo (ex –1 para inteiro), isso pode causar problemas futuramente. Nulo é um valor desconhecido e será diferente de –1, em uma função de agregação, por exemplo, AVG, o –1 é incluso no cálculo e o nulo não.

 

Identity

Todas tabelas possuem uma coluna ou um conjunto de colunas que identificam a linha, a primary key . Não é fácil definir uma boa coluna para ser a primary key , pois esses dados podem variar com frequência. Para contornar essa situação você pode utilizar uma coluna que gere automaticamente uma chave. O comando IDENTITY é utilizado para determinar uma coluna por tabela onde o valor será acrescido ou decrescido automaticamente quando um valor novo é inserido, esse campo não aceita valores nulos.

Sintaxe:

CREATE TABLE HR.Employees(

EmployeeID INT IDENTITY(1000,2) NOT NULL

,FirstName NVARCHAR(50) NOT NULL

,LastName NVARCHAR(50) NOT NULL

,BirthDate DATE NOT NULL

);

A numeração irá iniciar do 1000 e será acrecido de 2 em 2,  o ser valor negativo também.

A propriedade IDENTITY  não poderá ser utilizada em um campo já existente, somente em campos novos.

Quando um insert  falha a coluna dará sequencia para o próximo valor, veja no exemplo:

 

Compressão

Na versão SQL Server 2008 Enterprise, você pode comprimir dados em tabelas e índices, essa compressão pode ser implementada em dois níveis: linha e página.

ALTER TABLE HR.Employees

REBUILD

WITH (DATA_COMPRESSION=PAGE);

Quando a compressão de dados é ativada, o SQL Server irá converter todos os tipos de dados para variable-length. Quando há mais tipos de dados de tamanho fixo (como Datetime2, int, decimal e char), utilize o nível de compressão por linha.

Compressão a nível de pagina inclui a compressão a nível de linha e utiliza um dicionário de paginas com os prefixos das colunas a fim de evitar redundância de dados. Considerando o exemplo abaixo:

Linha 01 | Andressa Martins

Linha 02 | Lucas Romão

Linha 03 | Andressa Martins

 

Utilizando o dicionário de pagina os dados serão armazenados assim:

Linha 01 | Andressa Martins

Linha 02 | Lucas Romão

Linha 03 | 01

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Lição 3: GerenciandoTransações (Parte 2)

 

Para evitar acessos múltiplos ao mesmo recurso o banco de dados utiliza um recurso chamando LOCK. O controle de concorrência do Sql Server é classificado como otimista ou pessimista.

Pessimista – Permite acesso para leitura e atualização do mesmo dado em concorrência, bloqueios são utilizados para prevenir problemas devido aos múltiplos acessos ao dado.

Otimista – Não há bloqueio de recurso durante a leitura, no caso de atualização, o sistema verifica o dado foi alterado antes de lido.

 

Entendendo bloqueios (locks)

O SQL Server pode bloquear diferentes tipos de recursos ou granularidades, esses tipos podem ser RID (Row Identifier), página, índices, objetos e banco de dados.

Modos de bloqueios:

Shared (S) – Bloqueio utilizado em consultas, compatível com outros bloqueios compartilhados.

Update (U) – Bloqueio utilizado em recursos que podem ser atualizados, apenas uma transação por vez pode obter o Update lock no recurso.

Exclusive (X) – Bloqueio utilizado para modificação de dados, ex: INSERT, UPDATE, ou DELETE, incompatível com outros bloqueios

Intent (IS, IX, SIX) – Executa um bloqueio de objeto de alto nível (ex:table) antes de realizar um bloqueio exclusive (X) ou shared (S) no baixo nível (ex: página).

Schema (Sch-M, Sch-S) – Bloqueio utilizado quando uma operação dependente do esquema de uma tabela está em execução. Há dois bloqueios, o schema modification (Sch-M) e o schema stability (Sch-S), os dois são incompatíveis entre si.

Bulk Update (BU) – Bloqueio utilizado ao copiar massa de dados dentro de uma tabela e quando TABLOCK é especificado.

Key-Range – Bloqueio utilizado para evitar inserções/exclusões fantasmas ou exclusões em um conjunto de registros acessados ​​por uma transação. Esse bloqueio é utilizando transações que operam no nível de isolamento serializável.

 

Entendendo cenários de Deadlock e Blocking

As transações podem bloquear uma a outra, isso pode ocorrer de acordo com o nível de isolamento utilizado.

Uma situação de Deadlock ocorre quando uma transação “A” aguarda a liberação de um recurso bloqueado pela transação “B” que por sua vez aguarda uma liberação de um recurso bloqueado pela transação “A”. Para resolver esse impasse o Sql Server baseado no custo estimado de Rollback “derruba” uma das transações, essa transação é chamada de vítima.

Melhores práticas para evitar Deadlock:

- Faça transações curtas

- Obtenha e verifique os dados de entrada antes de iniciar uma transação.

- Mantenha as transações em um único batch

- Acesse a quantidade mínima possível de dados em uma transação.

 

Entendendo relatórios sobre o estado do bloqueio

Há diversas opções para visualizar o status de um bloqueio em um servidor de Sql Server. São eles Sql Profiler, System Monitor (perfom), Dmv’s e Activity Monitor.

 

Sql Server Profiler

Captura informações de locks e blocks. O Profiler disponibiliza um template já apropriado para detecção de bloqueios.

 

Activity Monitor

Utilize o Activity Monitor para visualizar dados de bloqueios, usuários, conexões e etc.

 

DMV’s

As DMV’s provém informações detalhadas sobre todos os bloqueios ativos.

Segue alguns exemplos.

 

SELECT *

FROM sys.dm_tran_active_transactions

 

 

SELECT Resource_type,Request_mode, Request_status,request_owner_type

FROM sys.dm_tran_locks

 

 

Tipos de recursos

 

RID (Row Identifier) – Linha física em um Heap.

KEY – Linha em um índice.

PAGE – Página em um arquivo de dados.

EXTENT – Uma extensão de arquivo de dados. Uma extensão é um grupo de oito páginas contíguas.

HoBT – Bloqueio em um Heap de uma página de dados ou na estrutura B-tree de um índice.

TABLE – Uma tabela.

FILE – Um arquivo de banco de dados.

APPLICATION – Um recurso de aplicativo especificado.

METADATA – Informações de metadados.

ALLOCATION_UNIT – Uma unidade de alocação.

DATABASE – Um banco de dados

 

Utilizando Sql Server Extended Events

Esse recurso está incluso no Sql Server 2008, assim como FindBlocker and lock_count que podem ser utilizados em conjunto com o Windows Event Logs, Sql Profiler ou System Monitor.

 

Usando DBCC LOG

Comando não documentado o qual retorna informações sobre o transaction log.

Sintaxe:

DBCC LOG (<databasename>,<output identifier>)

 

Níveis de output identifier

0: Retorno apenas o mínimo de informações para cada operação – incluindo operation, context, transactID, e log block generation.

1: Retorna toda as informações do nível acima, incluindo flags e record length information

2: Retorna toda as informações do nível acima, incluindo Object name, index name, page ID e slot ID.

3: Retorna todas as informações sobre a operação.

4: Retorna todas as informações sobre a operação, incluindo o hex dump da linha atual de log de transações.

 

DBCC LOG (’70_433′,1)

 

Definindo Níveis de Isolamento de Transações

Os seguintes níveis podem ser definidos através do comando SET TRANSACTIONS ISOLATION LEVEL:

 

READ UNCOMMITTED - Nível mais baixo de isolamento, permite leitura de transações não confirmadas (leitura suja)

READ COMMITTED – Nível de baixo de isolamento, porém só permite leitura de transações confirmadas.

REPEATABLE READ- Não permite leitura de transações não confirmadas e realiza um bloqueio compartilhado em um recurso de dados.

SNAPSHOT – Requer ALLOW_SNAPSHOT_ISOLATION setada com ON Não permite leitura de transações não confirmadas, quando a transação é iniciada o banco de dados obtém um snapshot dos dados confirmados.

SERIALIZABLE – Esse é o mais restritivo dos níveis de isolamento, pois ele bloqueia intervalos de chaves inteiros até que a transação seja concluída.

 

 

[]’s

Andressa Alves Martins

Twitter: Dre_Martins Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Lição 3: GerenciandoTransações (Parte 1)

Transações podem ser definidas como um conjunto de ações e podem ser realizadas com sucesso ou não. São importantes para garantir a integridade e consistência dos dados em um banco de dados OLTP (Online Transaction Processing).

Entendendo Transações

As transações fornecem quanto principais funções (ACID):

Atomicidade – Uma transação deve ser uma unidade atômica de trabalho ou todas as suas modificações de dados são executadas ou nenhuma delas é executada.

Consistência – Quando concluída, uma transação deve deixar todos os dados em um estado consistente.

Isolamento – Modificações feitas por transações simultâneas devem ser isoladas das modificações feitas por qualquer outra transação simultânea.

Durabilidade – Depois que uma transação tiver sido concluída, seus efeitos ficam permanentemente no sistema. As modificações persistem até mesmo no caso de uma queda do sistema.

Definindo transações explicitas

Uma transação explicita inicia quando o comando BEGIN TRANSACTION é executado e finaliza quando o é executado um dos seguintes comandos COMMIT TRANSACTION ou ROLLBACK TRANSACTION.

Entendendo cenários especiais de ROLLBACK

Quando há transações aninhadas na mesma sessão, um comando ROLLBACK desfaz a transação aninhada. Isso acontece quando um comando COMMIT é executado antes do comando ROLLBACK. No exemplo a os dados são revertidos a primeira linha.

BEGIN TRANSACTION
INSERT
INTO TestTable
VALUES (1,‘a’,‘b‘);

BEGIN TRANSACTION
UPDATE TestTable
SET Col2=‘c’
WHERE Id = 1
    COMMIT TRANSACTION
ROLLBACK

Caso queira desfazer apenas uma parte da transação você pode definir savepoints usando o comando SAVE TRANSACTION nome_do_SavePoint, dessa forma a transação será desfeita até o último savepoint. Vale lembrar que você precisa definir o COMMIT.

Obtendo informação sobre transações

Quando trabalhamos com transações em stored procedures, tratamentos de erros devem ser realizados antes de fechar uma conexão.

Você pode utilizar o comando @@trancount para verificar a quantidade de transações ativas na sessão.

Para obter mais detalhes consulte as DMV’s abaixo:

sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_snapshot
sys.dm_tran_current_transaction
sys.dm_tran_database_transactions
sys.dm_tran_locks
sys.dm_tran_session_transactions
sys.dm_tran_top_version_generators
sys.dm_tran_transactions_snapshot
sys.dm_tran_version_store

sys.dm_tran_active_transactions

[]’s

Andressa Alves Martins

Twitter: Dre_Martins  Skype: Dre.alvesm

Blog: http://sqlgo.wordpress.com

Lição 2: Utilizando a cláusula OUTPUT e a instrução MERGE

Olá pessoal!

Na lição de hoje estaremos estudando a cláusula OUTPUT e a instrução MERGE, e para entendermos bem como ambas funcionam vejamos o que cada uma delas faz.

OUTPUT: Permite que você retorne informações de registros afetadas por instruções como INSERT, UPDATE, DELETE ou pela instrução MERGE, podendo retornar à aplicação como mensagem de confirmação, variáveis, em tabelas de destino ou views. Uma vez recuperada, estas informações podemos manipulá-las da maneira que nos for mais conveniente.

Abaixo vamos comentar sobre 3 cenários possíveis para a utilização da cláusula OUTPUT, porém faremos o exemplo de apenas 1 as outras 2 fica a cargo de vocês e aguardo feedback! J

· Cenário 1: Muitos ambientes precisam salvar os dados inseridos em uma tabela de auditoria, como exemplo uma empresa pode querer tirar relatóriospara saber quantas linhas foram inseridas em diferentes períodos de tempo.

· Cenário 2: Outra situação é o arquivamento de dados. Por exemplo, suponha que uma empresa queira mover todos os registros de pedidos feitos antes 01 de dezembro de 1997, à partir de uma tabela de Pedidos para a tabela PedidosArchive. Você pode excluir as linhas da tabela Pedidos e movê-los para a tabela PedidosArchive em uma única etapa usando DELETE com a cláusula OUTPUT,

· Cenário 3: Neste último a idéia é de a empresa poder saber qual o status de antes e depois de uma alteração em uma determinada coluna. Vejamos o exemplo abaixo onde iremos alterar o nome de uma determinada categoria, para isso utilizaremos a base de dados Northwind, a tabela já existente Categories e uma segunda tabela que criamos chamada CategoriasModificadas que possui as colunas CategoryID, CategoryNameDeletado, CategoryNameNovo, DataCriacao e Usuario.

o Na tabela abaixo apresentamos os dados originais.

o Com o código abaixo iremos alterar o dado da coluna CategoryName que possue o CategoryID 1 e popular nossa nova tabela CategoriasModificadas com os dados alterados e recupoerados.

1: UPDATE Categories

2: SET CategoryName = ‘Bebidas’

3: OUTPUT inserted.CategoryID, deleted.CategoryName

4: , inserted.CategoryName, getdate(), SUSER_SNAME()

5: INTO CategoriasModificadas

6: WHERE CategoryID = 1;

o Com isso teremos os seguintes resultados respectivamente nas tabelas Categories e CategoriasModificadas

MERGE: Nos permite executar comandos como INSERT, UPDATE ou DELETE baseando-se em duas tabelas sendo uma de origem e outra de destino.

No exemplo abaixo iremos comparar os dados de duas tabelas e quando um destes dados não estiverem na tabela de origem, o valor da coluna Cancelled na tabela de destino será alterado para True.

· Neste primeiro momento exibimos os dados iniciais da tabela de origem, observe que ainda temos os registros com o OrderID 10248.

· Neste instante eu excluí todos os registros que possuíam o OrderID = 10248 e executei a seguinte instrução:

1: MERGE INTO [Historico Order Details] AS ODH

2: USING [order details] AS OD

3: ON ODH.orderid = OD.orderid

4: AND ODH.ProductID = OD.ProductID

5: WHEN NOT MATCHED BY TARGET THEN

6: INSERT (OrderID, ProductID, UnitPrice, Quantity, Discount, ModifiedDate, Cancelled)

7: VALUES (OrderID, ProductID, UnitPrice, Quantity, Discount, ModifiedDate, ‘False’)

8: WHEN NOT MATCHED BY SOURCE THEN

9: UPDATE SET ODH.Cancelled = ‘True’;

· Sendo assim teremos os seguintes resultados em nossas tabelas:

o Na tabela de origem os registros com OrderID = 10248 não mais existem.

o Já na tabela de destino os registros com OrderID = 10248 além de estarem presentes a coluna Cancelled foi alterada para True pois estes dados já existiam na tabela de destino e quando executamos a instrução acima atendeu nossa condição de que todos os dados que não estiverem na tabela de origem tivesse os dados da coluna Cancelled alterado para True.

Espero que tenham entendido, qualquer dúvida fico à disposição.

Grande abraço e sucesso a todos.

Lucas A. Romão
MSN: laromao@oxygenhost.com.br
Blog: http://laromao.wordpress.com/ | Twitter: @LucasRomao
Líder da Comunidade Azure Services Brasil | @AzureServicesBR

Capítulo 2 – Modifying Data—The INSERT, UPDATE, DELETE, and MERGE Statements

Lição 1: Modifying Data by Using INSERT, UPDATE, and DELETE Statements

Olá pessoal, depois de um longo período dedicado a outras atividades eis que retorno às atividades, prometo que desta vez teremos novidades diárias (ou quase)!

Mas vamos ao que interessa, nesta lição aprenderemos como inserir dados utilizando o INSERT, alterar dados utilizando o UPDATE, e excluir dados utilizando o DELETE dentro de uma determinada tabela.

Utilizando a instrução INSERT

Para inserir dados em uma tabela você pode inserir dados em determinadas colunas ou em todas, caso opte pela segunda opção vc não necessida indicar o nome das colunas.

Vamos dar uma olhada em ambas formas:

a) Inserindo dados em todas as colunas;

 1: Insert Into Customers

 2:     Values ('ASBR', 'Azure Services Br Corp', 'Lucas A. Romão', 'Manager',

 3:     'Gastão Vidigal, 267', 'Piracicaba', 'SP', '13418470', 'Brazil',

 4:     99999999, 888888888);

b) Inserindo dados em algumas colunas, para isso basta adicionar o nome das colunas a que desejamos inserir valores e na sequencia seus valores propriamente ditos.

 1: Insert Into Customers

 2:     (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region,

 3:     PostalCode, Country)

 4:     Values ('EP', 'Estudando para 70433', 'Lucas A. Romão', 'Manager',

 5: 'Gastão Vidigal, 267', 'Piracicaba', 'SP', '13418470', 'Brazil');

Uma outra forma de inserir dados em uma tabela é com base em uma pesquisa feita em uma outra tabela, veja o exemplo abaixo eu me baseei em uma consulta na tabela EMPLOYEES para inserir alguns dados na tabela CUSTOMERS.

 1: INSERT INTO Customers

 2: SELECT SUBSTRING(lastname,1,3), 'Northwind Traders', FirstName + ' ' + LastName

 3:        , 'Employee', Address, City, Region, PostalCode

 4:        , Country, HomePhone, NULL

 5: FROM Employees WHERE LastName='Fuller'

Veja o resultado após um SELECT na tabela CUSTOMERS.

Obs: Claro que este SELECT podia ser muito mais complexo utilizando-se , mais de uma tabela inclusive utilizando JOIN.

Que tal voces montarem um e postar nos comentários? Vale um brinde heim!!!!

Utilizando a instrução UPDATE

Nos próximos exemplos estarei demonstrando algumas maneiras de como atualizar os dados de uma tabela.

Na primeira forma estarei alterando todos os dados de uma coluna, onde aumentaremos em 1.25 o valor de todos os produtos da coluna UNITPRICE da tabela PRODUCTS e para isso utilizartemos a seguinte instrução.

 1: UPDATE Products

 2: SET UnitPrice = UnitPrice + 1.25;

Com apenas essas 2 linhas alteramos os preços de todos os produtos e se executarmos uma instrução SELECT teremos o seguinte resultado.

Agora se quisermos alterar o valor de um único ou determinado grupo de registros, basta utilizarmos a cláusula WHERE, abaixo iremos alterar o valor do Produto CHANG (observe a linha 2 da imagem acima) de 20.25 para 19.99.

 1: UPDATE Products

 2: SET UnitPrice = 19.99

 3: WHERE ProductID = 2;

Veja o resultado final.

Você também pode atualizar uma tabela baseando-se em um JOIN entre outras tabelas.

Utilizando a instrução DELETE

Para utilizarmos a instrução DELETE podemos utilizá-la das 3 formas mostradas anteriormente, vejamos as 3 formas.

a) Excluindo todas as informaçõe de uma única vez, para isso basta utilizarmos a seguinte sintaxe.

 1: DELETE FROM Customers;

PS: Cuidado ao executar esse comando pois ele definitivamente vai limpar sua tabela

b) Excluindo um determinado registro com base em uma cláusula WHERE, neste caso iremos excluir um registro que inserimos anteriormente.

Para isto basta utiliza a seguinte sintaxe.

 1: DELETE FROM Customers

 2: WHERE CustomerID = 'Ful';

c) E por último vamos ver como deletar determinadas informações baseadas em um resultado obtido por um JOIN feito em outras tabelas.

Neste exemplo iremos deletar todos os registros da tabela [Order Details] que possua o mesmo ORDER.ID entre ambas as tabelas e possuam o valor da coluna FREIGHT <= 1 e o valor da coluna SHIPVIA = 1

 1: DELETE FROM [Order Details]

 2: FROM ORDERS JOIN [Order Details]

 3: ON Orders.OrderID = [Order Details].OrderID

 4: WHERE Freight <= 1 AND ShipVia = 1

Utilizando a instrução TRUNCATE

Para finalizar temos a instrução TRUNCATE, que possui a mesma função do DELETE para apagar todos os registros de uma tabela, porém com algumas diferenças significativas como por exemplo a velocidade em que os dados são apagados (muito mais rápido), requer menos recursos do servidor além de que se existe uma coluna com valor identity o TRUNCATE TABLE redefine este valor.

Veja sua sintaxe abaixo.

 1: TRUNCATE TABLE Customers;

PS: Cuidado ao executar esse comando pois ele definitivamente vai limpar sua tabela

E é isto pessoal espero que possam ter aproveitado e nos encontramos no próximo post.

Grande abraço e sucesso.

Lucas A. Romão

Lição 5: Applying Built-in Scalar Functions

Olá pessoal!

Estou aqui mais uma vez “deitado” sobre o Trainning Kit estudando-o e pesquisando mais material na internet, o conteúdo de hoje é bem tranquilo de assimilar porém de uma grande importância no nosso dia-a-dia.

Bom proveito!

O SQL Server 2008 nos prove um grande número de funções internas e podemos utilizá-las para aprimoras nossas queries.

Em muitos casos quando usamos uma função interna frequentemente logo após o nome da função adicionamos os “()” mesmo quando não temos nenhum parâmetro isso pode indicar para que o engine do banco utilize o valor atual, veremos alguns exemplos logo mais.

Bom. Vamos partir para a prática e ver um pouco do que o SQL Server 2008 nos proporciona, iremos ver alguns exemplos das funções mais utilizadas.

As primeiras que iremos ver serão as que trabalham com DATE e TIME.

As primeiras funções que iremos ver serão SYSDATETIME, SYSDATETIMEOFFSET, e SYSUTCDATETIME. todas as 3 nos retornam a hora. Elas são ideais caso tenhamos a necessidade de saber o horário preciso do sistema naquele momento.

Vejamos o exemplo:

SELECT SYSDATETIME() AS 'Hora Exata do Sistema', 'SYSDATETIME' AS 'Nome da Função'
UNION
SELECT SYSDATETIMEOFFSET(), 'SYSDATETIMEOFFSET'
UNION
SELECT SYSUTCDATETIME(), 'SYSUTCDATETIME'

Fig. 30 - Exemplo de utilização de funções Date/Time

Veja que o resultado foi exatamente igual e que para retornarmos a data e hora do sistema é extremamente simples, aproveitando o exemplo ainda colocamos o conceito de UNION que aprendemos na lição passada (Lição 4 – se tiver alguma dúvida volte uma lição e revise o conceito de UNION e suas regrinhas de funcionamento).

Agora se vc não tem a necessidade de tamanha precisão você pode utilizar as funções GETDATE, CURRENT_TIMESTAMP e GETDATE que ainda lhe proporciona um ganho de performance por retornar um conjunto de valores menor dos que as funções mostradas anteriormente.

Vejamos um mesmo modelo de exemplo:

SELECT GETDATE() AS 'Hora Exata do Sistema', 'GETDATE' AS 'Nome da Função'
UNION
SELECT CURRENT_TIMESTAMP, 'CURRENT_TIMESTAMP'
UNION
SELECT GETUTCDATE(), 'GETUTCDATE'

Fig. 31 - Mais funções Date/Time

A única observação é a de que a função CURRENT_TIMESTAMP não necessita dos parenteses.

Agora se você tem a necessidade de apenas parte das informações podemos utilizar as funções DATEPART e DATENAME.

Vejamos alguns exemplos para melhorar explicar:

SELECT DATENAME(DAY, GETDATE()) AS 'Parte da data do Sistema', 'DATENAME' AS 'Nome da Função'
UNION
SELECT DATENAME(MONTH, GETDATE()), 'DATENAME'
UNION
SELECT (YEAR, GETDATE()), 'DATENAME'

Fig. 32 - Exemplo de uso da função DATENAME

Com o exemplo acima utilizando o DATENAME a informa que retorna é a especificada dentro da função, em especial quando solicitamos o mês o que nos é retornado é a string com o nome do mês corrente.

Vejamos agora um exemplo com DATEPART que tem a mesma função do exemplo anterior e o que difere as duas é que ela retorna apenas o valor numérico do mês e não mais a string.

SELECT DATEPART(DAY, GETDATE()) AS 'Parte da data do Sistema', 'DATEPART' AS 'Nome da Função'
UNION
SELECT DATEPART(MONTH, GETDATE()), 'DATEPART'
UNION
SELECT DATEPART(YEAR, GETDATE()), 'DATEPART'

Fig. 33 - Exemplo de uso da função DATEPART

Agora se quisermos adicionar alguns dias, meses ou anos devemos utilizar a função DATEADD, vejamos os exemplos.

 
SELECT DATEADD(YY, 5, GETDATE()) AS 'Resultado obtido'
       , 'DATEADD' AS 'Nome da função'
       , 'Adicionando 5 anos à partir da data atual' AS 'Observação'
UNION
SELECT DATEADD(DD, 25, GETDATE()), 'DATEADD', 'Adicionando 25 dias à partir da data atual'
UNION
SELECT DATEADD(MM, 8, GETDATE()), 'DATEADD', 'Adicionando 8 meses à partir da data atual'

Fig. 34 - Exemplo de uso de DATEADD

Agora se quisermos o inverso, sabermos quanto tempo se passou, vejamos os seguintes exemplos:

SELECT DATEDIFF(YEAR, '1/21/1980', GETDATE()) AS 'Resultado obtido'
    , 'DATEIFF' AS 'Nome da função'
    , 'Quantidade em anos que tenho desde o meu nscimento' AS 'Observação'
UNION
SELECT DATEDIFF(DAY, '1/21/2001', GETDATE()), 'DATEIFF'
    , 'Quantidade em dias que tenho desde o meu nscimento'
UNION
SELECT DATEDIFF(MONTH, '1/21/2001', GETDATE()), 'DATEIFF'
     , 'Quantidade em meses que tenho desde o meu nscimento'

Fig. 35 - Exemplo de uso de DATEIFF

Já que estavamos tratando de hora e data, vamos dar uma olhada em uma função de sistema, apresentando-lhes a função CONVERT que nos auxilia convertendo os formatos de data e hora de acordo com a região e formato necessário.

Vejamos alguns exemplos:

SELECT CONVERT(VARCHAR(30), GETDATE()) AS 'Data', 'Estilo padrão' AS 'Descrição'
UNION
SELECT CONVERT(VARCHAR(30), GETDATE(),103), 'Estilo Britânico/Francês | dd/mm/aaaa'
UNION
SELECT CONVERT(VARCHAR(30), GETDATE(),111), 'Estilo Japonês | aa/mm/dd'
UNION
SELECT CONVERT(VARCHAR(30), GETDATE(),105), 'Estilo Italiano | dd-mm-aa'

Fig. 36 - Exemplo de uso de CONVERT

Na tabela acima temos o formato que escolhemos para ser exibido e na segunda coluna a que região este padrão pertence seguido de uma máscara.

Veja tabela completa de estilos de formatação neste link: http://msdn.microsoft.com/pt-br/library/ms187928.aspx

String Functions

Vejamos agora as diversas funções que podemos utilizar para com strings.

• Left – Retorna o número de caracteres que for definido na função, iniciando da esquerda
• Right – Retorna o número de caracteres que for definido na função, iniciando da direita
• Upper – Retorna a string toda em maiúscula
• Lower – Retorna a string toda em minúscula
• Substring – Com esta função vc determina de que ponto a que ponto deve-se retornar o trecho da string
• Replace – Pesquisa dentro de uma string um conjunto de caracteres e substitui por outro que definimos
• RTrim – Remove os espaços em branco à direita de uma string
• LTrim – Remove os espaços em branco à esquerda de uma string

Vamos aos exemplos:

SELECT LEFT('Estudando para 70-433',5) AS 'Resultado Obtido', 'LEFT' AS 'Função'
UNION
SELECT RIGHT('Estudando para 70-433',5), 'RIGHT'
UNION
SELECT UPPER('Estudando para 70-433'), 'UPPER'
UNION
SELECT LOWER('Estudando para 70-433'), 'LOWER'
UNION
SELECT SUBSTRING('Estudando para 70-433',3,5), 'SUBSTRING'
UNION
SELECT REPLACE('Preparando para 70-433','Preparando','Estudando'), 'REPLACE'
UNION
SELECT RTRIM('Preparando para 70-433         '), 'RTRIM'
UNION
SELECT LTRIM('         Preparando para 70-433'), 'LTRIM'

Fig. 37 - Exemplo com funções de uso com String

E assim chegamos ao fim de mais uma Lição espero que tenham aproveitado e assimilado bem os conceitos, pois daqui para a frente eles serão essencias para as demais Lições visto que estaremos nos aprofundando cada vez mais e sempre que possível mesclando dois ou mais dos conceitos destas primeiras Lições.

Grande abraço, sucesso e nos encontramos no próximo post.

Lucas A. Romão