Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Is using master.dbo.spt_values dangerous?

    As a result of some recent posts here, I've discovered the spt_values table and its list of 2,048 sequential number--very handy.

    Is it dangerous, however, to use that system-critical table in an application's T-SQL?

    One of the more mysterious and complex areas of SQL is the locking and blocking paradigm. Given how much I have on my plate, I have never had the time to fully understand these issues.


    So, my concern is, if I use this spt_values table in an application (SELECT only), because of locking/blocking issues, am I in danger of halting, or damaging my SQL Server, given the fundamentally critical nature of system tables?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't believe so because (AFAIK) it is not transactionally active. It is a sort of OTLT. If you are really nervous, use NOLOCK or UNCOMMITTED isolation.

    However, I always use a dedicated numbers table anyway, like this: Adam Machanic : You REQUIRE a Numbers table!
    You'll get better performance too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The spt_values table has been around for quite a while, but I believe Microsoft is slowly retiring it. There is no guarantee that it will be persisted to the next version of SQL Server, so as Pootle said, you may want to create your own numbers table.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Definitely "roll your own" numbers table. There are always limits on any numbers table, and the performance of queries can often be optimized by a well constructed numbers table. If you create it, you can define the table to suit your needs.

    That said, I'm quite probably one of the most guilty parties when it comes to refering to spt_values for simple examples so I don't have much room to complain!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, this reminds me of a bit of sqlbation that went on at SQLTeam to produce the most efficient numbers table.
    SQL Server Forums - Number Table Function
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •