Viewing 15 posts - 2,386 through 2,400 (of 7,619 total)
No.
In particular, for example, often an EXISTS() check is more efficient that an INNER JOIN when you just to need to verify that a matching row exists in another table.
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".
July 13, 2020 at 4:12 pm
You'd also want to keep the closest building id in the table structure (with a datetime of when it was computed) and that distance.
The reason for that is to provide...
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".
July 13, 2020 at 3:58 pm
--if you just want to see if a dup key exists:
SELECT TOP (1) key_col1, key_col2, COUNT(*) - 1 AS duplicate_count
FROM dbo.table_name
GROUP BY key_col1, key_col2
HAVING COUNT(*) > 1
--if...
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".
July 13, 2020 at 3:25 pm
Any function against a table column in a WHERE clause is not a good idea and is potentially bad for performance, because it prevents index seeks for that comparison.
Remember that...
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".
July 13, 2020 at 3:22 pm
SELECT *
FROM tblInfo
WHERE @parameter IS NULL OR fldinfo = @parameter
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".
July 13, 2020 at 1:20 am
Don't forget all the overhead bytes that SQL requires internally to manage the row.
You might have to move some of the values to a second table, with a 1-1 relationship...
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 23, 2020 at 12:47 pm
We'd have to see the table DDL to find the exact cause of that.
Definitely force the varchar(max) data off row, as you have done.
You should also page compress the table...
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 22, 2020 at 3:39 pm
You need to call the Google address parser/"splitter" function or use some other address parser that does this for you. You will never be able to do this accurately by...
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 17, 2020 at 4:06 pm
SELECT t1.ID2, SUM(t1.Amount1) AS Amount1, ISNULL(MAX(t2.Amount2), 0) AS Amount2
FROM dbo.table1 t1
LEFT OUTER JOIN (
SELECT ID2, SUM(Amount2) AS Amount2
FROM dbo.table2
...
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 11, 2020 at 2:14 pm
Yeah, that's rather tricky. All I can think of now is to create a stored proc with an " EXEC AS 'powerful_user_name' " clause, then give the ScriptRunner the authority...
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, 2020 at 2:48 pm
MS has actually provided complex formulas for years. But it's so difficult to come up with accurate numbers to go into the formulas, and the result is only as good...
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, 2020 at 2:00 pm
Many of my dbs are in simple mode since, for those for the most part, we can reload data from the past 12 hours rather easily. So I can 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".
June 2, 2020 at 1:58 pm
I really wish SQL would add an option to rebuild within a specified key range. That would allow the benefits of a full rebuild without the downside of forcing it...
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 1, 2020 at 9:07 pm
As to reorganization, I believe it's still best practice to use reorg for columnstore tables (I'm on SQL 2016). Full rowgroups won't be affected at all by this process, but...
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 1, 2020 at 9:04 pm
There's no magic formula to tell you the best fillfactor for 3,000 indexes. Or for 10 indexes, for that matter.
Taking a step back for a broader look, the single most...
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 1, 2020 at 8:46 pm
Viewing 15 posts - 2,386 through 2,400 (of 7,619 total)