Andrew Savinykh's questions

Temos um script que roda todas as noites, esta é a parte relevante do script:

---- Restore DB from file system
print ''
print '----------------------------------------------'
print 'Restoring Database: $(DbName)'
print '' 
use master
alter database [$(DbName)] set single_user with rollback immediate
restore database [$(DbName)] 
    from  disk = '$(BackupFile)' 
    with replace,
    move @datalogname TO @datapath,
    move @loglogname TO @logpath,
    stats = 5
alter database [$(DbName)] set multi_user
alter database [$(DbName)] set new_broker with rollback immediate
print '----------------------------------------------'

Na maioria das noites funciona. Em algumas noites, ele falha com a seguinte saída:

[04:00:29] :     [Step 2/7] ----------------------------------------------
[04:00:29] :     [Step 2/7] Restoring Database: MYDATABASE
[04:00:29] :     [Step 2/7]  
[04:00:29] :     [Step 2/7] Msg 3101, Level 16, State 1, Server MYSERVER, Line 83
[04:00:29] :     [Step 2/7] Exclusive access could not be obtained because the database is in use.
[04:00:29] :     [Step 2/7] Msg 3013, Level 16, State 1, Server MYSERVER, Line 83
[04:00:29] :     [Step 2/7] RESTORE DATABASE is terminating abnormally.
[04:00:29] :     [Step 2/7] Msg 1205, Level 13, State 68, Server MYSERVER, Line 89
[04:00:29] :     [Step 2/7] Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
[04:00:29] :     [Step 2/7] Msg 5069, Level 16, State 1, Server MYSERVER, Line 89
[04:00:29] :     [Step 2/7] ALTER DATABASE statement failed.
[04:00:29] :     [Step 2/7] ----------------------------------------------

A linha 83 é a instrução RESTORE. A linha 89 é a set multi_userinstrução.

Por que isso está acontecendo e como podemos garantir que seja sempre bem-sucedido.

ATUALIZAR

Nenhuma transação explícita é definida no script. O script é executado a sqlcmdpartir de um arquivo. Existem vários processos fora da VM do SQL Server que estão pesquisando o banco de dados regularmente, e meu palpite é que um deles consegue se intrometer no meio set single_usere restorequando isso acontece. Foi sugerido que isso pode ser resolvido ativando temporariamente uma regra no firewall local que proíbe conexões de entrada, mas gostaria de saber se existe uma solução somente SQL.

Em um sistema, há um processo noturno de carregamento de dados em que todas as chaves estrangeiras são descartadas, todas as tabelas são truncadas, preenchidas novamente a partir de uma fonte externa e as chaves estrangeiras são colocadas de volta no lugar.

No final deste processo, eu esperaria sp_updatestatsexecutar, para que as estatísticas de todos esses novos dados fossem atualizadas.

No entanto, ao inspecionar o código, notei a seguinte linha:

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

Você pode comparar isso com correr sp_updatestats? Quais são as diferenças e vantagens/desvantagens de um sobre o outro?

No meu banco de dados eu tenho uma Tasktabela. Esta tabela possui vários campos que são chaves estrangeiras para outras tabelas. Existem também algumas tabelas de links, que têm Taskem uma extremidade.

Quando uma nova tarefa é criada (ou uma tarefa antiga é atualizada), preciso ter certeza de que essas chaves estrangeiras existem e retornar um erro significativo se não existirem. Isso é importante porque a chamada se origina de uma API da web, então os consumidores realmente precisam saber por que sua chamada não funcionou, portanto, uma mensagem de erro detalhada (sem fornecer muito) é crucial.

Como há uma dúzia de chaves estrangeiras (e também algumas outras condições, como a conta atual do aplicativo do usuário estar ativa) que tornam a tarefa correta, em vez de tentar inserir e falhar, estou tentando validá-las antes de inserir.

É claro que, quando faço isso, preciso ter certeza de que, quando realmente inserir o registro, essas validações ainda serão verdadeiras.

Portanto, vamos supor que uma tarefa faça referência a um grupo. Da perspectiva da API da Web, o grupo é um dado de referência e retorna uma lista de chaves de grupo válidas em uma chamada anterior. Ele recebe um deles de volta quando cria ou atualiza uma tarefa como um GroupIdcampo no Taskobjeto.

