Tabele tymczasowe służą jako obiekty pomocnicze do tymczasowego przechowywania danych, kontenerem dla tych tabel jest baza TEMPDB.
Baza tworzona jest poprzez kopiowanie bazy model przy każdym restarcie SQL Server. Tabele tymczasowe tworzymy takimi samymi metodami jak zwykłe tabele (CREATE TABLE, poprzez SELECT… INTO).
Na tabelach tymczasowych można zakładać klucze z wyjątkiem klucza obcego (FOREGIN KEY), oraz wszystkie constraints.
Tabele tymczasowe domyślnie używają collation bazy tempdb, jeżeli są tworzone przy pomocy INTO pola mają collation tabeli źródłowej.
Ze względu na obszar dostępu do tabeli możemy je podzielić na:
TABELE LOKALNE, rozpoznawalne po prefiksie #
Widoczne w wewnątrz sesji i niszczone po zakończeniu jej.
TABELE GLOBALNE, rozpoznawalne po prefiksie ##
Widoczne we wszystkich sesjach, brak możliwości ograniczenia dostępu do nich i niszczone po zakończeniu sesji która je utworzyła.
Czy na pewno nie możemy dostać się do tabeli tymczasowe lokalnej
Możemy obejrzeć jakie tabele tymczasowe zostały stworzone
Tworzymy tabelkę na SPID 52
SELECT @@SPID as spid
CREATE TABLE #tmp (pole varchar(20))
go
INSERT #tmp
SELECT 'abcdefghijk'
go 3
--------------------------
spid
------
53
(1 row(s) affected)
Beginning execution loop
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Batch execution completed 3 times.
Sprawdzamy
select count(*) from #tmp
------------------------
3
Łączymy się innym spidem
SELECT @@SPID spid
select * from #tmp
go
------------------------
spid
------
51
(1 row(s) affected)
Msg 208, Level 16, State 0, Line 3
Invalid object name '#tmp'.
Tabela jest niewidoczna, możemy ja zobaczyć zapytaniem
select name,id from tempdb.sys.sysobjects
where xtype ='U'
and name like '#tmp%'
---------------------------
name id
-------------------------------------------------------------------------------------------------------------------------------- -----------
#tmp________________________________________________________________________________________________________________00000000A457 77360186
Do nazwy tabeli dodawane są “podkreślenia” i unikalny numer który jest inkrementacją globalną bazy tempdb. Powodują unikalność nazwy w globalnym znaczeniu, Nazwa tabeli składa się z 128 znaków:
pierwszy znak #
116 znaków na nazwę tabeli, jeżeli jest krótsza system "dopełnia" ją znakiem"_"
12 znaków na numer identyfikujacy sesję.
Jeżeli na spid 51 założymy tabelę #temp będzie ona w bazie pod inną nazwą
CREATE TABLE #tmp (pole varchar(20))
go
INSERT #tmp
SELECT 'abcdefghijk'
go 3
select name,id from tempdb.sys.sysobjects
where xtype ='U'
and name like '#tmp%'
---------------------------
name id
-------------------------------------------------------------------------------------------------------------------------------- -----------
#tmp________________________________________________________________________________________________________________00000000A457 77360186
#tmp________________________________________________________________________________________________________________00000000A458 93360243
Możemy zobaczyć ile wierszy jest w takiej tabeli
SELECT @@SPID spid,t.name,
t.[object_id],
p.[rows]FROM
tempdb.sys.tables t
JOIN tempdb.sys.partitions p
ON t.[object_id] = p.[object_id]
WHERE t.name LIKE N'#tmp%';
-------------
spid name object_id rows
------ ---------------------------------------------
51 #tmp__ …. _____00000000A457 77360186 3
Możemy znależć miejsce na dysku w którym znajdujż się dane z tabeli i odczytać dane. Poniżej moja procedura która przeczytać z dysku dane i złożyć je powrotem w tabelę.
CREATE PROCEDURE rk_dajtemp @nazwa varchar(500)
AS
SET NOCOUNT on
DECLARE @sql varchar(500)
DECLARE @PageFID int
DECLARE @PagePID int
DECLARE @objectid int
CREATE TABLE #dbcc_ind(ROWID int IDENTITY(1,1) PRIMARY KEY,
PageFID smallint,
PagePID int,
IAMFID int,
IAMPID int,
ObjectID int,
IndexID int,
PartitionNumber bigint,
PartitionID bigint,
Iam_Chain_Type varchar(80),
PageType int,
IndexLevel int,
NexPageFID int,
NextPagePID int,
PrevPageFID int,
PrevPagePID int)
CREATE TABLE #kolumny(ColumnID int PRIMARY KEY,
Name varchar(800))
CREATE TABLE #dbcc_page(ROWID int IDENTITY(1,1) PRIMARY KEY,
ParentObject varchar(500),
Object varchar(500),
Field varchar(500),
Value varchar(Max))
CREATE TABLE #pomoc(ROWID int PRIMARY KEY,
Page varchar(100),
Slot varchar(300),
Object varchar(300),
FieldName varchar(300),
Value varchar(6000))
select @objectid=id from tempdb.sys.sysobjects
where xtype ='U'
and name like @nazwa+'________________%'
SELECT @sql = 'DBCC IND(tempdb, ' + CONVERT(varchar(20), @objectid) +
', 1) WITH NO_INFOMSGS'
INSERT INTO #kolumny
SELECT ColID,Name
FROM tempdb..syscolumns
WHERE id =@objectid
DBCC TRACEON(3604) WITH NO_INFOMSGS
INSERT INTO #dbcc_ind
EXEC (@sql)
DECLARE kursorek CURSOR FOR
SELECT PageFID, PagePID FROM #dbcc_ind
WHERE PageType = 1
OPEN kursorek
FETCH NEXT FROM kursorek INTO @PageFID, @PagePID
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE #dbcc_page
SELECT @sql = 'DBCC PAGE (tempdb, ' +
CONVERT(varchar(20), @PageFID) +
',' +
CONVERT(varchar(20), @PagePID) +
', 3) WITH TABLERESULTS, NO_INFOMSGS '
INSERT INTO #dbcc_page
EXEC (@sql)
DELETE FROM #dbcc_page
WHERE Object NOT LIKE 'Slot %'
OR Field = ''
OR Field IN ('Record Type', 'Record Attributes')
OR ParentObject in ('PAGE HEADER:')
INSERT INTO #pomoc
SELECT ROWID, cast(@PageFID as varchar(20)) + ':' + CAST(@PagePID as varchar(20)),
ParentObject, Object, Field, Value FROM #dbcc_page
FETCH NEXT FROM kursorek INTO @PageFID, @PagePID
END
CLOSE kursorek
DEALLOCATE kursorek
SELECT @sql = '
SELECT ' +
STUFF(CAST((SELECT ',[' + Name + ']'
FROM #kolumny
ORDER BY ColumnID FOR XML PATH('')) AS varchar(MAX)), 1,1,'')+'
FROM (SELECT CONVERT(varchar(20), Page) + CONVERT(varchar(500),Slot)
p, FieldName x_FieldName_x, Value x_Value_x FROM #pomoc) Tab
PIVOT(MAX(Tab.x_Value_x) FOR Tab.x_FieldName_x IN( '
+ STUFF((SELECT ',[' + Name + ']' FROM #kolumny order by ColumnID for xml path('')), 1,1,'') + ' )
) AS pvt'
EXEC (@sql)
Wywołujemy procedurę
SELECT @@SPID spid
execute rk_dajtemp '#tmp'
--------------------------
spid
------
51
pole
------------
abcdefghijk
abcdefghijk
abcdefghijk
