Jeff Moden

Forum Replies Created

Viewing 15 posts - 4,456 through 4,470 (of 59,087 total)

Reply To: Partitioning and Indexes needed on huge table

The big thing for partitioning for me has nothing to do with queries. As Grant indicates, partitioning almost never improves performance. Even so called "partition elimination" is usually slower than...
--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, 2021 at 5:56 pm

#3944230
Reply To: Identify what causes tempdb to increase

Johan Bijnens wrote:
SQL2005 doesn't have XE!
Check your default trace file or run a trace to figure it out !
Ah... thanks, Johan. Shows how much I use EE.
The problem with using...
--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, 2021 at 12:23 pm

#3944043
Reply To: How to get YYYYMMDDHHMMSS

Thank you for the great feedback and the quote from BOL.
Now if we could get the OP to cough up the reason for wanting to do this task. 😀
--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, 2021 at 6:30 pm

#3943810
Reply To: How to get YYYYMMDDHHMMSS

Bruin wrote:
This is getting the data I need, but not the format.
select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')
10212021102437
I want YYYYMMDD before the time portion.
Thanks.
Just out of sheer curiosity, why do 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 23, 2021 at 10:26 pm

#3943663
Reply To: How to get YYYYMMDDHHMMSS

A lot of the methods posed so far do the job but are actually relatively slow because of all the string manipulation using both concatenation and REPLACE. Using a nice...
--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 23, 2021 at 7:33 pm

#3943642
Reply To: Replace Numbers

RonMexico wrote:
My goal is to replace the numbers 1-9 in a column with a different number. For this example the numbers would change to
1=7
2=3
3=9
4=1
5=4
6=2
7=5
8=6
9=8
I tried using the replace function shown...
--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 23, 2021 at 5:44 pm

#3943631
Reply To: How to get YYYYMMDDHHMMSS

MMartin1 wrote:
Jeff Moden wrote:
p.s. Using VARCHAR with no length is one of those code smells that you might want to avoid.
It would default to varchar(30) , but still to wide for...
--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 23, 2021 at 5:37 pm

#3943630
Reply To: TSQL: Create dynamic partition DataRange

Lidou123 wrote:
Any help please?
If you want faster help in the future, please see the first link in my signature line below for one of many different ways to post "Readily...
--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 22, 2021 at 7:10 pm

#3943401
Reply To: Replace Numbers

This looks like it might be used for the old and totally unsafe hack for "encrypting" SSNs and other critical numeric information. If it is, STOP! Do it correctly with...
--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 22, 2021 at 7:04 pm

#3943393
Reply To: How to get YYYYMMDDHHMMSS

planetmatt wrote:
Replace all that with
SELECT FORMAT(GETDATE(),'yyyyMMddhhmmss')
You need to be really careful there. FORMAT is about 43 times slower than just about anything you might want to do with CONVERT. It's...
--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 22, 2021 at 6:05 pm

#3943375
Reply To: Need help on T-SQL
Read up on OUTER joins. This will do it for you.
https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15
```
 SELECT  p.ProjectId
        ,p.ProjectName
     ...
```
--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 22, 2021 at 1:34 am

#3943078
Reply To: Complex Searching

One of the things that I've been fascinated about for a while is how the camera software on our phones not only puts a box around human faces but 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 22, 2021 at 1:20 am

#3943076
Reply To: Identify what causes tempdb to increase

sgambale wrote:
I have sql server 2005 and recently my tempdb is hitting the limits of the logical disk drive and cannot expand. It's usually about 1- 4 GB in size,...
--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, 2021 at 9:45 pm

#3943028
Reply To: Conversion failed when converting the varchar value to data type int.

diglife wrote:
Thanks for the response, i was able to get the solution:
((CAST(Length AS Float)/12)*(16.33)) as Price
Thank you for the help.
You title said you were trying to cast to an INT. ...
--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, 2021 at 9:41 pm

#3943027
Reply To: How to get YYYYMMDDHHMMSS

p.s. Using VARCHAR with no length is one of those code smells that you might want to avoid.
--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, 2021 at 9:35 pm

#3943024

Viewing 15 posts - 4,456 through 4,470 (of 59,087 total)