By the way, you should change the WHERE clause to make it "sargable":

...

WHERE SamplingDate >= '20150101' AND SamplingDate < '20160101'

...

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, (...

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...

BSharbo (8/7/2015)

---

rmechaber (8/4/2015)

---

Setting aside the question of clustering key for the moment, which others here have already taken up, I would ask whether your assumption of having an 8-column natural...

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...

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.

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.

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...

Matt Miller (#4) (8/4/2015)

---

ScottPletcher (8/3/2015)

---

BSharbo (8/3/2015)

---

I can understanding adding an identity key to a table to facilicate joins to other tables.

However in this instance there is only the one table.

I...

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...

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...

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...

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

BSharbo (8/3/2015)

---

I can understanding adding an identity key to a table to facilicate joins to other tables.

However in this instance there is only the one table.

I guess I'm still a...

ben.brugman (8/3/2015)

---

This weekend I have been away from the keyboard. And as often the best idea's occure when away from the keyboard. E.g. on my bike (pushbike) or under the...