Viewing 15 posts - 1,126 through 1,140 (of 7,613 total)
You must use the alias to insure accurate UPDATEing. You can, and quite often will, get inaccurate results when using the full table name instead. (The error may now 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".
November 29, 2021 at 3:13 pm
I agree, sounds very much like class work.
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".
November 29, 2021 at 3:07 pm
Actually you're pulling more than 52 weeks' worth that way, but here goes. Note that since you provided no sample data to test it with, you'll need to do 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".
November 29, 2021 at 3:04 pm
Excellent idea. That gets much closer to the given size.
There's one very minor miscalc above, in that I believe page data can never exceed an absolute max of 8060 bytes,...
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".
November 24, 2021 at 5:25 am
SELECT title,
SUBSTRING(title, CHARINDEX('E00', title), 200),
Computed = CASE WHEN CHARINDEX('E00', title) = 0 THEN NULL
...
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".
November 24, 2021 at 12:33 am
Would you script out the table to make sure no other column "snuck" in there?
And please verify the row count:
SELECT SUM(rows) AS row_count
FROM sys.partitions
WHERE object_id = OBJECT_ID('dbo.SIData_222222_88_20211103_1436')...
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".
November 22, 2021 at 7:20 pm
Then follow the standard method and delete from the table in batches in clus key order.
In order to do that, you might first have to go thru the table and...
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".
November 22, 2021 at 3:36 pm
Well, if the datetime in the clus index is current datetime, you're ok. Otherwise, not good, since you wouldn't be able to easily tell when copying the "good" rows 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".
November 22, 2021 at 3:23 pm
I got an email with just the initial q, I just quickly reviewed all the comments above, but my first thought was this:
;WITH ACTUALS AS
(
SELECT...
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".
November 22, 2021 at 3:12 pm
Is the existing clustered composite key unique? And explicitly declared to SQL as unique?
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".
November 22, 2021 at 3:02 pm
Yes, absolutely. The test server has no idea what identity values are being assigned in prod and vice versa.
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".
November 22, 2021 at 2:58 pm
;WITH cte_dates AS (
SELECT CAST(GETDATE() AS date) AS today,
DATEPART(HOUR, GETDATE()) AS today_hour,
...
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".
November 19, 2021 at 3:18 pm
The code below generates SQL for from 1 to 20 @Tank entries, based on which ones were actually provided in the call to the proc, and then EXECs the 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".
November 18, 2021 at 7:11 pm
SELECT
[Shelf Location], date /*, ...*/
FROM (
SELECT *, DENSE_RANK() OVER(PARTITION BY [Shelf Location] ORDER BY date DESC) AS rank_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".
November 17, 2021 at 7:58 pm
I see no way to accurately parse that data will all the variations presented (and any number even worse than that that could happen).
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".
November 17, 2021 at 4:46 pm
Viewing 15 posts - 1,126 through 1,140 (of 7,613 total)