Melihat ke tabel sementara di SQL Server

Melihat ke tabel sementara di SQL Server 4 Desember 2013

Diposting oleh fofo di Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012.

Tags: tempdb, tabel sementara
lacak balik

Saya telah memberikan kursus bersertifikat di MS SQL Server 2012 baru-baru ini dan saya ditanya beberapa pertanyaan tentang tabel sementara, cara membuatnya, cara mengelolanya, kapan menggunakannya, dan apa batasannya.

Dalam posting ini saya akan mencoba menjelaskan masalah khusus ini dengan banyak demo langsung.

Tabel sementara dan variabel tabel menggunakan sistem tempdb basis data.

Saya telah menginstal SQL Server 2012 edisi Enterprise di mesin saya tetapi Anda dapat menggunakan SQL Server 2012/2008 edisi Ekspres demikian juga.

Saya terhubung ke instance lokal saya melalui Otentikasi Windows dan di jendela kueri baru saya mengetik (Anda dapat menyalin tempel)

Pertama saya akan membuat tabel sementara baru dan mengisinya. Jalankan skrip di bawah ini.


USE tempdb
GO

IF OBJECT_ID('tempdb..#footballer') IS NOT NULL

DROP TABLE #footballer;

GO
CREATE TABLE #footballer
 (
 [FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
 [lastname] [varchar](15) NOT NULL,
 [firstname] [varchar](15) NOT NULL,
 [shirt_no] [tinyint] NOT NULL,
 [position_played] [varchar](30) NOT NULL,

);

GO

SET IDENTITY_INSERT [dbo].[#footballer] ON

GO

INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO

SELECT * FROM #footballer


Seperti yang Anda lihat ada # awalan di depan meja. Tabel ini akan disimpan di tempdb.

Akhirnya saya memilih semuanya dari tabel sementara.

Jika saya membuka jendela kueri baru dan mencoba memilih semuanya (lihat kueri di bawah) dari #pemain bola meja.



USE tempdb
GO

SELECT * FROM #footballer


Anda tidak akan menerima hasil apa pun. Anda akan menerima kesalahan – Nama objek ‘#footballer’ tidak valid.

Ini adalah tabel sementara lokal dan hanya dalam cakupan sesi koneksi saat ini.

Kami juga dapat membuat tabel sementara global. Di jendela kueri baru, jalankan skrip berikut.



USE tempdb
GO

IF OBJECT_ID('tempdb..##footballernew') IS NOT NULL

DROP TABLE ##footballernew;

GO
CREATE TABLE #footballernew
(
[FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
[lastname] [varchar](15) NOT NULL,
[firstname] [varchar](15) NOT NULL,
[shirt_no] [tinyint] NOT NULL,
[position_played] [varchar](30) NOT NULL,

);

GO

SET IDENTITY_INSERT [dbo].[##footballernew] ON

GO

INSERT [##footballernew] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [##footballernew] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [##footballernew] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [##footballernew] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [##footballernew] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO

SELECT * FROM ##footballernew


Kami menunjukkan tabel sementara global dengan ####pesepakbolabaru

Tabel sementara global dihapus saat semua pengguna yang mereferensikan tabel terputus.

Tabel sementara global dan lokal harus dihapus dalam kode daripada bergantung pada penurunan otomatis.

Tabel sementara yang dibuat dalam prosedur tersimpan terlihat oleh prosedur tersimpan lainnya yang dijalankan dari dalam prosedur pertama.

Di jendela kueri baru, ketikkan yang berikut ini.



USE tempdb
GO

SELECT * FROM ##footballernew


Dalam hal ini tidak akan ada kesalahan. Tabel sementara global bertahan di seluruh koneksi sesi.

Anda juga dapat menambahkan kolom ke tabel sementara dan mengubah definisi kolom yang ada.

Dalam skrip ini saya menambahkan kolom lain dan kemudian mengubah definisi kolom yang ada.



USE tempdb
GO

IF OBJECT_ID('tempdb..#footballer') IS NOT NULL

DROP TABLE #footballer;

GO
CREATE TABLE #footballer
 (
 [FootballerID] INT IDENTITY NOT NULL PRIMARY KEY,
 [lastname] [varchar](15) NOT NULL,
 [firstname] [varchar](15) NOT NULL,
 [shirt_no] [tinyint] NOT NULL,
 [position_played] [varchar](30) NOT NULL,

);

GO

SET IDENTITY_INSERT [dbo].[#footballer] ON

GO

INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (1,N'Oliver', N'Regina', 4, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (2,N'Alexander', N'Roy', 8, N'goalkeeper')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (3,N'Mueller', N'Dewayne', 10, N'defender')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (4,N'Buckley', N'Beth', 3, N'midfielder')
INSERT [#footballer] ([FootballerID], [lastname], [firstname], [shirt_no], [position_played]) VALUES (5,N'Koch', N'Jolene', 7, N'striker')
GO

ALTER TABLE #footballer
ADD [is_retired] BIT NULL;
GO

ALTER TABLE #footballer
ALTER COLUMN [lastname] [nvarchar](50);
GO


Anda dapat menggunakan tipe data apa pun untuk definisi kolom dalam tabel sementara. Anda juga dapat menggunakan tipe data yang ditentukan pengguna.

Anda juga dapat memiliki batasan dalam tabel sementara. Jika Anda menjalankan kode di bawah ini, itu akan berfungsi dengan baik.



USE tempdb
GO

IF OBJECT_ID('tempdb..#Movies') IS NOT NULL

DROP TABLE #footballer;

GO

CREATE TABLE #Movies
 (
 MovieID INT PRIMARY KEY ,
 MovieName NVARCHAR(50) ,
 MovieRating TINYINT
 )
GO
ALTER TABLE #Movies
 WITH CHECK
 ADD CONSTRAINT CK_Movie_Rating
CHECK (MovieRating >= 1 AND MovieRating <= 5)

Tetapi Anda harus berhati-hati saat membuat-menerapkan kunci asing. KUNCI ASING batasan tidak diterapkan pada tabel sementara lokal atau global.
Jalankan skrip di bawah ini untuk melihat apa yang saya maksud. Kunci asing tidak akan dibuat.


USE tempdb
go

CREATE TABLE #Persons
 (
 P_Id INT NOT NULL ,
 LastName VARCHAR(255) NOT NULL ,
 FirstName VARCHAR(255) ,
 Address VARCHAR(255) ,
 City VARCHAR(255) ,
 PRIMARY KEY ( P_Id )
 )

CREATE TABLE #Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES #Persons(P_Id)
)


Harap diingat bahwa Anda dapat membuat tabel sementara dengan indeks berkerumun dan tidak berkerumun di atasnya.

Mari kita selidiki perilaku tabel sementara dan kolom IDENTITY.

Jika Anda menjalankan skrip di bawah ini, itu akan gagal. Ini adalah perilaku yang sama saat menjalankan skrip yang sama ke tabel biasa. Anda tidak dapat menentukan nilai untuk kolom IDENTITY. Jika Anda memilih untuk melakukannya, Anda harus menyetel IDENTITY_INSERT DI.


USE tempdb
GO

IF OBJECT_ID('tempdb..#Persons') IS NOT NULL

DROP TABLE #Persons;

GO

CREATE TABLE #Persons
 (
 P_Id INT PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
 LastName VARCHAR(255) NOT NULL ,
 FirstName VARCHAR(255) ,
 Address VARCHAR(255) ,
 City VARCHAR(255)
 )

--this will not work

INSERT #Persons(P_Id,LastName,FirstName,Address,City) VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')
SET IDENTITY_INSERT [#Persons] ON

GO

--this will work

INSERT #Persons(P_Id,LastName,FirstName,Address,City) VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')


Perhatikan juga bahwa transaksi dihormati dalam tabel sementara. Jika saya memulai transaksi eksplisit – sebuah insert – tanpa melakukan itu akan memasukkan baris data tetapi kemudian jika rollback dikeluarkan, seluruh operasi akan dibatalkan

Jalankan skrip di bawah ini.



USE tempdb
GO

IF OBJECT_ID('tempdb..#Persons') IS NOT NULL

DROP TABLE #Persons;

GO

CREATE TABLE #Persons
 (
 P_Id INT PRIMARY KEY CLUSTERED IDENTITY(1,1) ,
 LastName VARCHAR(255) NOT NULL ,
 FirstName VARCHAR(255) ,
 Address VARCHAR(255) ,
 City VARCHAR(255)
 )

SET IDENTITY_INSERT [#Persons] ON

GO

--this will insert the value

BEGIN TRAN
INSERT #Persons(P_Id,LastName,FirstName,Address,City) VALUES (1,'Steven','Gerrard','123 liverpool street','liverpool')

GO

SELECT * FROM #Persons

--this will rollback the transaction

ROLLBACK TRAN


Semoga membantu!!!