By Gennady Vanin Геннадий Ванин


2010-11-25 04:29:14 8 Comments

What is the purpose of system table master..spt_values?
Why was it provided and how one should use it?

What are the meanings of its type, low, high values?

Update:
Google search gives thousands of " its uses", for example:

3 comments

@Nicholas Humphrey 2019-04-20 18:10:29

One usage, definitely not its primary usage, is to generate a series of numbers:

--Generate a series from 1 to 100
SELECT
    TOP 100
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum
FROM master.dbo.spt_values ORDER BY RowNum

@Pondlife 2010-11-25 09:04:21

The spt_values table is not mentioned in the the SQL Server documentation but it goes back to the Sybase days and there is some extremely minimal documentation in the Sybase online docs that can be summed up in this comment:

To see how it is used, execute sp_helptext and look at the text for one of the system procedures that references it.

In other words, read the code and work it out for yourself.

If you poke around the system stored procedures and examine the table data itself, it's fairly clear that the table is used to translate codes into readable strings (among other things). Or as the Sybase documentation linked above puts it, "to convert internal system values [...] into human-readable format"

The table is also sometimes used in code snippets in MSDN blogs - but never in formal documentation - usually as a convenient source of a list of numbers. But as discussed elsewhere, creating your own source of numbers is a safer and more reliable solution than using an undocumented system table. There is even a Connect request to provide a 'proper', documented number table in SQL Server itself.

Anyway, the table is entirely undocumented so there is no significant value in knowing anything about it, at least from a practical standpoint: the next servicepack or upgrade might change it completely. Intellectual curiosity is something else, of course :-)

@PerformanceDBA 2010-11-25 18:48:23

It is heavily used by the system sprocs, no chance of it changing.

@Pondlife 2010-11-26 07:50:50

@PerformanceDBA: you're probably right (after all, the table has presumably been there from version 4.x or earlier all the way through to 2008) but that is still no guarantee that Microsoft won't change it in future. Relying on undocumented features always has some risk, even if it's a small one.

@PerformanceDBA 2010-11-26 08:17:55

I did not vote you down. I disagreed with one statement. It is not worth arguing about, since you refuse to address my point that it is heavily used by MS in their system stored procs and therefore removal will require those to be written, which is as likely as a snowball's chance in hell. Read my other Answer for further details. There is no guarantee that MS will exist in the future ;-)

@Pondlife 2010-11-26 08:55:19

@PerformanceDBA: As I said, I think you're probably right, but to address your specific point: Microsoft has already modified many system procs to use the new DMVs instead of the old system tables, so clearly the code is not set in stone and is actively being maintained as versions change. If they can replace a reference to dbo.sysusers with one to sys.database_principals then they can certainly change a reference to spt_values. Whether or not they ever will is pure speculation, of course (I don't work for Microsoft).

@didierc 2014-07-02 12:10:35

Link is dead...:(

@didierc 2014-07-02 12:19:03

Moreover, this page is now the top google search result...

@Pondlife 2014-08-15 21:18:39

@didierc Updated, since the original link is now dead

@Piotr Kula 2015-02-27 11:08:31

And its great when some dev uses it.. doesn't comment why.. and when I need to migrate the DB now.. it poops its self.

@KappaG3 2019-09-12 09:30:00

The next service pack didn't change anything btw. Nor any other update during the following 10 years lol

@PerformanceDBA 2010-11-25 18:48:31

Mostly answered in another question.

What are the meanings of its type, low, high values?

Most Types (ie. each specific lookup table, if they were separate) require either Name or Number. Some Types require Low and High columns as well. In Sybase, we have an ErrorNumber column, so the sproc can RAISERROR which is not hard-coded (can be changes with versions, etc).

It is not "cryptic" unless an enumerated list is "cryptic" to you; it is just a lookup table (all of them, differentiated by the Type column).

Related Questions

Sponsored Content

21 Answered Questions

17 Answered Questions

[SOLVED] What is the equivalent of 'describe table' in SQL Server?

  • 2008-11-26 00:22:14
  • dbg
  • 495437 View
  • 292 Score
  • 17 Answer
  • Tags:   sql sql-server

37 Answered Questions

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2632384 View
  • 1777 Score
  • 29 Answer
  • Tags:   sql duplicates

10 Answered Questions

4 Answered Questions

[SOLVED] What is the meaning of the prefix N in T-SQL statements?

  • 2012-04-05 08:19:53
  • Kartik Patel
  • 255458 View
  • 323 Score
  • 4 Answer
  • Tags:   sql sql-server tsql

4 Answered Questions

[SOLVED] What does character set and collation mean exactly?

4 Answered Questions

[SOLVED] What does ON [PRIMARY] mean?

7 Answered Questions

[SOLVED] Script to save varbinary data to disk

1 Answered Questions

Sponsored Content