当前位置: 动力学知识库 > 问答 > 编程问答 >

SQL Server: using table or @table in stored procedure

问题描述:

I have a stored procedure (see below) which inserts data into a physical table and then joins information with sys.databases. I was thinking that would it be better to not have a physical table for data insertion? Would it be better to fetch these results into a table variable within this procedure? If so, how to do that?

CREATE PROCEDURE dbo.PROC_getDbInfo

AS

SET NOCOUNT ON

GO

TRUNCATE TABLE dbo.dbinfo

GO

EXECUTE sp_msforeachdb 'insert into dbo.dbinfo

select ''?'' as name,

type_desc,

physical_name,

state_desc,

size * 1.0/128 as size_in_mb,

max_size,

growth * 1.0/128 as growth_in_mb,

is_percent_growth,

is_read_only

from [?].sys.database_files'

GO

SELECT @@SERVERNAME as instance_name,

f.name,

d.create_date,

d.compatibility_level,

d.collation_name,

d.user_access_desc,

d.state_desc,

d.recovery_model_desc,

d.page_verify_option_desc,

d.log_reuse_wait_desc,

f.type_desc,

f.physical_name,

f.state_desc,

f.size_in_mb,

f.max_size,

f.growth_in_mb,

f.is_percent_growth,

f.is_read_only

FROM dbo.dbinfo AS f INNER JOIN

sys.databases AS d

ON f.name = d.name

ORDER BY f.name

GO

网友答案:

You'll have to use a table. Either global temp (##) or a normal table.

A table variable will not be in scope for the sp_msforeachdb call if declared for the stored proc, and not visible to the stored proc if declared in sp_msforeachdb

网友答案:

@table is better -- the table is small and the i/o cost will slow it down.

网友答案:

Table variable usage is explained here:

http://msdn.microsoft.com/en-us/library/ms175010.aspx

It basically behaves like a table when it comes to how your script looks - but has very different behaviour under the hood, and if it's small enough should not result in any disc IO.

Also, if the table is only used and then removed during the course of a procedure, this scope limitation becomes a argument for using it.

分享给朋友:
您可能感兴趣的文章:
随机阅读: