Viewing 15 posts - 2,416 through 2,430 (of 7,619 total)
Edit: Corrected typo, as pointed out by Mr. Brian Gale.
UPDATE D
SET MarketCompleted = 0, MarketAssigned = 0
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID,...
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 24, 2020 at 7:31 pm
UPDATE dbo.table_name
SET data_column = REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))
WHERE (LEN(data_column_name) < 12 OR LEN(data_column_name) = 13) AND
(data_column 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".
April 22, 2020 at 4:57 pm
REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), ''))
Some examples:
SELECT data_column,
REPLACE(data_column, '*', ISNULL(REPLICATE('0', 12 - (LEN(data_column) - 1)), '')) AS new_data
FROM ( VALUES('ABC*1234'),('BC*234'),('CDEFG*1'),('D*23456789012')...
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 22, 2020 at 3:11 pm
Assuming the PKs are the clustering keys [*], for best performance you'd need to change the order of the keys in the Data table.
If you always (with perhaps only extremely...
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:55 pm
Create a proc in the master db, with a name beginning with sp_, and you can directly run it against any user db. [If you're in Azure, nevermind, this won't...
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:22 pm
We need the full DDL for all tables involved, including indexes, but for now:
1 How is the dmhdb.dbo.Data clustered?
2 The syntax on your update is not best practice and 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".
April 21, 2020 at 9:15 pm
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
Viewing 15 posts - 2,416 through 2,430 (of 7,619 total)