Jeff Moden

Forum Replies Created

Viewing 15 posts - 16,546 through 16,560 (of 59,101 total)

RE: function to clean a phonenumber

Luis Cazares (10/25/2016)
ninamahezi (10/25/2016)
Hello,
I want a sql function that will allow to make one update but with several replace on the same column.
For exemple
Replace the space by vacuum('')
...
--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 25, 2016 at 11:10 pm

#1908797
RE: Splitting Text Field into individual Columns

Maybe if you're trying to do all this to a table, the cCA method might improve performance a bit because you can then take advantage of the starting position parameter...
--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 25, 2016 at 10:41 pm

#1908796
RE: Splitting Text Field into individual Columns

pietlinden (10/25/2016)
Jeff,
My first thought was to be lazy and use DelimitedSplit8K but that would turn each occurrence into a separate record, right?
Yes. You'd have to do a CROSS TAB...
--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 25, 2016 at 10:03 pm

#1908795
RE: Windows Function

It's by design and definition. Referring to the note at the following URL...
[font="Arial Black"]OVER Clause (Transact-SQL)[/font]
NOTE:
If ORDER BY is not specified entire partition is used for a window frame....
--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 25, 2016 at 9:43 pm

#1908794
RE: Splitting Text Field into individual Columns

rlevy 18902 (10/25/2016)
I have a table with a Notes Field Called NOTES with the following content "C1 User Unable to Log in C2 User did not set password Correctly...
--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 25, 2016 at 7:17 pm

#1908790
RE: Looking for way to dynamically replace street suffix with abbreviation based on tables

Just a reminder that the OP doesn't need to build a "correct" address formatter that follows CASS certification rules. He needed something that would standardize typical spellings of address...
--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 25, 2016 at 6:55 pm

#1908789
RE: Can I do this?

Sterling Ostin (10/24/2016)
Jeff - Look at the fourth and sixth small paragraph of my original post. I did exactly what you are suggesting I did not do. 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)

October 25, 2016 at 6:46 pm

#1908788
RE: Delete massive records and still can't shrink db

WhiteLotus (10/24/2016)
Thanks for the reply . Much appreciated
No message because when I right click on that db and choose Shrink -> data file . It shows up a shrink...
--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 25, 2016 at 12:14 am

#1908508
RE: database features

Phil Parkin (10/24/2016)
sqlserverDBA2016 (10/24/2016)
Is adding the features later same as when installing SQL server
And it's not more xomplicated
I think that Jeff's point of view is that it's safer to reconfigure...
--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 24, 2016 at 6:23 pm

#1908500
RE: Stop query if it is taking too long

Eirikur Eiriksson (10/24/2016)
you will need this index on the dbo.Transaction
CREATE UNIQUE NONCLUSTERED INDEX [INDEX_NAME] ON [dbo].[Transaction]
(
[InvoiceUID] ASC,
[IsSuccess] ASC,
[TransKind] ASC,
[TransactionDate] ASC
)
INCLUDE ( [CardNumber],[ReferenceNumber],[SeqCounter],[UnqReferenceNumber]);
I came up with similar but was really...
--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 24, 2016 at 3:36 pm

#1908489
RE: Very bad performance on a good HP DL380 Gen9 Server

Dird (10/24/2016)
TheSQLGuru (10/6/2016)
I also note that 32GB on a server is just silly these days
??
He means that it's an insufficient amount of ram. They've got USB memory sticks larger...
--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 24, 2016 at 3:28 pm

#1908488
RE: Clownpocalypse and DR

Rod at work (10/24/2016)
I'm old enough to have been working in the IT field during the whole Y2K "event". Major decisions were put off because of it. A lot 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 24, 2016 at 3:23 pm

#1908487
RE: Best Date Dimension Table

krypto69 (10/24/2016)
Thanks.
I think I found one that is pretty good - has fiscal dates (Quarters/Year end, etc)
Can you post the link, 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)

October 24, 2016 at 3:15 pm

#1908485
RE: Big data: transfer x-number of rows per batch?

Another thing to strongly consider when transferring such large amounts of data is "Minimal Logging" and the proper use of Trace Flag 610, which has been available since 2008 R2....
--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 24, 2016 at 3:14 pm

#1908484
RE: Compare two columns before insert

komal145 (10/24/2016)
TABLE ATABLEB
STATUS STATUS
ABCD ABCD If the value are same No insert
BCDG ...
--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 24, 2016 at 2:56 pm

#1908481

Viewing 15 posts - 16,546 through 16,560 (of 59,101 total)