Viewing 15 posts - 4,936 through 4,950 (of 7,613 total)
rootfixxxer (7/29/2015)
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".
July 29, 2015 at 9:01 am
rootfixxxer (7/29/2015)
@ScottPletcher
I didnt use the pf as key, because it's not unique, i can have several comments for each pf...
Not a problem. But, if you prefer, add the id...
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".
July 29, 2015 at 6:43 am
It's a virtual certainty that the clustering key should be pf, not a meaningless id. That would also give better performance across the board on that table. ...
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".
July 28, 2015 at 1:09 pm
Were you able to use the rename approach?
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".
July 28, 2015 at 10:50 am
Yes, non-trusted constraints could seriously degrade your performance because SQL won't be able to use the index associated with those constraints and thus might have to scan the table or...
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".
July 27, 2015 at 10:27 am
But don't you have to save the permissions on those objects before you drop them?
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".
July 24, 2015 at 12:59 pm
I just rename the existing user table type to some other type name -- which you can then immediately drop -- then (re)create the original type name with the new...
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".
July 24, 2015 at 12:38 pm
--Edit:
--this provides the years which were serviced;
--could add years not serviced by generating all years, using a tally table,
--and doing a left join from that.
--unfortunately, my work server blocks...
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".
July 24, 2015 at 11:19 am
I suppose you could. I think inevitably you will damage some data eventually by trying to have the same column try to contain plain data and encrypted 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".
July 24, 2015 at 10:32 am
Looks ok. But you don't need to do a full count of rows to determine INSERT vs UPDATE:
CREATE TRIGGER dbo.IU_TestSSNs ON dbo.TestSSNs
INSTEAD OF INSERT, UPDATE
AS
SET NOCOUNT ON;
OPEN...
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".
July 23, 2015 at 2:36 pm
Lynn Pettis (7/23/2015)
ScottPletcher (7/23/2015)
...
I do say never. Never use ISNULL() in a WHERE or JOIN -- it's never needed, and it can be very harmful.
Never specify a unicode literal...
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".
July 23, 2015 at 2:27 pm
Lynn Pettis (7/23/2015)
ScottPletcher (7/23/2015)
DECLARE @starting_year int
SET @starting_year = 2012
SELECT subquery.ID, e.NAME, subquery.Year, subquery.Max_Date
FROM (
...
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".
July 23, 2015 at 2:02 pm
Lynn Pettis (7/23/2015)
ScottPletcher (7/23/2015)
Lynn Pettis (7/23/2015)
WayneS (7/23/2015)
ScottPletcher (7/23/2015)
Btw, the WHERE clause can be improved to allow an index seek, if applicable:WHERE
LEFT(name, 5) = 'APTMP'should be:
WHERE name LIKE 'APTMP%'
Good luck...
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".
July 23, 2015 at 1:58 pm
Here's an alternate method: with only two columns, I'd probably skip using CROSS APPLY:
DECLARE @starting_year int
SET @starting_year = 2012
SELECT subquery.ID, e.NAME, subquery.Year, subquery.Max_Date
FROM (
SELECT ID, Year,...
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".
July 23, 2015 at 1:52 pm
But a temp table / table variable won't begin to match the performance of a true array.
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".
July 23, 2015 at 1:37 pm
Viewing 15 posts - 4,936 through 4,950 (of 7,613 total)