insert within a function

CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END
CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END

I m getting error at the Insert statement, it says error 443, invalid use of insert within a function,

Cann we use insert in a function, if we cann, what is the alternative to insert the values?
do help me asap.Try moving the "Insert" into a stored procedure and then "exec procedure" from your function. Other solution would be to transform your function in a stored procedure by itself|||Did you look at BOL?

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.

And why are you define the same udf twice...and why isn't this a sproc?

Insert Trigger sometimes not firing

hi all

i have an issue with an insert trigger sometimes not firing.

here is the trigger

CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175]
FOR INSERT
AS

INSERT into tPABillToAddr
(
chrJobNumber
)

SELECT chrJobNumber
FROM inserted

when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.

thanksYou can be sure the trigger is firing.
Are you sure the insert is occurring? Add a few more lines to the trigger so that data is also inserted into a log table indicating the datetime, login, and chrJobNumber of each insert.
Keep in mind that your trigger will not fire for updates that may modify an existing chrJobNumber value.|||what would be a probable cause for it not to fire? this is just an insert...i have no updates associated with it.|||While I've investigated a few cases where people thought that triggers didn't fire, I've only seen one or two cases in SQL 4.2 and SQL 6.0 where there were actually cases where a trigger should have fired, but didn't. Those were bugs in the database engine itself.

I haven't seen that happen since the release of SQL 6.5.

-PatP|||what if you have more than 1 insert trigger...lets say the other, for whatever reason doesnt fire, does it cause this one to cease as well?

INSERT the OUTPUT of update statement - a neat trick that doesn't work?

Here's the code
ALTER procedure [dbo].[BalanceUpdate]
As
declare @.DateX datetime
set @.DateX = CONVERT(varchar(10),dateadd(hour,6,getdate()),120)
--INSERT INTO Payment (Summ, UserID, Reason, RelatedOrderID, DT)
UPDATE [User] SET Balance = Balance - Cost, PaidThru = DATEADD(month,
1, ISNULL(PaidThru, @.DateX))
OUTPUT PayPlan.Cost, inserted.ID, 'Monthly payment blah blah blah',
null, @.DateX
FROM [User]
INNER JOIN PayPlan ON Payplan.ID = PayplanID
WHERE (PaidThru <= @.DateX or PaidThru is null) AND (Cost = 0 OR
(Balance >= Cost)) and Confirmed = 1
You see the commented insert statement - in theory it should work, in
practice it says syntac error. I tried surrounding the update with
SELECT * FROM (...) tmp but it's still syntax error.
Is there any way to make this trick work? I don't want to write an
ugly cursor!
Woops. Problem solved, nm
|||Could you elaborate? Where was the syntax error?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Sergei Shelukhin" <realgeek@.gmail.com> wrote in message
news:1182716922.462969.87000@.k79g2000hse.googlegro ups.com...
> Woops. Problem solved, nm
>

Insert statement help

any one can help what is wrong on this statement
insert into Test1 (FCODE, FNO) values (
SELECT 'A', Number
FROM dbo.Numbers
WHERE Number >=3
AND Number <= 5
and Number not in (select FNO from Test1 where FCODE = 'A')
)insert into Test1 (FCODE, FNO)
SELECT 'A', Number
FROM dbo.Numbers
WHERE Number >=3
AND Number <= 5
and Number not in (select FNO from Test1 where FCODE = 'A')|||Just AND operator for preventing from NULL's
SELECT 'A', Number
FROM dbo.Numbers
WHERE Number >=3
AND Number <= 5
and Number not in (select FNO from Test1 where FCODE = 'A' AND FNO IS
NOT NULL)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9765AB05-8B87-41D7-92C5-C971EB50C549@.microsoft.com...
> insert into Test1 (FCODE, FNO)
> SELECT 'A', Number
> FROM dbo.Numbers
> WHERE Number >=3
> AND Number <= 5
> and Number not in (select FNO from Test1 where FCODE = 'A')
>
>

Insert Statement Fails

All,

Trying to format some data before I drop it into a grid. I have this in a stored proc but it fails

CREATE TABLE dbo.tmpSummary (
AE NVARCHAR(50)
, PRODUCT_LINE NVARCHAR(20)
, ANNUAL_REV NUMERIC (9)
, [GRWTH/ACQ] NUMERIC (9)
, RETENTION NUMERIC (9)
, CATEGORY NVARCHAR(20)

)

INSERT INTO dbo.tmpSummary (
[AE]
, [PRODUCT_LINE]
, [ANNUAL_REV]
, [GRWTH/ACQ]
, RETENTION
, CATEGORY
)
SELECT
A.AE
, A.PRODUCT_LINE
, A.ANNUAL_REV
, A.[GRWTH/ACQ]
, A.RETENTION
, B.PRODUCT_CATEGORY AS CATEGORY

FROM
tmpSummary A RIGHT OUTER JOIN PRODUCT B
On A.PRODUCT_LINE=B.PRODUCT_CATEGORY

I keep getting an error "Invalid Column name CATEGORY" Anyone know why? Thanks

Never mind. Maybe if I learn to read i could see that i am trying to insert data BACK into the same table. It should have been something else.

|||

Only reason why you would get that error is that you don't have a column in your table named CATEGORY. Check your table defenition again to make sure you have the spelling of the column name correct. I know that gets me alot

Insert Statement

Does anyone see anything wrong with this Insert

INSERT INTO dbo.Report
(LossType, Loss, Area)
SELECT [Loss Type], Loss, Area
FROM dbo.[Main Table]

I keep getting does not alow nulls insert fails

this is the Report Table
CREATE TABLE [Report] (
[Incident Report No] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Time] [datetime] NULL ,
[Investigators Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Reported by] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Law Enforcement Agency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Name of Officer] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Evidence Seized] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Associated Reports] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Corrective Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Corrective Action] DEFAULT (N'YES'),
[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_I/RDocument] DEFAULT (N'SCANNED REPORT'),
[TypeOfIncident] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Exclusion] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Exclusion] DEFAULT (N'NO'),
[86_D] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_86_D] DEFAULT (N'NO'),
[Loss] [money] NULL CONSTRAINT [DF_Report_Loss] DEFAULT (0.0000),
[LossType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action/Incident] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Report_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
(
[Incident Report No]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

and the [Main Table]

CREATE TABLE [Main Table] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [int] NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [int] NULL ,
[Loss] [money] NULL ,
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [int] NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Main Table] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOYour insert does not supply a value for the column [Incident Report No] which is defined as NOT NULL and does not have a default value.

(Column names with spaces... Shudder...)|||It appears that it's the [Incident Report No] field which is giving you a hassle. It's the sole column that appears to not allow nulls and is not in your insert statement.|||INSERT INTO dbo.Report
( [Incident Report No],
Area,
LossType,
Loss
)
SELECT
dbo.[Main Table].[IR Number]
dbo.[Main Table].Area,
dbo.[Main Table].LossType,
dbo.[Main Table].Loss
FROM dbo.[Main Table]
WHERE [Incident Report No] = [IR NUMBER]

RETURN

Insert Select

Hello all,

Im trying to insert into a table

INSERT INTO [SPIResConv5].[dbo].[TransactionsTemp]

([RESORT_ID]

,[TRANSTYPE_ID]

,[BILLCODE_ID]

,[MAINTENANCE_ID]

,[CONTACT_ID]

,[POSTED]

,[DATE]

,[USER_ID]

,[BATCH]

,[TYPE]

,[AMOUNT]

,[PAYMENTCODE_ID]

,[BANKCODE_ID]

,[DOCNO]

,[Shift4Trx]

)

Select Resort_ID.

'' as TRANSTYPE_ID,

'' as BILLCODE_ID,

'' as MAINTENANCE_ID,

Contact_ID,

'False' as Posted,

Date = getdate(),

'Hwells' as [USER_ID],

3000 as BATCH,

2 as Type,

Amount,

'LockBox' as PAYMENTCODE_ID,

'Conv' as BANKCODE_ID,

DOCNO,

'' as [Shift4Trx]

fromTransactionTempToTransaction

but I get a --

Cannot insert the value NULL into column 'TRXNO', table 'SPIResConv5.dbo.TransactionsTemp'; column does not allow nulls. INSERT fails.

The TRXNO is the first column in the table--but it has a wierd setup.

For example

TRXNORESORT_ID

3ELL2

3FAC

3CSI

3ATR

4CSI

4FAC

4ELL2

It Creates a tranaction code- based on the last resort itselfs transaction -instead just adding a number for the next transaction.

How would I know the last tranasaction for the resort to insert the data from the other table?

Thanks for your help

Based on the information supplied I can't tell if any of the following will work for you or not.

1. Add TRXNO to your insert and calculate its value in a troed procedure or user-defined function before the insert. This might require another temp table.

2. ALTER TABLE before and after the transactionto DROP then ADD the NOT NULL constraint.

3. Remove the NOT NULL constraint.

Insert Select

Hello all,

Im trying to insert into a table

INSERT INTO [SPIResConv5].[dbo].[TransactionsTemp]

