Jeff Moden

Forum Replies Created

Viewing 15 posts - 5,911 through 5,925 (of 59,072 total)

Reply To: Help Transforming TSQL to MSSQL

jbeclapez wrote:
Hello,
I am not great with SQL and the statement I wrote is in TSQL and it is working. I would like this same statement to run in MSSQL, can...
--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)

December 11, 2020 at 10:58 pm

#3819563
Reply To: Help Transforming TSQL to MSSQL
Jonathan AC Roberts wrote:
Are you terminating the preceding statement with a semicolon?
You can start statements that begin with "WITH" with a semicolon:
;WITH ret AS(
Now, there's an interview question if...
--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)
December 11, 2020 at 10:53 pm

#3819561
Reply To: Perf hit for Increasing tran log frequency

TangoVictor wrote:
we are currently doing a 1 hour tran log backup for a system, I'd like to push that up to 1 minute. Is there a performance hit to be...
--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)

December 11, 2020 at 10:20 pm

#3819555
Reply To: comma issues in sql statement

ScottPletcher wrote:
The standard pattern uses 0 as the base, not 1 or -1 or some other "cute" trick.
No sir... There are a couple of "standards". The "standard" that YOU 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)

December 11, 2020 at 9:00 pm

#3819536
Reply To: Creating indexes on a very large table takes over 5 hours

ScottPletcher wrote:
Those extra steps are not required for SQL 2016, at least according to MS internals folks. For example, your statement of needing an explicit CREATE TABLE statement? Again, I...
--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)

December 11, 2020 at 8:57 pm

#3819535
Reply To: Extract data with variable column names and order

@aaron.reese ,
If you're absolutely stuck with doing the way that you've outlined, you've posted in a 2017 forum so lets use some of the functionality available.
First, here's the test data...
--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)

December 11, 2020 at 8:33 pm

#3819534
Reply To: Extract data with variable column names and order

aaron.reese wrote:
...the extracts will be ad-hoc so I'm not concerned about speed, rather about maintainability.
Right now, the "config" table is setup only to provide a list of columns 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)

December 11, 2020 at 8:14 pm

#3819530
Reply To: comma issues in sql statement

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
Convoluted method, don't do it.
Only to those that think so.
No. The human brain is designed to recognize patterns. Sticking with a familiar patterns drastically speeds up recognition...
--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)

December 11, 2020 at 4:14 pm

#3819443
Reply To: Creating indexes on a very large table takes over 5 hours

@pamkagel ,
Ok... you said that the entire table is generated nightly. That makes me want to assume that you're not actually doing any updates or additional inserts to the 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)

December 11, 2020 at 7:31 am

#3819249
Reply To: Extract data with variable column names and order

aaron.reese wrote:
not wanting to get off-topic and there are dozens of threads on EAV on this site, why would you prefer EAV for this problem. I can turn it into...
--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)

December 11, 2020 at 3:15 am

#3819175
Reply To: comma issues in sql statement

John_2 wrote:
I'm clueless after trying everything and searching for others with this experience still getting the same errors again and again.
Please help me out with this error
Like I said earlier...
--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)

December 11, 2020 at 2:57 am

#3819170
Reply To: comma issues in sql statement

ScottPletcher wrote:
Convoluted method, don't do it.
Only to those that think so.
--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)

December 11, 2020 at 2:53 am

#3819169
Reply To: Inventory Running Total & calculations
Just as a bit of a drive by shooting, the following code...
```
select

DATEPART (d, datetime) AS Day,
CASE DATEPART(DW, datetime)
    WHEN 1 THEN 'Sun'
  ...
```
--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)
December 10, 2020 at 4:28 pm

#3819023
Reply To: Extract data with variable column names and order

aaron.reese wrote:
(at least it's not EAV!)
Actually, too bad it's not! 😉
--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)

December 10, 2020 at 4:17 pm

#3819021
Reply To: Inflow / outflow report per day

Again, can't use SUM() OVER for the running total on this one. The OP said he's actually using 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)

December 9, 2020 at 6:54 pm

#3818711

Viewing 15 posts - 5,911 through 5,925 (of 59,072 total)