Viewing 15 posts - 4,906 through 4,920 (of 7,613 total)
For best overall performance, for this proc and for others, I strongly urge you to switch the clustering on the Performance to be on ReportDate, or, if you prefer, (...
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 7, 2015 at 4:11 pm
GROUP BY will typically perform less well than DISTINCT:
SELECT DISTINCT DateYear ,
DateMonth ,
Nbr ,
Nbr1 ,
Nbr2 ,
Datafield1 ,
Datafield2,
ID
FROM [dbo].[TableName]
WHERE 1 = 1
AND ID...
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 7, 2015 at 3:55 pm
BSharbo (8/7/2015)
rmechaber (8/4/2015)
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 7, 2015 at 2:51 pm
To get rows from another table that may or may not have a match, you use an OUTER JOIN rather than a "regular" (INNER) JOIN. The columns from 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 5, 2015 at 10:25 am
Unfortunately, you have to go back before SQL 2005 for this to be a "useful" script, since ALTER DATABASE provided a vastly better way to do this.
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 5, 2015 at 9:27 am
I believe that's exactly what the code I posted will do. You can uncomment the PRINT statement to verify the views that are being created.
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 4, 2015 at 1:24 pm
SET Ansi_Nulls On;
SET Quoted_Identifier On;
GO
ALTER Procedure [dbo].[sp_ViewCreate]
@TableName varchar(100),
@Dist varchar(20)
AS
Set Nocount On;
Declare @SQLQuery AS nvarchar(4000)
Declare @ParamDefinition AS nvarchar(2000)
Declare @TSN AS nvarchar(20)
Declare...
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 4, 2015 at 9:43 am
Matt Miller (#4) (8/4/2015)
ScottPletcher (8/3/2015)
BSharbo (8/3/2015)
However in this instance there is only the one table.
I...
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 4, 2015 at 8:55 am
ben.brugman (8/4/2015)
ChrisM@Work (8/4/2015)
ScottPletcher (8/3/2015)
You can cancel the identity property by using a union or union all:
select top (0) * into #D from D1 union all select top (0) * 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".
August 4, 2015 at 8:51 am
You can do it with a single pass thru the item_categories table -- I renamed the tables to reflect their contents. Data set up first, then the main 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".
August 3, 2015 at 2:10 pm
I've actually resorted to having some procs accept a special param value that "tells" the proc just to return the current create table command for its result table. Then...
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 3, 2015 at 1:52 pm
Use sys.columns rather than the information_schema views.
select *
from sys.columns c
where cast(objectpropertyex(c.object_id, 'BaseType') as varchar(2)) = 'u' and c.is_nullable = 1 and c.is_computed = 0
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 3, 2015 at 1:45 pm
BSharbo (8/3/2015)
However in this instance there is only the one table.
I guess I'm still 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".
August 3, 2015 at 1:39 pm
ben.brugman (8/3/2015)
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 3, 2015 at 9:11 am
Lynn Pettis (7/31/2015)
ScottPletcher (7/31/2015)
ZZartin (7/31/2015)
ben.brugman (7/31/2015)
Sean Lange (7/31/2015)
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 31, 2015 at 4:52 pm
Viewing 15 posts - 4,906 through 4,920 (of 7,613 total)