Viewing 15 posts - 1,936 through 1,950 (of 7,619 total)
My personal opinion is that this has all the ear-markings of a classic invoice/invoice detail or work-order/work-order detail problem and should be handled as two individual tables rather than...
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".
February 2, 2021 at 1:14 am
Or you could do this 😉
SELECT IDENTITY(int, 1, 1) AS id, A.*,B.*
INTO ##C
FROM A
JOIN B ON X = Y
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".
February 1, 2021 at 8:01 pm
No, there's no direct way to do that.
However, you could use a standard identity column for the second part of the key, and use ROW_NUMBER() to get a sequential number...
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".
February 1, 2021 at 2:20 am
Since you always want to list all recording artists, I'd start at that table. I would think it's possible that info on an album by a new artist might not...
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".
February 1, 2021 at 12:53 am
A common approach to fix that issue in general is to isolate the SUM() in a separate query, so that the later join doesn't increase the total. For example, something...
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".
February 1, 2021 at 12:28 am
Be sure to cluster the table on ( MyDate, $IDENTITY ). The $IDENTITY just insures that the key is unique. That will make the deletes very efficient.
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".
January 30, 2021 at 3:40 am
Yes, it is very important here, to ensure that the nonclus index key is unique.
And, besides, all clustered index key column(s) will always be added to every nonclus index whether...
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".
January 30, 2021 at 3:37 am
I changed to using descriptive variable names. And I added some debugging displays to help you work through any issues. The sample number you provided is failing the test, 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".
January 30, 2021 at 3:33 am
Make the nonclus index UNIQUE yourself, don't force SQL to do it for you. This is important for best performance.
And, yes, you should include the DateDeleted in the index. I...
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".
January 29, 2021 at 3:53 pm
I don't think the current method is a bad approach.
You can use a filtered index to identify the rows needing recalculated.
IF the calcs were easy, I guess you could put...
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".
January 29, 2021 at 3:43 pm
Are you genuinely on SQL 2016 or higher?
If so, I think CHOOSE should save you a lot of coding here.
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".
January 28, 2021 at 12:24 am
No. SQL doesn't really care which proc is using the index, and even trying to capture that kind of data about index usage would be huge overhead. Indexes need tuned...
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".
January 27, 2021 at 6:02 pm
Do you pre-restore the full backup each time to speed up the next restore? (Assuming you have the disk space available -- and if not, you can get the space...
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".
January 26, 2021 at 4:40 pm
@@TRANCOUNT itself is fine, I think. Does UPDATE increment and decrement @@TRANCOUNT as it processes?!
CREATE TABLE #foo (i INT NOT NULL);
INSERT INTO #foo(i) VALUES (0);
SET NOCOUNT ON;
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".
January 26, 2021 at 3:17 pm
How large is the log file? The disk area for a log file must be preformatted, whereas data files do not (assuming you have IFI activated, which is standard now).
Do...
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".
January 26, 2021 at 3:04 pm
Viewing 15 posts - 1,936 through 1,950 (of 7,619 total)