Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 use of master..spt_values

Author  Topic 

mathiyazhagan.sekar@gmail
Starting Member

11 Posts

Posted - 2008-05-15 : 07:27:32
Hi all,
I am new to this forum.could any one help me when and how to use master..spt_values ?

Thanks,
Mathi

raky
Aged Yak Warrior

767 Posts

Posted - 2008-05-15 : 07:39:46
quote:
Originally posted by mathiyazhagan.sekar@gmail

Hi all,
I am new to this forum.could any one help me when and how to use master..spt_values ?

Thanks,
Mathi



It is a system table and it contains numbers from 1 to 2047. It is very useful.for example if you need to populate a table with 100 numbers from 1.You can use this like

declare @t table( id int)
insert into @t
select distinct number from master..spt_values where number between 1 and 100
select * from @t
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-15 : 07:41:27
To use the index seek prerogative, use

select number from master..spt_values where type = 'p' and number between 1 and 100

instead.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

mathiyazhagan.sekar@gmail
Starting Member

11 Posts

Posted - 2008-05-15 : 08:07:17
Thank You guys for providing clear picture of spt_values.why we are two dots after system DB (for example,master.. and tempdb..).Is there any special purpose for this ?

Thanks,
Mathi
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-15 : 10:07:34
quote:
Originally posted by mathiyazhagan.sekar@gmail

Thank You guys for providing clear picture of spt_values.why we are two dots after system DB (for example,master.. and tempdb..).Is there any special purpose for this ?

Thanks,
Mathi


Yes. they denote fully qualified names
i.e Server.Database.Schema.object

so master..spt_values suggests database is master and object name is spt_values.
Go to Top of Page
   

- Advertisement -