Viewing 15 posts - 3,346 through 3,360 (of 7,619 total)
The "standard" way to do that is to make sure the key doesn't already exist in the table to be insert to. If you need to, delete dups from 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".
June 7, 2018 at 11:32 am
The table's already clustered by [TRANSACTION_TIME]. That's fantastic, it'll make your job so much easier. We'll just want to increase the fill factor from 90 to 99 for the historical...
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".
June 6, 2018 at 3:02 pm
Need to see the definitions of indexes too, especially for the clustered index (or pk, as that will be the clustering key unless you explicitly say it isn't).
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".
June 6, 2018 at 12:07 pm
Is(are) there any index(es) at all on this table? If not, you'll definitely want to "create a brand new table to insert into while the old table data is being...
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".
June 6, 2018 at 11:32 am
What you've stated and shown is contradictory. Is transaction_time a "string", as you stated, or a datetime, as your code shows?
Assuming it's a datetime, you don't really need...
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".
June 6, 2018 at 10:33 am
Below is a re-coded version of the stored proc. It does the UPDATE of IsUsed inline, to reduce (eliminate?) chances of two near-same-time execs of it getting the same NextID...
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".
June 5, 2018 at 2:05 pm
1) How is clustered? By ( Col, Col1) or something else?
2) How is normally searched? By ( Col, Col1 ) or by something else?
3)...
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".
June 4, 2018 at 3:32 pm
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".
June 4, 2018 at 1:41 pm
INSERT INTO dbo.exportLogTable
SELECT
...list_of_all_columns_in_table...
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY tableID ORDER BY tableID) AS row_num
FROM dbo.exportLogTable2
) AS elt2
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".
June 4, 2018 at 12:32 pm
SELECT * INTO Title2 FROM Title;
Not a good idea. A direct SELECT ... INTO new_table can cause system table locking and can cause concurrence issues. You should...
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".
June 4, 2018 at 12:26 pm
To the OP:
I still wish you'd run the script I posted earlier and post the results (on Page 4; can't get the link to it to copy correctly right...
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".
June 4, 2018 at 8:03 am
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".
June 4, 2018 at 7:54 am
You want five separate stored procs regardless. Then, if you want only to have to call 1 main proc from the report/SSRS/whatever, create a parent/"driver" proc that executes those 5...
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".
June 1, 2018 at 1:49 pm
For [AMT].[ItemVersions] :
If [Description] and [PropertyBag] are (1) used more than rarely *and* (2) are used in at least 1 time-critical task *and* (3) for a majority of rows,...
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".
June 1, 2018 at 1:41 pm
Definitely need index overhauls. [And wow do you all love guids!] Would you mind running the script below and posting the results? I always prefer to tune including actual SQL...
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".
June 1, 2018 at 1:10 pm
Viewing 15 posts - 3,346 through 3,360 (of 7,619 total)