Viewing 15 posts - 2,401 through 2,415 (of 7,613 total)
My opinion, if it is a sequence or an identity really depends on a lot more than we can accurately guess from what was posted. It might be 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".
May 16, 2020 at 4:58 am
It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is...
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".
May 16, 2020 at 4:57 am
It really sounds like you should be using a SEQUENCE rather than an identity, that is, that this INSERT is just to get an identity value, which is no longer...
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".
May 15, 2020 at 1:30 pm
An alternative is like below. Which performs better depends on total data volume and index(es) present:
Edit: I'm at work so I have only a limited time to review all posts,...
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".
May 4, 2020 at 6:19 pm
You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just...
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 29, 2020 at 5:22 pm
The full, unlimited Cartesian join will be huge overhead. At least turn the match of the tables into a standard join:
select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
from SampleTable...
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 29, 2020 at 1:59 pm
D'OH, quite right, a copy/paste mishap I think. I have corrected the original code.
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 27, 2020 at 5:08 pm
Thanks for the queries above. For the PL update, it's not working as expected .
OK, adjust it as you need to. You didn't provide enough details to know which...
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:47 pm
I completely agree with Celko.
At my place of work we had to import some employee data that came from 2 completely separate systems, one for temporary and one for...
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:46 pm
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
Viewing 15 posts - 2,401 through 2,415 (of 7,613 total)