Blog – Fabiano Neves Amorim

Muitas vezes temos a necessidade de criar 2 índices em variáveis do tipo table, mas o comando CREATE INDEX @TMP… não roda pois não são permitidos comandos DDL em variáveis do tipo table, porém é possível definir 2 índices no comando de create da tabela, segue um exemplo bem prático.

  

SET STATISTICS PROFILE ON

 

DECLARE @Tmp TABLE(ID   Int IDENTITY(1,1) PRIMARY KEY,

                   Nome VarChar(200))

 

SELECT * FROM @Tmp

WHERE ID = 10

/*

SELECT * FROM @Tmp  WHERE ID = 10

  |–Clustered Index Seek(OBJECT:(@Tmp), SEEK:([ID]=(10)) ORDERED FORWARD)

*/

 

SELECT * FROM @Tmp

WHERE Nome = ‘Teste’

/*

SELECT * FROM @Tmp  WHERE Nome = ‘Teste’

  |–Clustered Index Scan(OBJECT:(@Tmp), WHERE:([Nome]=’Teste’))

*/

GO

— Exemplo de UNIQUE

DECLARE @Tmp TABLE(ID   Int IDENTITY(1,1) PRIMARY KEY,

                   Nome VarChar(200) UNIQUE)

 

SELECT * FROM @Tmp

WHERE ID = 10

 

/*

SELECT * FROM @Tmp  WHERE ID = 10

  |–Clustered Index Seek(OBJECT:(@Tmp), SEEK:([ID]=(10)) ORDERED FORWARD)

*/

 

SELECT * FROM @Tmp

WHERE Nome = ‘Teste’

 

/*

SELECT * FROM @Tmp  WHERE Nome = ‘Teste’

  |–Index Seek(OBJECT:(@Tmp), SEEK:([Nome]=’Teste’) ORDERED FORWARD)

*/

Hoje precisei ler os dados direto de uma planilha excel, normalmente jogo os dados que quero trabalhar para uma tabela temporária, para isso utilizei o seguinte comando.

 

— Comando para habilitar recurso – OPENROWSET

EXEC sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;

GO

RECONFIGURE;

GO

 

— Select para ler dados direto da planilha excel.

SELECT * INTO #TMP FROM OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\teste.xls;’,

‘SELECT * FROM [Plan1$]’)

GO

 

SELECT * FROM #TMP

 

Isso pode ser bem útil._________________________________________________________________

Good News sobre SQL Server 2005, Microsoft anuncia que o Service Pack 3 ta no forno e até o final deste ano será liberado.

 

Mais informações no link abaixo:

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx

 

Bom também,

Extents são unidades de alocação de dados do SQL Server, para cada extent podemos ter 8 pages de dados.

Se você achar melhor antes de continuar a leitura deste post leia um pouco mais sobre extents no artigo que escrevi estes dias.

 

Vamos analisar mais um comportamento bem interessante da Engine do SQL Server.

 

Os dados das tabelas de um Banco de Dados o SQL Server ficam alocados em extents e dentro de cada extent temos 8 pages, o dado físico que você escrever em uma tabela fica salvo nestas pages que por sua vez estão nos extents.

As vezes fica um pouco confuso entender estes tal de Pages e Extents, para tentar simplificar vamos imaginar o seguinte, um livro(file do SQL Server), dentro de um livro temos várias páginas(Pages) e cada página está dentro de um determinado capítulo(extents), o SQL Server é trabalha exatamente como este livro para armazenar os dados.

 

Voltando ao assunto da Engine, o interessante na história é que quando os dados de uma tabela são apagados e novos dados são inseridos o SQL Server não reutiliza os extents que não estão mais sendo utilizados, isso por questão de Performance, o SQL é otimizado para alocar novos extents ao invés de ficar dando manutenção nos extents que seus dados  foram anteriormente excluídos.

 

Para cada arquivo do SQL Server ele mantém um ponteiro dizendo qual é o próximo extent livre, sendo assim ele simplesmente vai no ponteiro e aloca os dados neste extent. E após alocar os dados ele atualiza este ponteiro com a informação do próximo extent disponível. O SQL só irá marcar este ponteiro com a informação do Extent que teve seus dados apagados quanto ele chegar no fim do arquivo do SQL ou então quando não conseguir mais alocar extents por falta de espaço no arquivo ou ocorrer um Auto-Grow por exemplo um ShrinkDatabase.

 

A razão para este comportamento é que se o SQL tiver que ficar voltando para verificar quais extents estão vazios ele iria perder muito mais tempo do que simplesmente alocar outro novo extent e também por questões de fragmentação já que um extent no inicio do arquivo seria utilizado logo depois outro extent no final do arquivo depois outro no meio e por ai vai…

 

Chega de blablabla e vamos ver um pouco de código para comprovar este comportamento:

 

USE Master

 

— Caso exista um banco chamado DBTesteExtent, apaga ele.

