Counting the Tally Table

Question

Post reply

Counting the Tally Table

Steve Jones - SSC Editor

SSC Guru

Points: 736291
More actions
October 19, 2015 at 8:27 pm

#77462

Comments posted to this topic are about the item Counting the Tally Table
October 19, 2015 at 11:54 pm

This was removed by the editor as SPAM
October 20, 2015 at 5:27 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Daleen SSC Rookie Points: 29 More actions · Answer 1

Hi.

I'm not a DBA and I need to understand this.

When I executed that query in SSMS, it gave an error - why does it not work when executed like that?

Regards

Daleen

Louisa Whitton SSCrazy Points: 2577 More actions · Answer 2

What version of SQL Server Management Studio are you using, Daleen?

Inserting multiple value lists is only possible from SQL Server 2008 onwards, so if you are running SQL Server 2005 or earlier you will receive an error ("Incorrect syntax near the keyword 'VALUES'.")

Daleen SSC Rookie Points: 29 More actions · Answer 3

Hi, Martin

I use SSMS 2012, but when I executed the query, I was connected to a SQL2005 server.

I ran it on my local host, which is on 2012, and it worked and brought back the 30 rows as what the awnser says it should 🙂

Thank you.

Regards

Daleen

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Umer Akram

SSC-Insane

Points: 22209

October 20, 2015 at 4:14 am

#1834284

Nice question. Thanks for sharing

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Thank you for the question about tally tables. While they're common to me, I know that not everyone uses them, but it's an awesome tool to have in your toolbox.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Daleen (10/20/2015)
Hi, Martin
I use SSMS 2012, but when I executed the query, I was connected to a SQL2005 server.
I ran it on my local host, which is on 2012, and it worked and brought back the 30 rows as what the awnser says it should 🙂
Thank you.
Regards
Daleen

This query uses a table value constructor, which didn't come along until SQL 2008. The MSDN page is at https://msdn.microsoft.com/en-us/library/dd776382.aspx.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 7

michal.lisinski

SSCertifiable

Points: 6933

October 20, 2015 at 5:28 am

#1834317

Nice 😉 Welcome to Cartesian join.

Thanks

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 8

Thanks for the good question, Steve.

Welcome to the forum, Daleen!

Marcia J SSCertifiable Points: 5649 More actions · Answer 9

One of those days when I click submit and then say, "No, no. That's not what I meant."

Ken SSCoach Points: 17191 More actions · Answer 10

Nice question Steve. I don't use tally tables much but they sure are a nice option to have.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Use them a lot. Easy question, didn't even need to run to verify.

HappyGeek SSCoach Points: 18729 More actions · Answer 12

Cheers for that, it seems I can still do math!!! 🙂

...