Learn to use the Master.dbo.spt_values table

If there is a contiguous number column in the staging table to be generated, or a continuous date column, as follows:

2012-1-1

2012-1-2

2012-1-3

... ...

It can be written like this:

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<[email protected]

In the above statement, @begin and @end can be thought of as more than two arguments outside, and we require a date sequence between these two dates.

Of course, it's a good idea to review the data in the Master.dbo.spt_values table before looking at the code above.

SELECT * FROM Master.dbo.spt_values

We used the type with the number two column.

Spt_values stores the system values of Sybase.

Master.. Spt_values is equivalent to a numeric auxiliary table, which is used primarily in SQL for the Number field.

select number  from master..spt_values  where type= ‘p‘ --这样查询一下就知道什么意思了A relatively fixed general-purpose table for fetching numbers
The main function is to take consecutive numbers
But one flaw is that it only takes 2047.

Inside the technical Insider series there is an introduction to this table that seems to have been borrowed from DB2, which stores some of the data values that the system stored procedures need to run before the current value, the table common name, value, type, minimum, maximum, state, etc. six columns, a constraint, a clustered index, and a nonclustered index.
The network can not find the meaning of this table structure, but from the data values of the table, it is possible to guess some, for example, type B, that should be a Boolean, its name and value there are four kinds, yes or no,no,yes, None. Also, type I appears to be some data name related to the index. You can also guess by the range of names and values.
The natural number sequence 0~2047 name is null, the type is P, cannot guess what the English word corresponds to this p, perhaps in a system stored procedure to use its natural number sequence, perhaps the corresponding name of the stored procedure.
Some of the things in the system table are to be made clear, and the table like Spt_values, which is not given in the Books Online, is that it does not require you to know it, we just know that it is enough to refer to it (such as a sequence of numbers of type P), and it is recommended that you do not attempt to change the contents of this table   Otherwise, unexpected consequences may occur. You can search for u_tables.sql files in the installation file to view Spt_values table annotations

The following paragraph:

SQL Code?

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364

insertspt_values (name,number ,type ,low ,high ,status) values(null,0 ,‘P  ‘,1 ,0x00000001 ,0)insertspt_values (name,number ,type ,low ,high ,status) values(null,1 ,‘P  ‘,1 ,0x00000002 ,0)insertspt_values (name,number ,type ,low ,high ,status) values(null,2 ,‘P  ‘,1 ,0x00000004 ,0)insertspt_values (name,number ,type ,low ,high ,status) values(null,3 ,‘P  ‘,1 ,0x00000008 ,0)insertspt_values (name ,number ,type ,low ,high ,status) values(null,4 ,‘P  ‘,1 ,0x00000010 ,0)insertspt_values (name,number ,type ,low ,high ,status) values(null ,5 ,‘P  ‘,1 ,0x00000020 ,0)insertspt_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        = 7select@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 thatwhile @number_track < 1024    begin    raiserror(‘type=‘‘P  ‘‘,@number_track=%d‘ ,0,1 ,@number_track)    EXECUTE(    ‘    insertspt_values (name,number ,type ,low ,high ,status)      select         null         ,(selectmax(c_val.number)            fromspt_values    c_val            wherec_val.type = ‘‘P  ‘‘            andc_val.number between0 and‘ + @char_number_track + ‘         )            + a_val.number + 1         ,‘‘P  ‘‘        ,(selectmax(b_val.low)            fromspt_values    b_val            whereb_val.type = ‘‘P  ‘‘            andb_val.number between0 and‘ + @char_number_track + ‘         )            + 1 + (a_val.number / 8)        ,a_val.high        ,0        from         spt_values    a_val        where         a_val.type = ‘‘P  ‘‘        anda_val.number between0 and‘ + @char_number_track + ‘    ‘)    select@number_track = ((@number_track + 1) * 2) - 1    select@char_number_track = convert(varchar,@number_track)    end --loopgo

Master.. Spt_values equivalent to Master.dbo.spt_values

Master is the database name
Spt_values is the table name spt_values is a constant table system table the table is inherited from Sybase and is an internal dictionary table for internal use within SQL Server.
We can find it in the source code of many system stored procedures and functions. It can actually be understood as the data dictionary we use when we program.

Column names are name, value, type, lower limit, upper limit, state, respectively;

The value of the Type column means:
D=database Option p=projection dbr=database Role dc=database Replication i=index l=locks v=device Type
Because there are more, can not be enumerated. Where type P is special, it is just a simple list of numbers between 0-2047 (relative to the version), as a predictor of the relationship between all types.

Select month (varchar), DATEADD (MONTH, Number-1,
DATEADD (Yy,datediff (Yy,0,getdate ()), 0)) 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 '

Learn to use the Master.dbo.spt_values table