Viewing 15 posts - 3,376 through 3,390 (of 7,619 total)
Yep, that's not gonna work, SQL needs the sort column in the data. Here's one workaround:
SELECT MONTH, Attendance
FROM (
SELECT
DATENAME (MONTH, [Date]) 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".
May 29, 2018 at 3:26 pm
A) Yeah ... I've worked with date shifting a lot 🙂
C) Yes, we strive for performance here quite a bit. The "DATEADD" technique is only math, not text, so...
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".
May 25, 2018 at 2:22 pm
Don't have any usable test data, but this should be close at least. [Btw, overlaying the day name was trivial, so I didn't include that.]
UPDATE...
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".
May 25, 2018 at 10:52 am
Typically the best way to get great response time for such queries is to cluster the lookup table on the lookup keys.
Unfortunately, when the primary lookup key is a...
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".
May 24, 2018 at 11:35 am
CREATE PROCEDURE [dbo].[ServSP_AuditAllTables_test]
AS
SET NOCOUNT ON;
INSERT INTO dbo.audit_table ( TableName, NumberOfRecords, Date )
SELECT ATL.TableName, CA1.row_count, GETDATE() AS date
FROM AllTablesList ATL
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".
May 23, 2018 at 3:51 pm
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".
May 23, 2018 at 10:43 am
I don't think "AVG" really has any meaning for the diff between only 2 dates?!
SELECT cust_id, DATEDiff(day,Min(visit_date),Max(visit_date))
FROM (
SELECT cust_id, visit_date
, ROW_NUMBER()...
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".
May 23, 2018 at 10:11 am
Try forcing a MERGE join, see if that helps significantly. If it does, then go ahead and cluster the SOH table on ( IT_ID, ST_ID, DateID ). Yes, that means the rows...
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".
May 23, 2018 at 10:04 am
Partition the clus columnstore index on that date. Then SQL can eliminate partitions with non-matching dates. I partition by month on many of our very large tables, but you might...
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".
May 22, 2018 at 7:48 am
It's definitely possible to do. You'll most likely need an index on the table to support the lookup. You might be able to use a filtered index, but I can't...
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".
May 21, 2018 at 2:18 pm
SELECT ut.IdUser,
MIN(ut.IdShop) AS IdShop_Min, MAX(ut.IdShop) AS Id_Shop_Max /*MIN & MAX are optional, of course*/
FROM @UserTransaction ut
GROUP BY ut.IdUser
HAVING COUNT(DISTINCT ut.IdShop) >...
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".
May 18, 2018 at 2:54 pm
Still seems too long for only 76K rows.
1) Check fragmentation on the clus index. If it's bad, the index may need reorganized or rebuilt.
2) Compress the 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".
May 18, 2018 at 11:09 am
SELECT GL_TransLines.[GLL_Account] as "Account"
, GL_TransLines.[Amount] as "Current Expenses"
, GL_TransLines.[COP_Period] as "Period"
, GL_TransLines.[GLT_TransDate] as "Trans Date"
,...
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".
May 18, 2018 at 11:04 am
Your clustered index is fine.
Actually that's a significant understatement by me. Your clus index is generally superb (although you could probably remove the Attribute* columns with no real...
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".
May 17, 2018 at 10:25 am
Your clustered index is fine. 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".
You really don't need the temp tables for this specific query, but if building them is not taking much time, then that's OK too.
May 17, 2018 at 8:24 am
Viewing 15 posts - 3,376 through 3,390 (of 7,619 total)