March 25, 2009 at 2:44 pm
Bruce W Cassidy (3/25/2009)
[font="Verdana"]Another idea...Part of the SQLCMD is the ability to create variables for the environment (rather than T-SQL variables.) You could use this to build a command line script that you give it the database name, and it runs your statements within SQLCMD.
So then you'd have something like:
1. Copy out the table of commands to a script file
2. Change to the correct database
3. Run the script file
[/font]
Hi Bruce
I also thought about this, but I think this is much slower than a concatenated SQL script or even a cursor.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 25, 2009 at 2:52 pm
Florian Reischl (3/25/2009)
RBarryYoung (3/25/2009)
I do these kind of scripts (a lot) and I always concatenate them all together using either the Variable method or the XML method.The Variable method is more efficient at the low end, however, because it is inherently O(n2), the XML solution will eventually overtake it because it is only O(n). And of course that means that it scales perfectly. So your upper limit there would be the size limit of Varchar(MAX) or NVarchar(MAX).
Hi Barry
Thanks for your feedback. You are right, I think I will take more to the FOR XML functionality. At the moment our SQL Server 2000 become less and less so this becomes more possible.
Greets
Flo
Barry - we are going to have to agree to disagree on this. Now, if this was code that was going to be executed from the application, then yes - I would agree.
However, with a script that will be run once on a server to initially create the users - I just don't see the need to avoid using a cursor.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2009 at 6:35 am
I'm going to have to side with the cursor argument on this one.
A one time use/db script probably isn't going to take the server down.
The probability of overfilling an nvarchar(MAX) I think is higher.
---
Dlongnecker
March 30, 2009 at 8:02 am
dlongnecker (3/30/2009)
I'm going to have to side with the cursor argument on this one.A one time use/db script probably isn't going to take the server down.
The probability of overfilling an nvarchar(MAX) I think is higher.
Strawman. I never said that it was going to take the server down. I said that you damage your credibility and ability to persuade others to not use cursors when you are lazy and use them for your own procs.
Since the non-cursor method is faster and only slightly more work, there is simply no reason to throw away one's credibility on this issue.
As for over-filling an NVarchar(MAX), let's see even at 10,000 characters per database (which is way more than I have ever needed) I would still be good up to 100,000 databases on one server. Yeah, I'll take that chance.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 8:16 am
My mistake RBarryYoung, I tackeld the wrong argument.
Preaching the evil of cursors then using them yourself for particular instances can be a mixed message for neophytes. I'd say if you document your use of the cursor that should be able to squash the issue. Depending on your shop it could be an issue.
Also, I thought nvarchar(max) had a max of 4000 characters. I ran his code on one of my test servers and all the statements concatenated together had a length of 6150 characters for 28 databases. I know there are fancy pants set-based ways to fix it but we might be sacrificing code readability.
---
Dlongnecker
March 30, 2009 at 8:42 am
dlongnecker (3/30/2009)
My mistake RBarryYoung, I tackeld the wrong argument.
s'alright. 🙂
Preaching the evil of cursors then using them yourself for particular instances can be a mixed message for neophytes. I'd say if you document your use of the cursor that should be able to squash the issue. Depending on your shop it could be an issue.
Nope. Been there, tried that. Many, many times. And I work in dozens of different client shops as "the SQL Expert". Didn't work in any of them. One reason was because they saw that I was using them on some of my own administrative sProcs and that became their excuse for using it themselves. This happened even with my own people!
That was the incident that finally made me realize how insidious this problem really was. The way to look at Cursors is as though it was black-tar heroin and sql developers are serious addicts who are desperately jonesing for a fix of procedural code. They will use any reason they can find to justify it, behind your back if necessary.
The only solution is to deny them all possible reasons. It isn't enough to be able to win this argument when you are there, you have to win this argument even when you are not there.
Also, I thought nvarchar(max) had a max of 4000 characters. I ran his code on one of my test servers and all the statements concatenated together had a length of 6150 characters for 28 databases. I know there are fancy pants set-based ways to fix it but we might be sacrificing code readability.
The limit for all non-MAX NVarchar's is 4000 characters. For MAX it is just over 1 billion characters.
And yes, I am willing to sacrifice some readability to stop Cursors. Though honestly, I don't think that I'm sacrificing much readability given the low readability of Cursor-based code to begin with.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2009 at 8:52 am
I haven't worked in the field that long, I'll take your word on the inability of others to use their heads. We're in IT, we're not the users! I wish people could learn appropriate use of things.
Good call on the nvarchar(max) thing. From BOL:
Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The ISO synonyms for nvarchar are national char varying and national character varying.
Talking about lengths in terms of chars then switching to bytes caught me offgaurd.
(((2^31) - 1) - 2) / 2 = 1,073,741,820
edit: in light of this, I'd definately go with the XML string concat method I originally proposed
---
Dlongnecker
March 30, 2009 at 9:01 am
dlongnecker (3/30/2009)
edit: in light of this, I'd definately go with the XML string concat method I originally proposed
Agreed. I am in the process of switching over to it myself as it is much faster for the larger strings.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply