Jeff Moden

Forum Replies Created

Viewing 15 posts - 16,891 through 16,905 (of 59,101 total)

RE: delete millions of rows

The Dixie Flatline (9/10/2016)
I'll admit the loop is going to run a long time, but you don't have to worry about missing a constraint, nonclustered index, foreign key, etc.
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)

September 11, 2016 at 10:26 am

#1900241
RE: A Case FOR Cursors...

Charles Kincaid (9/9/2016)
I would like for some body to test what happens if a procedure that uses a cursor is re-coded as a CLR proc where FOR loops (like 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)

September 9, 2016 at 11:40 pm

#1900201
RE: delete millions of rows

As a bit of a sidebar, the use of a table-wide EXCEPT with temporal criteria is VERY quick at resolving differences between two tables for true ups and is one...
--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)

September 9, 2016 at 11:18 pm

#1900200
RE: delete millions of rows

The Dixie Flatline (9/9/2016)
Jeff, wouldn't you have to stop activity on the ORIGINAL copy of the table to bring it current with transactions that occur not only during the initial...
--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)

September 9, 2016 at 11:10 pm

#1900199
RE: delete millions of rows

coool_sweet (9/9/2016)
i have table which has 5 millions record and i need to delete 4 millions in production.
what is the best approach to do it ?
DELETING 4 million rows 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)

September 9, 2016 at 8:32 pm

#1900193
RE: Are the posted questions getting worse?

Lynn Pettis (9/9/2016)
Sean Lange (9/9/2016)
The Dixie Flatline (9/9/2016)
I have been making bacon like that for years...never knew it had a name. Another great way is to put 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)

September 9, 2016 at 6:21 pm

#1900189
RE: The Bones of SQL - The Calendar Table

The Dixie Flatline (9/9/2016)
I think the link was premature. The article on the Holidays table should be out next week.
Correct. The article at that link won't...
--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)

September 9, 2016 at 6:18 pm

#1900188
RE: Datatype for storing images\documents

PJ_SQL (9/9/2016)
This is new implementation that we would be working on, the demo has data stored in database but the final product would store images on filesystem.
Understood. I'm actually...
--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)

September 9, 2016 at 6:16 pm

#1900187
RE: A Case FOR Cursors...

dbishop (9/9/2016)
Let me rephrase that or someone will take exception: there are times (rare cases) where using a cursor turns out to be the more efficient way of doing something....
--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)

September 9, 2016 at 6:04 pm

#1900186
RE: Adding Leading Zeros to Integer datatype

The Dixie Flatline (9/9/2016)
Thanks Jason. I never format data in SQL itself, so I had no idea about the performance hit. But I just...
--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)

September 9, 2016 at 3:41 pm

#1900182
RE: Datatype for storing images\documents

PJ_SQL (9/9/2016)
Database was storing images so the column was of datatype varbinary(max), since we moving all the images to file system , and will only have information of the pointer...
--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)

September 9, 2016 at 3:30 pm

#1900179
RE: Partition tables with 1 filegroup

sp_seeker (9/9/2016)
I would like to know if there is any performance gain if you DO NOT create separate filegroup for your partitions and you put all the Partition in PRIMARY
Generally...
--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)

September 9, 2016 at 3:14 pm

#1900176
RE: Are the posted questions getting worse?

Luis Cazares (9/9/2016)
Sean Lange (9/9/2016)
The Dixie Flatline (9/9/2016)
I have been making bacon like that for years...never knew it had a name. Another great way is to put 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)

September 9, 2016 at 1:20 pm

#1900153
RE: Rewriting the code using merge statement

Alvin Ramard (9/9/2016)
GilaMonster (9/9/2016)
...
Personally I rather avoid merge.
Really? I now feel better about avoiding merge too. 🙂
Makes three of us.
--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)

September 9, 2016 at 8:04 am

#1900046
RE: Rewriting the code using merge statement

There's no advantage to rewriting such a thing as a MERGE. I wouldn't waste my time doing such a thing.
If you want to learn MERGE, start on something...
--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)

September 9, 2016 at 7:16 am

#1900025

Viewing 15 posts - 16,891 through 16,905 (of 59,101 total)