Jeff Moden

Forum Replies Created

Viewing 15 posts - 34,831 through 34,845 (of 59,099 total)

RE: how grab text before a "-" character in a join

drew.allen (3/2/2011)
Another option is to use the LIKE operator. I don't know which will perform better.
SELECT *
FROM TableA
INNER JOIN TableB
ON TableA.ID LIKE Cast(TableB.ID as varchar(11)) + '%'
This assumes 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)

March 2, 2011 at 12:14 pm

#1293682
RE: Query plan metamorphosis

Can't you just add OPTION(RECOMPILE) to the end of the query?
--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)

March 2, 2011 at 12:11 pm

#1293680
RE: Need some help with query taking forever

Michael T2 (2/17/2011)
is i run the the 2 cte queries alone they are fast, less then a second...
I went to the doctor, held my arm over my head in a...
--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)

March 2, 2011 at 11:47 am

#1293664
RE: splitting a column in to 3 columns

gupta.sreekanth (3/2/2011)
thanks a ton......................
You bet... thank you for the feedback.
--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)

March 2, 2011 at 10:33 am

#1293625
RE: splitting a column in to 3 columns

Koen Verbeeck (3/2/2011)
That is a very interesting method. (I had never heard of the PARSENAME function before).
Another one for my knowledge base 🙂
grahamc (3/2/2011)
Me too 😀
Just having a play with...
--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)

March 2, 2011 at 10:31 am

#1293622
RE: splitting a column in to 3 columns

If the expected format of the source data is always the same, this is a simple way to do it...
DECLARE @SplitString VARCHAR(100)
SELECT @SplitString = 'bas-06-12586'
SELECT Col1 = PARSENAME(REPLACE(@SplitString,'-','.'),3),
...
--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)

March 2, 2011 at 6:59 am

#1293468
RE: Row transpose

NewBeeSQL (3/2/2011)
Mark-101232 .. You rock...I was struggling with pivot..and you gave a solution by not using PIVOT..Thanks a lot mark....
As a suggestion, take the time to read the article about...
--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)

March 2, 2011 at 6:49 am

#1293465
RE: datafiles details

Lookup sp_spaceused in Books Online. I "leverage" code from it frequently.
--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)

March 2, 2011 at 6:45 am

#1293462
RE: 'Pretty-up' the code

pharmboy4u (3/2/2011)
Thanks Rich. How did you know I was a PharmD and not BS-Pharm? I appreciate your comment, it made me smile.
I hope to someday get a good...
--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)

March 2, 2011 at 6:06 am

#1293442
RE: Help for one query to solve

So did that help at all? Heh... "Enquiring minds want to know." 😉
--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)

March 1, 2011 at 10:41 pm

#1293329
RE: Enough memory to load entire DB?

coder_t2 (2/22/2011)
At times, my server is hitting memory and I/O bottlenecks, and I was thinking about managing the database to fit entirely in memory to overcome those bottlenecks.
That usually turns...
--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)

March 1, 2011 at 7:34 am

#1292919
RE: Help for one query to solve

Three hints...
CTE
ROW_NUMBER() OVER (PARITION BY ________ ORDER BY ________)
OUTER JOIN
Look those up in Books Online (the "Help" system that comes with SQL Server)
--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)

March 1, 2011 at 7:28 am

#1292912
RE: using oSQL utility

Saravanan M (3/1/2011)
Essentially, I need to backup and restore a database from SQL server 2008 into SQL server 2005. Hence I decided to create SQL scripts for data and restore...
--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)

March 1, 2011 at 6:56 am

#1292888
RE: SSIS Flat File Import with different file layouts

Heh... thanks for the feedback, Koen. I may just have to teach myself C# so I can write a conditional splitter that would make it all a whole lot...
--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)

March 1, 2011 at 6:50 am

#1292884
RE: Update Column Value using After INSERT Trigger on table with no Primary Key

Understood and thanks for the feedback.
Does the original DB have a PK or unique index on the data? If so, you could always target that column or columns as...
--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)

March 1, 2011 at 6:41 am

#1292876

Viewing 15 posts - 34,831 through 34,845 (of 59,099 total)