Viewing 15 posts - 1,561 through 1,575 (of 7,620 total)
Did you try 'EXECUTE AS USER ='? Would that help you 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".
May 21, 2021 at 4:21 pm
SQL itself will automatically update stats when it determines that it needs to. By default, that will use sampling.
You can force SQL to stick with a FULLSCAN unless someone explicitly...
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 20, 2021 at 2:46 am
CREATE TABLE #data (
col1 varchar(10) NOT NULL,
replication_count int NOT NULL
)
INSERT INTO #data VALUES('abc', 5),('defgh', 3),('xx',...
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 19, 2021 at 3:25 pm
There's a technique callled "Tally table" that is perfect for doing that. A tally table is simply a table with a single column containing 1, 2, 3, .... The beauty...
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 19, 2021 at 3:21 pm
Glad it helped out! Thanks for the feedback.
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 19, 2021 at 3:07 pm
Hmm, you need explicit code -- great, that's what we're here for -- but you didn't give us:
table DDL (this is what we really need, not just a table name...
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 18, 2021 at 8:20 pm
I've always liked the idea of assigning a unique standard alias for every table and always using it. You can add a 1, 2, etc., if you 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".
May 18, 2021 at 4:46 pm
For performance, first try converting it into an inline TVF; those get compiled directly into the code whereas a multi-line TVF does not.
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
CREATE...
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 18, 2021 at 4:18 pm
D'OH, sorry, didn't realize SQL wouldn't let you OFFLINE the db in that case. But, one of the reasons I avoid SINGLE_USER like the plague is the issues it causes.
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 18, 2021 at 4:07 pm
You need to make sure that TABLOCK is specified on the load so that you get minimal logging. I'm not 100% sure how to do that via SSIS but look...
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 18, 2021 at 4:04 pm
Again, I'd say create the clustered index before loading the table. Use row compression, since that could save some I/O which would reduce the time needed.
Are other processes using "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".
May 18, 2021 at 8:23 am
SQL does not like that where statement line.
What specifically do you mean by that? What error message did you get?
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 18, 2021 at 8:07 am
CREATE TABLE ##temp_table_order#s ( id int NOT NULL PRIMARY KEY, data_field varchar(8000) );
INSERT INTO ##temp_table_order#s
SELECT id, data_field
FROM ##temp_table
WHERE data_field LIKE 'Order #%'
SELECT
Order_No,
...
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 18, 2021 at 8:03 am
I've always liked the idea of assigning a unique standard alias for every table and always using it. You can add a 1, 2, etc., if you need to use...
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 17, 2021 at 10:47 pm
No, you can miss user(s) of the db that way. Instead, force the db offline; then bring it back online and immediately USE the db yourself and then set 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".
May 17, 2021 at 6:32 pm
Viewing 15 posts - 1,561 through 1,575 (of 7,620 total)