Início / dba / Perguntas / 164698
Accepted
MrVimes
Asked: 2017-02-18 06:58:14 +0800 CST

Armazenar NULL versus armazenar '' em uma coluna varchar

  • 772

Percebo que isso pode estar marcado como duplicado, mas estou perguntando especificamente em relação ao SQL Server 2005

Eu li conselhos conflitantes na internet, então estou perguntando aqui. Especificamente no SQL Server 2005, um NULL em uma coluna varchar ocupa o mesmo espaço que uma string vazia?

Eu construí uma tabela 'holding' em outra unidade e a preenchi com os dados da tabela de origem e, onde quer que os campos estivessem em branco, eu costumava nullif([field],'')inserir nulos no lugar dos espaços em branco.

Então eu construí uma nova tabela com exatamente a mesma estrutura da tabela de espera, mas em vez de substituir espaços em branco por nulo eu apenas inseri os espaços em branco, e até agora parece estar ocupando mais espaço (ainda não terminei de preenchê-lo e Não posso ter certeza de que está consumindo mais dados ainda)

Portanto, antes de preenchê-lo ainda mais e acabar com uma tabela maior do que pensei que seria, é melhor inserir nulos ou espaços em branco?

Editar:

Depois de migrar os dados da tabela de retenção para a nova tabela, a nova tabela é aproximadamente 4 GB maior.

Diferenças de tamanhos de mesa

Existem apenas duas pequenas diferenças no design da tabela - O campo 'serial_number' é char(15) na tabela de retenção, mas varchar(15) na tabela de destino. (O comprimento máximo de um número de série é 14 e há muitos valores vazios - acho que cerca de 30 milhões, se bem me lembro), e o índice clusterizado para a tabela de retenção tem uma coluna extra - program_name ..

Mesa de espera

USE [Temp_holding_EWS]
GO
/****** Object:  Table [dbo].[AmtoteAccountActivity_holding]    
 Script Date: 02/17/2017 20:41:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AmtoteAccountActivity_holding](
    [_Date] [char](8) NULL,[Community] [varchar](10) NULL,
    [AccountNumber] [varchar](50) NULL,
    [Branch] [varchar](10) NULL,
    [Window] [varchar](3) NULL,
    [Time] [char](8) NULL,[Balance_Forward] [varchar](10) NULL,
    [Transaction_Type] [varchar](10) NULL,
    [Program_Name] [varchar](10) NULL,
    [Race] [varchar](10) NULL,[Pool_Type] [varchar](10) NULL,
    [Amount] [money] NULL,[Runners] [varchar](60) NULL,
    [Total_Bet_Amount] [varchar](10) NULL,
    [Debit_Amount] [varchar](10) NULL,
    [Credit_Amount] [varchar](10) NULL,
    [Tx_Date] [char](8) NULL,
    [Check_Clear_Date] [varchar](10) NULL,
    [Refund_Amt] [varchar](10) NULL,
    [Bet_Pool_Modifier] [varchar](5) NULL,
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [serial_number] [char](15) NULL,
    [handle]  AS 
       (CONVERT([money],[total_bet_amount],(0))-CONVERT([money],[refund_amt],(0))),
    [txdatetime]  AS (CONVERT([datetime],([tx_date]+' ')+[time],(11))),
    [dbdate]  AS (CONVERT([datetime],[_date],(11))),
    [Audit_Trail] [varchar](20) NULL,
 CONSTRAINT [PK_AmtoteAccountActivity_holding] PRIMARY KEY NONCLUSTERED 
(
    [RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

(Índice agrupado)

USE [Temp_holding_EWS]
GO
/****** Object:  Index [IX_AmtoteAccountActivity_holding] 
    Script Date: 02/17/2017 21:08:44 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity_holding] ON 
    [dbo].[AmtoteAccountActivity_holding] 
(
    [AccountNumber] ASC,
    [_Date] ASC,
    [Program_Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Tabela de destino

USE [EWS]
GO
/****** Object:  Table [dbo].[AmtoteAccountActivity]    
Script Date: 02/17/2017 20:48:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AmtoteAccountActivity](
    [_Date] [char](8) NULL,     [Community] [varchar](10) NULL,
    [AccountNumber] [varchar](50) NULL,
    [Branch] [varchar](10) NULL,[Window] [varchar](3) NULL,
    [Time] [char](8) NULL,  [Balance_Forward] [varchar](10) NULL,
    [Transaction_Type] [varchar](10) NULL,
    [Program_Name] [varchar](10) NULL,
    [Race] [varchar](10) NULL,
    [Pool_Type] [varchar](10) NULL,
    [Amount] [money] NULL,[Runners] [varchar](60) NULL,
    [Total_Bet_Amount] [varchar](10) NULL,
    [Debit_Amount] [varchar](10) NULL,
    [Credit_Amount] [varchar](10) NULL,
    [Tx_Date] [char](8) NULL,
    [Check_Clear_Date] [varchar](10) NULL,
    [Refund_Amt] [varchar](10) NULL,
    [Bet_Pool_Modifier] [varchar](5) NULL,
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [serial_number] [varchar](15) NULL,
    [handle]  AS 
       (CONVERT([money],[total_bet_amount],(0))-CONVERT([money],[refund_amt],(0))),
    [txdatetime]  AS (CONVERT([datetime],([tx_date]+' ')+[time],(11))),
    [dbdate]  AS (CONVERT([datetime],[_date],(11))),
    [Audit_Trail] [varchar](20) NULL,
 CONSTRAINT [PK_AmtoteAccountActivity2] PRIMARY KEY NONCLUSTERED 
(
    [RecordID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

(Índice agrupado)

USE [EWS]
GO
/****** Object:  Index [IX_AmtoteAccountActivity2]  Script Date: 02/17/2017 21:06:29 ******/
CREATE CLUSTERED INDEX [IX_AmtoteAccountActivity2] ON [dbo].[AmtoteAccountActivity] 
(
    [AccountNumber] ASC,
    [_Date] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

( Nota: Para quem se pergunta por que aparentemente tem valores financeiros e numéricos armazenados em campos de caracteres: Esse foi o design original da tabela há 17 anos (não por mim) e agora existem centenas de consultas sql que são executadas neste banco de dados, é menos trabalhe para mantê-los como varchar e as consultas mantenham sua conversão, do que alterá-las para money,int ou decimal e alterar centenas de consultas)

sql-server sql-server-2005

3 respostas

  1. Best Answer

    Vamos criar três tabelas com uma coluna varchar, duas delas permitindo NULL, uma não.

    CREATE TABLE dbo.x1(id int IDENTITY(1,1) PRIMARY KEY, field varchar(5) null);
CREATE TABLE dbo.x2(id int IDENTITY(1,1) PRIMARY KEY, field varchar(5) null);
CREATE TABLE dbo.x3(id int IDENTITY(1,1) PRIMARY KEY, field varchar(5) not null);

    Preencha-os com 1.000.000 de linhas:

    ;WITH x(x) AS (SELECT 0 UNION ALL SELECT x+1 FROM x WHERE x < 1000000)
INSERT dbo.x1(field) SELECT NULL FROM x OPTION (MAXRECURSION 0);
INSERT dbo.x2(field) SELECT '' FROM dbo.x1;
INSERT dbo.x3(field) SELECT '' FROM dbo.x1;

    Vamos verificar o tamanho:

    SELECT COUNT(*)*8192/1024. FROM sys.dm_db_database_page_allocations(DB_ID(), 
  OBJECT_ID(N'dbo.x1'), 1, NULL, 'DETAILED');
SELECT COUNT(*)*8192/1024. FROM sys.dm_db_database_page_allocations(DB_ID(), 
  OBJECT_ID(N'dbo.x2'), 1, NULL, 'DETAILED');
SELECT COUNT(*)*8192/1024. FROM sys.dm_db_database_page_allocations(DB_ID(), 
  OBJECT_ID(N'dbo.x3'), 1, NULL, 'DETAILED');

    Resultados:

    12,928 KB
12,936 KB
12,936 KB

    Portanto, parece que para 1.000.000 de linhas, escolher NULLsalva ''8 KB (e isso nem é refletido em sp_spaceused, porque aquela página que você salvou ainda está reservada, apenas não alocada).

    Repetido para um heap (novamente, temos que fazer vários testes, pois estamos adivinhando sua estrutura de tabela real):

    12,872 KB
12,872 KB
12,928 KB

    Portanto, insignificante, como sugeri, mesmo extrapolando mais de 120.000.000 de linhas, a maior diferença possível (mais uma vez, dependendo do seu esquema) seria 960 KB em uma tabela adequada e 6,7 MB em um heap. Se o seu servidor estiver tão apertado no espaço em disco que 6,7 MB vão orientar as decisões, você pode considerar quanto custaria um disco adicional em comparação com o tempo que você está gastando investigando isso.

    IMHO, existem razões muito mais importantes entre decidir usar NULLs ou não representar "sem dados". Uma boa pergunta com muitas opiniões e comentários está aqui:

    • 9

  2. Consulte este artigo, que explica como o SQL armazena NULLs .

    Basicamente, uma coluna de largura variável (varchar) armazena um bitmap que indica nulo ou não nulo. Se for nulo, zero bytes serão alocados para o campo varchar e o bit será invertido.

    Para colunas de largura fixa (char), o campo inteiro ainda é alocado, sem dados armazenados nele. Portanto, um campo char de 10 yte alocará 10 bytes, NULL ou não.

    Esse artigo faz uma inserção com dados, com NULL e com string vazia. Em seguida, ele pesquisa o tamanho da página para ver o que está acontecendo internamente.

    Para string Null e Empty, 0 bytes são alocados para campos varchar.

    • 3

  3. Pelo menos para o formato de registro padrão ( FixedVar ), isso não faz diferença na quantidade de espaço que a tabela consome (pode fazer uma diferença marginal nos índices, conforme discutido posteriormente).

    Tanto uma string nula varcharquanto uma vazia são armazenadas exatamente da mesma maneira. A única maneira de distingui-los é se havia um 1ou 0no bitmap nulo. Ambos têm comprimento zero na seção de dados da coluna de comprimento variável e ambos também podem evitar ocupar dois bytes na matriz de deslocamento da coluna variável se não forem seguidos por nenhuma coluna que contenha dados.

    Um dos comentários diz

    Para a tabela em que NULLs não são permitidos, você deseja definir a coluna como NOT NULL, por vários motivos, entre os quais remove o requisito de um bitmap nulo para essa colunan

    Isso não é verdade para páginas de dados. Consulte o Mito 6b: O bitmap nulo contém apenas bits para colunas anuláveis .

    Há uma pequena diferença para índices em que, se todas as colunas que participam de um índice não forem anuláveis, o bitmap nulo será omitido.

    No entanto, a diferença é insignificante e você deve escolher a opção que lhe dá a semântica desejada.

    • 2

relate perguntas