Viewing 15 posts - 331 through 345 (of 7,613 total)
I would think you would want to use "EXEC AS" on the proc itself rather than inside the proc.
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".
October 31, 2023 at 5:20 pm
There should have been 2 result sets. Did the first query -- for missing indexes -- not return any 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".
October 30, 2023 at 6:31 pm
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...
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".
October 26, 2023 at 2:13 pm
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...
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".
October 23, 2023 at 3:03 pm
Compress the data. Look at both page compression and COMPRESS/DECOMPRESS for large char columns.
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".
October 20, 2023 at 1:45 pm
You might also look into data compression (both page compression and COMPRESS()/DECOMPRESS() for long text data).
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".
October 19, 2023 at 6:22 pm
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.
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".
October 19, 2023 at 3:21 pm
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...
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".
October 19, 2023 at 3:18 pm
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 (
...
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".
October 16, 2023 at 7:23 pm
Maybe this, if I understand the requirements correctly:
SELECT *
FROM POSDLYCHMQ P
LEFT OUTER JOIN (SELECT P0ISBN, p0CHN, P0WEDT,
COUNT(DISTINCT CASE WHEN cast(cast(sq.P0ADAT as varchar(30)) as date)
BETWEEN DATEADD(DAY,-7,DATEDIFF(DAY,0,cast(cast(sq.P0WEDT...
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".
October 15, 2023 at 5:58 pm
As Jeffrey stated.
Most importantly, make sure you have the best clustering index on all the main tables so that you limit the scan/search activity on the tables. (Hint: most often...
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".
October 15, 2023 at 5:42 pm
You don't need to actually count VLFs yourself, you can use sys.dm_db_log_stats instead (via column total_vlf_count).
You could fairly easily capture previous count(s) and compare them to current count using a...
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".
October 13, 2023 at 3:01 pm
The problem in your original statement is that your calc -- CHARINDEX('^',RIGHT(UNDERLYING_SYMBOL, LEN(UNDERLYING_SYMBOL) - CHARINDEX('^', UNDERLYING_SYMBOL))) -- involves only numeric values, so the result is numeric. Since it's being assigned...
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".
October 12, 2023 at 9:12 pm
update dbo.MainTable
set UNDERLYING_SYMBOL = STUFF(UNDERLYING_SYMBOL, 1, 1, '')
where underlying_symbol LIKE '^%'
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".
October 12, 2023 at 9:10 pm
It's possible it would be far better to cluster the dbo.User_Events table by AuditTimestamp rather than UserID. That could be a significant change, however, so if you would first run...
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".
October 12, 2023 at 2:27 pm
Viewing 15 posts - 331 through 345 (of 7,613 total)