([RESORT_ID]

,[TRANSTYPE_ID]

,[BILLCODE_ID]

,[MAINTENANCE_ID]

,[CONTACT_ID]

,[POSTED]

,[DATE]

,[USER_ID]

,[BATCH]

,[TYPE]

,[AMOUNT]

,[PAYMENTCODE_ID]

,[BANKCODE_ID]

,[DOCNO]

,[Shift4Trx]

)

Select Resort_ID.

'' as TRANSTYPE_ID,

'' as BILLCODE_ID,

'' as MAINTENANCE_ID,

Contact_ID,

'False' as Posted,

Date = getdate(),

'Hwells' as [USER_ID],

3000 as BATCH,

2 as Type,

Amount,

'LockBox' as PAYMENTCODE_ID,

'Conv' as BANKCODE_ID,

DOCNO,

'' as [Shift4Trx]

fromTransactionTempToTransaction

but I get a --

Cannot insert the value NULL into column 'TRXNO', table 'SPIResConv5.dbo.TransactionsTemp'; column does not allow nulls. INSERT fails.

The TRXNO is the first column in the table--but it has a wierd setup.

For example

TRXNORESORT_ID

3ELL2

3FAC

3CSI

3ATR

4CSI

4FAC

4ELL2

It Creates a tranaction code- based on the last resort itselfs transaction -instead just adding a number for the next transaction.

How would I know the last tranasaction for the resort to insert the data from the other table?

Thanks for your help

Based on the information supplied I can't tell if any of the following will work for you or not.

1. Add TRXNO to your insert and calculate its value in a troed procedure or user-defined function before the insert. This might require another temp table.

2. ALTER TABLE before and after the transactionto DROP then ADD the NOT NULL constraint.

3. Remove the NOT NULL constraint.

Insert running slow

