Viewing 15 posts - 121 through 135 (of 7,619 total)
Definitely focus on the clustered index, first and foremost!
The best way to choose the keys is to look at the missing index stats and current index usage stats and decide...
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 15, 2024 at 1:57 pm
If you just want to change the result column in the SELECT, then do this:
...,
CustID =
(CASE
WHEN custid like '%abc%' and company = 'abc'
then null
ELSE custid
END),
...
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 8, 2024 at 7:47 pm
As to performance:
I figure SQL would have to partition the data twice to get the COUNT() and the AVG() from different queries, but I'd need to look at the query...
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 8, 2024 at 6:35 pm
It's "> 2" :-).
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 8, 2024 at 6:25 pm
I'm not sure how we'd be able to determine whether more than 2 rows are present with that style of query, in order to meet the stated requirements. Then again,...
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 8, 2024 at 5:30 pm
My primary goal is to meet the stated business requirements for the code, which I don't believe your code does. You will list the "truePct" for even a single row,...
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 8, 2024 at 5:15 pm
You will need a trigger if you have to reference other rows in the table. A well-written trigger won't hurt your performance that much assuming you have an index available...
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, 2024 at 5:43 pm
Maybe this?:
SELECT poll_id ,start_date,end_date,
candidate_name, sample_size,
(select avg(pp2.pct) from [president_polls] pp2
where pp2.state = pp.state and pp2.poll_id = pp.poll_id
having...
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, 2024 at 7:08 am
I may not fully understand your requirement, but either:
(1) A CHECK constraint
or
(2) an index definition
can be created to do what you want to do.
For example, if you just want to make...
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, 2024 at 6:38 am
I'd flip the check around: check first for = 1 as 'True' else 'False'. That way NULL will show as false, if a NULL happens to slip in there.
Use 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 19, 2024 at 10:34 pm
You can use ctes, which will create virtual tables, but no actual physical tables:
;WITH TempA AS (
SELECT
InvoiceNo, Date,Type,Code
...
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 18, 2024 at 3:39 pm
I would use STUFF for this, as most custom-fitted to do this task:
SELECT s_Result,
STUFF(s_Result, 1, CASE s_credit_card_type
...
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 16, 2024 at 3:13 pm
;WITH cte_1 AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY HH ORDER BY HH) AS row_num
FROM MyTable
)
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".
September 13, 2024 at 2:04 pm
There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to...
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".
August 30, 2024 at 3:59 pm
There might be some fair overhead to getting the current length of a text column. If you really have to have that, add a column to 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".
August 30, 2024 at 3:31 pm
Viewing 15 posts - 121 through 135 (of 7,619 total)