Jeff Moden

Forum Replies Created

Viewing 15 posts - 16,711 through 16,725 (of 59,101 total)

RE: Duplicate rows confusion

Anil Paneru (10/6/2016)
Hello,
I've this table that I pulled from sqlzoo. I loaded this data in my sql server 2016.
name region area...
--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 6, 2016 at 10:44 pm

#1905314
RE: How to flatten rows when in unknown for a PIVOT

Ah... if you want a bit of a bonus column that identifies how many addresses there are for each AddressKey, we just need to add another aggregation.
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 6, 2016 at 10:19 pm

#1905313
RE: How to flatten rows when in unknown for a PIVOT

First, thank you for posting truly readily-consumable test data. You sure made it easy for me to concentrate on the answer.
Here's the code to solve your problem. CROSSTABs...
--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 6, 2016 at 10:15 pm

#1905312
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

To be honest and IMHO, a simple auditing system would serve you and the task of proof much better than doing a linked list. You could even setup 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)

October 6, 2016 at 9:54 pm

#1905311
RE: Do you have DBA syndrome?

joshua 15769 (10/6/2016)
I do have to sometimes say "NO" to something but I'm normally able to suggest an alternative and the reason why the alternative is better.
This is what 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)

October 6, 2016 at 9:37 pm

#1905309
RE: Error: 8355, Severity: 16, State 1

ALTAIR SOUZA (10/6/2016)
You'll have to set a new broker, after sqlagent is stoped, run:
ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
In sys.databases column is_broker_enabled will be set to 1 and...
--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 6, 2016 at 9:02 pm

#1905308
RE: Need Suggestion on How to work on Parsename command with dot or decimal?

Or use a real splitter.
--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 6, 2016 at 8:59 pm

#1905306
RE: Delete files that are older than 7 days

rsampson (10/6/2016)
Using strictly ssis tasks, for each file enumerator, script task to get file info,
conditionally delete file, done.
Works great if the directory is not empty, barfs a warning, then fails...
--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 6, 2016 at 8:33 pm

#1905304
RE: Good hardware-slow performance. Possibly misconfigured.

RVO (10/6/2016)
24 Partitions mystery is clear now.
I talked to DBA.
They are planning to partition {Pink} table
and created these partitions as placeholders.
They partitioned {Poink_parttioned} tables will be moving data to {Poink}...
--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 6, 2016 at 7:06 pm

#1905303
RE: Select all child nodes

Alan.B (10/6/2016)
No need to use the Query method, it would be more efficient to just use value like this:
SELECT Id ,
...
--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 6, 2016 at 6:59 pm

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

ankur_libra (10/6/2016)
Hi,
Please also monitor following on databases involved:-
1. Index Fragmentation
2. Statistics Update
3. Blocking to ensure that jobs are not being blocked by any oher process
4. I/O & Waitstats (As...
--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 6, 2016 at 6:31 pm

#1905301
RE: Are the posted questions getting worse?

Brandie Tarvin (10/6/2016)
This doesn't affect me because I'm on "high ground".
Isn't that where it's most windy? :blink:
I hope everything turns out ok. You guys be safe.
--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 6, 2016 at 5:15 pm

#1905295
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Hugo Kornelis (10/6/2016)
... then at least add the failsafe trick that is suggested earlier in this discussion so that you'll get an error rather than incorrect results if the processing...
--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 6, 2016 at 5:12 pm

#1905294
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

If you're using SQL Sever 2012 or above, you shouldn't be using the "Quirky Update" method... period. 😉
--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 6, 2016 at 5:05 pm

#1905292
RE: Do you have DBA syndrome?

Gary Varga (10/6/2016)
Jeff Moden (10/5/2016)
...I do have to sometimes say "NO" to something but I'm normally able to suggest an alternative and the reason why the alternative is better...
I don't...
--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 6, 2016 at 9:03 am

#1905202

Viewing 15 posts - 16,711 through 16,725 (of 59,101 total)