Tricks using system table spt_values – SQL Server

What is the purpose of master.dbo.spt_values table in SQL Server? frankly I don’t know but I use this table a lot in my queries. This table resides in Master database, it has a sequence number from 0 to 2047 in a column called “number” and for type “P” (SELECT number FROM master..spt_values where type = ‘p’ ). So far there are no documents available in MSDN for this table. Now the question is, how this table is so useful to us? because this has a number sequence, which will help us in looping, recursion etc. You must have seen a simple number table created manually in most of the databases. If you do not have one in your database then no worries, Microsoft has one for you in master database  🙂 . I would explain couple of tricks here that can be done using table spt_values in SQL Server.

Trick 01: Find letters, numbers and special characters from a string

Trick 02: Generate all the month names of a year

Trick 03: Split comma delimited string

Trick o4: Find date sequence between two dates

A commonly asked interview question: Find first and last Saturday of a month.

Trick 05: Find the identity gap in a table

If you have a huge table and have identity value greater than 2047 then this method will not work since it has a sequence up to 2047.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: