Jeff Moden

Forum Replies Created

Viewing 15 posts - 34,876 through 34,890 (of 59,099 total)

RE: Help needed to decode comma delimited string and insert into tables

Ok... I've not tested every eventuality, but this works with the test data provided by other posters.
First, here's that test data.
CREATE TABLE dbo.CLOB
(
Row_ID INT IDENTITY(1,1),
Parse_Clob NVARCHAR(4000)
)
INSERT INTO dbo.CLOB
SELECT ',2...
--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)

February 27, 2011 at 11:12 pm

#1292159
RE: Help needed to decode comma delimited string and insert into tables

{edit} I found a bug in my code after I posted it, so I took it down. I'm trying to fix the bug right now.
--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)

February 27, 2011 at 10:32 pm

#1292156
RE: Help needed to decode comma delimited string and insert into tables

JJH82 (2/27/2011)
nvarchar(4000) is set to the column, so I assume that no string is longer than 4000.:-)
Very cool. I'm sure that Paul will come up with something great on...
--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)

February 27, 2011 at 9:04 pm

#1292137
RE: Help needed to decode comma delimited string and insert into tables

JJH82 (2/27/2011)
to decode 9632 records into 32,000+, it took 9 hours and 45 minutes using kumar20's method.
I created a loop to process 1000 Records at a time.
any comments or suggestions...
--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)

February 27, 2011 at 8:58 pm

#1292135
RE: How to solve this 2 query

SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
Yep... but those are for distributed partitioned views... not local partitioned views. 😉 The fact that the partitioning column must be a part of the PK...
--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)

February 27, 2011 at 8:56 pm

#1292134
RE: How to solve this 2 query

SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
Heh... you should practice what you preach in this case. 😉
Heh. No. My comments were as much for daveriya's benefit as anyone else's - so...
--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)

February 27, 2011 at 8:54 pm

#1292132
RE: How to solve this 2 query

SQLkiwi (2/27/2011)
Jeff Moden (2/27/2011)
I found it under "partitioning schemes" in BOL... not under "partitioned views".
The is a good deal of coverage of this topic in BOL, in separate entries. ...
--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)

February 27, 2011 at 8:32 pm

#1292127
RE: How to solve this 2 query

SQLkiwi (2/27/2011)
pietlinden (2/27/2011)
For someone who "needs to solve [this] on his own", you sure got the newsgroup to do a lot of your homework. The problem with that is...
--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)

February 27, 2011 at 8:30 pm

#1292126
RE: How to solve this 2 query

Actually, after all these years, I finally found the correct rules for the partitioning column including the fact that the partitioning column must be a part of the PK. ...
--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)

February 27, 2011 at 7:56 pm

#1292115
RE: How to solve this 2 query

So, instead of having the procedure add union alls for only the tables that would be required on a per call basis, you standardized certain views for certain distances against...
--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)

February 27, 2011 at 7:42 pm

#1292111
RE: storage and performance issues of using NVarchar(Max)

But there IS a hit on performance... try adding an index to NVARCHAR(MAX) or anything that involves over 900 bytes.
--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)

February 27, 2011 at 5:57 pm

#1292098
RE: 'Pretty-up' the code

Brandie Tarvin (1/31/2011)They work wonderfully well for recursive querying...
You forgot to mention that recursion is usually hidden RBAR that should usually be avoided. 😛
--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)

February 27, 2011 at 5:23 pm

#1292096
RE: drop temp table in dynamic SQL

Craig Farrell (2/21/2011)
Jeff Moden (2/21/2011)
Umm... nope... I believe that's a problem, as well.
<snip proof>
The only way to do this using the same Temp Table name is with dynamic SQL.
Dangit, you're...
--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)

February 27, 2011 at 5:17 pm

#1292095
RE: Group by and Concatenate just one field?

jason-772533 (2/23/2011)
Thanks for the pointer, Lutz. I've searched SSC as suggested, and it seems that the XML functionality is only available in SS2005 and later. Unfortunately, although I'm...
--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)

February 27, 2011 at 5:12 pm

#1292093
RE: Split coulumn value

infodemers (2/25/2011)
That did niot do the trick!
I found my answer here -->
Thanks anyway!
Could you post the code that didn't do the trick? Thanks.
--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)

February 27, 2011 at 5:07 pm

#1292092

Viewing 15 posts - 34,876 through 34,890 (of 59,099 total)