Viewing 15 posts - 1,216 through 1,230 (of 7,613 total)
With stored procedures, I like a 2-3 letter acronym at the start to define the process (adm for admin, fin for finance, etc) followed by _ and then what...
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".
October 9, 2021 at 12:42 am
Give your databases names that reflect their business purpose. Don't use prefixes or suffixes in the name to indicate "database". MS got this half right: MS has dbs "master", "model",...
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".
October 9, 2021 at 12:38 am
Make sure the D_Date table is uniquely clustered on Date (it should already be, but verify, just in case). I'd stick with a scalar function for now, but get rid...
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".
October 7, 2021 at 2:54 pm
Actually, for an even number of rows, the median is the avg of the middle two (IIRC).
For example, for values:
1, 2, 3, 101, 200, 500 :: median is 52 (...
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".
October 6, 2021 at 8:15 pm
Agreed, we need to see the code.
And, do you have a table with non-workdays in it? We'll need to know the DDL for that table to properly determine business days.
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".
October 6, 2021 at 6:49 pm
That's not an accurate method to get a median value.
For example, if you have these values:
SELECT (MAX(value) - MIN(value)) / 2 + MIN(value)
FROM ( VALUES(1),(2),(3),(1000),(3000) ) AS data(value)
the median (middle) value...
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".
October 6, 2021 at 6:46 pm
;WITH cte_add_row_nums AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY Branch) AS row_num
FROM #temp
)
SELECT id, branch, city
FROM cte_add_row_nums
WHERE...
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".
October 6, 2021 at 12:57 am
You might be able to use a login trigger to deny the connection for those specific users if they were not coming from a linked server. You'd have to review...
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".
October 5, 2021 at 3:30 pm
NTILE specifies the number of groups, not the size of each group. So in your example it should be 100 not 800.
For the sample data shown, 10 groups makes more...
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".
October 5, 2021 at 1:55 pm
I agree with Phil. I would use a "holding" table to contain the data prior to it being summarized in 15-minute "packets" to be added to the main 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".
October 5, 2021 at 1:44 pm
Scott, fyi...I do have a clustered index defined for the column that is the primary key - an IDENTITY column.
[UserId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED (...
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".
October 5, 2021 at 1:34 am
I
[1] To log in, I request a User Name. Should I have a non-clustered index on that column?
[2] Also, in another table, I query on the email address, should...
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".
October 4, 2021 at 8:19 pm
Maybe something like this?:
ALTER TABLE dbo.your_table_name ADD REPORT_DATE AS CAST(DATEADD(DAY, CASE WHEN CREATE_TIME >= '18:00:00' THEN 1 ELSE 0 END, CREATE_DATE) AS date) PERSISTED;
...
WHERE REPORT_DATE = CAST(GETDATE() AS 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".
October 4, 2021 at 5:39 pm
Yep, there's not much you can do to speed it up. However, you can be sure to shrink only the specific file(s) you need to shrink, not the entire db. ...
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".
October 4, 2021 at 5:03 pm
Don't use a function against the column, that can make performance worse, potentially much worse.
WHERE CREATE_DATE = (DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AND CREATE_TIME >=...
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".
October 4, 2021 at 4:53 pm
Viewing 15 posts - 1,216 through 1,230 (of 7,613 total)