Para ter certeza de que esta (e outras) chaves estrangeiras estão presentes no banco de dados, abro uma REPEATABLE READtransação e verifico a existência delas uma a uma antes de tentar inserir a tarefa. Em seguida, compilo uma lista daqueles que estão ausentes (se houver) e reverto a transação e os devolvo para que a API possa formar uma mensagem de erro significativa.

A questão é se REPEATABLE READo nível de isolamento é adequado neste caso. Paul White mencionou que muitas vezes não é .

Eu abro uma transação (leitura repetível) ( BEGIN TRAN) para fazer algum trabalho em determinados registros. A primeira coisa que faço é verificar se os dados que preciso alterar estão no banco de dados. Em alguns casos haverá e então prossigo para minhas alterações. Mas em alguns casos não haverá nada a fazer. Nesse caso, eu COMMIT TRANou ROLLBACK TRANe volto do procedimento armazenado. Neste momento nenhuma mudança foi feita nos dados ainda, então o efeito de commit e rollback é o mesmo.

Há alguma consideração que devo estar ciente sobre a escolha entre confirmação e reversão? Existe custo de desempenho diferente? Outras considerações?

Esta afirmação:

INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN  deleteme  ON deleteme.id = #t.id
WHERE deleteme.id IS NULL;

... falhará com uma violação de chave primária no cenário simultâneo (ou seja, ao inserir a mesma chave que está ausente em deleteme de vários threads ao mesmo tempo) com o nível de isolamento de transação padrão de READ COMMITTED:

Erro: violação da restrição PRIMARY KEY 'PK_DeleteMe'. Não é possível inserir a chave duplicada no objeto 'dbo.deleteme'.

Qual é a melhor forma de prevenir isso?

A tabela fica mais ou menos assim:

CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY ([id] ASC));

Atualizar

Dos comentários:

Por que você tem várias sessões, que obviamente podem extrair o mesmo id de algum lugar, usando a mesma tabela permanente sem ter algum tipo de chave de sessão para diferenciá-las? Mesmo sem um erro duplicado, como você saberá quais linhas pertencem a qual sessão?

Este é um procedimento armazenado que é chamado por um serviço externo que preenche dados nesta tabela. O serviço gera o ID do registro e não garante que não enviará os mesmos dados duas vezes. Ou envie os mesmos dados ao mesmo tempo.

O código do banco de dados deve descartar todos os registros com o mesmo id, se já existir um. O serviço nunca envia dois registros com o mesmo id no mesmo lote.

Como faço para modelar o relacionamento de zero ou um para zero ou um no Sql Server da maneira mais natural?

Existe uma tabela de 'Perigo' que lista os perigos em um local. Há uma tabela de 'Tarefas' para o trabalho que precisa ser feito em um site. Algumas tarefas são para corrigir um perigo, nenhuma tarefa pode lidar com vários perigos. Alguns perigos têm uma tarefa para corrigi-los. Nenhum perigo pode ter duas tarefas associadas a eles.

O abaixo é o melhor que eu poderia pensar:

CREATE TABLE [dbo].[Hazard](
  [HazardId] [int] IDENTITY(1,1) NOT NULL,
  [TaskId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Hazard] PRIMARY KEY CLUSTERED 
(
  [HazardId] ASC
))

GO

ALTER TABLE [dbo].[Hazard]  WITH CHECK ADD  CONSTRAINT [FK_Hazard_Task] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Task] ([TaskId])
GO


CREATE TABLE [dbo].[Task](
  [TaskId] [int] IDENTITY(1,1) NOT NULL,
  [HazardId] [int] NULL,
  [Details] [varchar](max) NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
(
  [TaskId] ASC
))

GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Hazard] FOREIGN KEY([HazardId])
REFERENCES [dbo].[Hazard] ([HazardId])
GO

Você faria isso diferente? A razão pela qual não estou satisfeito com esta configuração é que precisa haver uma lógica de aplicação para garantir que as tarefas e perigos apontem uns para os outros e não para outras tarefas e perigos e que nenhuma tarefa/perigo aponte para o mesmo perigo/tarefa outra tarefa/risco aponta.

