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 - 31 through 45 (of 79 total)

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

Sinshith S Anand SSChasing Mays Points: 605 More actions · Answer 1

Hello Friends,

I made necessary changes on the program. Now it will work smoothly..I will be careful from next time

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

Sinshith S Anand (6/26/2009)
Hello Friends,
I made necessary changes on the program. Now it will work smoothly..I will be careful from next time

Okay, this is a two way street. You asked a question expecting answers. I have asked YOU a question with the same expectation. Third time: What do you consider a correct answer????

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Sinshith S Anand (6/26/2009)
Hi friends,
I am really happy to see Lots of ideas....You can visit my blog also
http://sinshith.wordpress.com/
And let me know your comments...I started writing blog recently

I'd recommend that you do a wee bit more research on how to do things like finding the start or end of previous months. The methods you have use some conversions that will be relatively slow in the big picture of thigs.

I'd also recommend that you test such functionality for performance before posting it. Although UDF's can certainly make life easy and consistent for programmers, it is frequently at the expense of performance.

If you combine those two recommendations, you'll discover that finding the first or last day of any month (previous, current, next, whatever) is so simple that the performance hit of a UDF across a large number of rows (I always test with at least a million rows) just isn't worth it.

--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)

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

Lynn Pettis (6/26/2009)
Sinshith S Anand (6/26/2009)
Hello Friends,
I made necessary changes on the program. Now it will work smoothly..I will be careful from next time
Okay, this is a two way street. You asked a question expecting answers. I have asked YOU a question with the same expectation. Third time: What do you consider a correct answer????

Hey Sinshith... he's right... considering all the help you got here, it would be appropriate if you'd take the time to answer the question. 😉

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 5

Guys,

Does it really matter that much what was meant by the word 'correct'?

Taking the poor guy to task over semantics seems unfair.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 6

Paul White (6/28/2009)
Guys,
Does it really matter that much what was meant by the word 'correct'?
Taking the poor guy to task over semantics seems unfair.
Paul

Nah... not the word "correct"... I want to know what he thinks the correct solution is because he posted the following question after getting a couple of technically correct high performance methods....

Sinshith S Anand (6/23/2009)
Please contribute ur ideas...I have got only one correct answer....

I'm not trying to bust his chops on this and I don't believe that Lynn is either... I believe that Lynn and I both just want to know what he considers the correct answers to be and why especially because he wrote that post after some correct solutions were posted.

--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)

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

Jeff Moden (6/28/2009)
Paul White (6/28/2009)
Guys,
Does it really matter that much what was meant by the word 'correct'?
Taking the poor guy to task over semantics seems unfair.
Paul
Nah... not the word "correct"... I want to know what he thinks the correct solution is because he posted the following question after getting a couple of technically correct high performance methods....
Sinshith S Anand (6/23/2009)
Please contribute ur ideas...I have got only one correct answer....
I'm not trying to bust his chops on this and I don't believe that Lynn is either... I believe that Lynn and I both just want to know what he considers the correct answers to be and why especially because he wrote that post after some correct solutions were posted.

Jeff is correct. I'm not asking what he meant by "correct". To state that he had only one correct answer when multiple posts had correct solutions simply led me to wonder which solution was correct and why the others weren't. Could I have phrased the question better, perhaps. Biggest problem I have right now is that the OP hasn't even tried to answer the question at all. He has simply ignored me. Perhaps in the future we too should ignore him, but that would not be a professional response, would it? 😉

Paul White SSC Guru Points: 150467 More actions · Answer 8

Lynn Pettis (6/28/2009)
Jeff is correct. I'm not asking what he meant by "correct". To state that he had only one correct answer when multiple posts had correct solutions simply led me to wonder which solution was correct and why the others weren't. Could I have phrased the question better, perhaps.

Cool. I think the thread has benefited from that clarification.

Lynn Pettis (6/28/2009)
Biggest problem I have right now is that the OP hasn't even tried to answer the question at all. He has simply ignored me. Perhaps in the future we too should ignore him, but that would not be a professional response, would it? 😉

No it wouldn't.

Maybe one will be forthcoming now? Or maybe the OP didn't subscribe to this thread and has simply moved on. We may never know.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sinshith S Anand SSChasing Mays Points: 605 More actions · Answer 9

Actually i was expecting different methods for counting 1 to 100.Methods using CTE,While loop,Go to,Cursor......I was expecting some different methods than this...Gail posted a different method...Thats why i told 'Correct answer'....But i should have posted 'Expected answer'.....

Sinshith S Anand SSChasing Mays Points: 605 More actions · Answer 10

Thank you all..I got lots of new ideas....New techniques..And lots of suggestions....I have taken all comments positively..And i have to learn more..I am new to DB development(One year experience)..I need all your help to improve...Pls post more ideas and suggestions..

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

Sinshith S Anand (6/28/2009)
Actually i was expecting different methods for counting 1 to 100.Methods using CTE,While loop,Go to,Cursor......I was expecting some different methods than this...Gail posted a different method...Thats why i told 'Correct answer'....But i should have posted 'Expected answer'.....

Awesome. Thanks for taking the time to reply.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 12

GilaMonster (6/23/2009)
SELECT TOP (100) ROW_NUMBER() OVER (ORDER BY Object_id)
FROM master.sys.columns

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.

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

Sinshith S Anand (6/28/2009)
Actually i was expecting different methods for counting 1 to 100.Methods using CTE,While loop,Go to,Cursor......I was expecting some different methods than this...Gail posted a different method...Thats why i told 'Correct answer'....But i should have posted 'Expected answer'.....

Thanks for the feedback, Sinshith. Just a suggestion but, in the future, you may actually want to include such a description of you expectations and restrictions in your original post.

Just curious... one of the suggestions was to use a Tally Table. Since you're "new to DB", have you taken the time to look into that handy little tool and how it's built? How it's built is actually appropriate for this thread.

--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)

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

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

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.

--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)