printing 1 to 100 in SQL Server as a batch....

Question

Post reply

printing 1 to 100 in SQL Server as a batch....

Viewing 15 posts - 46 through 60 (of 79 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Paul White (6/29/2009)

The usual construction I see has master.sys.columns cross joined to itself. Of course that will not work for anyone with only 9 rows...

I didn't cross join because master.sys.columns as 659 rows in it and 100 were required.

I'm not sure why someone would trim master down. 90% of what's in there are system objects and the few that aren't (like spt_values) are often used by the management tools.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150457 More actions · Answer 2

Paul White

SSC Guru

Points: 150457

June 29, 2009 at 6:26 am

#1016772

😀 Don't tell me about it - I didn't post the daftness!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

Jeff Moden (6/29/2009)

Paul White (6/29/2009)

Peso (6/29/2009)

I only have 92 column in my master.sys.columns.

This is because I have an edited very slim version of MASTER database.

This is not something common, but you should beware that it can happen.

If you are going to promote a suggestion at your blog, use an alternative that "always" works, or post attention notes to your posts.

Heh. That's like saying "I have deleted my MASTER database. Your solution doesn't work". 😀

The usual construction I see has master.sys.columns cross joined to itself. Of course that will not work for anyone with only 9 rows...

Unless I missed some very well-hidden humour in your post, it seems a bit of a daft remark.

Paul

I'll typically use Master.dbo.SysColumns so it works in 2k and 2k5. If you're going to use just a 2k5 configuration, you should use Master.sys.SYSColumns as even manipulated Master databases will have more than 11,000 rows available.

And that will work until the SQL Server 2000 system table "views", like sys.syscolumns, go away in a future version od SQL Server. They have been depreciated.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Lynn Pettis (6/29/2009)

And that will work until the SQL Server 2000 system table "views", like sys.syscolumns, go away in a future version od SQL Server. They have been depreciated.

I'm pretty sure that Master.dbo.SysColumns is the 2000 compatability view... not Master.Sys.SysColumns. Has Master.Sys.SysColumns also been deprecated? Haven't been keeping up on these.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Jeff Moden (6/29/2009)

Lynn Pettis (6/29/2009)

And that will work until the SQL Server 2000 system table "views", like sys.syscolumns, go away in a future version od SQL Server. They have been depreciated.

I'm pretty sure that Master.dbo.SysColumns is the 2000 compatability view... not Master.Sys.SysColumns. Has Master.Sys.SysColumns also been deprecated? Haven't been keeping up on these.

master.dbo.syscolumns == master.sys.syscolumns and, yes, they are depreciated.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Paul White (6/29/2009)

😀 Don't tell me about it - I didn't post the daftness!

I didn't say it was daft, I just said I don't know why anyone would do it. Peter probably has a good reason, one I'd be interested in hearing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Whatever the reason that Peter has for the stripped down master database, his point is valid about making sure that the system table you use has enough rows.

Personally, I avoid using system tables to generate number tables, especially in production applications where you might run into trouble in the next SQL Server version if a table is deprecated.

Depending on the situation, I would use a number table, or a function that generates a number table, or code that generates a number table.

SwePeso SSC-Dedicated Points: 39725 More actions · Answer 8

GilaMonster (6/29/2009)

Peter probably has a good reason, one I'd be interested in hearing.

There's nothing more to say that I wanted to experiment with a SQL Server instance too see how small I can make MASTER database and still working.

So there's nothing more than curiosity to this than that. As I said, it's probably not common.

N 56°04'39.16"
E 12°55'05.25"

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Peso (6/29/2009)

There's nothing more to say that I wanted to experiment with a SQL Server instance too see how small I can make MASTER database and still working.

So there's nothing more than curiosity to this than that. As I said, it's probably not common.

Thanks.

I kinda doubt many people delete stuff from master. Most cases of messing with master is more likely stuff added accidentally.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

myheadhurts Ten Centuries Points: 1262 More actions · Answer 10

myheadhurts

Ten Centuries

Points: 1262

June 29, 2009 at 2:53 pm

#1017213

here's fast, less typing:

print ('1 ' + '2 ' + '3 ' + '4 ' +'...')

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

Paul White SSC Guru Points: 150457 More actions · Answer 11

GilaMonster (6/29/2009)

Paul White (6/29/2009)

😀 Don't tell me about it - I didn't post the daftness!

I didn't say it was daft, I just said I don't know why anyone would do it. Peter probably has a good reason, one I'd be interested in hearing.

I said it was daft. My point about the cross join was directed to Peso - not you. I still think it was daft, for the record.

Michael Jones

Whatever the reason that Peter has for the stripped down master database, his point is valid about making sure that the system table you use has enough rows. Personally, I avoid using system tables to generate number tables, especially in production applications where you might run into trouble in the next SQL Server version if a table is deprecated. Depending on the situation, I would use a number table, or a function that generates a number table, or code that generates a number table.

The question was about various ways of generating the numbers from one to a hundred. Using ROW_NUMBER and a system table is a perfectly valid way to do that. No-one said it was a production requirement, or that code submitted should be guaranteed to work in future versions of the product.

You've all missed the point of what was supposed to be a fun question.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 12

Paul White

SSC Guru

Points: 150457

June 29, 2009 at 2:58 pm

#1017222

molson (6/29/2009)

here's fast, less typing:

print ('1 ' + '2 ' + '3 ' + '4 ' +'...')

Ha. That's better. More compact than the version I posted before, and nicely optimized for performance :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 13

Paul White (6/29/2009)

Michael Jones

Whatever the reason that Peter has for the stripped down master database, his point is valid about making sure that the system table you use has enough rows. Personally, I avoid using system tables to generate number tables, especially in production applications where you might run into trouble in the next SQL Server version if a table is deprecated. Depending on the situation, I would use a number table, or a function that generates a number table, or code that generates a number table.

The question was about various ways of generating the numbers from one to a hundred. Using ROW_NUMBER and a system table is a perfectly valid way to do that. No-one said it was a production requirement, or that code submitted should be guaranteed to work in future versions of the product.

You've all missed the point of what was supposed to be a fun question.

Paul

I didn't miss the point of the question.

My response was just making a point about generating number tables from system tables, something that was already being discussed on this thread. I hope that's OK with you.

Paul White SSC Guru Points: 150457 More actions · Answer 14

Paul White

SSC Guru

Points: 150457

June 29, 2009 at 4:23 pm

#1017292

Michael Valentine Jones (6/29/2009)

I didn't miss the point of the question. My response was just making a point about generating number tables from system tables, something that was already being discussed on this thread. I hope that's OK with you.

*shrug*

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

I'm pretty happy that someone brought up the fact that when you're going to be doing something like genning numbers using systems tables or views, that the end user "must look eye" because a lot of this code will simply not report an error if it exhausts the available row count.

I'll also add that once a Tally or Numbers table with a known and guaranteed rowcount is established that you should probably never use system tables to gen larger rowcounts after that. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions