Viewing 15 posts - 4,906 through 4,920 (of 7,619 total)
Most likely rather than a blank the first char in the column is some non-displayable character, such as a tab (char(9)), cr (char(13)), or line feed (char(10)).
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 17, 2015 at 9:43 am
The single biggest SQL tuning payback, after any infrastructure issues are addressed, is making sure you have the best clustered index on every significant table. Hint: This is most...
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 14, 2015 at 3:27 pm
If you (almost) always process data by month (or other date range(s) too) then cluster the table on the related date column. You should far less performance issues then.
Edit:...
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 14, 2015 at 3:23 pm
FridayNightGiant (8/14/2015)
ScottPletcher (8/12/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 14, 2015 at 7:31 am
Here's a method without an external function, just out of curiosity mostly about how it would perform vs. other methods. It uses a "standard" tally table. This method...
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 12, 2015 at 12:04 pm
By the way, you should change the WHERE clause to make it "sargable":
...
WHERE SamplingDate >= '20150101' AND SamplingDate < '20160101'
...
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 10, 2015 at 9:31 am
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
Viewing 15 posts - 4,906 through 4,920 (of 7,619 total)