Table Variable
-
DECLARE @Temp TABLE
(RowId int NOT NULL IDENTITY(1, 1),
Col1 varchar(20) NULL,
PRIMARY KEY CLUSTERED (RowId) )
Does declaring a Primary Key for a table variable create a searchable Index or just a unique constraint?
-
yes :
StmtText
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @Temp TABLE
(RowId int NOT NULL IDENTITY(1, 1),
Col1 varchar(20) NULL,
PRIMARY KEY CLUSTERED (RowId) )
Select * from @Temp where RowId = 1
(1 ligne(s) affectée(s))
StmtText
----------------------------------------------------------------------------------
|--Clustered Index Seek(OBJECT
@Temp), SEEK@Temp.[RowId]=1) ORDERED FORWARD)I know that functions with table variable have an entry in sysindexes. Maybe there's one in this case (in tempdb).. cause there doesn't seem to be one in the same db.
-
Yup, there it is :
DECLARE @Temp TABLE
(RowId int NOT NULL IDENTITY(1, 1),
Col1 varchar(20) NULL,
PRIMARY KEY CLUSTERED (RowId) )
Select * from @Temp where RowId = 1
Select Object_id('Tempdb..@Temp')
--null
Select id, name, indid, status from Tempdb.dbo.SysIndexes where name like '%@temp%'
/*
id name indid status
----------- -------------------------------------------------------------------------------------------------------------------------------- ------ -----------
2050624752 PK__@Temp__7B2E3129 1 18450
*/
-
I see said the blind man
Thanks Remi
-
You just have to know where to look for this kind of stuff
. -
BTW, I do know how to view the query plan but how do you get that in text format?
-
Try this in text result mode :
SET SHOWPLAN_TEXT ON
GO
DECLARE @Temp TABLE
(RowId int NOT NULL IDENTITY(1, 1),
Col1 varchar(20) NULL,
PRIMARY KEY CLUSTERED (RowId) )
Select * from @Temp where RowId = 1
Select Object_id('Tempdb..@Temp')
--null
Select id, name, indid, status from Tempdb.dbo.SysIndexes where name like '%@temp%'
GO
SET SHOWPLAN_TEXT OFF
-
Got it. Thanks again.
-
I feel I must mention this one too :
SET SHOWPLAN_ALL
(gets the whole set of stats)
-
I'll take a look at that also.
-
Cool.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply