Jeff Moden

Forum Replies Created

Viewing 15 posts - 19,411 through 19,425 (of 59,072 total)

RE: Deleting large batches of rows - optimum batch size?

Another trick to help the deletes along is to ensure that the deletes are done in the same order as the clustered index.
--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)

October 17, 2015 at 12:53 pm

#1833946
RE: CASE Statement with Date

You don't need a CASE statement for this. Post the code you used to generate the dates/times and we'll show 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)

October 16, 2015 at 6:12 pm

#1833924
RE: Deleting large batches of rows - optimum batch size?

It's going to vary based on the number of indexes, foreign keys, adjacency of rows, etc. The old method of try 10K, 50K, then 100K is probably the best...
--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)

October 16, 2015 at 6:04 pm

#1833923
RE: One time database shrink catch-22 solution?

Eric M Russell (10/16/2015)
Jeff Moden (10/15/2015)
Eric M Russell (10/15/2015)
Create indexes on the NEW tables. Remember to create clustered indexes before non-clustered indexes.
As with a clustered index rebuild, the HEAP...
--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)

October 16, 2015 at 3:45 pm

#1833920
RE: Which is more efficient? WHERE SUBSTRING = ...or... WHERE fldField LIKE 'x%'

_watching (10/16/2015)
Did you read Matt Miller's comment a few posts above? Try making the query SARGable, re test and post the results.
With all due respect, anyone is just as capable...
--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)

October 16, 2015 at 3:35 pm

#1833918
RE: Which is more efficient? WHERE SUBSTRING = ...or... WHERE fldField LIKE 'x%'

ChrisM@Work (10/16/2015)
Jeff Moden (10/15/2015)
To be honest, I trust no one that uses pure RBAR to create test data nor do I trust fixed width/grooved test data where the data 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)

October 16, 2015 at 3:32 pm

#1833917
RE: Data Stewardship

Hi Brandie,
How did this all work out 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)

October 16, 2015 at 12:22 pm

#1833890
RE: Which is more efficient? WHERE SUBSTRING = ...or... WHERE fldField LIKE 'x%'

To be honest, I trust no one that uses pure RBAR to create test data nor do I trust fixed width/grooved test data where the data is usually variable in...
--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)

October 15, 2015 at 7:10 pm

#1833651
RE: Procedure with execute as owner - failing

jghali (10/15/2015)
Hi Jeff,
The person that is always logged is the operator (a windows user which is already logged in), part of the Operators (windows group).
We don't have their login 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)

October 15, 2015 at 6:18 pm

#1833649
RE: Newbie question - SQL server / development

Luis Cazares (10/15/2015)
Phil Parkin (10/15/2015)
Luis Cazares (10/15/2015)
You can use either the SQL Server Express Edition which is free. It has several limitations, but for simple use, it should be fine.
Where...
--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)

October 15, 2015 at 2:30 pm

#1833619
RE: One time database shrink catch-22 solution?

Eric M Russell (10/15/2015)
Create indexes on the NEW tables. Remember to create clustered indexes before non-clustered indexes.
As with a clustered index rebuild, the HEAP will be preserved until 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)

October 15, 2015 at 2:25 pm

#1833618
RE: One time database shrink catch-22 solution?

Michael L John (10/15/2015)
Shrinking a database likely will cause fragmentation, but re-building the indexes does not automatically cause the database to grow.
If an index is taking up 10 GB,...
--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)

October 15, 2015 at 2:22 pm

#1833615
RE: Procedure with execute as owner - failing

Why do you feel it necessary to change ownership in a proc to run DBCC CHECKDB? It shouldn't be executed by users.
--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)

October 15, 2015 at 2:13 pm

#1833612
RE: How do we calculate the distance in miles ? Syntax help please ?

mw112009 (10/15/2015)
Slight error in that previous code section...... This is correct
DECLARE @RangeDivisor FLOAT
SET @RangeDivisor = 1609.344;
DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)
Set @MyLatitude = 42.430883;
Set @MyLongitude =...
--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)

October 15, 2015 at 2:08 pm

#1833610
RE: Automating backup/restore for a non DBA-user

There are a whole bunch of unknowns here.
First, are there any clear text SSNs/TaxIDs or other personally identifiable information (PII) in the database that needs to be obfuscated? 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)

October 15, 2015 at 10:53 am

#1833569

Viewing 15 posts - 19,411 through 19,425 (of 59,072 total)