Jeff Moden

Forum Replies Created

Viewing 15 posts - 19,396 through 19,410 (of 59,072 total)

RE: need to convert string to datetime datatype in DFT

Not sure about SSIS but it's mostly a breeze in T-SQL.
SELECT CAST(CAST(20151020 AS CHAR(8)) AS DATETIME)
--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 21, 2015 at 4:15 pm

#1834791
RE: Query on a large database

Jayanth_Kurup (10/20/2015)
with 100 million rows your better off using a Columnstore index or moving the table to SSAS. If the report requires data on a monthly basis partitioning it by...
--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 21, 2015 at 10:13 am

#1834722
RE: The Poor Soul

From the article
"So Steve, what would you recommend then for those poor souls? As being one of them it drives me nuts to constantly be told I can't get...
--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 21, 2015 at 8:24 am

#1834690
RE: Concatenate Random Date + Random Time = Error!

Jayanth_Kurup (10/20/2015)
It doesnt 🙂 i figured the random data is generated row by row and not from a set
Even then, the set based method will work for a single...
--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 21, 2015 at 7:58 am

#1834677
RE: Removing duplicate character from string

Not enough coffee, yet. Made a Copy/Paste mistake.
Here's my shot at it. I'll do a performance test tonight (on my way to work just now).
--===== Build a long...
--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 21, 2015 at 6:52 am

#1834620
RE: Removing duplicate character from string

Post removed. I made a mistake.
--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 21, 2015 at 6:12 am

#1834604
RE: Can't CAST as INT and back again

Alvin Ramard (10/20/2015)
Lynn Pettis (10/20/2015)
Alvin Ramard (10/20/2015)
Or:
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;
Remember, the OP is working with SQL Server 2005...
--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 20, 2015 at 12:06 pm

#1834450
RE: Can't CAST as INT and back again

Lynn Pettis (10/20/2015)
Alvin Ramard (10/20/2015)
Or:
d eclare @testTime char(5);
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;
But with the above, the following will also...
--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 20, 2015 at 12:04 pm

#1834448
RE: Can't CAST as INT and back again

This problem deserves "KISS". 😀
--===== Setup the test. This is NOT a part of the solution.
DECLARE @testTime CHAR(5);
SELECT @testTime = '23:45';
--===== Exploit the simplicity of implicit conversions 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)

October 20, 2015 at 12:00 pm

#1834445
RE: Removing duplicate character from string

nadersam (10/19/2015)
Unfortunately the lag function is not supported on the servers on site, they have SQL Server 2008.
Any other option please?.
In future posts, I recommend you post to the correct...
--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 19, 2015 at 3:12 pm

#1834211
RE: Creating Clustered index on a large table

Kim Crosser (10/19/2015)
Jeff Moden (10/17/2015)It's no wonder C-Level types think they don't need a DBA.
But doesn't Microsoft sell SQL Server against Oracle by telling everyone they don't need a DBA...
--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 19, 2015 at 3:05 pm

#1834208
RE: Are the posted questions getting worse?

I realize this post should probably be under "Jobs" but I thought I'd give the dedicated denizens of the thread first crack at it.
Any "hybrid" DBAs that have a good...
--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 18, 2015 at 4:50 pm

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

Kristen-173977 (10/18/2015)
If deleting, say, 90% of the table then worth considering "copying" the 10% to be retained into a new temporary table, and dropping old table / renaming temporary 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)

October 18, 2015 at 4:22 pm

#1833973
RE: Removing duplicate character from string

nadersam (10/18/2015)
Hi All,
I need a neat way please guys to write a function to remove duplicated characters only if they come in sequence.
Examples
darrk should return dark
eeagle should return eagle
redd should...
--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 18, 2015 at 11:47 am

#1833970
RE: Creating Clustered index on a large table

TheSQLGuru (7/7/2010)
Jeff Moden (7/2/2010)
TheSQLGuru (7/2/2010)
Once again we are moving into the world where a professional would be worth his/her weight in gold - or at least silver (metals are SOOOO...
--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 9:37 pm

#1833952

Viewing 15 posts - 19,396 through 19,410 (of 59,072 total)