Existe uma maneira melhor?

Quando faço o script de dados em meu banco de dados em Tarefas do SSMS->Gerar scripts e, em seguida, seleciono somente dados, verifica-se que as instruções de inserção são geradas em ordem diferente sempre que ocorre uma pequena alteração. Isso torna impossível para os desenvolvedores executar um programa diff com os dados do script anterior e ver as alterações de dados: todas as tabelas são sempre reembaralhadas, mesmo que nenhuma linha tenha sido inserida/excluída, de modo que um programa diff não pode realmente mostrar o poucas alterações de dados de maneira coerente.

Existe uma maneira de criar um script de dados em um banco de dados para que eles retornem aproximadamente a mesma ordem se não ocorrerem adições/exclusões de dados importantes que possam afetar a ordem?

Existe uma maneira de gerar um script de criação a partir de uma tabela existente puramente em T-SQL (ou seja, sem usar o SMO, pois o T-SQL não tem acesso ao SMO). Digamos que um procedimento armazenado que recebe um nome de tabela e retorna uma string que contém o script de criação para a tabela fornecida?

Agora deixe-me descrever a situação que estou enfrentando, pois pode haver uma maneira diferente de abordar isso. Eu tenho uma instância com várias dezenas de bancos de dados. Todos esses bancos de dados têm o mesmo esquema, todas as mesmas tabelas, índice e assim por diante. Eles foram criados como parte de uma instalação de software de terceiros. Eu preciso ter uma maneira de trabalhar com eles para que eu possa agregar dados deles de maneira ad-hoc. Boas pessoas do dba.se já me ajudaram aqui Como criar uma trigger em um banco de dados diferente?

Atualmente preciso encontrar uma maneira de fazer uma seleção de uma tabela em todos os bancos de dados. Gravei todos os nomes de banco de dados em uma tabela chamada Databaseese escrevi o seguinte script para executar uma instrução select em todos eles:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp

select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) = 
  N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'

DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0

DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int

SELECT TOP 1 @DatabaseNameToHandle = Name,
@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No

WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN

  DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
  EXEC @sql @statement

  SET @LastDatabaseID = @DatabaseIDToHandle
  SET @DatabaseIDToHandle = NULL

  SELECT TOP 1 @DatabaseNameToHandle = Name,
  @DatabaseIDToHandle = Database_Ref_No
  FROM Databasees
  WHERE Database_Ref_No > @LastDatabaseID
  ORDER BY Database_Ref_No
END

select * from #tmp
DROP TABLE #tmp

No entanto, o script acima falha com a seguinte mensagem:

Um valor explícito para a coluna de identidade na tabela '#tmp' só pode ser especificado quando uma lista de colunas for usada e IDENTITY_INSERT estiver ON.

Adicionando isso:

SET IDENTITY_INSERT #tmp ON

não ajuda, pois não consigo especificar a lista de colunas e mantê-la genérica.

No SQL, não há como desativar a identidade de uma determinada tabela. Você só pode soltar uma coluna e adicionar uma coluna, o que obviamente altera a ordem das colunas. E se a ordem das colunas mudar, você, novamente, precisa especificar a lista de colunas, que seria diferente dependendo da tabela consultada.

Então, eu estava pensando se eu pudesse obter o script de criação de tabela no meu código T-SQL, eu poderia manipulá-lo com expressões de manipulação de string para remover a coluna de identidade e também adicionar uma coluna para o nome do banco de dados ao conjunto de resultados.

Alguém pode pensar em uma maneira relativamente fácil de alcançar o que eu quero?

É possível criar um procedimento armazenado que crie um gatilho de tabela (DDL) em um banco de dados diferente daquele em que o próprio procedimento armazenado reside. Os bancos de dados estão na mesma instância do servidor. Se sim, então como?

Por exemplo, isso não funciona:

create PROCEDURE [dbo].[CreateTriggrer]
  @db varchar(60)
AS
BEGIN
  SET NOCOUNT ON;   
  declare @statement nvarchar(max) = N'CREATE TRIGGER [dbo].[TestTrigger]
   ON  [$database$].[dbo].[TestTable] 
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
  PRINT ''test''
