Viewing 15 posts - 2,416 through 2,430 (of 7,613 total)
1 The nonclus indexes should be unique, not non-unique. Worst case, add an identity to the table and use that to make unique non-clus keys.
2 Are the nonclus indexes page...
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".
April 21, 2020 at 9:09 pm
Create two base source tables: one with only current_record = 'Y' rows, and one with only current_record <> 'Y' rows. Create a SQL constraint on the each table that enforces...
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".
April 21, 2020 at 9:00 pm
1.2M rows * 10KB bytes per row is a huge amount of data, ~12GB. I can imagine it could take 6 mins to transfer that much data.
It would be very...
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".
April 20, 2020 at 6:27 pm
As I noted above, views sys.foreign_keys and sys.foreign_key_columns should give you FK details. I can't imagine how your SQL doesn't have those views, since they've been around a long time.
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".
April 1, 2020 at 6:38 pm
Agreed.
Views sys.foreign_keys and sys.foreign_key_columns should give you FK details.
Also look at sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.
For just any reference to another object, not for a defined relationship, try sys.sql_expression_dependencies. For example, 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".
April 1, 2020 at 6:13 pm
I prefer this method:
declare
@start_dt datetime='2019-12-02',
@end_dt ...
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".
April 1, 2020 at 4:05 pm
You need to be aware that "Execution plan Query cost relative to the batch" is meaningless. SQL is very often quite wrong on this number. Just ignore it!
You need to...
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".
March 26, 2020 at 8:50 pm
Great. I guess that confirms it gave you the results you needed :-).
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".
March 26, 2020 at 5:45 pm
LEFT OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY customer, ApplicationName ORDER BY Created DESC) AS row_num FROM [CIS].[dbo].[patches]
) AS patches ON patches.customer = a.number and patches.ApplicationName = custapps.app AND patches.row_num...
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".
March 26, 2020 at 5:10 pm
SELECT ...same_as_before_except_add_patches_columns...
FROM customers
LEFT OUTER JOIN (
SELECT *, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Created DESC) AS row_num
FROM [CIS].[dbo].[patches]
) 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".
March 26, 2020 at 4:47 pm
What I usually do in those situations, if the file's not way too extremely large (2GB max file size; for any size up to 1.5GB, I've found that it takes...
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".
March 24, 2020 at 9:27 pm
I think this will give you what you need. I went down to the minute to allow bookings like '10:10' to '10:45'. You may not have that now, but 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".
March 23, 2020 at 5:32 pm
You're right, you can't avoid dynamic SQL here. You need dynamic SQL here to insure you get the best query plan.
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".
March 20, 2020 at 2:36 pm
Steve and Jeff, thank you. Good to have other eyes on this. 🙂 What should I do with the earlier incorrect code? Wipe it out? Should I have kept...
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".
March 19, 2020 at 8:50 pm
I'd stick with something more SQL-ish, and also more readily readable and understandable, like below. If you're converting Access to SQL Server, it's convenient to use IIF, otherwise avoid 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".
March 19, 2020 at 8:06 pm
Viewing 15 posts - 2,416 through 2,430 (of 7,613 total)