ГлавнаяРазноеMs sql временные таблицы

MS SQL Временная циклизация таблицы. Ms sql временные таблицы


server - MS SQL временной таблица неудача обновления

Я не могу найти что-нибудь, чтобы объяснить, почему при вызове SP, который делает вставки или обновления в зависимости от того, если записи уже существует на временные таблицы я получаю

модификации данных не удалась на системы- versioned table «MYDB.dbo.TemporalExample», поскольку время транзакции было раньше времени начала периода для затронутых записей.

какая доза это означает? Кажется, что это случается некоторое время, я задаюсь вопросом, не связано ли это с тем, что im работает многопоточный код и azure sql, но не любит взаимные соединения с одной и той же таблицей, когда это временная? Им будет хотя рамки сущности (последняя версия), но я сомневаюсь, что это вопрос

мой зр только это

create PROCEDURE mysp @ID bigint, @a FLOAT, @b NVARCHAR(10), @c DECIMAL(19, 4) AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SET NOCOUNT ON BEGIN TRY IF EXISTS (SELECT TOP 1 Id FROM my_Temporal_Table WITH (NOLOCK) WHERE id = @ID AND a = @a AND b = @b) BEGIN UPDATE my_Temporal_Table SET Id = @ID, a = @a, b = @b c = @c DateModified = GETUTCDATE() WHERE Id = @Id END ELSE BEGIN INSERT INTO my_Temporal_Table (Id, a, b, c, DateModified) VALUES (@ID, @a, @b, @c , GETUTCDATE()) END END TRY BEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() -- Use RAISERROR inside the CATCH block to return error -- information about the original error that caused -- execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ) END CATCH

Update мой височной скрипт создания таблицы:

CREATE TABLE [Temporal]( [TemporalId] [bigint] IDENTITY(1,1) NOT NULL, [Payment] [decimal](19, 4) NOT NULL, [DateModified] [datetime2](7) NOT NULL, [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL, [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL, CONSTRAINT [TemporalId] PRIMARY KEY CLUSTERED ([TemporalId] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON), PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]) )WITH( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [Car2].[TemporalHistory]) )

может кто-то объяснить, почему я могу увидеть эту проблему, что это значит и что еще более важно, как я могу ее исправить?

благодаря

stackoverrun.com

Сравнение временных таблиц и таблиц переменных.

Существует много путаницы о том, что такое таблицы переменные(Table Variables) и чем они не являются. А так же как они соотносятся с временными таблицами(Temporary Tables). Я хочу внести свою лепту в объяснения различий между двумя этими типами таблиц. И в развенчивании нескольких мифах об этих объектах.

Таблицы переменные

Таблицы переменные впервые появились в SQL Server 2000. Так что такое в действительности таблицы переменные? Microsoft определяет это в BOL как переменная типа таблица. Это определение включает в себя определения колонок, имен, типов данных и ограничений. Возможно использовать только следующие типы ограничений: PRIMARY KEY, UNIQUE, NULL, и CHECK. А FOREIGN KEY недоступен для использования. Таблица определяется с помощью инструкции DECLARE.

  • Этот тип таблиц имеет четко определенные рамки видимости. Таблица видна в текущем пакете инструкций, но не доступна в процедурах и функциях, вызванных из текущего пакета. Таблицы уничтожаются по окончанию выполнения пакета.
  • Транзакции с использованием переменных table продолжаются только во время процесса обновления соответствующей переменной table. Поэтому переменные table реже подвергаются блокировке и требуют меньших ресурсов для ведения журналов регистрации.
  • Откат транзакции не влияет на содержимое таблицы переменной.
Таблица переменная может быть использована, в пределах своей области, как и любая другая таблице. В частности, он может быть использован в виде таблицы или табличного выражения везде, где это допустимо. Почти во всех операциях SELECT, а также UPDATE, INSERT, DELETE. Но они не могут быть использованы в выражении SELECT INTO [select_list] table_variable, и в SQL Server 2000, они не могут быть использованы в выражении INSERT INTO table_variable EXEC stored_procedure.

