February 1, 2005 at 7:42 am
Okay, so yesterday was my first day in a new project, which is actually a data warehourse using the Star Schema, and after a healthly dose of leartning by diving in, I am tired of waiting the 37-40 minutes for some of the procedures to run. Since the process is using 2 subselects and joining them into another table, it is losing the advantage of having indexes. SO my thought was to use a Table variable inside the Stored Proc to speed things up. However, I just cannot get the syntax correct on the constraint for the Temporary table, and I am not even sure this is the best way to go.
I am running SQL 2000 on a Win2K server, and this is the part of the proc that is giving me issues.
DECLARE @InterestIncome Table (
Period_Amount FLOAT,
Regular_Calendar_Key INT,
Scenario_Key INT,
Hierarchy_Key INT,
Service_Type_Key INT,
Category_Key INT,
Concept_Key INT,
Event_Key INT,
Fiscal_Calendar_Key INT,
CONSTRAINT [PK_InterestIncome] PRIMARY KEY CLUSTERED
(
Regular_Calendar_Key,
Scenario_Key,
Hierarchy_Key,
Service_Type_Key,
Category_Key,
Concept_Key,
Event_Key,
Fiscal_Calendar_Key) ON [Primary]) ON [Primary]-->Updated to remove unwamted smileys...
--Add Interest Income Data Rows to Table Variable
INSERT INTO @InterestIncome
SELECT
ISNULL(SUM(Period_Amount), 0) Period_Amount,
Regular_Calendar_Key,
Scenario_Key,
Hierarchy_Key,
Service_Type_Key,
Category_Key,
Concept_Key,
Event_Key,
Fiscal_Calendar_Key
FROM MART_OFA_GL_Balances_Primary_Fact prim
WHERE prim.Scenario_key = 1
AND EXISTS(
SELECT account_key
FROM dbo.MART_COA_Account_Code_Dimension acc
WHERE account_code IN ('3450', '3445')
AND prim.account_key = acc.account_Key)
AND EXISTS(
SELECT category_key
FROM MART_COA_Category_Dimension cat
WHERE category_code IN ('417000', '416600', '417200', '000000')
AND prim.category_key = cat.category_key)
GROUP BY
Regular_Calendar_Key,
Scenario_Key,
Hierarchy_Key,
Service_Type_Key,
Category_Key,
Concept_Key,
Event_Key,
Fiscal_Calendar_Key
The error it is giving is invalid syntax near Constraint, however, if I change this to a Create Table instead - it works without an issue.
Any help is GREATLY appreciated.
Learn2Live
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
February 2, 2005 at 1:05 am
Hi,
write the table definition like this:
DECLARE @InterestIncome Table ( Period_Amount FLOAT, Regular_Calendar_Key INT, Scenario_Key INT, Hierarchy_Key INT, Service_Type_Key INT, Category_Key INT, Concept_Key INT, Event_Key INT, Fiscal_Calendar_Key INT, PRIMARY KEY ( Regular_Calendar_Key, Scenario_Key, Hierarchy_Key, Service_Type_Key, Category_Key, Concept_Key, Event_Key, Fiscal_Calendar_Key) )
As you can see, the word CONSTRAINT and the constraint name are missing, probably the T-SQL syntax does not support naming for constraints on in-memory tables. Yes, this is NOT a temporary table (temporary table names are prefixed by #, not by @). Also, the statement should not contain the ON clause, since in-memory tables are not stored on file groups.
Maybe you really needed a temporary table, so you can cache some intermediate query result. They are stored in the tempdb database.
By the way, why don't you post some sample table structure and describe the desired result ? Somebody may solve the SQL without temporary tables.
Regads,
Goce.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply