学习使用master.dbo.spt_values表

最新推荐文章于 2023-04-25 14:03:56 发布

weixin_34166472

最新推荐文章于 2023-04-25 14:03:56 发布

阅读量489

点赞数

文章标签：数据库

如果要生成的临时表中有个连续的数字列，或者连续的日期列，如下所示：

2012-1-1

2012-1-2

2012-1-3

... ...

可以这样写：

declare @begin datetime,@end datetime
set @begin='2012-1-1'
set @end='2012-1-5'

declare @days int
set @days=DATEDIFF(dd,@begin,@end)
select DATEADD(dd,number,@begin)
from master.dbo.spt_values
where type='p' AND number<=@days

上面的语句中，@begin与@end可以认为是多外面传来的两个参数，我们要求这两个日期之间的日期序列。

当然，在看上面的代码之前最好先回顾一下master.dbo.spt_values表的数据。

select * from master.dbo.spt_values

我们使用了其中的type与number两列。

spt_values存储的是sybase的系统值。

master..spt_values相当于一个数字辅助表，在sql中主要用到number这个字段。

select number from master..spt_values where type= 'p'

--这样查询一下就知道什么意思了

相对固定通用的取数字的表
主要作用就是取连续数字
不过有个缺陷就是只能取到2047

技术内幕系列丛书里面有介绍

这个表貌似是从DB2借用过来的,它存储了一些系统存储过程运行所需要的数据取值范围以前当前值,这个表共有名称,值,类型,最小,最大,状态等六个列,一个约束,一个聚集索引和一个非聚集索引.
网上找不到这个表结构所代表的含义,但从表的数据值来看,可以猜出一些来,比如,类型为B,那就应该是布尔型,它的名称和取值有四种,yes or no,no,yes,none.又如类型I似乎是与索引有关的一些数据名.你也可以通过名称及值的范围来猜出一些.
自然数序列0~2047的name为NULL,类型为p,猜不出这个p对应什么英文单词,或许在某个系统存储过程中要用它自然数序列,或许就是对应的这个存储过程名吧.
系统表中的东西,有些是要弄清楚的,而像spt_values这个表,联机丛书中都没有给出说明,那就是说它并不要求你知道它,我们只要知道能拿它来引用(比如类型p的数字序列)就足够了,而且,建议你不要试图去更改这个表的内容,否则可能会出现无法意料的后果.

可以到安装文件里搜索 u_tables.sql 文件查看spt_values表注释情况

如下一段：

 
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,0 , 
          'P  '  
          ,1 ,0x00000001 ,0) 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,1 , 
          'P  '  
          ,1 ,0x00000002 ,0) 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,2 , 
          'P  '  
          ,1 ,0x00000004 ,0) 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,3 , 
          'P  '  
          ,1 ,0x00000008 ,0) 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,4 , 
          'P  '  
          ,1 ,0x00000010 ,0) 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,5 , 
          'P  '  
          ,1 ,0x00000020 ,0) 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,6 , 
          'P  '  
          ,1 ,0x00000040 ,0) 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status)  
          values  
          ( 
          null  
          ,7 , 
          'P  '  
          ,1 ,0x00000080 ,0) 
         
          go 
         
          -- 'P  ' continued.... 
         
          declare 
         
          @number_track         
          integer 
         
          ,@char_number_track     
          varchar 
          (12) 
         
          select      
          @number_track        = 7 
         
          select      
          @char_number_track    =  
          convert 
          ( 
          varchar 
          ,@number_track) 
         
          -- max columns is 1024 so we need 1024 bit position rows; 
         
          -- we'll actually insert entries for more than that 
         
          while @number_track < 1024 
         
          begin 
         
          raiserror('type= 
          '' 
          P   
          ''  
          ,@number_track=%d 
          ' ,0,1 ,@number_track) 
         
          EXECUTE( 
         
          ' 
         
          insert  
          spt_values ( 
          name  
          ,number ,type ,low ,high ,status) 
         
          select 
         
          null 
         
          ,( 
          select      
          max 
          (c_val.number) 
         
          from      
          spt_values    c_val 
         
          where      
          c_val.type =  
          '' 
          P   
          '' 
         
          and      
          c_val.number  
          between  
          0  
          and  
          ' + @char_number_track + ' 
         
          ) 
         
          + a_val.number + 1 
         
          , 
          '' 
          P   
          '' 
         
          ,( 
          select      
          max 
          (b_val.low) 
         
          from      
          spt_values    b_val 
         
          where      
          b_val.type =  
          '' 
          P   
          '' 
         
          and      
          b_val.number  
          between  
          0  
          and  
          ' + @char_number_track + ' 
         
          ) 
         
          + 1 + (a_val.number / 8) 
         
          ,a_val.high 
         
          ,0 
         
          from 
         
          spt_values    a_val 
         
          where 
         
          a_val.type =  
          '' 
          P   
          '' 
         
          and      
          a_val.number  
          between  
          0  
          and  
          ' + @char_number_track + ' 
         
          ') 
         
          select  
          @number_track = ((@number_track + 1) * 2) - 1 
         
          select  
          @char_number_track =  
          convert 
          ( 
          varchar 
          ,@number_track) 
         
          end  
          --loop 
         
          go

master..spt_values 相当于 master.dbo.spt_values

master 是数据库名
spt_values是表名

spt_values 是一张常量表系统表

该表是从sybase继承过来的，是个内部字典表，供SQL Server内部使用。
我们可以在许多系统存储过程和函数的源代码中发现它的身影。其实可以将它理解成我们编程时常用的数据字典.

列名分别为名称、值、类型、下限、上限、状态；

类型列的取值含义：
D=Database Option P=Projection DBR=Database Role DC=Database Replication I=Index L=Locks V=Device Type
因为比较多，无法一一列举。其中类型P较为特殊，它只是0-2047(与版本有关)之间的数字的简单列表，作为对所有类型之间关系的预测。

select number from master..spt_values with(nolock) where type='P'
/**解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/

select MONTH(convert(varchar(10), dateadd(MONTH, number - 1,
DATEADD(yy,DATEDIFF(yy,0,getdate()),0)), 120)) as yue from master.dbo.spt_values WHERE
type='P' AND number <= datediff(MONTH, DATEADD(yy,DATEDIFF(yy,0,getdate()),0),
dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0)) ) + 1 AND number>0

select * from master.dbo.spt_values where type='p'

select number from master..spt_values with(nolock) where type='P'