Что вы не можете делать с этими таблицами?

  • Хотя переменная table и является переменной, ей не может быть присвоена другая таблица переменная.
  • Ограничение CHECK, значения по умолчанию и вычисляемые столбцы в определении таблиц не могут ссылаться на UDF.
  • Вы не можете создавать именованные ограничения.
  • Вы не можете использовать инструкцию TRUNCATEс такими таблицами.
  • Вы не можете вставлять явно определенные значения в столбцы с идентификаторами. То есть не поддерживается SET IDENTITY_INSERT ON.

Временные таблицы

Прежде чем перейдем к временным таблицам нужно определить, что такое сессии. Сессия - это просто подключение к базе данных. В SSMS каждая октрытая вкладка имеет свою свое собственное подключение к базе. Приложение может иметь одно или более подключений, так же приложение может держать подключение открытым все время или открывать и закрывать его при необходимости.

Так что такое временная таблица? Просматривая BOL мы можем обнаружить, что временная таблица, создается точно так же как и обычные за исключением

  • Имя такой таблице ограничено 116 символами. Это делается для того, что бы ядро базы могло идентифицировать одинаковые временные таблицы, созданные в разных сессиях во одно и тоже время. Для внутренней идентификации ядро добавляет цифровой суффикс к имени таблицы.
  • Локальные временные таблицы, имеющие в имени знак #, видимы только для текущего соединения, в точке создания таблицы и внутри вложенных хранимых процедур.
  • Такие таблицы будут удалены в одном из тех случаев.
    • Явное удаление с помощью инструкции DROP TABLE.
    • Если таблица является локальной временной таблицей определенной в хранимой процедуре, то по факту завершения выполнения процедуры.
    • Все остальные таблицы будут удалены по факту закрытия сессии.
  • Глобальные временные таблицы, имеющие в имени двойной знак ##, видимы для всех сессий. Вы должны всегда проверять существование такой таблицы перед тем как попытаться создать ее, иначе вы получите ошибку дублирования объектов.
  • Глобальные временные таблицы удаляются, когда закрывается сессия, в которой они были созданы, а все оставшиеся сессии прекращают ссылаться на таблицу.
  • Эта привязка работает на уровне инструкций.
  • Временная таблица не может быть секционирован.
  • FOREIGN KEY не может быть применен к временным таблицам.
  • Столбцы временной таблицы не могут быть определены с помощью UDDT, не определенных в temdb, вы должны использовать нативные типы данных (UDDTs определенные в вашей БД и в tempdb). Так как при старте экземпляра сервера база tempdb пересоздается, вы должны использовать специальные хранимые процедуры для создания этих типов. Альтернативным вариантом может быть изменение базы model.
  • Колонки с типом xml не могут быть определены с использованием xml collection, пока коллекция не будет добавлена в tempdb
Временная таблица может быть создана с помощью выражения CREATE TABLE. Или выборкой в таблицу с помощью инструкции SELECT [select_list] INTO #table, вы так же можете использовать предложение INSERT INTO #table EXEC stored_procedure при работе с временными таблицами.

Временные таблицы могут иметь именованные ограничения. Однако, если 2 пользователя выполняют одну и туже процедуру одновременно, при выполнении второй возникнет ошибка There is already an object named [objectname] in the database. В таком случае вы должны будете создавать неименованные ограничения, а ядро бд само даст им уникальные идентификаторы.

Мифы

А теперь самое интересно: самые распространенные мифы о временных таблицах и таблицах переменных.

Мифы № 1 и 2: таблицы переменные находятся только в памяти, а временные таблицы располагаются на диске.

Оба утверждения ложны. Оба типа таблиц будут созданы и обрабатываться в памяти, если будет достаточно свободного пространства. Тем не менее, они могут в любой момент выгружены на диск.

Миф № 3: Таблицы переменные не могут иметь индексы.

Это так же ошибочное утверждение. Почему оно возникло? А все дело в том, что если таблица переменная создана, то она не может участвовать ни в каких DDL выражениях. Например в CREATE INDEX. Однако вы можете создать индексы ассоциированные с PRIMARY KEY или UNIQUE, если эти ограничения будут определены как часть определения таблицы. Например

declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

Этот скрипт создает таблицу с первичным ключом на столбце RowID. С этим ключом будет связан кластерный индекс так же на колонке RowID.

Еще немного фактов.

  • SQL Server не может строить статистику для таблиц переменных, в отличии от временных таблиц. Это значит что план выполнения для таблицы переменной всегда будет подразумевать, что в таблице всего одна запись. Очевидно, что это обозначает, что сервере не сможет всегда выбирать оптимальный план.
  • Как уже упоминалось ранее, вы не можете выполнять любые DDL операции с таблицами переменными. Например, вам необходимо заполнить таблицу, а затем добавить индекс или столбец, но все это можно сделать с временной таблицей.
  • Невозможно вставить данные в таблицу переменную с помощью команды SELECT INTO, однако так можно поступить с временной таблицей.
  • В SQL Server 2008, вы можете передать табличную переменную в процедуру, если вы определили UDTT и этот тип является параметром процедуры. Но в более ранних версиях SQL Server'a это сделать невозможно.
  • Область применения: таблицы переменные видны только в текущем пакете инструкций, и не видны в вызываемых процедурах. Локальные временные таблицы видны в текущей сессии после создания, в том числе вложенных процедур, однако они не будут видны в родительских процедурах. Глобальные временные таблицы видны все сеансы до закрытия сессии в которой таблица была объявлена и прекращения ссылок из других сессий.
  • Collation: таблицы переменные используют collation текущей базы данных. Временные таблицы использовать collation база tempdb. Если они не совместимы, то вам ghbltncz указать collation для использования в любом запросе.
  • Если вы хотите использовать таблицы переменные в динамическом SQL коде, то вы должны определить таблицу в этом же коде. В то время как временная таблица должна быть просто определена заранее.

Что я должен использовать?

Microsoft рекомендует использовать таблицы переменные, если количество строк, которые вы собираетесь вставлять в таблицу очень мало. Большинство "интернет гуру" скажет вам, что количество записей должно быть меньше 100, так как это то количество, при котором отсутствие статистики начинают снижать производительность запроса - но они также скажут вам протестировать вашу задачу используя оба типа таблиц. Если вы можете использовать индекс полученный при определении PRIMARY KEY или UNIQUE на таблице переменной, то вы можете получить прекрасную производительность с таблицей содержащей десятки тысяч строк. До тех пор пока вы не используете ее с инструкцией join. Когда вы начнете ее джойнить с другой таблицей, отсутствие статистики приведет к большим проблемам с производительностью. Так же если вам понадобится что-то изменить в определении таблицы по ходу пьесы, то сделать это можно будет только с временной таблицей.

Оба типа таблиц служат для достижения похожих целей, поэтому нет какого-то конкретного ответа на вопрос какие таблицы лучше? Каждый случай должен рассматриваться отдельно и исходя из требований и результатов тестов можно будет определить, что нужно использовать в данной ситуации

billibook.blogspot.com

sql - MS SQL Временная циклизация таблицы

Вам нужно использовать Recursive CTE

;WITH DATA AS (SELECT * FROM (VALUES ('ALI','ABU'), ('JOSH','LIM'), ('JAMES','KAREN'), ('LIM','JERRY'), ('JERRY','GEM')) TC(EMP_ID, EMP_L1)), REC_CTE AS (SELECT EMP_ID, EMP_L1, Cast(EMP_L1 AS VARCHAR(8000)) AS PARENT, LEVEL = 1 FROM DATA UNION ALL SELECT D.EMP_ID, D.EMP_L1, Cast(RC.PARENT + '.' + D.EMP_L1 AS VARCHAR(8000)), LEVEL = LEVEL + 1 FROM DATA D JOIN REC_CTE RC ON RC.EMP_ID = D.EMP_L1) SELECT TOP 1 WITH TIES EMP_ID, EMP_L1 = COALESCE(Parsename(PARENT, 1), ''), EMP_L2 = COALESCE(Parsename(PARENT, 2), ''), EMP_L3 = COALESCE(Parsename(PARENT, 3), ''), EMP_L4 = COALESCE(Parsename(PARENT, 4), '') FROM REC_CTE ORDER BY Row_number()OVER(PARTITION BY EMP_ID ORDER BY LEVEL DESC) OPTION (MAXRECURSION 0)

Результат:

