Viewing 15 posts - 6,241 through 6,255 (of 7,627 total)
A "heap" is a table without a clustered index. It may or may not have nonclustered indexes.
You should review heaps carefully. Typically it's far better in SQL Server...
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 19, 2014 at 12:27 pm
Here's my suggested code for this trigger.
CREATE TRIGGER [dbo].[MAYTABLE_A_IUD_WD]
ON [dbo].[MYTABLE]
AFTER DELETE, INSERT, UPDATE
AS
SET NOCOUNT ON;
DECLARE @action char(6)
...
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 19, 2014 at 12:20 pm
N/A.
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 17, 2014 at 12:37 pm
Please try code below. I didn't have any data to test it first, of course.
SELECT
a.ValetNO AS PlanNo,
cross_apply_1.Status1,
...
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 14, 2014 at 4:00 pm
bkmooney (2/13/2014)
By using a table (and adding a csv list id, and a sequence number) won't I be tripling the size of the data that is required to be stored?
Perhaps....
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 13, 2014 at 1:53 pm
I'd still use a table. You just need to add some type of csv list id and a sequence #.
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 13, 2014 at 1:40 pm
If the function just removes an optional trailing '-' followed by other chars, you can avoid a function completely using CROSS APPLY:
UPDATE ts
SET
Col1 = CASE WHEN...
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 13, 2014 at 1:37 pm
amy26 (2/11/2014)
The only thing that I could think of was we do have an if statement that calls another stored procedure...
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 11, 2014 at 3:28 pm
The single index with the included columns can handle queries for both ProductKey alone and for ProductKey and one/both of the included columns. That means you only need one...
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 11, 2014 at 3:17 pm
CREATE TRIGGER [dbo].[tr_a] on [dbo].[A]
AFTER UPDATE
AS
SET NOCOUNT ON
BEGIN TRY
IF UPDATE(STATUS)
BEGIN
INSERT INTO dbo.B
(
col0,
col1,
...
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 11, 2014 at 3:11 pm
Look at Change Tracking; it can identity changes vastly more efficiently than what you're doing now.
If you're on Enterprise Edition, also look at Change Data Capture, which gives you "point-in-time"...
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 11, 2014 at 2:57 pm
You most likely do have nested transactions, either implicitly or explicitly.
Just remove the transaction name from the ROLLBACK, which is not meaningful for SQL Server anyway:
ROLLBACK TRANSACTION /*UPDT_DATA*/
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 11, 2014 at 2:49 pm
You need to run these commands (or the equivalent). Run them online if possible, and using tempdb for sort if possible:
DROP INDEX [IX_PurchaseOrderDetail_ProductKey] ON [dbo].[PurchaseOrderDetail]
CREATE NONCLUSTERED INDEX [IX_PurchaseOrderDetail_ProductKey]...
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 11, 2014 at 2:45 pm
george sibbald (2/7/2014)
ScottPletcher (2/7/2014)
No real DBA would ever use a maintenance plan;
thats a bit over the top surely?
Nowhere does it say using maintenance plans per se is 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 7, 2014 at 2:40 pm
And, if the query uses more than one table, use an alias and prefix all columns with the correct alias name. Remember, we have absolutely no idea what columns...
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 7, 2014 at 1:52 pm
Viewing 15 posts - 6,241 through 6,255 (of 7,627 total)