Viewing 15 posts - 5,491 through 5,505 (of 7,613 total)
If you primarily use columns "Company" and "Form_Name" to do lookups and/or joins on table "COLUMN_NEXT_ID_RECENT_ITEMS", make sure the table is clustered on those columns. [It might, instead, be...
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 7, 2015 at 12:13 pm
If the query otherwise works the way you want it to, just remove the unneeded column from the subquery:
...,
(SELECT C1.containerCode as containerCode --,SE.createdOn
FROM Containers C1
LEFT JOIN StoringsEvents SE ON C1.containerIncId...
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 7, 2015 at 12:09 pm
I don't like the extra overhead of generating hundreds of rows every time when you only need a dozen or two.
A "standard" sequential-numbers tally table can easily handle those types...
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 7, 2015 at 9:31 am
Grant Fritchey (1/6/2015)One other point, I'm not sure what industry you're working in, but many, especially healthcare, now have criminal penalties if you expose personal data to people without permission...
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 6, 2015 at 5:17 pm
First thought for first task:
Compare one customer's pattern to the concatenated pattern of another customer(s). This should alleviate the off-by-n issue. Naturally you could also reduce the repeating...
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 6, 2015 at 4:42 pm
Eirikur Eiriksson (1/6/2015)
ScottPletcher (1/6/2015)
Eirikur Eiriksson (1/6/2015)
ScottPletcher (1/6/2015)
Jeff Moden (1/4/2015)
asita (1/3/2015)
Nolock is ignore the locks (please ignore it)
Trust me Dhani, we know what it does and folks in-the-know can't just ignore...
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 6, 2015 at 3:59 pm
Great, glad it helped. It was a fun exercise :). And thanks so much for posting the final solution so I could see it! Yeah, I thought...
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 6, 2015 at 3:51 pm
DECLARE @CopyParams TABLE (
CopyFromVIEGuid uniqueidentifier,
CopyToVIEGuid uniqueidentifier
)
UPDATE [dbo].XdsInboundSecurity
SET ClientCertificateThumbprint = (
Select...
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 6, 2015 at 3:40 pm
Keep in mind that a proc exists in that view as long as it is still in the procedure cache. As long as the proc is in the cache,...
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 6, 2015 at 3:19 pm
Eirikur Eiriksson (1/6/2015)
ScottPletcher (1/6/2015)
Jeff Moden (1/4/2015)
asita (1/3/2015)
Nolock is ignore the locks (please ignore it)
Trust me Dhani, we know what it does and folks in-the-know can't just ignore it. It's...
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 6, 2015 at 2:07 pm
I agree, NEVER use the GUIs for backups and restores. For one thing, you have no record of what was done, whereas a script can be enhanced over time.
I...
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 6, 2015 at 1:17 pm
For a table that size, and esp. one that apparently static, I'd make sure the statistics were based on the entire table rather than just a small sampling.
You can see...
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 6, 2015 at 1:10 pm
Jeff Moden (1/4/2015)
asita (1/3/2015)
Nolock is ignore the locks (please ignore it)
Trust me Dhani, we know what it does and folks in-the-know can't just ignore it. It's normally a bad...
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 6, 2015 at 12:51 pm
------------------------------------------------------------------------------------------------------------------------
--declare temp tables and variables
IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
DROP TABLE #sql
CREATE TABLE #sql (
ident int IDENTITY(1, 1) NOT NULL,
...
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 6, 2015 at 10:15 am
I'd probably generate dynamic SQL in this case, as it's much clearer to me. Sample below. I'm sure it will need tweaked but it should give you enough...
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 5, 2015 at 4:12 pm
Viewing 15 posts - 5,491 through 5,505 (of 7,613 total)