╔════════╦════════╦════════╦════════╦════════╗ ║ EMP_ID ║ EMP_L1 ║ EMP_L2 ║ EMP_L3 ║ EMP_L4 ║ ╠════════╬════════╬════════╬════════╬════════╣ ║ ALI ║ ABU ║ ║ ║ ║ ║ JAMES ║ KAREN ║ ║ ║ ║ ║ JERRY ║ GEM ║ ║ ║ ║ ║ JOSH ║ LIM ║ JERRY ║ GEM ║ ║ ║ LIM ║ JERRY ║ GEM ║ ║ ║ ╚════════╩════════╩════════╩════════╩════════╝

Примечание: В этом случае может быть не более 4 уровней. Чтобы разделить данные на разные столбцы, я использовал функцию PARSENAME, которая не будет работать, если у вас будет более 4 уровней.

Если вы не хотите разделить родителей на разные столбцы, удалите PARSENAME и выберите только столбец PARENT.

qaru.site

Временные таблицы в sql-сервере? MS SQL Server

Существует много применений для временных таблиц. Они могут быть очень полезны при обработке данных в сложных запросах. Ваш вопрос нечеткий, и на самом деле нет ответа, но я связываюсь с некоторой временной табличной документацией.

Они имеют почти одинаковые возможности таблицы, включая ограничения и индексирование. Они могут быть глобальными или ограничены текущим объемом. Они также могут быть неэффективными, поэтому будьте осторожны, как всегда.

http://www.sqlservercentral.com/articles/T-SQL/temptablesinsqlserver/1279/ http://msdn.microsoft.com/en-us/library/aa258255%28SQL.80%29.aspx

При написании кода T-SQL вам часто нужна таблица, в которой временно хранить данные, когда приходит время для выполнения этого кода. У вас есть четыре параметра таблицы: обычные таблицы, локальные временные таблицы, глобальные временные таблицы и переменные таблицы. Каждый из четырех вариантов таблицы имеет свою собственную цель и использование, и каждый из них имеет свои преимущества и проблемы:

* Normal tables are exactly that, physical tables defined in your database. * Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. * Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database. * Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Какой из них использовать:

* If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables. * If you need to create indexes on it then you must use a temporary table. * When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.

Пожалуйста, обратитесь к этой странице http://www.sqlteam.com/article/temporary-tables

Временные таблицы могут быть созданы во время выполнения и могут выполнять все виды операций, которые может выполнять одна нормальная таблица. Но, исходя из типов таблиц, объем ограничен. Эти таблицы создаются внутри базы данных tempdb.

SQL Server предоставляет два типа временных таблиц, основанных на поведении и области действия таблицы. Эти:

• Локальная таблица темпов

• Глобальная таблица темпов

Local Temp Table Локальные временные таблицы доступны только для текущего подключения для пользователя; и они автоматически удаляются, когда пользователь отключается от экземпляров. Локальное временное имя таблицы отображается с символом « хэш» ( «#» ). Глобальная таблица Temp Глобальные временные имена таблиц начинаются с двойного хэша ( «##» ). После того, как эта таблица была создана соединением, как постоянная таблица, она доступна любому пользователю любым подключением. Его можно удалить только после того, как все соединения были закрыты.

Когда использовать временные таблицы?

• Когда мы делаем большое количество манипуляций с строками в хранимых процедурах. • Это полезно для замены курсора . Мы можем сохранить данные набора результатов в временную таблицу, затем мы можем манипулировать данными оттуда. • Когда у нас сложная операция объединения .

Вопросы, которые следует помнить перед использованием временных таблиц –

• Временная таблица, созданная на tempdb SQL Server. Это отдельная база данных. Таким образом, это дополнительные накладные расходы и может вызвать проблемы с производительностью. • Количество строк и столбцов должно быть как минимум необходимым. • Таблицы необходимо удалить, когда они будут выполнены с их работой.

Альтернативный подход: Таблица Variable-

Альтернативой временной таблицы является переменная таблицы, которая может выполнять все виды операций, которые мы можем выполнять в таблице Temp. Ниже приведен синтаксис использования переменной Table.

Когда использовать таблицу Variable Over Temp Table –

Tablevariable всегда полезен для меньшего количества данных. Если результирующий набор возвращает большое количество записей, нам нужно перейти на временную таблицу.

sqlserver.bilee.com