ScottPletcher

Forum Replies Created

Viewing 15 posts - 331 through 345 (of 7,619 total)

Reply To: Query to find latest full backup with all database except tempDB
```
;WITH cte AS (
    SELECT @@SERVERNAME AS ServerName,
           b.database_name AS DatabaseName,
   ...
```
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 2, 2023 at 1:47 pm

#4319733
Reply To: Table scan shows too many records

Still, be sure you do not have some type of accidental join / cross join that will generate extra rows.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

November 2, 2023 at 1:42 am

#4319354

Reply To: Find the active Group

I'm not sure I understand correctly, nor what result you expect to see from the sample data, but maybe this(?), based on my reading of your requirements so far:

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

November 1, 2023 at 7:11 pm

#4319191

Reply To: Execute AS and Impersonate

Brandie Tarvin wrote:

ScottPletcher wrote:

I would think you would want to use "EXEC AS" on the proc itself rather than inside the proc.

Sadly, without giving IMPERSONATE to the users, I cannot let...

November 1, 2023 at 6:42 pm

#4319168

Reply To: Execute AS and Impersonate

Great, glad it helped. You might want to mark my comment as the solution, to potentially help future readers.

November 1, 2023 at 6:34 pm

#4319165

Reply To: Unexpected deadlocks in test environment but not in production environment

Yeah, in the test environment, that's not surprising.

We're back to having to look at the query plans.

October 31, 2023 at 5:24 pm

#4318412

Reply To: Execute AS and Impersonate

I would think you would want to use "EXEC AS" on the proc itself rather than inside the proc.

October 31, 2023 at 5:20 pm

#4318410

Reply To: Unexpected deadlocks in test environment but not in production environment

@zoggling:

There should have been 2 result sets. Did the first query -- for missing indexes -- not return any rows?

October 30, 2023 at 6:31 pm

#4317745

Reply To: can a DB user access that DB objects without a SQL server login?

I would think so. If any objects have permissions set to "public", for example, I would think UserA could access them.

And naturally for contained dbs permissions would not require a...

October 26, 2023 at 2:13 pm

#4315356

Reply To: Delete large number of rows help

DELETE in batches rather than all at once, as long as each DELETE won't require (up to) a full scan of the table to find the rows to delete.

If you...

October 23, 2023 at 3:03 pm

#4313437

Reply To: Regarding update stats

Compress the data. Look at both page compression and COMPRESS/DECOMPRESS for large char columns.

October 20, 2023 at 1:45 pm

#4311839

Reply To: Expand disk size for SQL 2019 High Avalability Group

You might also look into data compression (both page compression and COMPRESS()/DECOMPRESS() for long text data).

October 19, 2023 at 6:22 pm

#4311258

Reply To: Regarding update stats

Agreed. Partition the data if you need to update stats on a very large table and you can separate active data from inactive data by partitioning.

October 19, 2023 at 3:21 pm

#4311182

Reply To: Nolock and RCSI

NOLOCK makes sense in very limited cases. For example, for lookup tables, such as a table of state codes. There's zero reason to do locking when reading that table.

The big...

October 19, 2023 at 3:18 pm

#4311181

Reply To: Adding dates between data ???

If you want every day in every month, then something like this:

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
  ...

October 16, 2023 at 7:23 pm

#4309247

Viewing 15 posts - 331 through 345 (of 7,619 total)