Jeff Moden

Forum Replies Created

Viewing 15 posts - 42,841 through 42,855 (of 59,063 total)

RE: Normalising data in semicolon-delimited format

Thanks, Simon. I can't take the credit for the original idea, though.
As a side-bar, you did make my day. So many people take a bit of code and...
--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)

July 21, 2009 at 8:38 am

#1027668
RE: Read a non-delimited file and insert into table

touchmeknot123 (7/21/2009)
hey Jeff,
Yes, As I had written earlier that certain records have fixed length.
Like say
H1234NRina
H2345A123abcstreet
H3456Z23456
Above, 1st record "type" i.e. type N will have columns of length 5,1,4 only
Similarly, 2nd...
--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)

July 21, 2009 at 8:33 am

#1027660
RE: Simple Query - long time

Tara (7/21/2009)
As i have union all from 2 tables, i thout of creating to seperate view for each table with indexes and join them in 3rd view but when creating...
--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)

July 21, 2009 at 8:29 am

#1027653
RE: Sort a string in a stored procedure: not order by

chandrika5 (7/21/2009)
The 4 values was just an example. The strings could be of varying length. I would not expect this to go in double digits.
I have not worked with tally...
--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)

July 21, 2009 at 8:27 am

#1027648
RE: How to convert hhmm into datetime 13:30:00 T-SQL?

Chethan Srinivas Shetty (7/21/2009)
Thanks for enlightening me on the use of reverse and substring. Is a flexible way of manipulating string formats into numbers. cheers
Just be careful, Chethan... REVERSE 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)

July 21, 2009 at 6:28 am

#1027560
RE: Creating millions of records in one go

Chris Quinn (7/21/2009)
I have just tried both your and my procedures on a copy of my live database with the following results:
My procedure - 500,000 records - 1m 27s
Your procedure...
--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)

July 21, 2009 at 6:21 am

#1027553
RE: T-sql help!

As a side bar... the report looks a little racist... I thought the days of Whites vs Non-Whites was gone. Guess the days of truly equal opportunity still...
--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)

July 20, 2009 at 11:10 pm

#1027412
RE: Sort a string in a stored procedure: not order by

chandrika5 (7/20/2009)
I am building an SP where I need to compare values like:
VAL1 = ABCD ; VAL2=BDAC
I need to sort both the values so that If I do a
IF VAL1...
--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)

July 20, 2009 at 10:44 pm

#1027409
RE: Sort a string in a stored procedure: not order by

. (7/20/2009)
This will get it done. It looks clunky, but it runs fast. You can build it into a user_defined function or CROSS APPLY it to...
--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)

July 20, 2009 at 10:42 pm

#1027408
RE: Simple Query - long time

Tara (7/20/2009)
I have this simple query for 10 records and it is taking more than an hour, not sure where to look into for exact reason.
select top 10* from vwemp...
--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)

July 20, 2009 at 10:39 pm

#1027407
RE: Sort a string in a stored procedure: not order by

Wesley Norton (7/20/2009)
the sum of the 4 character code should be the same.
Ummm... not necessarily...
'ABBC' = 'AABD'
--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)

July 20, 2009 at 10:29 pm

#1027406
RE: Creating millions of records in one go

Heh... couldn't wait. Since this is SQL Server 2000, there's no "programmable" TOP. So, I cheated to avoid the dynamic SQL (not that I'm against using dynamic SQL......
--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)

July 20, 2009 at 8:47 pm

#1027392
RE: Creating millions of records in one go

Hey Chris,
Thanks for posting your code. Lot's of folks just take the money and run. I'd like to return the favor in the form of another enhancement or...
--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)

July 20, 2009 at 7:51 pm

#1027387
RE: Datefunctions

Heh... no... I'm saying you're too late... no mind left. 😛
--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)

July 20, 2009 at 6:48 pm

#1027380
RE: Crosstab not returning desired results

Thanks... I appreciate that. Here's the solution... explanation is in the code comments.
--===== This is your original test table with just a bit of reformatting.
...
--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)

July 20, 2009 at 6:21 pm

#1027373

Viewing 15 posts - 42,841 through 42,855 (of 59,063 total)