Viewing 15 posts - 4,351 through 4,365 (of 7,619 total)
Another possibility is to leave the main table alone, and create a separate table to hold the partial partids to match on. Use a trigger on the main table...
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 5, 2016 at 3:35 pm
MS provides tablediff.exe for free. It works ... but it is a command-line tool and, like bcp, takes a bit of time/work to get used to and get 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".
April 5, 2016 at 2:31 pm
No, a stop / start of SQL Server will not cause an identity value to go down. Your code must be resetting it for that to 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".
April 5, 2016 at 2:04 pm
Yes, creating a non-clustered index on the clus index column(s) can indeed help performance for certain queries. It's a valid thing to do when that situation exists.
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 5, 2016 at 9:57 am
You could also consider using an A/B table approach. That is, the nightly load goes into a different table name. When the load process is complete, either 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".
April 1, 2016 at 10:24 am
Most likely a LOOP join will be used. That is not guaranteed, of course, but it is likely.
If you want to be sure what join algorithm will be used,...
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, 2016 at 10:18 am
Look at the tempdb log file size and growth amount, particularly if it is a % (which you should change to a fixed amount: never use %, no matter what...
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, 2016 at 10:08 am
Alan.B (3/31/2016)
1. Nothing I posted uses recursion. Look again.
2. People should put unique nonclustered indexes on their dim_date or calendar but dont. It's not like it gets modified all...
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, 2016 at 9:02 am
We don't really know why MS chose to make the PK by default also the clustering index.
Personally I think it was more about making it "easy to use" so 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 31, 2016 at 10:51 am
You should definitely cluster the temp table on the key column as well. It can't hurt, but it can help under certain conditions.
SELECT TOP (0) D.X INTO #tmp FROM...
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 31, 2016 at 9:46 am
Luis Cazares (3/31/2016)
and the PK needs an index to work correctly
Hmm, not sure what you mean there. There's no problem at all explicitly creating a PK as nonclustered, even...
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 31, 2016 at 9:35 am
souLTower (3/30/2016)
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 31, 2016 at 9:26 am
Simply because Microsoft chose to assume that a PK would also automatically be the clustering key (if one didn't already exist, of course). Other dbms's don't make that assumption,...
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 31, 2016 at 9:22 am
Alan.B (3/30/2016)
Alan.B (3/30/2016)
ScottPletcher (3/30/2016)
Alan.B (3/30/2016)
ScottPletcher (3/30/2016)
Alan.B (3/29/2016)
Lastly, ditto the last Thursday of the month:
SET STATISTICS IO ON;
PRINT 'Old way:';
SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]
FROM ...
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 31, 2016 at 8:44 am
Alan.B (3/30/2016)
ScottPletcher (3/30/2016)
Alan.B (3/29/2016)
Lastly, ditto the last Thursday of the month:
SET STATISTICS IO ON;
PRINT 'Old way:';
SELECT DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]
FROM dbo.itvf_CALENDAR('01/01/2014','12/31/2014')
WHERE ...
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 30, 2016 at 11:52 am
Viewing 15 posts - 4,351 through 4,365 (of 7,619 total)