Jeff Moden

Forum Replies Created

Viewing 15 posts - 39,811 through 39,825 (of 59,102 total)

RE: Single column to multiple columns

I haven't tried his, but Steve's looks like it'll work just fine (as he says, not in 2000), as well. He does it by building an on-the-fly Tally table...
--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 13, 2010 at 10:37 am

#1118468
RE: query question

Jakke (2/12/2010)
Hi Markus
The "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it 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 12, 2010 at 5:43 pm

#1118375
RE: query question

Jakke (2/12/2010)
Hi Markus
The "Quirky Update" did the trick 🙂
I tested it against about 20000 records and the outcome was satisfying and quite fast as well.
Next, i will put it 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 12, 2010 at 5:39 pm

#1118372
RE: strange query to get data

AndrewSQLDBA (2/12/2010)
I think that I have figured out a way by using a temp table
Thanks
Andrew SQLDBA
Cool... can you share it please?
--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 12, 2010 at 5:32 pm

#1118371
RE: How to catch a screen-scraper?

That's how I caught the "bots" at my last job. Just simple hit counters that capture both the user info and the IP address because bots are capable of...
--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 12, 2010 at 5:26 pm

#1118369
RE: horizontal vs vertical table structure

In the horizontal model, what will the columns represent?
--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 12, 2010 at 5:18 pm

#1118368
RE: Temp DB Issue

UT- (2/12/2010)
Thanks for the response RP, but no such thing in our case.
I used following queries to check the row count for each object in tempdb and the open trans,...
--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 12, 2010 at 4:49 pm

#1118358
RE: Deleting records from a huge table

TheSQLGuru (2/12/2010)
I agree that it would seem that SELECT/INTO is troublesome for getting consistent data but so would any SELECT unless WITH(TABLOCK) were used.
That doesn't sound right. I thought...
--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 12, 2010 at 2:48 pm

#1118322
RE: Are the posted questions getting worse?

Roy Ernest (2/12/2010)
it is carnival weekend here. From tonight onwards it is "Drink, Dance and Be merry" here. 🙂 I think I will not be sober till Wednesday.. :hehe:
As your...
--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 12, 2010 at 10:56 am

#1118160
RE: Deleting records from a huge table

TheSQLGuru (2/12/2010)
Jeff, here was my test:
MMREFW21099: fat table, no indexes, 72667 rows
CompanyID: column on that table with 48 rows with value '20067255'
with clean buffer cache I had 2 window...
--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 12, 2010 at 10:52 am

#1118156
RE: Deleting records from a huge table

mar1jxm (2/12/2010)
I am not sure I understand why we are comparing UPDATE and SELECT INTO?
The UPDATE is DML and the general consensus is that DML can't be executed against the...
--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 12, 2010 at 10:47 am

#1118152
RE: Single column to multiple columns

@sqlserver12345,
Any feedback on this? Did any of this work for you?
--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 12, 2010 at 6:30 am

#1117985
RE: store output of stored proc into temp table

Sergiy,
I guess I have a magic machine because I can't make it do the same locks you've shown. Which version, edition, and service pack of SQL Server are you...
--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 12, 2010 at 6:14 am

#1117976
RE: Deleting records from a huge table

I just ran a test using a large SELECT/INTO while running an UPDATE on the source table. Same effect as previously mentioned. The source table accepts DML... the...
--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 11, 2010 at 11:00 pm

#1117883
RE: Deleting records from a huge table

TheSQLGuru (2/11/2010)
3) [font="Arial Black"]that select [/font]will block ALL DML activity to [font="Arial Black"]the table [/font]while it is running, which could be a long time.
Kevin... a couple of questions on the...
--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 11, 2010 at 8:41 pm

#1117874

Viewing 15 posts - 39,811 through 39,825 (of 59,102 total)