從凌開始

筆記一下，下面的指 令可以查所有 Table 使用的空間(單位是k) declare @PageSize float select @PageSize=1024 from master.dbo.spt_values v where v. number =1 and v. type = 'E' select object_id, object_name(object_id) as name , ( select @PageSize * SUM ( CASE WHEN a. type 1 THEN a.used_pages WHEN p.index_id THEN a.data_pages ELSE 0 END ) FROM sys.indexes as i JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id JOIN sys.allocation_units as a ON a.container_id = p.partition_id where i.object_id = o.object_id) data , ( select @PageSize * SUM (a.used_pages - CASE WHEN a. type 1 THEN a.used_pages WHEN p.index_id THEN a.data_pages ELSE 0 END ) FROM sys.indexes as i JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id JOIN sys.allocation_units as a ON a.container_id = p.partition_id where i.object_id = o.object_id) [ index ] from sys.objects o order by 3 desc

在寫SQL句時，一個Stored Procedure，傳入一個參數是一群資料的集合作為篩選條件 這時常常會用組字串的方式，使用 IN 的子句當作 WHERE條件，例如下面這個例子： CREATE PROC GetProjects ( @ProjectList nvarchar ( max ) ) AS BEGIN DECLARE @sql nvarchar ( max ) SET @sql = 'SELECT * FROM Project WHERE projectSn IN (' + @ProjectList + ')' EXEC sp_executesql @sql END 上面的例子，呼叫時傳入 " 1,2,3 " 這樣的字串當作 @ProjectList， 就可以找出Project資料表中，ProjectSn為1,2,3的資料 但這方法就是透過組字串來執行 在SQL2005增加了xml的xQuery方法，可以將xml變成關聯式資料，於是我修改上面的句子，變成： CREATE PROC GetProjects ( @ProjectList nvarchar ( max ) ) AS BEGIN DECLARE @xml xml SET @xml = @ProjectList ; WITH LIST AS ( SELECT N. value ( './text()[1]' , 'int' ) ProjectSn FROM @xml. nodes ( '/r/n' ) V(N) ) SELECT * FROM Project WHERE ( @ProjectList = '' OR EXISTS ( SELECT TOP 1 * FROM Project.ProjectSn = List.ProjectSn) ) END 在上面的例子，同樣的用法， 將@ProjectList的傳入值改成 " <r><n>1</n><n>2</n><n>3</n>...