Jeff Moden

Forum Replies Created

Viewing 15 posts - 4,951 through 4,965 (of 59,072 total)

Reply To: Split String into columns
Jeffrey Williams wrote:
Here is a little different take on the solution:
```
 Select [Code]        = ltrim(substring(v.Item, 1, p01.pos - 2))
  ...
```
--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)
July 11, 2021 at 9:04 pm

#3906752
Reply To: Split String into columns
Steve Collins wrote:
Similar to the accepted solution but without the common table expression. It appears Jeff began to code it one way and then switched
```
select max(case when sp2.ItemNumber=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)
July 11, 2021 at 7:28 pm

#3906742
Reply To: Do We Need to Learn Linux?

Heh... "Do We Need to Learn Linux?"
I guess the obvious joke here would be "GOOD SIR!!! CONTAIN YOURSELF!!!"

--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)

July 10, 2021 at 11:21 pm

#3906577
Reply To: Permissions needed to run query on master db

My take on it all this is not only "No" but "Hell No".
First of all, it would be a security violation to have a bunch of "unknowns" running a bunch...
--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)

July 10, 2021 at 9:25 pm

#3906541
Reply To: Rows to Column
Thank you for the readily consumable test data. Details for one solution are in the comments in the code below...
```
--===== One solution.
     --...
```
--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)
July 10, 2021 at 8:25 pm

#3906528
Reply To: text column in date format in excel to datetime column in sql server via SSIS
I don't know much about SSIS but there MUST be some easy way to do the following in SSIS just like it's easy in T-SQL.
```
  ...
```
--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)
July 10, 2021 at 7:38 pm

#3906511
Reply To: Split String into columns
```
--===== Test string 
DECLARE @String VARCHAR(8000) = 
' ;T41,Vending,9000011;T42,Laundry,9000012;T43,Car Wash,9000013;T44,Amusement,9000014;T45,Promotion,9000015;T46,Transit,9000016;T47,Other,9000017;T73,?,9000011'
;
--===== Solution
   WITH cteSplit1 AS
(--==== Split the groups out based on the ";" delimiter, not returning...
```
--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)
July 9, 2021 at 5:18 pm

#3906151
Reply To: Clustered vs Nonclustered Columnstore Update Performance

MadOwl wrote:
Add to that that we have ~4000 tables to maintain...
Are they mostly ColumnStore or RowStore?

--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)

July 9, 2021 at 4:45 pm

#3906144
Reply To: when the data of sys.dm_exec_query_stats will be reset/removed

Even Grant will tell you that's just scratching the surface on such things. Before you use one of them, you really need to study the documentation.
--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)

July 8, 2021 at 9:51 pm

#3905898
Reply To: Clustered vs Nonclustered Columnstore Update Performance

I've not had to work with ColumnStore yet but that sounds perfect, Eric.
--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)

July 8, 2021 at 9:46 pm

#3905895
Reply To: when the data of sys.dm_exec_query_stats will be reset/removed

892717952 wrote:
Jeff Moden wrote:
892717952 wrote:
I notice that even if we don't restart SQL Service, the data of sys.dm_exec_query_stats( sys.dm_exec_sql_text() and sys.dm_exec_query_plan() ) will be reset or removed/refreshed. when the data 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)

July 8, 2021 at 2:37 pm

#3905754
Reply To: Happy Birthday USA!

Eric M Russell wrote:
skeleton567 wrote:
... Eric, maybe I didn't understand your meaning then when you said: "so for everyone to mount a flag in their front yard seems to me like...
--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)

July 8, 2021 at 2:31 pm

#3905752
Reply To: How to use parameter in set function and calculated field in where
fahad.kazi wrote:
Hi Jeffrey,
The data type is date time because when I use query wizard to put filter and choose a date - the SQL code says:
```
WHERE

pr_time_attendance.h_date >=...
```
--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)
July 7, 2021 at 8:02 pm

#3905494
Reply To: Happy Birthday USA!

Wow... ok. I raise my drink to all of you, thank my lucky stars that I "know" all of you, and be incredibly thankful that we all live in 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)

July 7, 2021 at 6:32 pm

#3905470
Reply To: How to use parameter in set function and calculated field in where

fahad.kazi wrote:
Hi Jeffrey,
Sorry for asking a noob question but why should I change the code if it is working for me? Is Jeff's more resource-efficient?
Also maybe my Microsoft Report Builder...
--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)

July 7, 2021 at 6:08 pm

#3905455

Viewing 15 posts - 4,951 through 4,965 (of 59,072 total)