Jeff Moden

Forum Replies Created

Viewing 15 posts - 51,481 through 51,495 (of 59,091 total)

RE: Deleting millions rows from some tables

Here's a couple of facts for you...
Creating a temp table that holds 750000 rows of primary keys of rows to be deleted takes 2.1 seconds using SELECT/INTO.
Adding a primary key...
--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 28, 2008 at 6:08 am

#795103
RE: are there "arrays" in sql 2005 ?

blahknow (3/25/2008)
Hi everyone
My stored procedure accepts 2 parameters: names, number of names like this: (name1, name2, name3,3) Problem is: the number of names changes each time i want to run...
--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 28, 2008 at 4:59 am

#795071
RE: are there "arrays" in sql 2005 ?

Mahesh Bote (3/28/2008)
Better you go for XML.
A well formed UDF or open code that uses a Tally table to do the split will blow the doors off of any form...
--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 28, 2008 at 4:53 am

#795066
RE: How to convert char to real to numeric

If it's for a join, as you say... why are you concerned with converting it back to a number? Just join to it...
--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 28, 2008 at 4:42 am

#795062
RE: What would you do?Update situation....

Rather than testing your code on 300 million rows, here's some code that generates a million rows of test data. This particular code also has what I call 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 27, 2008 at 6:36 pm

#794959
RE: PK / FK design question

Harveysburger (3/27/2008)
BAM! Jeff has spoken
gotta say although it's not the most popular answer your arguments are strong
thanks for the input, I will meditate on that for a while and see...
--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 27, 2008 at 6:07 pm

#794956
RE: PK / FK design question

John Rowan (3/27/2008)
Wow, that was great Jeff...thanks. I'll have to admit, I've been just watching this thread waiting for a solid explanation of the benefits of any of 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)

March 27, 2008 at 5:54 pm

#794955
RE: CrossTab Query-URGENT PLEASE!

erdem (2/28/2008)
Hello Again,
How can I prevent duplicate values on a row without using Primary keys?
For example you can easily see that Sales price2 and sales price3 are same.How can I...
--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 27, 2008 at 8:14 am

#794610
RE: can we tune this long ruuning query

Madhu... a little feedback on what you end up doing would be much appreciated.
--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 27, 2008 at 8:01 am

#794603
RE: DTS Issues

Glad you got it resolved... what did you do?
--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 27, 2008 at 7:59 am

#794602
RE: Remove leading zeros and add spaces at the end

Sergei Zarembo (3/26/2008)
Jeff,
i agree in this simple replace it would be an overkill. But replace pattern can change by the minute. Wouldn't it be more convenient to have one [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)

March 27, 2008 at 7:51 am

#794597
RE: Remove leading zeros and add spaces at the end

Heh... no, you're right, Matt. I was just anticipating the next possible question (I should probably get out of the habit) which usually turns up as either "How would...
--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 27, 2008 at 7:45 am

#794589
RE: PK / FK design question

The popular answer on this and many of the other threads is scenario 3 and some for scenario 2. Just my humble opinion, but I think they might all...
--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 27, 2008 at 7:07 am

#794558
RE: Is there any way I can find out who deleted the database?

Todd Engen (3/26/2008)
Jeff,
Try changing "DROP" to "DELETE", or set a WHERE clause with EventClass = 47 for Object : Deleted events.
Opening the traces in Profiler will resolve the EventClass 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)

March 26, 2008 at 12:51 pm

#794285
RE: getting unique records from a table

See? Now, I get that... thanks, Wayne. Why couldn't someone say something like that instead of thinking that I was trying to give them an eWedgie? :P...
--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 26, 2008 at 12:33 pm

#794275

Viewing 15 posts - 51,481 through 51,495 (of 59,091 total)