December 1, 2014 at 8:56 am
Voted as 174.
I'd like to see this, if for no other reason than it makes vandalism harder and it's trivial to implement. A DMV that has 32B (int's worth) could easily be made in every database.
December 1, 2014 at 9:23 am
voted!! 177
December 2, 2014 at 1:47 am
Its not as if its that difficult to create a system view in the model DB that gets replicated to all new databases that are created.
PS : Voted +1
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 2, 2014 at 8:15 am
Voted!
Don Simpson
December 3, 2014 at 4:59 pm
Probably not, Joe. There are already a whole bunch of workarounds that are as fast as or faster and certainly easier to use than a SEQUENCE, if I understand where you're going with this. The goal is to make it all both simple, reliable, supported, and very fast and have it be available everywhere all the time just like any other function in SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 3, 2014 at 8:23 pm
CELKO (12/3/2014)
But SQL also has the goal of orthogonality and that means re-use of existing syntax constructs when possible. This table is not a function; it is a set, not just one value and it has no parameters (in the SQL model, the empty parameter would not apply here).
Exactly. That's why I'm not suggesting a table and neither is the CONNECT item that Erland Sommarskog submitted for this.
Is there only one such table in the schema or can we create several of them with different limits and increments?
Should it construct an actual table or effectively construct a table?
Those questions have been answered many times in this thread and by the title of this thread. It should NOT be an actual table. It should be a very high speed function that returns a set that can be used as a derived table in the FROM clause or as part of an APPLY (Cross Apply or Outer Apply). It could be made to take parameters for things like increment but that would only be a bonus. The goal is to persuade MS that the tool is needed and to make it a part of T-SQL. I'd be happy with a function that only returns a set of sequenced integers from 0 to some value as a first blush. And, no... using ROW_NUMBER() OVER isn't fast enough, IMHO, because that needs to play over an existing set rather than creating a set at the machine language level.
Without any analysis, I am thinking this could be something that gets turn into a construction inside a query by the optimizer at execution time rather than actually being materialized on disk.
Exactly correct. That's why the title of this thread is "Please vote for the "built in Tally Table" [font="Arial Black"]Function[/font]" and not just for a Table. As someone else suggested the obvious, if that's all you wanted, you could simply build one into the Model database and it would automatically appear in all new databases.
Of course, you'd still have the problem from installation to installation of what that table is called and what its domain was. A lot of us call it a "Tally" table. Some call it a "Numbers" table. Still others simply call it "Nums", and, if I recall correctly, you just call it something like "Seq". Some build 10K rows. Others build 11K rows. Still others build a million rows. There's no wrong answer there but there's also no standardization for the very common tool. Some poor buggers have even built functions but a lot of them use WHILE Loops (slothful scalar function guaranteed there) while others build a slow, resource intensive rCTE (not to be confused with Itzik's high performance cCTEs) that counts into an iTVF and actually believe that it will be fast and set based.
Since it is a constructor and not a chunk of data, we ought to be able to have many of them. Odds, evens, primes (maybe not; no simple formula), and whatever.
Agreed and how very useful that would be. Let's hope they do that and include directional increments, as well. Better yet, a single function that you'd control through parameters to do all those wonderful things you suggested would be the bee's knees. Like I said before, that would be a grand bonus provided that it did not slow the code down as they've done with so many other things. FORMAT and the previous row stuff in the Windows functions are two great examples of how they didn't really think about performance.
Talking out loud here, if it is in code and not on disk, can we optimize it? I have no idea.
Absolutely, especially when used in a CROSS APPLY much like has been done with Itzik's cCTEs but, at Machine Language speeds instead of SQL speeds. Since each machine language iteration of the APPLY code would be correlated to the outer query and would pass a "right size" parameter to the new function, there'd be no need for something like a Clustered Index anymore than there is for Itzik's cCTEs. It would just be fast as hell and produce no reads.
Great conversation, Joe. Thanks for jumping in.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 4, 2014 at 6:49 am
I didn't put anything in this thread until today but I "voted" for this about two weeks ago..
God, I love Tally tables....
December 4, 2014 at 9:11 am
My vote made it 189
December 5, 2014 at 11:10 am
I have tried multiple times, both yesterday and today. I can't get through...
The system has encountered an unexpected error. We apologize for the inconvenience. The issue will be addressed as quickly as possible.
If this error continues, click the Help link at the top of the page to report the issue and include this error ID in your e-mail: a016907d-66cb-4977-90a0-fe14bd216aef
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply