Jeff Moden

Forum Replies Created

Viewing 15 posts - 2,566 through 2,580 (of 59,086 total)

Reply To: Create table having a StartDate and EndDate for each week

Generate your dates and then play the following function against those dates using "3" as an @DateFirst parameter. Post back if needed.
https://www.sqlservercentral.com/articles/how-to-find-the-start-and-end-dates-for-the-current-week-and-more
The dates don't need to be contiguous. You could...
--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 27, 2022 at 6:12 pm

#4105489
Reply To: Patching AlwaysOn AG

The flow chart at the following MS link identifies where to find all the steps based on your configuration.
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/choose-a-database-engine-upgrade-method
The main article that came from is at the following.
https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances
You just need...
--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 27, 2022 at 6:06 pm

#4105488
Reply To: Get Read and Writes per a duration

aYasser97 wrote:
But this returns the total number of read and writes from the start.
So use LAG to find the difference between the current reading and the previous reading to find...
--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 27, 2022 at 5:50 pm

#4105485
Reply To: Get Read and Writes per a duration

Johan Bijnens wrote:
sys.dm_io_virtual_file_stats returns data since last startup of SQLServer.
To get what you aim for, you should persist its data yourself.
By itself that data means nothing at all.
It's only use...
--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 27, 2022 at 5:48 pm

#4105480
Reply To: Shrink database issue

It could also be that that data isn't being deleted in the same order as the Clustered Index. If that's true, you can recover the space left by the deletes...
--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 27, 2022 at 5:24 pm

#4105448
Reply To: Create table having a StartDate and EndDate for each week
GrassHopper wrote:
ratbak wrote:
Something like this using a tally table or function:
```
DECLARE @startDate date = '2021-01-06'
DECLARE @nextYear date = DATEADD(year,1,DATEADD(year,DATEDIFF(year,0,@startDate),0));
SELECT DATEADD(WEEK,Number,@startDate ) AS StartDate, DATEADD(DAY,6,DATEADD(WEEK,Number,@startDate )) AS EndDate,...
```
--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 27, 2022 at 5:15 pm

#4105441
Reply To: Cone of Uncertainty

+1 Million!
--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 26, 2022 at 7:13 pm

#4105024
Reply To: Get Read and Writes per a duration

aYasser97 wrote:
Hello, I want to be able to view the number of read and writes for a database or (tables in the database) in a day or week or month....
--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 26, 2022 at 7:05 pm

#4105023
Reply To: Good Enough

larry.blake wrote:
Often a project has multiple customers and multiple perspectives. A developer trying to satisfy an external customer, might prioritize that party over an internal customers like a DBA. Time...
--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 26, 2022 at 7:00 pm

#4105018
Reply To: Good Enough

Steve Jones - SSC Editor wrote:
glhohman wrote:
Excellent article, ...
I absolutely love it when the challenge at hand requires a graceful and carefully engineered solution, but I also find it gratify to simply craft something...
--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, 2022 at 6:58 pm

#4104413
Reply To: Get last year data + use search criteria within the last year date range

So test the values of @startdate and @enddate to make sure that they're both >= DATEADD(yy,-1,CONVERT(DATE,GETDATE())) and pop the message if they're 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)

October 25, 2022 at 2:28 pm

#4104259
Reply To: How to remove data and index fragmentation in a table

Jeff Moden wrote:
Getting back to the original post... you do know what sys.dm_db_index_physical_stats() is for, correct?
Ok... After a year, I'll take that as a "No". 😀
--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, 2022 at 2:16 pm

#4104257
Reply To: Why does a select on 1 table end up putting locks on other tables?

Tobar wrote:
From what I read, and think I understood, this code is creating a watchdog that looks for SQL statements that contain the "Having" clause. When it sees that clause,...
--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, 2022 at 1:59 pm

#4104213
Reply To: combine values as pivot

Phil Parkin wrote:
Jeff Moden wrote:
Reminder... STRING_AGG() wasn't available until 2017. The OP posted in a 2016 forum.
Thanks, Jeff, I should have checked that!
Not to worry... I've been bitten by that type 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)

October 25, 2022 at 1:54 pm

#4104212
Reply To: How to select parts that have messages based on feature id and feature value ?

ahmed_elbarbary.2010 wrote:
also i working on sql server 2019
so are using CHECKSUM_AGG will working
or not working good
I don't understand what you're saying here. Is the STRING_AGG() solution working for you but...
--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, 2022 at 1:46 pm

#4104211

Viewing 15 posts - 2,566 through 2,580 (of 59,086 total)