Viewing 15 posts - 6,226 through 6,240 (of 7,613 total)
Instead of the overhead of a self-join, try this for case #3:
WHERE
myField LIKE '_%' + @SearchParameter + '%' AND
CHARINDEX(@SearchParameter, myField) >...
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 20, 2014 at 2:58 pm
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
Viewing 15 posts - 6,226 through 6,240 (of 7,613 total)