Viewing 15 posts - 6,241 through 6,255 (of 7,619 total)
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
A CONVERT to format 101 will give us format "mm/dd/yyyy". Then, using STUFF, we can remove the "dd/", leaving you with what you want:
SELECT STUFF(CONVERT(varchar(10), GETDATE(), 101), 4, 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".
February 7, 2014 at 1:47 pm
A basic maintenance plan is just terrible for large databases, because it rebuilds every table and every index regardless. You just can't waste that much I/O in a critical...
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:42 pm
Many of us prefer to generate such code from the table itself, something like below. Uncomment the EXEC(@sql) when ready to actually run the code:
USE css
DECLARE @nonrepeated_cols nvarchar(max)
DECLARE @repeated_cols...
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 11:42 am
You could use subqueries instead of joins, like this:
SELECT
FI.*,
(SELECT TOP (1) C.ClassName_FD
FROM DBA.Class_TB AS C...
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 6, 2014 at 4:14 pm
If you want just the company name returned, this should (will?) be more efficient:
SELECT [Company Name]
FROM dbo.CompanyInfo --#CompanyInfo
GROUP BY
[Company Name]
HAVING
MAX(CASE WHEN [Transaction...
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 3, 2014 at 12:10 pm
Be sure to add a:
SET NOCOUNT ON
to the start of the trigger:
CREATE TRIGGER ... ON ... AFTER|INSTEAD OF ...
AS
SET NOCOUNT ON
...
And for a very minor efficiency gain you should add...
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".
January 29, 2014 at 12:59 pm
Michael Valentine Jones (1/29/2014)
Debora (1/28/2014)
delete from table
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".
January 29, 2014 at 10:45 am
Luis Cazares (1/28/2014)
ScottPletcher (1/28/2014)
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".
January 29, 2014 at 8:12 am
Viewing 15 posts - 6,241 through 6,255 (of 7,619 total)