É possível saber quanto um índice rowstore é usado, mas e o texto completo?
Não consegui encontrar nada em nenhuma GUI ou DMV. Mesmo dbatools não suporta isso .
Herdei um servidor que possui alguns índices de texto completo e não tenho ideia se eles são necessários. Esta questão é minha tentativa de obter os dados necessários para me sentir seguro ao abandoná-los.
Suponha que eu tenha uma mesa. Sua chave primária está agrupada e consiste em exatamente uma coluna. Existe algum motivo para criar também um índice não clusterizado na mesma coluna, sem outras colunas em qualquer parte do índice não clusterizado?
Atualmente, estou desenvolvendo alguns recursos para um projeto onde os clientes são capazes de alterar o esquema do banco de dados por meio de uma UI e nós cuidamos de todos os detalhes essenciais sobre a execução das instruções necessárias para atualizar o banco de dados (PostgreSQL).
Eu pesquisei um pouco sobre quais poderiam ser algumas pegadinhas e uma das pegadinhas que encontramos são os nomes dos índices, especialmente aqueles que são gerados automaticamente ao criar UNIQUErestrições.
Permitimos que os usuários declarem qual coluna desejam indexar, mas não percebemos que agora criamos dois índices sempre que eles definem indexedcomo verdadeiro em uma UNIQUEcoluna.
Isso já está resolvido, mas levantou a questão sobre o que acontece quando as colunas são renomeadas. No que diz respeito aos nossos testes, renomear colunas não altera os nomes dos índices e, ao criar uma nova coluna com o nome ocupado anteriormente, é criado um índice com um número anexado:
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
ALTER TABLE "users" RENAME COLUMN "email" TO "email_one";
ALTER TABLE "users" ADD COLUMN "email" INTEGER UNIQUE;
/*
postgres@localhost:postgres> \d users
+-----------+------------------------+----------------------------------------------------------+
| Column | Type | Modifiers |
|-----------+------------------------+----------------------------------------------------------|
| user_id | integer | not null default nextval('users_user_id_seq'::regclass) |
| email_one | character varying(255) | not null |
| email | integer | |
+-----------+------------------------+----------------------------------------------------------+
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_email_key" UNIQUE CONSTRAINT, btree (email_one)
"users_email_key1" UNIQUE CONSTRAINT, btree (email)
*/
Como não sou especialista em bancos de dados e nunca mantive bancos de dados grandes, não tenho certeza se isso poderia causar problemas. Perguntei a um amigo meu e ele me deu a seguinte função que eu poderia usar em vez de RENAME TO:
CREATE OR REPLACE FUNCTION rename_column_and_index(
tbl_name TEXT,
old_col_name TEXT,
new_col_name TEXT
) RETURNS void AS $$
DECLARE
index_record RECORD;
old_index_suffix TEXT;
new_index_name TEXT;
BEGIN
-- Find all indexes that contain the old column name
FOR index_record IN
SELECT indexname
FROM pg_indexes
WHERE tablename = tbl_name
AND indexdef LIKE old_col_name || '%'
LOOP
-- Start a savepoint
EXECUTE 'SAVEPOINT before_rename';
BEGIN
-- Extract the suffix from the old index name
old_index_suffix := substring(index_record.indexname from length(quote_ident(old_col_name)) + 1);
-- Create the new index name
new_index_name := quote_ident(new_col_name) || old_index_suffix;
-- Rename the column
EXECUTE 'ALTER TABLE ' || quote_ident(tbl_name) ||
' RENAME COLUMN ' || quote_ident(old_col_name) ||
' TO ' || quote_ident(new_col_name);
-- Rename the index
EXECUTE 'ALTER INDEX ' || quote_ident(index_record.indexname) ||
' RENAME TO ' || new_index_name;
EXCEPTION
WHEN OTHERS THEN
-- Rollback to the savepoint in case of error
EXECUTE 'ROLLBACK TO SAVEPOINT before_rename';
RAISE;
END;
-- Release the savepoint
EXECUTE 'RELEASE SAVEPOINT before_rename';
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- SELECT rename_column_and_btree_index('users', 'email', 'email_one');
Agora minha pergunta é: estou com muito medo dessa questão? Esta função é boa/facilita a manutenção futura? Ou posso simplesmente parar de me preocupar completamente com os índices automáticos?
Em primeiro lugar, peço desculpas pela extensão - este é um problema estranho, então estou tentando fornecer detalhes suficientes para torná-lo reproduzível.
Acho que encontrei um problema ao exportar um *.bacpacaplicativo de camada de dados entre dois servidores MS SQL para migrar uma instância de um banco de dados Indeo ProGet que cria um bacpac inconsistente. Tentar importar este bacpac resulta em um erro, e eu apreciaria uma segunda olhada no problema para ter certeza de que não estou fazendo nada estúpido...
Acho que a causa raiz é um possível bug no processo "Exportar aplicativo da camada de dados" quando há várias colunas de esquema usando o mesmo tipo definido pelo usuário e o UDT está vinculado a uma regra de validação - o resultado parece ser um *.bacpacarquivo corrompido que não pode ser importado sem primeiro fazer alguns ajustes manuais.
Minha pergunta é:
A seguir, recriamos uma pequena parte do esquema do banco de dados Inedo ProGet que demonstra o problema. (FWIW, atualmente estou usando o SQL Server 2022 v16.0.1105.1, mas tenho quase certeza de que isso também ocorre em outras versões).
Basicamente faz isso:
YNINDICATORYeNYNINDICATOR(veja [dbo].[CustomLanguage].[Active_Indicator]e [dbo].[ClusterNodes].[Primary_Indicator])CREATE TYPE [dbo].[YNINDICATOR] FROM [char](1) NULL
GO
CREATE RULE [dbo].[YNINDICATOR_Domain]
AS
@Ind COLLATE Latin1_General_BIN IN ('Y', 'N');
EXEC sp_bindrule 'YNINDICATOR_Domain', 'YNINDICATOR'
GO
CREATE TABLE [dbo].[CustomLanguages](
[CustomLanguage_Id] [int] IDENTITY(1,1) NOT NULL,
[Culture_Name] [varchar](50) NOT NULL,
[Language_Name] [nvarchar](100) NOT NULL,
[Active_Indicator] [dbo].[YNINDICATOR] NOT NULL,
[CustomLanguage_Xml] [xml] NOT NULL,
CONSTRAINT [PK__CustomLanguages] PRIMARY KEY CLUSTERED
(
[CustomLanguage_Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ClusterNodes](
[Server_Name] [nvarchar](64) NOT NULL,
[NodeType_Code] [char](1) NOT NULL,
[LastUpdated_Date] [datetime] NOT NULL,
[Primary_Indicator] [dbo].[YNINDICATOR] NOT NULL,
[Node_Configuration] [xml] NOT NULL,
CONSTRAINT [PK__ClusterNodes] PRIMARY KEY CLUSTERED
(
[Server_Name] ASC,
[NodeType_Code] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClusterNodes] WITH CHECK ADD CONSTRAINT [CK__ClusterNodes__NodeType_Code] CHECK (([NodeType_Code]='S' OR [NodeType_Code]='W'))
GO
ALTER TABLE [dbo].[ClusterNodes] CHECK CONSTRAINT [CK__ClusterNodes__NodeType_Code]
GO
A exportação do esquema acima para um arquivo bacpac do aplicativo da camada de dados é concluída sem erros, mas a tentativa de reimportá-lo como um novo banco de dados gera o seguinte erro:
Estúdio de gerenciamento do Microsoft SQL Server
Não foi possível importar o pacote.
Erro SQL72014: Framework Microsoft SqlDataClient Data Provider: MSG 2714, Nível 16, Estado 3, Procedimento YNINDICATOR_Domain, Linha 1 Já existe um objeto chamado 'YNINDICATOR_Domain' no banco de dados.
Erro SQL72045: Erro de execução de script. O script executado:
CRIAR REGRA [dbo].[YINDICATOR_Domain]
AS @Ind COLLATE Latin1_General_BIN IN ('Y', 'N')
(Microsoft.SqlServer.Dac)
Descompactei o bacpac e descobri que quando há mais de uma coluna definida usando o YNINDICATORUDT, o model.xmlarquivo dentro do bacpac contém algo assim, com dois SqlRule elementos definidos para [dbo].[YNINDICATOR_Domain]- o primeiro contém as referências da coluna e o segundo contém o UDT referência:
Bacpac quebrado – duas referências de coluna e uma referência UDT -> dois elementos SqlRule
... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
... snip ...
Esta é provavelmente a causa do already an object named 'YINDICATOR_Domain'erro porque ele tenta criar o SqlRule duas vezes – uma vez para cada um Element.
Se eu descartar qualquer uma das colunas usando o YNINDICATORUDT e reexportar um aplicativo da camada de dados, o bacpac conterá um único Element Type="SqlRule"nó que contém a coluna e as referências do UDT:
Trabalhando bacpac - uma referência de coluna e uma referência UDT -> um elemento SqlRule
... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
... snip ...
e se eu usar sp_unbindrulepara remover a ligação, o bacpac contém apenas uma Element- ele não cria mais uma segunda para a ligação UDT, pois obviamente não existe mais:
Trabalhando bacpac - duas referências de coluna e nenhuma referência UDT -> um elemento SqlRule
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
Na verdade, não posso usar nenhuma dessas opções, pois ela não corresponde ao esquema de banco de dados real do aplicativo ProGet, mas é interessante observar como a coluna e a ligação UDT são serializadas dependendo se há uma ou mais de uma coluna usando o UDT.
Se eu hackear manualmentemodel.xml o arquivo bacpac original e mesclar os dois SqlRule Elements para que haja apenas um que contenha as colunas e a vinculação de regras, posso importá-lo corretamente e ele criará todos os objetos de esquema sem erros:
Isso não é muito útil, pois não quero continuar fazendo isso toda vez que migrar o banco de dados (não farei isso muitas vezes, mas o suficiente para que seja uma solução alternativa dolorosa).
Bacpac funcionando - duas referências de coluna e uma referência UDT hackeadas em um elemento SqlRule
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
Observação lateral: se eu reexportar esse banco de dados, ele voltará à forma original de 2 elementos SqlRule - um que contém as colunas e outro que contém a vinculação da regra.
O problema também acontece com o Az CLI:
PS> az version
{
"azure-cli": "2.56.0",
"azure-cli-core": "2.56.0",
"azure-cli-telemetry": "1.1.0",
"extensions": {
"storage-preview": "1.0.0b1"
}
}
PS> az sql db export `
--subscription "My Subscription" `
--resource-group "my-resource-group" `
--server "my-sql-server" `
--name "ProGet" `
--admin-user "my-admin-user" `
--admin-password "my-admin-password" `
--storage-uri "https://mystorageaccount.blob.core.windows.net/proget-db-backups/proget.bacpac" ``
--storage-key-type "StorageAccessKey" `
--storage-key "my storage key"
Baixar o blob e descompactá-lo mostra o problema com vários SqlRule Elements.
Se você chegou até aqui, obrigado pela leitura, e só para recapitular, minha pergunta no "tl; dr" no topo foi:
Qualquer ajuda será muito apreciada.
Estou usando o MariaDB 10.24.8 no Linux e estou encontrando este erro de sintaxe que tem me deixado perplexo nas últimas horas:
ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6
com esta consulta SQL para criar um procedimento armazenado:
USE getranke;
DROP PROCEDURE IF EXISTS insert_scan;
-- param scan INT: Barcode Value
-- out-param result INT: 0 = registered; 1 = DB is in inventory mode
CREATE PROCEDURE insert_scan(
IN scan INT,
OUT result INT
)
BEGIN
DECLARE stateName TEXT;
SELECT Name INTO stateName
FROM StateIDs
JOIN SystemState
ON StateIDs.ID = SystemState.FK_StateIDs_ID
WHERE SystemState.ID = 1;
IF stateName = 'Inventory' THEN
SET result = 1;
ELSE
SELECT scan;
INSERT INTO Scans (FK_Drinks_barcode, when_datetime)
VALUES (scan, NOW());
SET result = 0;
END IF;
END;
O que me irritou especialmente é que o Rust-Rover consegue executar essa consulta sem problemas, mas se eu executá-lo usando o console mysql/mariadb, ocorrerá esse erro. Também ocorre com outros front-ends como phpmyadmin.
Se precisar de mais informações, ficarei feliz em fornecê-las. Obrigado.
Tenho uma pergunta rápida: é seguro executar IndexOptimizea atualização de estatísticas SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDpara evitar o bloqueio conforme abaixo?
USE [DBA]
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on
EXECUTE [dbo].[IndexOptimize]
@Databases = 'XYZ',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@Indexes = 'XYZ.dbo.XYZ',
@StatisticsSample = 30,
@MaxDOP = 8,
@LogToTable = 'Y';
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off
Qual é a diferença entre a versão estável do Apache Cassandra e a versão de disponibilidade geral. Isso significa que a versão de disponibilidade geral pode ter mais bugs do que a versão estável? Ou é outra coisa. Por favor, esclareça quais são os critérios para esta segregação
Bom dia a todos. Recentemente, nosso plano de manutenção que reorganiza índices tem gerado erros, e isso ocorre porque os bloqueios no nível da página foram desativados em alguns índices por motivos desconhecidos. O trabalho é interrompido após a falha de 2 índices, então continuo tendo que executá-lo para descobrir quais consultas têm seus bloqueios de página desabilitados. Parece que apenas dois bancos de dados têm os bloqueios de página de seus índices desabilitados.
É possível ativar bloqueios de nível de página em todos os índices de um banco de dados por meio de uma consulta? Há muitas tabelas e fazer isso manualmente é uma dor.
eu tenho 2 tabelas como essas;
faturas
id | total_amount | currency | discount
pagamentos
id | invoice | amount
Como você pode imaginar, a tabela de pagamentos mantém registros do pagamento feito em uma fatura específica.
Preciso de uma consulta para obter faturas parcialmente pagas;
SELECT
invoices.*,
SUM(payments.amount) as paid
FROM
invoices
LEFT JOIN
payments ON payments.invoice = invoices.id
GROUP BY
invoices.id
Estou conseguindo receber o valor pago com esta consulta, mas também preciso fazer algo como
WHERE paid < total_amount AND paid > 0
Mas aqui diz "Coluna desconhecida 'paga' na 'cláusula where'". Como posso conseguir esse trabalho?
Todas essas 3 solicitações retornam NULL com MySQL 8 e SQLite 3.39:
SELECT 1 - NULL;
SELECT NULL - 1;
SELECT NULL - NULL;
Mas com o PostgreSQL 15, o último falha com:
error: operator is not unique: unknown - unknown
Qual é o código mais curto para que o PostgreSQL retorne NULL no último caso?
(Observe que com SELECT a.x - a.y FROM a;e
podem xambos yser NULL. O PostgreSQL não reclamará, pois pode determinar o tipo a partir das definições da coluna.)
Uma solução simples é apenas:
SELECT CASE
WHEN x IS NULL THEN NULL
WHEN y IS NULL THEN NULL
ELSE x - y
END;