SQLServerMASTER... spt

Source: Internet
Author: User
This table is often used by people. You can select it by yourself, but it is inexplicable. Take some time to learn. As shown above, this table is mainly used to save some enumerations. It is said that it is inherited from sybase and can be seen by many functions and stored procedures. It can also be called a system constant table. There are many things in it, but the most common is: -- 0

This table is often used by people. You can select it by yourself, but it is inexplicable. Take some time to learn. As shown above, this table is mainly used to save some enumerations. It is said that it is inherited from sybase and can be seen by many functions and stored procedures. It can also be called a system constant table. There are many things in it, but the most common is: -- 0

This table is often used by people. You can select it by yourself, but it is inexplicable. Take some time to learn.


As shown above, this table is mainly used to save some enumerated values. It is said that it is inherited from sybase and can be seen by many functions and stored procedures. It can also be called a system constant table.

There are many things in it, but the most common ones are:

-- 0 ~ 2047 a total of 2048 numbers SELECT number from master .. spt_values where type = 'P'

The following are some examples of applications:

1. Given a value, you want to obtain the date data in a daytime range.

For example, if we know that today is, the date of the next 30 consecutive days is:

Select convert (CHAR (10), DATEADD (DAY, number, GETDATE (), 120) AS [date] from master .. spt_values where type = 'p' AND number> 0

2. Equal score

-- Divide a value into multiple equal parts in 1, and show decimals if the number is insufficient. WITH cte (past) AS (SELECT 2.2) SELECT number, (case when past> = number THEN 1.0 -- WHEN the original small value> the current row value shows 1.0 ELSE past-FLOOR (past) -- otherwise it is displayed AS a small value END) AS resultFROM cte, MASTER .. spt_valuesWHERE 1 = 1 and ceiling (past)> = number -- the original small value is greater> = number and type = 'p' AND number> 0/* number result ----------- ------------------------------------------- 1 1.02 1.03 */

It can be seen that this is quite flexible. The key is how you use it.

However, there are limitations. After all, it is only 0 ~ 2047 digits

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.