Viewing 15 posts - 4,951 through 4,965 (of 7,619 total)
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
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 with adding that...
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:21 pm
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%'
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 10:03 am
gshouse (7/22/2015)
If I wanted to get real nit-picky I'd even ask why int is lower case for one parameter-type and INT is upper case for the other. But that...
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 22, 2015 at 1:45 pm
Often a good start is to look at current input and display screens, as well as reports, but just to identity needed data elements, not to copy the format/layout.
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 22, 2015 at 9:28 am
Agree with using REPLICATE instead, and get rid of any extraneous local variables, so fully coded would look like this:
CREATE FUNCTION [dbo].[NumberToString] (
@value int,
...
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 22, 2015 at 9:26 am
The most important thing is to treat it first as a data project. The database part comes later!
That is, start with logical data modeling. And stick with that...
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 21, 2015 at 2:28 pm
Do not cluster this table by identity. Cluster it instead on EmailDate, for example.
I'd strongly consider using nvarchar(max) rather than nvarchar(3000) to allow longer messages if needed.
Status should 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".
July 21, 2015 at 9:57 am
Hmm, no reason to insert a large number of rows and then do a distinct. Do either of the following, depending on if you need a count for each...
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 20, 2015 at 3:19 pm
Without further info, the truly best clustering index is a complete guess, but based on what is known so far, I'd say try:
Cluster the table on ( StartDate, EndDate )
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 20, 2015 at 3:15 pm
Viewing 15 posts - 4,951 through 4,965 (of 7,619 total)