Viewing 15 posts - 4,951 through 4,965 (of 7,613 total)
Lynn Pettis (7/23/2015)
WayneS (7/23/2015)
ScottPletcher (7/23/2015)
Btw, the WHERE clause can be improved to allow an index seek, if applicable:WHERE
LEFT(name, 5) = 'APTMP'should be:
WHERE name LIKE 'APTMP%'
Good luck with adding 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".
July 23, 2015 at 1:21 pm
Btw, the WHERE clause can be improved to allow an index seek, if applicable:
WHERE LEFT(name, 5) = 'APTMP'
should be:
WHERE name LIKE 'APTMP%'
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".
July 23, 2015 at 10:03 am
gshouse (7/22/2015)
If I wanted to get real nit-picky I'd even ask why int is lower case for one parameter-type and INT is upper case for the other. But 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".
July 22, 2015 at 1:45 pm
Often a good start is to look at current input and display screens, as well as reports, but just to identity needed data elements, not to copy the format/layout.
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".
July 22, 2015 at 9:28 am
Agree with using REPLICATE instead, and get rid of any extraneous local variables, so fully coded would look like this:
CREATE FUNCTION [dbo].[NumberToString] (
@value int,
...
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".
July 22, 2015 at 9:26 am
The most important thing is to treat it first as a data project. The database part comes later!
That is, start with logical data modeling. And stick with 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".
July 21, 2015 at 2:28 pm
Do not cluster this table by identity. Cluster it instead on EmailDate, for example.
I'd strongly consider using nvarchar(max) rather than nvarchar(3000) to allow longer messages if needed.
Status should not...
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".
July 21, 2015 at 9:57 am
Hmm, no reason to insert a large number of rows and then do a distinct. Do either of the following, depending on if you need a count for each...
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".
July 20, 2015 at 3:19 pm
Without further info, the truly best clustering index is a complete guess, but based on what is known so far, I'd say try:
Cluster the table on ( StartDate, EndDate )
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".
July 20, 2015 at 3:15 pm
Is the key column really that long?
Why not just use a separate row for each interval, 5, 15 and 60 minutes?:
settlement_key, interval, 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".
July 20, 2015 at 3:12 pm
I'd prefer a single UPDATE statement:
UPDATE raa
SET tt_TotalPay = aan.TotalPay,
tt_CountOf100s = aan.TotalPay / 100,
tt_CountOf50s = aan.TotalPay % 100 / 50,
...
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".
July 20, 2015 at 8:26 am
SELECT Capacity
FROM (
SELECT '1234' AS Capacity UNION ALL
SELECT '-987' UNION ALL
SELECT NULL UNION ALL
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".
July 17, 2015 at 3:19 pm
Put double quotes around the column in the SELECT list:
SELECT ..., '"' + column_with_leading_zeros + '"' AS column_name, ...
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".
July 16, 2015 at 11:40 am
You're welcome!
Btw, note that for "SELECT TOP (1) *", the * is not a problem, because SQL can determine that it needs to get only the columns that are actually...
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".
July 16, 2015 at 10:37 am
You need to use CROSS APPLYs (CA) rather than an INNER JOIN (IJ). CA only, without the IJ, assumes you need to see only the low and high hematocrit...
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".
July 16, 2015 at 10:23 am
Viewing 15 posts - 4,951 through 4,965 (of 7,613 total)