Viewing 15 posts - 2,221 through 2,235 (of 7,613 total)
> Missing the point on this last one - you have multiple joins, not just 1 join. <<
It depends on how you look at it. The view itself has only...
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 22, 2020 at 10:06 am
DECLARE @job_name nvarchar(128)
SET @job_name = 'JobA'
SELECT
job_name AS name,
COALESCE(CAST(j.enabled AS varchar(1)), 'Job not present') AS enabled,
CASE...
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 22, 2020 at 9:59 am
For SQL Express, you'd need to use the task scheduler in Windows directly, instead of using SQL Server jobs. It's more of a pain, but it should work.
You should explicitly...
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 22, 2020 at 1:36 am
That's why I put the note: my EXISTS example does NOT reference an outer table. A rare situation perhaps, but not impossible, more likely:
WHERE EXISTS(SELECT 1 FROM sys.tables WHERE name...
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 21, 2020 at 2:37 pm
Wouldn't that count every reference as a "join", including a select from a single table? And any other single select from a table.
SELECT ...
FROM dbo.table1
SELECT ...
FROM dbo.table1
WHERE EXISTS(SELECT 1 FROM...
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 21, 2020 at 12:55 pm
I think should be pretty accurate, at least without going to a lot more trouble to write. Yes, it uses recursion, because we need to find every JOIN. If somehow...
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, 2020 at 9:24 pm
When using NOT IN, you should make sure a NULL value does not appear in the results. For example, like below. Try that first and see if it cleans up...
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, 2020 at 5:25 pm
SELECT T1.DocumentNo, T2.LongSentence
FROM Table2 T2
INNER JOIN Table1 T1 ON T2.LongSentence LIKE '%' + T1.DocumentNo + '%'
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, 2020 at 5:17 pm
Yes, yes and yes, as long as the proc not being EXEC'd by each other.
That is, this should work fine:
--ok
EXEC sp1
EXEC sp2
EXEC sp3
because SQL will clear temp tables created within...
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 18, 2020 at 6:35 am
Here's my version, also no recursion, with added alias names for more clarity:
SELECT
start_date, end_date,
full_weeks * 2 +
...
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, 2020 at 1:49 pm
Sorry to the OP for this bit of a side issue.
As noted by others, there's no easy, sure way to know which tables are / aren't being used. Periodically capturing...
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, 2020 at 1:16 pm
Since it's based on name, I just exclude tables that start with 'tmp_', 'temp_', 'work_' or 'wk_'. Seems just as easy as excluding schemas to me. And I don't have...
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, 2020 at 1:15 pm
I don't use separate schemas (nor would I personally recommend it, given the general hassle of it -- different approaches for different folks). How does one resolve it when the...
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, 2020 at 9:37 pm
Again, as I stated, it took only 1 more byte to store YYYY vs YY. Dates were stored as numeric and not char, also to save space. YYMMDD...
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, 2020 at 3:33 pm
OK, if you set the fillfactor to 51 and rebuild you will have far fewer splits, perhaps none, because you've already at least doubled the size of the table. Any...
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, 2020 at 2:57 am
Viewing 15 posts - 2,221 through 2,235 (of 7,613 total)