IF (SELECT DB_ID(‘DBTesteExtent’)) IS NOT NULL

BEGIN

  USE Master

  ALTER DATABASE DBTesteExtent SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  DROP DATABASE DBTesteExtent

END

GO

 

— Criar um banco de dados chamado DBTesteExtent

IF (SELECT DB_ID(‘DBTesteExtent’)) IS NULL

BEGIN

  CREATE DATABASE DBTesteExtent

END

GO

 

USE DBTesteExtent

 

— Vamos criar uma tabela para utilizar nos testes.

CREATE TABLE TesteExtent(ID Int Identity(1,1),

                         Nome char(8000) default ‘8000 Bytes’)

GO

 

— Vamos inserir uma linha na tabela para verificar as pages e extents que foram alocadas

INSERT INTO TesteExtent DEFAULT VALUES

 

— Utiliza a unDocumented Database Console Commands for SQL Server – DBCC EXTENTINFO

— Podemos visualizar que foi alocada uma page de dados em um extent misto

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

 

 

— Vamos inserir mais dados para forçar a alocação de um extent uniforme

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

INSERT INTO TesteExtent DEFAULT VALUES

 

— Podemos visualizar que foram alocadas várias páginas e 2 extents

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

 

— Agora vamos apagar os dados da tabela e verificar novamente os extents

DELETE FROM TesteExtent

 

— Os extents continuam aparecendo, assim como a informação do tamanho da tabela.

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

GO

EXEC sp_spaceUsed TesteExtent

 

— Vamos criar uma nova tabela e inserir alguns registros nela,

— repare que os extents da tabela TesteExtent não são reutilizados

— na verdade o SQL aloca novo extents para a tabela.

CREATE TABLE TesteExtent2(ID Int Identity(1,1),

                          Nome char(8000) default ‘8000 Bytes’)

GO

 

— Vamos inserir uma linha na tabela para verificar as pages e extents que foram alocadas

INSERT INTO TesteExtent2 DEFAULT VALUES

 

— Repare que o page_id é diferente dos page_id da tabela TesteExtent

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent2’,-1)

GO

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

 

— Ao executar o SHRINKDATABASE os extents da tabela TesteExtent são apagados.

— e a informção do tamanho é atualizada

DBCC SHRINKDATABASE (DBTesteExtent);

 

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent’,-1)

GO

EXEC sp_spaceUsed TesteExtent

GO

 

— Vamos inserir mais dados para forçar a alocação de um extent uniforme na tabela TesteExtent2

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

INSERT INTO TesteExtent2 DEFAULT VALUES

 

— Verifique que os page_id que pertenciam a tabela TesteExtent foram reutilizados.

DBCC EXTENTINFO(DBTesteExtent, ‘TesteExtent2’,-1)

 

Fonte – How It Works: SQL Server Page Allocations

_________________________________________________________________
Fabiano Neves Amorim – MCITP DBD / MCTS / MCP – SQLServer

* fabiano@cnpm.com.br – http://fabianosqlserver.spaces.live.com/

 

Corrigiram o erro que acontecia ao iniciar o Serviço do SQL 2008 no dia 29 de fevereiro,

 

http://sqlserver-qa.net/blogs/sql2008/archive/2008/04/07/3987.aspx

 

Fico mais calmo! J

Cuidado ao utilizar a função IsNumeric para verificar se valores String são realmente números. Segue um exemplo de como a função pode retornar alguns valores não desejados.

 

— Cria uma tabela de teste

CREATE TABLE #Teste (ID Int Identity(1,1) Primary Key,

                    Numero VarChar(200))

 

INSERT INTO #Teste VALUES(‘$55.69’)

INSERT INTO #Teste VALUES(‘1.4e35’)

INSERT INTO #Teste VALUES(‘2d4’)

INSERT INTO #Teste VALUES(‘3.7’)

INSERT INTO #Teste VALUES(‘412’)

INSERT INTO #Teste VALUES(‘0e2155’)

INSERT INTO #Teste VALUES(CHAR(9)) –Tab

GO

 

SELECT * FROM #Teste

— Cuidado ao usar IsNumeric com colunas VarChar

SELECT ISNUMERIC(Numero) [Numeric ?],

       ISNUMERIC(Numero + ‘e0’) AS [Numeric], Numero AS Valor

FROM #Teste

GO

 

— Criei uma função chamada IsNumber que valida o valor utilizando o ‘e0’

CREATE FUNCTION dbo.IsNumber(@Value VarChar(200))

RETURNS BIT

AS

BEGIN

  RETURN (SELECT IsNumeric(@Value + ‘e0’))

END

GO

–Ex de uso da função

SELECT dbo.IsNumber(Numero) AS [Numeric], Numero AS Valor

FROM #Teste

 

DROP TABLE #Teste

 

Fonte – http://sqlblog.com/blogs/denis_gobo/archive/2007/08/23/2311.aspx