Viewing 15 posts - 1,531 through 1,545 (of 7,619 total)
Came across an actual query which gave me an idea about another version of the test script for DISTINCT vs. GROUP BY.
create table #BalanceType (
id int...
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".
June 8, 2021 at 2:37 pm
SELECT j.name AS job_name, jh.*
FROM (
SELECT jh.job_id, MAX(jh.instance_id) AS instance_id
FROM msdb.dbo.sysjobhistory jh
WHERE jh.run_status = 0...
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".
June 7, 2021 at 6:22 pm
You need more than just year to accurately calculate age. Some people born in 2011 are 10 years old, but some are only 9 years old.
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".
June 7, 2021 at 3:38 pm
My code works fine with that format of string:
;WITH test_data AS (
SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(a) Mary Edwards -15425'),...
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".
June 7, 2021 at 3:35 pm
Make catalog changes to that your new tempdb file set up takes effect the next time SQL starts up. You just have to delete the old tempdb files yourself. I...
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".
June 7, 2021 at 3:22 pm
SQL provides a CASE expression to test for things like that:
SELECT ..., CASE WHEN PERSON.TERMINATION_DATE > '19000101' THEN 'N' ELSE '' END AS TERMINATION, ...
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".
June 7, 2021 at 3:19 pm
LTRIM(RTRIM(SUBSTRING(name, CHARINDEX(')', name) + 1, PATINDEX('%[+-][0-9]%', name) - (CHARINDEX(')', name) + 1))))
;WITH test_data AS (
SELECT * FROM ( VALUES('(b) Joe Brown -12563'), ('(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".
June 7, 2021 at 3:15 pm
It depends. If there's a large volume of data written to your SQL log even when there are no errors, then you should probably switch the logs more often (and,...
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".
June 4, 2021 at 3:32 pm
No directly usable data to test with, but I think this should at least be close:
SELECT PH.*, PA.*
FROM dbo.PolicyHeader PH
OUTER APPLY (
SELECT TOP...
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".
June 4, 2021 at 3:27 pm
Add WITH (TABLOCK) hint after the table name.
Examples:
UPDATE dbo.table_name WITH (TABLOCK)
SET ...
INSERT INTO dbo.table_name WITH (TABLOCK)
SELECT ...
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".
June 4, 2021 at 3:00 pm
I think you can combine the first two queries into a single query, also avoiding having to rank all the totals:
WITH cteCombined AS
(
SELECT TOP (1) WITH...
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".
June 3, 2021 at 9:10 pm
For the much more straightforward approach:
SELECT DISTINCT N
vs
SELECT N
...
GROUP BY N
my machine shows a slight edge for DISTINCT. That's what I've seen in normal usage, too. For a straight list...
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".
June 3, 2021 at 4:01 pm
No, it's not a row total, as Steve noted. It's the number of seek operations since the stats for that index were created.
One query that reads, say 5,000 rows, might...
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".
June 3, 2021 at 3:09 pm
Well, good luck. Since your posts seem to disagree with themselves, I don't think I can be of any more assistance.
"we are getting emails of large queries taking longer than...
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".
June 2, 2021 at 2:31 pm
Hmm, do you have tables under different schema names? Is it possible that dbo.same_table_name is empty, but some_other_schema.same_table_name has 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".
June 2, 2021 at 12:49 pm
Viewing 15 posts - 1,531 through 1,545 (of 7,619 total)