END'

    set @statement = REPLACE(@statement,'$database$',@db)
    EXEC dbo.sp_executesql @statement = @statement

END

Quando chamado assim:

EXEC [dbo].[CreateTriggrer] @db = N'TestDatabase'

Ele retorna este erro:

Msg 2108, Nível 15, Estado 1, Procedimento TestTrigger, Linha 6
Não é possível criar o gatilho em 'TestDatabase.dbo.TestTable' porque o destino não está no banco de dados atual.

O que é justo. Existe uma maneira de conseguir o que eu quero?

O procedimento armazenado sp_add_alert pode reagir a diferentes mensagens do sistema e executar um trabalho em resposta. Ele também pode notificar uma pessoa com o texto da mensagem por e-mail, pager ou net send.

Mas como passo a sysmessagemensagem (correspondente ao evento que gerou o alerta) não para uma pessoa, mas para o job que é executado em resposta ao alerta?

Vamos considerar esta mensagem:

select [description] from sysmessages where msglangid = 1033 and error = 829

Isso renderá:

ID do banco de dados %d, a página %S_PGID está marcada como RestorePending, o que pode indicar corrupção de disco. Para se recuperar desse estado, execute uma restauração.

Eu gostaria de receber esta mensagem no trabalho, para que o trabalho saiba o que %d e %S_PGID causaram um problema.

Eu tenho um procedimento armazenado que consulta a exibição sys.dm_exec_requests . No procedimento armazenado, a exibição retorna apenas uma linha, enquanto o procedimento armazenado precisa ver todos eles. O artigo do MSDN na exibição diz que o que é retornado depende da permissão do usuário:

Se o usuário tiver permissão VIEW SERVER STATE no servidor, o usuário verá todas as sessões em execução na instância do SQL Server; caso contrário, o usuário verá apenas a sessão atual.

O procedimento armazenado é, na verdade, um procedimento armazenado de ativação de uma fila do intermediário:

CREATE QUEUE test_queue
  WITH 
  STATUS = ON,
  RETENTION = OFF ,
  ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = test_procedure,
    MAX_QUEUE_READERS = 1, 
    EXECUTE AS SELF ),
POISON_MESSAGE_HANDLING (STATUS = ON) 
ON [PRIMARY]

Quando li o artigo do MSDN, mudei

EXECUTE AS SELF

para

EXECUTE AS 'dbo'

o que não fez nenhuma diferença. sys.dm_exec_requestsainda retornaria uma única linha.

Também tentei fazer

EXECUTE AS OWNER

O que também não fazia diferença. Pelo que entendi, o proprietário do procedimento armazenado é quem o criou, que fui eu. E como estou em uma função de administrador de sistema, isso deveria estar funcionando, mas não está. Agradeço qualquer dica de solução de problemas.

Em particular, gostaria de saber como listar as permissões de um servidor para um determinado usuário para que eu pudesse verificar se eles incluem a permissão 'VIEW SERVER STATE' conforme o artigo.

A parte confusa é que o usuário , pelo que entendi, está no nível do banco de dados, então não está claro para mim como ele pode ter permissão de servidor. Se você pudesse esclarecer isso, seria ótimo também.

Em um post de blog de 2008, o famoso Kalen Delaney escreveu:

Qual é a diferença entre estatísticas e índices? As estatísticas criadas automaticamente indicam que você deve criar um índice?

Confira a resposta a esta pergunta em SQLCommunity.com

Infelizmente SQLCommunity.com não existe mais. Mas a resposta a esta mesma pergunta eu gostaria muito de saber. Principalmente ao segundo.

Atualização : Um artigo perspicaz está aqui: http://msdn.microsoft.com/en-us/library/ms190397.aspx

Eu crio dois bancos de dados, uma tabela no segundo banco de dados e um procedimento armazenado no primeiro banco de dados. O banco de dados cruzado do procedimento armazenado acessa a tabela. Eu crio um login do sql server e também mapeio esse login para um usuário em cada um dos bancos de dados. Dou permissão db_owner aos usuários. Aqui está o script que o realiza (estou conectado como administrador do sistema SQL ao executar o script):

USE [master]
GO

CREATE DATABASE [TestDatabase1] ON  PRIMARY 
( NAME = N'TestDatabase1', FILENAME = N'd:\database\TestDatabase1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDatabase1_log', FILENAME = N'd:\database\TestDatabase1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

CREATE DATABASE [TestDatabase2] ON  PRIMARY 
( NAME = N'TestDatabase2', FILENAME = N'd:\database\TestDatabase2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TestDatabase2_log', FILENAME = N'd:\database\TestDatabase2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE [TestDatabase2]
GO

CREATE TABLE [dbo].[TestTable](
    [Test] [int] NULL
) ON [PRIMARY]
GO

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[TestDatabase1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDatabase1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [TestDatabase1]
GO
ALTER USER [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TestDatabase2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [TestDatabase2]
GO
ALTER USER [TestUser] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [TestDatabase2]
GO
EXEC sp_addrolemember N'db_owner', N'TestUser'
GO
USE [TestDatabase1]
GO
EXEC sp_addrolemember N'db_owner', N'TestUser'
GO

Feito isso, eu me conecto ao servidor com SSMS sob a identidade do TestUser. Eu executo o procedimento armazenado TestSp no SSMS e ele é bem-sucedido.

Agora vou em frente e crio um trabalho que executa o mesmo procedimento armazenado. Eu faço assim (estou conectado como administrador de sistema SQL ao executar o script):

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestJob', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'SELECT TOP 1 * FROM TestDatabase2.dbo.TestTable', 
        @database_name=N'TestDatabase1', 
        @database_user_name=N'TestUser', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Depois que o trabalho é criado, eu o executo do SSMS (estou conectado como um administrador de sistema SQL ao fazer isso). O trabalho falha com o seguinte erro:

Date        10/04/2012 3:26:31 p.m.
Log     Job History (TestJob)

Step ID     1
Server      obfuscated
Job Name        TestJob
Step Name       TestStep
Duration        00:00:00
Sql Severity        14
Sql Message ID      916
Operator Emailed        
Operator Net sent       
Operator Paged      
Retries Attempted       0

Message
Executed as user: TestUser. The server principal "TestUser" is not able to access the database "TestDatabase2" under the current security context. [SQLSTATE 08004] (Error 916).  The step failed.

E aqui está o script de limpeza para remover os objetos de banco de dados criados pelos scripts acima:

USE [master]
GO

alter database TestDatabase1 set single_user with rollback immediate
GO

alter database TestDatabase1 set multi_user
GO

alter database TestDatabase2 set single_user with rollback immediate
GO

alter database TestDatabase2 set multi_user
GO

drop database TestDatabase1
GO

drop database TestDatabase2
GO

USE [msdb]
GO

declare @job_id uniqueidentifier
SELECT @job_id = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'TestJob'

EXEC msdb.dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=1
GO

DROP LOGIN [TestUser]
GO

Perguntas:

1. Por que resultados diferentes de um trabalho e do SSMS?
2. Como faço para o trabalho funcionar (em vez de falhar)?

ATUALIZAÇÃO 1

Usando o google-fu avançado, consegui determinar que uma das respostas para a pergunta 2 pode ser esta:

ALTER DATABASE TestDatabase1 SET TRUSTWORTHY ON;
GO
RECONFIGURE WITH OVERRIDE;
GO

A pergunta 1 continua sem resposta

ATUALIZAÇÃO 2

Ok, acho que entendi, postei uma resposta abaixo. No entanto, uma coisa ainda não está clara. Esse erro do job, não acontece no SQL 2005. Acontece no SQL 2008 mas não no SQL 2005. Obviamente algo mudou. Alguém sabe o que exatamente foi a mudança?

Eu tenho uma tabela que erroneamente tem a opção ANSI_NULLS definida como OFF. Eu gostaria de configurá-lo para esta mesa. A tabela tem milhões de linhas de dados. É possível alterar esta opção sem criar uma nova tabela com ANSI_NULLS ON, mover todos os dados para esta nova tabela e renomear esta tabela. Atualmente, o tempo de inatividade para usar esta solução a torna impraticável. Existe outra maneira?

Estou olhando para um impasse, que acontece quando dois processos executam uma instrução UPDATE na mesma tabela. Ambas as transações são implícitas, o que significa que abrangem apenas a instrução atual, o que, por sua vez, significa que os bloqueios que estou vendo não são um transporte de uma instrução anterior.

Há dois bloqueios e dois processos envolvidos no impasse. Um dos dois bloqueios é um bloqueio de página na tabela (um processo possui bloqueio de atualização, o outro deseja bloqueio exclusivo de intenção) e o segundo é o bloqueio de chave no índice clusterizado da tabela (um processo possui bloqueio exclusivo e o outro deseja o bloqueio de atualização).

Agora, os campos que estão sendo atualizados nas duas instruções UPDATE não fazem parte do índice clusterizado.

Ainda assim, estou me perguntando se o fato de esse índice clusterizado ter toneladas de valores duplicados pode contribuir para o impasse?

Me desculpe por não postar o rastreamento de deadlock, as instruções de atualização, a definição da tabela e do índice, é muito para ofuscar.

Minha pergunta não é como resolver meu impasse, minha pergunta é sobre o efeito do índice clusterizado de baixa cardinalidade na probabilidade de impasses em princípio.

Preciso listar as colunas de uma tabela na ordem de definição da tabela:

select * from syscolumns
where id = object_id('MyTable')
--order by colid

Ao examinar syscolumnsas tabelas, duas colunas parecem relevantes: colide colorder. O artigo do MSDN sobre syscolumns diz:

colid    | smallint | Column or parameter ID.
colorder | smallint | Identified for informational purposes only. 
                    | Not supported. Future compatibility is not guaranteed.

eu tentei correr

select * from syscolumns where colorder <> colid

que não rendeu linhas, e isso me faz pensar que essas colunas têm os mesmos valores na maioria das vezes.

Parece que a aposta mais segura é usar colid. No entanto, gostaria de saber: existe alguma diferença entre essas duas colunas e, se houver, qual é essa diferença?

Também o artigo do MSDN, não confirma, que colid reflete a ordem da definição da tabela. Embora seja razoável supor que seja esse o caso, você poderia me informar, se tiver certeza de que é o caso, como você sabe disso?

Eu estava pesquisando outra coisa quando me deparei com essa coisa. Eu estava gerando tabelas de teste com alguns dados e executando diferentes consultas para descobrir como diferentes maneiras de escrever consultas afetam o plano de execução. Aqui está o script que usei para gerar dados de teste aleatórios:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('t') AND type in (N'U'))
DROP TABLE t
GO

CREATE TABLE t 
(
 c1 int IDENTITY(1,1) NOT NULL 
,c2 int NULL
) 
GO

insert into t
select top 1000000 a from
(select t1.number*2048 + t2.number a, newid() b
from [master]..spt_values t1 
cross join  [master]..spt_values t2
where t1.[type] = 'P' and t2.[type] = 'P') a
order by b
GO

update t set c2 = null
where c2 < 2048 * 2048 / 10
GO


CREATE CLUSTERED INDEX pk ON [t] (c1)
GO

CREATE NONCLUSTERED INDEX i ON t (c2)
GO

Agora, com esses dados, chamei a seguinte consulta:

select * 
from t 
where 
      c2 < 1048576 
   or c2 is null
;

Para minha grande surpresa, o plano de execução gerado para esta consulta foi este arquivo . (Desculpe pelo link externo, é muito grande para caber aqui).

Alguém pode me explicar o que há com todas essas " varreduras constantes " e " escalas de computação "? O que está acontecendo?

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
       |--Merge Interval
       |    |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
       |         |--Compute Scalar(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))
       |              |--Concatenation
       |                   |--Compute Scalar(DEFINE:([Expr1005]=NULL, [Expr1006]=NULL, [Expr1004]=(60)))
       |                   |    |--Constant Scan
       |                   |--Compute Scalar(DEFINE:([Expr1008]=NULL, [Expr1009]=(1048576), [Expr1007]=(10)))
       |                        |--Constant Scan
       |--Index Seek(OBJECT:([t].[i]), SEEK:([t].[c2] > [Expr1010] AND [t].[c2] < [Expr1011]) ORDERED FORWARD)