Jeff Moden

Forum Replies Created

Viewing 15 posts - 3,991 through 4,005 (of 59,067 total)

Reply To: Unable to append strings to record from SELECT Statement

Perhaps the following but of study will convince you... 😀
https://docs.microsoft.com/en-US/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15
https://www.w3schools.com/sql/sql_select.asp
https://www.techonthenet.com/sql_server/select.php
https://sql-server-tutorial-blog.blogspot.com/2022/01/sql-server-select-statement.html
--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)

January 28, 2022 at 2:02 am

#3978121
Reply To: Data not returned in Date Range WHERE

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are...
--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)

January 28, 2022 at 1:54 am

#3978118
Reply To: How to reclaim space from a table

WhiteLotus wrote:
every night we perform Index maintenance on that database
As a bit of a sidebar, I can almost guarantee that you're doing you index maintenance wrong. Spend more time doing...
--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)

January 27, 2022 at 8:50 pm

#3978056
Reply To: How to reclaim space from a table

ScottPletcher wrote:
I'd clean up the prod problem first. You can use a backup and research to research the other stuff, if you think it's necessary.
Only if you have a place...
--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)

January 27, 2022 at 8:41 pm

#3978055
Reply To: Create View in stored procedure

Ummmmm... I don't see anywhere where the OP did an EXEC [art].[pCreateArtList_View]... 😉
You DO have to actually execute the stored procedure for IT to create the view.
--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)

January 27, 2022 at 8:33 pm

#3978053
Reply To: Data not returned in Date Range WHERE

ScottPletcher wrote:
Don't use functions in a WHERE clause, that's a very bad habit. Yes, SQL Server now generally covers for you with dates/datetimes, but not always, there are some restrictions. ...
--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)

January 27, 2022 at 8:25 pm

#3978052
Reply To: QQ regarding DB_ID()

ScottPletcher wrote:
Don't rely on the DB_ID() to reference a db. Assume it could change at any time.
If you'll notice, in its system tables, MS stores the db name rather than...
--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)

January 27, 2022 at 8:21 pm

#3978050
Reply To: QQ regarding DB_ID()

David Burrows wrote:
Not sure what you are alluding too but I tried all three options without creating a new database and in each case the id was reused. Only the following...
--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)

January 27, 2022 at 8:18 pm

#3978049
Reply To: Restore autmomation

ramyours2003 wrote:
We are using the sql agent job to take the local drive backup of all the databases on the source and having sufficient permissions on the destination server .xp_CmdShell...
--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)

January 27, 2022 at 2:55 pm

#3977932
Reply To: Optimize Query

@ktflash - Just as a suggestion to help clarify exactly what you want for future posts.. It would be helpful if you'd post a clipping of a spreadsheet or columnized...
--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)

January 27, 2022 at 2:52 pm

#3977931
Reply To: sql query help
sqlenthu 89358 wrote:
And here is the the result I need:
```
businessdayColumnNameColumnViewBUSINESS_RISKMARKET CREDMONETARY FUND
2022-01-01ABCDIM_VIEW2.351.35 / $2.404.80
2022-01-01ABCAPP_VIEW0.95nullnull
2022-01-01DEFDIM_VIEW1.15 / $0.90null71.70 / $122.47
2022-01-01DEFAPP_VIEW5.60nullnull
2022-01-01GHKAPP_VIEW3.85 / $10.08nullnull
```
The pivot columns can vary from week to week....
--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)
January 27, 2022 at 2:46 pm

#3977930
Reply To: QQ regarding DB_ID()

David Burrows wrote:
Out of curiosity, did some tests
Offline/Online - No Change Detach/Attach - No Change Delete/Restore - No Change
However if you attach or restore a database with an id that 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)

January 27, 2022 at 2:21 pm

#3977923
Reply To: Create View in stored procedure

In real life, I've created views from stored procedures with no problems at all using dynamic SQL. There were two reasons to do so... "Pass through" views to support "Swap'n'Drop"...
--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)

January 27, 2022 at 2:03 pm

#3977914
Reply To: Static data masking is not showing in 18.0 above

I don't believe it actually hit the streets. I think it was only in preview version.
--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)

January 27, 2022 at 2:25 am

#3977737
Reply To: non-prod database data scrambling

sqlfriend wrote:
Our database is a pretty complicated enterpirse database which has many tables. we do want data to make sense, and this is for our training enviroment, it has to...
--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)

January 27, 2022 at 1:17 am

#3977722

Viewing 15 posts - 3,991 through 4,005 (of 59,067 total)