Jeff Moden

Forum Replies Created

Viewing 15 posts - 7,306 through 7,320 (of 59,072 total)

Reply To: Saving integers as varchar so that I can choose '' (empty) as content

Joe, if the indexing is setup correctly, wrapping the ENDDATE in a COALESCE can cause a range scan (at best) instead of a SEEK because it makes that part 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)

April 9, 2020 at 7:07 pm

#3741866
Reply To: bcp format file and import fixed width text file

frederico_fonseca wrote:
this is based on the file he supplied on his last post. https://www.sqlservercentral.com/wp-content/uploads/2020/04/PRV414WSW_2.txt
Ah... gotcha... I missed that he reposted. Thank you. I'll take a look at this after work...
--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)

April 9, 2020 at 6:11 pm

#3741850
Reply To: bcp format file and import fixed width text file
frederico_fonseca wrote:
I think the record layout is correct - its just that column "provider name" should be further divided into LastName, FirstName, Middle Initial (probably)
```
if object_id('tempdb..#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)
April 9, 2020 at 6:01 pm

#3741847
Reply To: bcp format file and import fixed width text file

smattiko83 wrote:
I've attached a sample text and the import specs. Thanks!
Ok... again, there's something wrong with the sample data you provided. Here's the data using the fixed width font 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)

April 9, 2020 at 4:36 pm

#3741825
Reply To: The Coping Thread

My only trouble is that management feels compelled to force engagement between people with virtual lunches, virtual happy hours, group puzzle solving, sharing workspace pictures, sharing pictures of what 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)

April 9, 2020 at 4:12 pm

#3741822
Reply To: Are the posted questions getting worse?

To be sure, I was just kidding about Redgate. It was pretty obvious which company you were talking about. As for Grant... Kind of like real brothers (we both survived...
--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)

April 9, 2020 at 3:27 pm

#3741802
Reply To: Saving integers as varchar so that I can choose '' (empty) as content

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
Ralph Kimball has a method of not having any nulls in dimension tables. Instead, on the dimension table there are rows with negative keys: -1...
--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)

April 9, 2020 at 2:15 pm

#3741782
Reply To: Partitioning table techniques which one is best way?

Saravanan_tvr wrote:
I am sorry - >5GB and predicted partition key which we have configured in configuration table.
I have to agree with Frederico... 5GB is very small and not worth 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)

April 9, 2020 at 2:10 pm

#3741780
Reply To: Saving integers as varchar so that I can choose '' (empty) as content

jonas.gunnarsson 52434 wrote:
Try normalize the Employee table, to move out rows that may me NULL. Good normalization also gives more understanding of the model. Sometimes NULL, may be acceptable, e.x. 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)

April 9, 2020 at 2:05 pm

#3741778
Reply To: Iterating Over Calculated Tables using Power Query

Gouri Shankar wrote:
@jeff-moden, Yes, I agree with you that the steps I'm trying to get through Power Query can also be done with T-SQL. The main purpose of this article was...
--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)

April 9, 2020 at 1:58 pm

#3741773
Reply To: Saving integers as varchar so that I can choose '' (empty) as content

christoph.kauflin wrote:
I'm curious as to why any row in an Employee table would ever have a NULL (or a -1 alternative or an "empty" alternative) for the Employee number 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)

April 9, 2020 at 1:15 pm

#3741762
Reply To: Saving integers as varchar so that I can choose '' (empty) as content

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
Ralph Kimball has a method of not having any nulls in dimension tables. Instead, on the dimension table there are rows with negative keys: -1 for unknown,...
--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)

April 9, 2020 at 1:11 pm

#3741761
Reply To: bcp format file and import fixed width text file

smattiko83 wrote:
Thanks. I think they are the same when including spaces. They come from the government as a .dat file and they won't change so I'm stuck with these. I've...
--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)

April 9, 2020 at 1:09 pm

#3741759
Reply To: SUM only numbers in varchar column
Phil Parkin wrote:
```
SELECT t.userid
      ,t.rid
      ,Total = SUM(TRY_CAST(t.value AS INT))
FROM temp t
GROUP BY t.userid
    ...
```
--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)
April 9, 2020 at 4:18 am

#3741657
Reply To: different state on the same error message

OrangeBronco wrote:
Chris Harshman wrote:
DamianC wrote:
Does it store these states anywhere though?
State is not really a part of the error itself, but extra information that is added when RAISERROR is called that...
--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)

April 9, 2020 at 4:15 am

#3741656

Viewing 15 posts - 7,306 through 7,320 (of 59,072 total)