Are inserts really slow in 2005 or am I doing something stupid?
Here's the table:
CREATE TABLE [dbo].[Tickets](
[ticket] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[data] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[added] [datetime] NOT NULL CONSTRAINT [DF_Tickets_added] DEFAULT
(getdate()),
[lastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED
(
[ticket] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I wrote this stored procedure:
ALTER PROCEDURE [dbo].[CreateTicket]
@.ticket varchar(20) OUTPUT,
@.data varchar(2000)
AS
DECLARE @.key varchar(20)
DECLARE @.len int
DECLARE @.added bit
DECLARE @.cypher varchar(52)
SET NOCOUNT ON;
SET @.added=0
SET @.cypher='abcdefghijklmnopqrstuvwxyzABCDE
FGHIJKLMNOPQRSTUVWXYZ0123456789'
WHILE @.added=0
BEGIN
SELECT @.key='', @.len=20
WHILE @.len>0
BEGIN
-- The following line is the SLOW one!!!
SET @.key = @.key + SUBSTRING(@.cypher, CAST(FLOOR(RAND()*52) AS int)+1,1)
SET @.len = @.len -1
END
IF NOT EXISTS(SELECT 1 FROM Tickets WHERE ticket=@.key)
BEGIN
INSERT INTO Tickets (ticket, data,lastupdated) VALUES(@.key, @.data,GETDATE())
SET @.ticket = @.key
SET @.added = 1
END
END
And then used this to test it's speed:
DECLARE @.ticket varchar(20)
DECLARE @.sec datetime
DECLARE @.cnt int
TRUNCATE TABLE Tickets
SET @.cnt = 0
SET @.sec = DATEADD(second, 1, GETDATE())
WHILE GETDATE()<@.sec
BEGIN
EXEC CreateTicket @.ticket, 'this is a test'
SET @.cnt=@.cnt + 1
END
PRINT @.cnt
When I run this on SQL 2000, I get roughly 3000 records a second. When I
run it against 2005 I get roughly 160 records per second! The statement tha
t
is taking all the time in 2005 is the insert statement!
On 2005 if I comment it out I can execute 8,600ish loops per second. If it
isn't commented out I run 160ish.
On 2000 if I comment it out I can execute 5,900 loops per second, If it
isn't commented out I run 3,000ish.
Is inserting really that expensive or am I missing some knob I forgot to tur
n?Never mind. It appears there's something wrong with the server I was testin
g
on. Testing on another server I got 5,600ish inserts per second. What's
really weird though is the box that's performing slowing is a faster box tha
n
either of the other two with faster disks. Guess it's time to reinstall :)|||Before reinstalling, I would check perfmon and profiler and see what is
actually taking so long. It might be something easy to fix (or it might be
that reinstalling would cause the same performance problems.) A reinstall
might be in order, but unless that is really easy to do for you, it is
probably just something in how something is set up.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Larry Charlton" <LarryCharlton@.discussions.microsoft.com> wrote in message
news:A378F5F2-63BC-4FE0-AD6D-DFBB2952BB06@.microsoft.com...
> Never mind. It appears there's something wrong with the server I was
> testing
> on. Testing on another server I got 5,600ish inserts per second. What's
> really weird though is the box that's performing slowing is a faster box
> than
> either of the other two with faster disks. Guess it's time to reinstall
> :)
>

Insert query takes lot of time

Hello
I have these tables:
CREATE TABLE [dbo].[COREAttribute] (
[oid] [uniqueidentifier] NOT NULL ,
[CLSID] [uniqueidentifier] NOT NULL
) ON [PRIMARY]

CREATE UNIQUE CLUSTERED INDEX [COREAttributeOidIndex] ON
[dbo].[COREAttribute]([oid], [CLSID]) WITH FILLFACTOR = 90 ON
[PRIMARY]

CREATE TABLE [dbo].[COREBstrAttribute] (
[oid] [uniqueidentifier] NOT NULL ,
[iid] [uniqueidentifier] NOT NULL ,
[dispid] [int] NOT NULL ,
[value] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

CREATE CLUSTERED INDEX [COREBstrAttributeOidIndex] ON
[dbo].[COREBstrAttribute]([oid]) WITH FILLFACTOR = 90 ON [PRIMARY]

Now when I try this query, it's taking 8-10mins.

Declare @.t TABLE (oid uniqueidentifier primary key,
[Description] nvarchar(1024) NULL,
[Name] nvarchar(1024) NULL,
[UID] nvarchar(1024) NULL)

DECLARE @.COREBSTRAttribute TABLE (oid uniqueidentifier, dispid int
NULL, value nvarchar(1024) NULL)
INSERT INTO @.COREBSTRAttribute select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid ='{1449DB20-DB97-11D6-A551-00B0D021E10A}'
INSERT @.t
SELECT distinct
c0.oid,
c1.Value,
c2.Value,
c3.Value
FROM(
SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}')
) AS c0
LEFT JOIN @.COREBSTRAttribute AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN @.COREBSTRAttribute AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN @.COREBSTRAttribute AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

Any help is greatly appreciated.

thanks
SunitI admit I haven't read your post in detail, but clustering on a GUID
probably isn't a good idea. Since the GUID values are random, but the
index is ordered, that means a lot of fragmentation as the index has to
be constantly rebuilt and reordered. You might try changing to a
nonclustered index instead, and keep the clustered index for columns
which you often use in GROUP BY or ORDER BY clauses.

Simon|||I changed to this and now I get 'Invalid object name 'bstr' during
runtime. It parses successfully though !

Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From
(SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,

(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
) As bstr

LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

thanks
Sunit|||Simon Hayes (sql@.hayes.ch) writes:
> I admit I haven't read your post in detail, but clustering on a GUID
> probably isn't a good idea. Since the GUID values are random, but the
> index is ordered, that means a lot of fragmentation as the index has to
> be constantly rebuilt and reordered. You might try changing to a
> nonclustered index instead, and keep the clustered index for columns
> which you often use in GROUP BY or ORDER BY clauses.

As always in the database world, it depends!

SQL Server MVP Greg Linwood taught me a great way to use GUID for a
clustered index. You start off with a fairly low fill factor, say 50%.
Now, new guids will most cases find empty holes to jump in, so there
will not be any page split. Instead, fragmentation will decrease by
time.

Until, that is, pages starts to become full, and there will be page
splitting galore. That's when you need a maintenance window so yuu
can reindex with your fill factor. Obviously, you need a good feeling
for which fill factor you need to the next maintenance window. This is
definitely not a method for the DBA on auto-pilot.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||sjoshi (sjoshi@.ingr.com) writes:
> Now when I try this query, it's taking 8-10mins.

While we got the tables and the query, we did not get any details on
the amount of data involved. For instance, how many rows could we
expect in the involved tables?

Here are some assorted thoughts:

o Replace table variable with temp tables. Temp tables have statistics,
and this may the optimizer a second chance for a better job.
o Add a WHERE clause to insert only the dispid of interest into
@.COREBSTRAttribute
o Leave out Value from @.COREBSTRAttribute. That serves to make the
table slower.
o Add a clustered index on dispid. (Possible for temp table.)
o Long IN clauses are takes long time to optimize. Could be a better
idea to put the guids into a temp table instead.
o You insert into a table variable @.t. When you insert into temp
variables, you kill parallelism. Someties parallelism is good for
your queries. (Sometimes it is not!)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp