Viewing 15 posts - 5,641 through 5,655 (of 7,613 total)
I used a CROSS APPLY to assign an alias name to the result of the string to datetime conversion, so the conversion doesn't have to be repeated anywhere.
I also changed...
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".
November 10, 2014 at 3:47 pm
CELKO (11/7/2014)
This kind of problem shows up too often to be real. What school is using this as homework? We need to find...
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".
November 7, 2014 at 8:27 pm
First, check for RAM pressure/bottlenecks. If you have (way) too little RAM, nothing else you do will matter much.
Then check for I/O pressure/bottlenecks.
Finally look for CPU pressure/bottlenecks. These...
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".
November 7, 2014 at 3:29 pm
Fillfactor 0 is potentially extremely dangerous to performance, and can be very prone to fragmentation, esp. on a table where it's common to lengthen [n]varchar column(s).
I also noted above that...
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".
November 7, 2014 at 8:38 am
1)
fill factor of 80%
That's pretty low. How did you decide on that? You really need to tailor for each table's situation and not just apply a low...
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".
November 6, 2014 at 3:41 pm
My guess is it's just some quirk with the output clause and ROW_NUMBER(), because the expected row gets kept even when the OUTPUT seems to indicate otherwise:
declare @t table (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".
November 6, 2014 at 9:51 am
If possible, you want to specify contiguous clustered key ranges on the main/larger table to UPDATE. You only want to each affected block of that table one 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".
November 6, 2014 at 9:42 am
If you (almost) always provide created_date in the WHERE clause when querying that table, change the clustered index on the table to be on created_date. That will 100% solve...
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".
November 6, 2014 at 9:38 am
Those queries themselves are relatively trivial. You'll sort that out quickly.
But the clustering index is the most critical overall factor in performance and two tables need changed. Without...
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".
November 6, 2014 at 9:37 am
FWIW, sure potentially sounds to me like homework or the equivalent.
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".
November 5, 2014 at 3:23 pm
I don't know exactly how you want to order the results, but something like this should do what you want:
SELECT
ol.OrdNo, ol.ProdNo, ol.Descr,ol.DPrice, pr.PictNo, pr.NoteNm as DescriptLong,
(ROW_NUMBER() OVER(ORDER BY ol.OrdNo,...
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".
November 5, 2014 at 3:21 pm
You can use CROSS APPLY to assign alias names to expressions:
select distinct top 100 filedate, transaction_date, duedate,
event_instance, event_name, eventstatus, age,
DaysAged,
coalesce (DaysAged, new, 0) as aged
from...
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".
November 4, 2014 at 3:20 pm
Best is to always strip non-numeric chars before storing the value. If you have to, store both the original, edited version and a stripped version, or, better yet, 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".
November 3, 2014 at 3:42 pm
Does that user/login also exist in prod? If so, the SIDs of the prod login and the nonprod login are different. If you want the user 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".
November 3, 2014 at 3:38 pm
GilaMonster (10/29/2014)
The data type returned will be the 'larger' of INT and whatever the replacement value is.
btw, SQL doesn't have booleans. It has the bit data type which...
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".
November 3, 2014 at 3:33 pm
Viewing 15 posts - 5,641 through 5,655 (of 7,613 total)