Viewing 15 posts - 4,801 through 4,815 (of 7,613 total)
SQLPain (9/14/2015)
Thanks Scott,How do I fix this code:
AppDetails.DateDetailDisposed BETWEEN getdate() - 25 AND getdate() - 20
I only need getdate() -21, but that's giving me blank output
WHERE ... AND
...
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".
September 14, 2015 at 3:07 pm
Check the compatibility level of the db. Compatibility needs to be at least 90 for ROW_NUMBER() to work.
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".
September 14, 2015 at 11:34 am
Hmm, interesting. I thought the InspectionId was a "primary key", and therefore unique. Sorry, I'm confused now about the data structure and relationships.
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".
September 11, 2015 at 2:58 pm
From what you've described, I think this will select the rows you need. Btw, I hope the table isn't clustered on that guid, even though that is the PK.
Edit:...
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".
September 11, 2015 at 1:59 pm
No, I don't recall anything like that, not on newer versions. Maybe under SQL 6.5, which I never used. Perhaps under SQL 7.0, which probably didn't defer the...
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".
September 9, 2015 at 5:14 pm
Jeff Moden (9/9/2015)
It looks like someone may have followed the old idea of adding one index to each column that could ever be used in a WHERE clause and that'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".
September 9, 2015 at 5:08 pm
Check the compatibility level of the dbs. If that is too low, you'll get that syntax error, since earlier versions of SQL didn't allow that type of reference.
Edit: OOPS,...
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".
September 9, 2015 at 4:50 pm
Why do you refuse to create a clustered index??
Since you have Enterprise Edition, you can create the index online so it will barely, if at all, affect other users.
Drop all...
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".
September 9, 2015 at 11:05 am
Quite right, sorry, left that table out of the outer query. Also forgot to check the CollectorCode in the inner query, I've added that back as well:
SELECT
H_Totals.David
, H_Totals.DavidCt
,...
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".
September 8, 2015 at 2:37 pm
I don't see anywhere that the dbo.Company table is being used, so I removed it.
I also removed the multiple JOINs, although I did not add a GROUP BY...
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".
September 8, 2015 at 2:25 pm
You could partition the table, but it's likely overkill in this situation. All you probably need is the best clustered index and only the nonclustered index(es) you actually need,...
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".
September 8, 2015 at 11:22 am
There are two main reasons it's taking so long:
1) no clustered index
2) too many nonclustered indexes
My best guess is that Trx_Date should be the clustered index. But we need...
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".
September 8, 2015 at 10:59 am
I don't use MPs either.
But check for missing dbs. For example, maybe a db was moved or renamed.
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".
September 8, 2015 at 10:50 am
There's a simple rule for this:
NEVER use ISNULL() in a WHERE or JOIN clause.
You can easily code around it, and it could cause performance issues. Even if there's no...
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".
September 8, 2015 at 10:48 am
SELECT first_appointment.*, next_appointment.*
FROM (
SELECT customer#, MIN(appoinment_date) AS appointment_date
FROM table_name
GROUP BY customer#
HAVING MIN(appointment_date) >= '20140101'...
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".
September 4, 2015 at 3:30 pm
Viewing 15 posts - 4,801 through 4,815 (of 7,613 total)