Viewing 15 posts - 7,321 through 7,335 (of 7,619 total)
Presumably the ContactID is indexed.
If there can only be single-line gaps, you can do this:
SELECT
c1.ContactID - 1 AS Missing_Number
FROM #Contact c1
WHERE NOT EXISTS...
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 13, 2012 at 4:11 pm
dan-572483
I was surprised to see that the Estimated Execution Plan shows a slightly higher cost for LEFT(LastName,2) over LIKE. The Plan for LIKE recommended creating a nonclustered index...
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 13, 2012 at 3:48 pm
Loner (7/13/2012)
Please seeLEFT() is a function it would result in an index scan where like '% %' would result in index seek which is more efficient.
Not quite.
LIKE 'xxx%' can...
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 13, 2012 at 3:41 pm
I don't think you really need indexing, since you can do everything in a single pass of the table, w/o JOINs, etc..
SELECT
ACTIONID,
CASE...
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 13, 2012 at 2:42 pm
Lynn Pettis (7/11/2012)
Matt Miller (#4) (7/11/2012)
Lynn Pettis (7/11/2012)
CREATE TABLE [dbo].[TEST1](
[ID] [VARCHAR](10) NULL,
[VALUE] [int] NULL
);
GO
INSERT INTO dbo.TEST1 (ID, VALUE) SELECT 1, 10;
SELECT 'PRE' LABEL1, * FROM dbo.TEST1;
DECLARE @SAVE1...
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 11, 2012 at 1:50 pm
Lynn Pettis (7/11/2012)
ScottPletcher (7/11/2012)
gerard-593414 (7/11/2012)
There are numerous...
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 11, 2012 at 12:29 pm
gerard-593414 (7/11/2012)
There are numerous users and want...
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 11, 2012 at 12:12 pm
I think the query below is equivalent to your original one, w/o all the unnecessary join (unless the join on DAY restricts the rows selected from the "report" 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 10, 2012 at 2:45 pm
cfradenburg (7/10/2012)
ScottPletcher (7/10/2012)
Jeff Moden (7/9/2012)
ScottPletcher (7/9/2012)
SELECT
(ident - 1) % 3,
MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0...
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 10, 2012 at 11:11 am
Jeff Moden (7/9/2012)
ScottPletcher (7/9/2012)
SELECT
(ident - 1) % 3,
MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS...
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 10, 2012 at 8:33 am
SELECT
(ident - 1) % 3,
MAX(CASE WHEN (ident - 1) % 3 = 0 THEN value ELSE 0 END) AS part,
...
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 9, 2012 at 4:41 pm
In general, I too prefer NOT EXISTS, although SQL will often generate identical plans for either one.
It's definitely strongly preferred whenever possible to have an index to support the lookup,...
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 9, 2012 at 4:37 pm
I think this gets you pretty close, w/o having to resort to CLR:
WHERE
--one +/- optional, but must be first if used
PATINDEX('%[+-]%', varcharValue)...
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 9, 2012 at 4:21 pm
SQL Server explicitly discards SET values at the end of the stored proc and, AFAIK, there is absolutely no way to change that directly.
You could, of course, save the desired...
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 9, 2012 at 3:36 pm
Please substitute your db name below,
and your table name in BOTH places below where <your_table_name> appears, then run the queries and post the results.
USE <your_db_name>
SELECT
GETDATE()...
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 9, 2012 at 3:14 pm
Viewing 15 posts - 7,321 through 7,335 (of 7,619 total)