Viewing 15 posts - 6,076 through 6,090 (of 7,619 total)
SQL always interprets format 'YYYYMMDD' correctly, regardless of SQL settings:
, @start='20140426 00:00:00',@end='20140607 00:00:00'
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 19, 2014 at 10:55 am
Under "sys.dm_db_partition_stats" in Books Online:
"
Requires VIEW DATABASE STATE permission to 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".
May 15, 2014 at 10:40 am
GilaMonster (5/14/2014)
ScottPletcher (5/14/2014)
Besides, for two params do I then have 4 separate procs, for 3 parameters I have 8 separate procs, ...?
No, that would be silly. Dynamic SQL works excellently...
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 14, 2014 at 1:22 pm
That's not for me. I don't like the maintenance pain of "separate but equal" procs. Besides, for two params do I then have 4 separate procs, for 3...
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 14, 2014 at 12:26 pm
ChrisM@Work (5/14/2014)The execution plans for those two queries is likely to be completely different. If you were to write the query as a single "catch-all" query, it would be optimised...
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 14, 2014 at 9:04 am
What is the clustering key on 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 14, 2014 at 8:30 am
If you just want the total hours, you don't need to return all the rows at all, simply total the time differences. This is very easy if there are...
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 14, 2014 at 8:29 am
In the Stores table, use a function to compute a persisted column that is the store name with all non-alphabetic characters stripped. Do the comparisons on that column, but...
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 14, 2014 at 8:25 am
To make sure you don't get a really poor plan, force SQL to recompile the query:
SELECT p.ProjectID, p.ProjectName, p.ProjectDescription
FROM tbl_Projects p
WHERE
p.ProjectID = @ProjectNumber
OPTION(RECOMPILE)
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 14, 2014 at 8:20 am
What is the clustering key on the table?
Can you determine a range of clustering key values for the rows you want to DELETE?
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 14, 2014 at 8:17 am
Full-text indexing would likely help you the most here.
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 13, 2014 at 4:45 pm
I don't think you actually need to go thru all those gyrations; instead, try this:
;WITH allstores(storeid, doc, article) AS(
SELECT 'ALL', '0010', '001' UNION ALL
...
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 13, 2014 at 4:43 pm
Looks reasonable to me. I don't see a "better" way.
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 13, 2014 at 4:35 pm
SELECT
ProjectID,
Project_Name AS [Project_Name|UpdateDescription],
CONVERT(varchar(10), Start_date, 120) AS Start_date,
ProjectDescription
FROM #tbl_projects
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".
May 13, 2014 at 4:29 pm
Since it's only three values, maybe this:
...
,isnull(cast(CASE ca1.COMM_max WHEN 1 THEN COMM1 WHEN 2 THEN COMM2 ELSE COMM3 END*QTY*1.8 as numeric(6,2)),0) as RATE
FROM dbo.yourtablename
CROSS APPLY (
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".
May 13, 2014 at 12:54 pm
Viewing 15 posts - 6,076 through 6,090 (of 7,619 total)