UDF As Computed Column In Temporary Table In Proc

Jun 16, 2008

I've created a stored procedure that creates and uses a temporary table with a computed column. I want the computed column to call a user-defined function(UDF) that I've created. Here's an example:

CREATE PROCEDURE [dbo].[usp_Proc]
(
@Date datetime
)
AS
BEGIN


--Drop the temp table if it still exists so reports come out accurate
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp]') AND type in (N'U'))
DROP TABLE #temp;

--Create the temp table for use in this stored procedure
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#temp]') AND type in (N'U'))
CREATE TABLE #temp (
[ID] INT PRIMARY KEY IDENTITY(1,1),
[Column1] NVARCHAR (30) DEFAULT ('XXXX-XXXXX'),
[Column2] INT DEFAULT (0),
[Column3] INT DEFAULT (0),
[Column4] INT DEFAULT (0),
[Column5] as ([Column2] + [Column3] + [Column4]),
[Column6] as (dbo.FunctionName('5381', [Column1]))
)

--Insert data
INSERT INTO #temp([Column1], [Column2], [Column3], [Column4])
SELECT 'String', 1, 2, 3

--Perform more calculations
<snipped...>

SELECT * FROM #temp

DROP TABLE #temp

END

This is an example of the function:

CREATE FUNCTION [dbo].[FunctionName]
(
-- Add the parameters for the function here
@Type nvarchar(4),
@Quantity int
)
RETURNS Money
AS
BEGIN

RETURN (cast((SELECT ([Value] * cast(@Quantity as int)) FROM tblTable WHERE [ID] = @Type) as Money))
END

The error message I'm getting after I've created both the stored procedure and the UDF is when calling the stored procedure. Here it is:

Msg 4121, Level 16, State 1, Procedure usp_Proc, Line 13
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.FunctionName", or the name is ambiguous.

There's no way the function name is ambiguous. Is it even possible to do what I'm trying to do or am I just calling it the wrong way?

Hosmerica

View 1 Replies


ADVERTISEMENT

(re)using A Temporary Table In A Stored Proc (was Confusion)

Feb 15, 2005

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?


Howdy!

View 14 Replies View Related

Temporary Table In 3 Diffirent Stored Proc

Mar 30, 2004

I know there maybe something similar of what im asking for but i just cant find it.

I have 3 Stored procedure.

SPA - create a temporary table "sp_getListOfChildren"
SPB - insert the data into the temp table "sp_InsertCategoriesFound"
SPC - display the list of categories i found "sp_ListingAvailableCategories"

process:
SPA call SPB and SPC call SPA

my problem is in the SPC. it seems that the table doesnt exist anymore when i do a select but in the message tab of my sql analyser i can see that the table have some data before executing that store proc..

Invalid object name '#TblTempCat'. for my SPC !! ??? why.. how do i detect a temp table in diffirent stored procedure per user and as to be temp table.. for multiple access.. "WEB"

============MY "SPC" CODE=============
alter PROCEDURE sp_ListingAvailableCategories @CurrentCategoryID AS uniqueidentifier
AS

exec sp_getListOfChildren @CurrentCategoryID

select * from #TblTempCat

select * from TblCategories where CatID not in (select CatID from #TblTempCat) and CatId <> @CurrentCategoryID

View 7 Replies View Related

Computed Column In A Replicated Table

Apr 10, 2015

I have a strange problem. I have a computed column in a replicated table, the Formula is as follows:

(isnull(hashbytes('SHA2_256',CONVERT([varchar](256),[AccrualReference],(0))),(0))) the column is also Persisted.

This gets around a case sensitivity issue and is used as the Primary Key column, which works well.The problem is that this table is then replicated to another server. On the subscriber the value of this computed field is being returned as 0x00000000 for every row, so this must be the ISNULL function doing its job. But why? The AccrualReference is the true PrimaryKey and is never NULL.

If I remove the computed specification and set the field up as varbinary(64) the value then gets replicated. This then means maintaining a different table schema for in excess of 500 tables.

View 2 Replies View Related

Table Partioning With Computed Column

Oct 25, 2006

This full version of this reporting table will have about 12 million row for each of three years. Prior years will be in separate partitions and frozen but the current year will be reloaded each night by source_key, probably in parallel.

I am trying to do this with a computed column but I can't slide the partition back into the main table due to an apparent problem with the Check constraint. I have tried everything I can think of and still can't get it to work.

I hope I am missing something simple. Anyone know why this does not work or how to fix it?

ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'db_template.dbo.foo_year_source_partition_test_stage' allows values that are not allowed by check constraints or partition function on target table 'db_template.dbo.foo_year_source_partition_test'.

------------------------------------------------------------
CREATE PARTITION SCHEME zzYearSourcePScheme
AS
PARTITION zzYearSourceRangePFN
TO
(
[fg_template_0],
[fg_template_0],
[fg_template_0],
[fg_template_0],
[fg_template_0]
)
go

CREATE TABLE [dbo].[foo_year_source_partition_test](
[detail_date] [datetime] NULL,
[source_key] [int] NULL,
[year_source] AS ((CONVERT([char](2),right(datepart(year,[detail_date]),2),0)+'-')
+right('0000'+CONVERT([varchar](3),[source_key],0),(3))) PERSISTED,
[ys_id] int identity (1,1)
) ON zzYearSourcePScheme(year_source)
go

create unique clustered index ix_year_source_ys_id on [foo_year_source_partition_test] ([year_source], [ys_id]) ON zzYearSourcePScheme(year_source)
go

insert into [foo_year_source_partition_test] values('20060131',2)
insert into [foo_year_source_partition_test] values('20060131',3)
insert into [foo_year_source_partition_test] values('20060131',4)

SELECT *, $PARTITION.zzYearSourceRangePFN(year_source) AS Partition from [foo_year_source_partition_test] order by detail_date
go

CREATE TABLE [dbo].[foo_year_source_partition_test_stage](
[detail_date] [datetime] NULL,
[source_key] [int] NULL,
[year_source] AS ((CONVERT([char](2),right(datepart(year,[detail_date]),2),0)+'-')
+right('0000'+CONVERT([varchar](3),[source_key],0),(3))) PERSISTED,
[ys_id] int identity (1,1)
) --on same one ON YearSourcePScheme(year_source)

create unique clustered index ix_year_source_ys_id
on [foo_year_source_partition_test_stage] ([year_source], [ys_id]) --ON YearSourcePScheme(year_source)


ALTER TABLE db_template.dbo.foo_year_source_partition_test
SWITCH PARTITION 3 to db_template.dbo.[foo_year_source_partition_test_stage]

TRUNCATE TABLE db_template.dbo.[foo_year_source_partition_test_stage]


ALTER TABLE db_template.dbo.foo_year_source_partition_test_stage
WITH CHECK
ADD CONSTRAINT CK_foo_year_source_partition_test_stage_YearSource
CHECK
(
[year_source] = '06-003'
)

insert into foo_year_source_partition_test_stage values('20060202',3)
insert into foo_year_source_partition_test_stage values('20060303',3)
insert into foo_year_source_partition_test_stage values('20060404',3)
insert into foo_year_source_partition_test_stage values('20060505',3)

ALTER TABLE db_template.dbo.foo_year_source_partition_test_stage
SWITCH TO db_template.dbo.foo_year_source_partition_test PARTITION 3



View 6 Replies View Related

How To Check If Column Exists In Temporary Table

Jun 13, 2008

I am trying
IF OBJECT_ID(''tempdb..#tempTable.Column'') IS NOT NULL



But its returning me a null every time. Is there any other way to check if column exists in temporary table.

View 4 Replies View Related

Computed Column Referencing A Computed Column

Sep 5, 2007

Hi all,

I have a table with 3 computed columns, the first two reference a function for there value. The last computed column should be total of the other computed columns, however when trying to write the formula for this column SQL Server rejects it.

Am i correct in thinking it is not possible to reference other computed columns in a computed column's forumla.

My current work around is to call the functions again that the other computed columns use to generate the total, though this seems to me like a performance issue.

Could anyone offer some advise on my situation.

Any input apreciated.

View 1 Replies View Related

SQL Server Admin 2014 :: Create Temporary Table With Dynamics Column

Jun 10, 2014

I have created a stored procedure for retrieving column name, as shown below

CM_id, CM_Name, [Transaction_Month], [Transaction_Year], [Invoice raised date],[Payment Received date],[Payout date],[Payroll lock date]

Now I am trying to create a temporary table using the above generated coluimns from Stored Procedure with datatype.

View 3 Replies View Related

Reseeding Temporary Tables Or Table Data Types With Identity Column

Feb 17, 2006

Hi,

I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.

This works only for the first ExitCoilID then I need to RESEED.



Here is my code:



DECLARE

@EntryCoilCnt AS INT,

@ExitCoilID AS INT,

@SubtractedFromEntyCoilCnt AS INT

DECLARE

@ExitCoilID_NotProcessed TABLE

(ExitCoilID int)



INSERT INTO @ExitCoilID_NotProcessed

SELECT DISTINCT ExitCoilID

FROM

dbo.TrendEXIT

where

ExitCoilID is not null and

ExitCnt is null

order by

ExitCoilID



DECLARE

@ExitCoilID_Cnt_Index TABLE

(ExitCoilID int, ExitCnt int IDENTITY (1,1))

IF @@ROWCOUNT > 0

BEGIN

DECLARE ExitCoilID_cursor CURSOR FOR

SELECT ExitCoilID FROM @ExitCoilID_NotProcessed

ORDER BY ExitCoilID

OPEN ExitCoilID_cursor

FETCH NEXT FROM ExitCoilID_cursor

INTO @ExitCoilID

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO @ExitCoilID_Cnt_Index

SELECT ExitCoilID

FROM dbo.TrendEXIT

WHERE

ExitCoilID = @ExitCoilID

ORDER BY

EntryCoilID, Cnt

select * from @ExitCoilID_Cnt_Index

--truncate @ExitCoilID_Cnt_Index

--DBCC CHECKIDENT ('@ExitCoilID_Cnt_Index', RESEED, 1)

FETCH NEXT FROM ExitCoilID_cursor

INTO @ExitCoilID

END

CLOSE ExitCoilID_cursor

DEALLOCATE ExitCoilID_cursor

select * from @ExitCoilID_Cnt_Index

END --IF @@ROWCOUNT <> 0

View 8 Replies View Related

Reporting Services :: Get Computed Current Row Closing Balance Column Value In Next Row Opening Value Column

Nov 17, 2015

I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.

In ssrs how can i have a report showing closing value = Opening+Addition-Sale  in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...

Date Opening AdditionTotalTank saleClosing Stock
01-11-15 14435 0 14435 8243 20627
02-11-15 0 15000 15000 9433
03-11-15 0 9000 9000 9436
04-11-15 0 12000 12000 8392
05-11-15 0 6000 6000 8157
06-11-15 0 12000 12000 8456
07-11-15 0 15000 15000 10903
08-11-15 0 6000 6000 8485
09-11-15 0 6000 6000 9413
10-11-15 0 21000 21000 6413

View 7 Replies View Related

Find Stored In Server Vide Proc For Given Table And Column

Jul 1, 2004

Hi,
I need to list out all the stored proc for all the DB in entire server.
My inpute parameter will be table name and column1/column2/column3...etc.
Please give me your input for my request.
Thank,
Ravi

View 2 Replies View Related

PK On Computed Column

Aug 3, 2004

Maybe I am missing something very obvious, but I couldn't do it: begin trangocreate table foo (f1 int not null,f2 int not null,f3 as (f1 + f2) not null primary key clustered)gorollback trango This returns:Server: Msg 8183, Level 16, State 1, Line 8Only UNIQUE or PRIMARY KEY constraints are allowed on computed columns.

View 4 Replies View Related

Computed Column?

Apr 5, 2008

i have a table that contains 2 columns

A B (B values are only 1 and 2)
-- --
x 1
y 2
z 1
x 2
z 1
j 1
k 2

i want to make a query that will check for dinstinct x what B values it has Ex. It will show result like this

result
A B
-- --
X 1 and 2
Y 2
z 1
K 2

how to make please help

View 2 Replies View Related

Computed Column

Feb 16, 2007

I want to create computed column in table.
Suppose I have three physical column A,B and C
I want to create compute column with computed column.
A+B= X
X+C=Z
Is it possible.

View 2 Replies View Related

Computed Column

Jul 20, 2005

I created a index on a computed column. I did not see any improvementin performance with a join to this column and also my inserts andupdates to this table are failing. Any ideas?Chender

View 2 Replies View Related

Computed Column

May 22, 2008



Hi,

i have to use a datetime field in all the tables in a database as a updated timestamp.
i.e. : whenever an update happens to a row in a table, this column called LASTUPDATED has to be updated with current date time.Is there any way to implement this without using the trigger ?
can i use COMPUTED column to acheive this ?

Please help me..thnks in advance..

View 2 Replies View Related

Computed Column Question

Jun 25, 2007

I have a SQL table that maintains a field on the status of a report being completed.
I have in the record the date the report is due (DateDue)
I also have a field called DaysLate which I have set to be a calculated field with formula:
DATEDIFF(dd, DateDue, GETDATE())
Thsi works but when the report is *not* late I'd like this to be null  is there I way I can do this conditional calculation in a calculated field?
Regards
Cvive

View 2 Replies View Related

Computed Column Of A View

Jun 11, 2003

I have a view that has 2 columns. The first column is associated with a function for the Units. The second column calculates the Market Value:

View
====
Col1: Unit = get_number_of_units()
Col2: MV = get_number_of_units() * get_unit_value()

I need to call get_number_of_units() twice in the view. Is it possible to changes Col2 to something like: MV = Col1 * get_unit_value()?

Is get_unit_value() being called if I do Select Col1 from View?

Thanks.

View 1 Replies View Related

Altering A Computed Column?

Feb 27, 2008

somehow I am not able to figure this out.

How do I change a computed column using the ALTER TABLE ALTER COLUMN... command?

View 1 Replies View Related

Computed Column Issue

May 19, 2008

Hi,

I'm having a problem with a computed field in a table. I have a stored procedure that inserts a row into a table and returns the id and the computed value.
The computed colmn is returned as a decimal (29,6) but for some reason the value is returned with no decimals (confirmed by the Profiler).
The value is calculated and displayed in the database properly with decimals. Also, Is Persisted is OFF.

Below is the stored procedure, computed column foruma (both give same result), and the profiler trace.

Thanks

Stored Procedure
================================================== ==============
ALTER PROCEDURE [Purchasing].[ntp_PurchaseOrderDetail_Insert]
(
@PurchaseOrderDetailId int OUTPUT,
@PurchaseOrderId int ,
@OrderQty decimal (11, 4) ,
@VendorProductId int ,
@Description nvarchar (255) ,
@UnitPrice decimal (18, 6) ,
@PackingQty decimal (14, 4) ,
@DueDateValue nvarchar (50) ,
@ModifiedDate datetime ,
@IsUnitPriceManual bit ,
@LineTotal decimal (29, 6) OUTPUT
)
AS
INSERT INTO [Purchasing].[PurchaseOrderDetail]
(
[PurchaseOrderID]
,[OrderQty]
,[VendorVendorProductID]
,[Description]
,[UnitPrice]
,[PackingMethod]
,[PackingQty]
,[DueDateValue]
,[ModifiedDate]
,[IsUnitPriceManual]
)
VALUES
(
@PurchaseOrderId
,@OrderQty
,@VendorVendorProductId
,@Description
,@UnitPrice
,@PackingMethod
,@PackingQty
,@DueDateValue
,@ModifiedDate
,@IsUnitPriceManual
)

-- Get the identity value
SET @PurchaseOrderDetailId = SCOPE_IDENTITY()

-- Select computed columns into output parameters
SELECT
@LineTotal = [LineTotal]
FROM
[Purchasing].[PurchaseOrderDetail]
WHERE
[PurchaseOrderDetailID] = @PurchaseOrderDetailId


Computed Column Formula
================================================== ==============
isnull(CONVERT(DECIMAL (29,6),[OrderQty]*([UnitPrice]*[PackingQty])),0.000000)
or
isnull([OrderQty]*([UnitPrice]*[PackingQty]),0.000000)

Profiler Trace
================================================== ==============
declare @p1 int
set @p1=115
declare @p16 numeric(29,0) <- should be numeric(29,6)
set @p16=5 <- value should be 5.259200
exec Purchasing.PurchaseOrderDetail_Insert @PurchaseOrderDetailId=@p1 output,@PurchaseOrderId=68,@OrderQty=4,@VendorProd uctId=28,@Description=N'93678975 - GL-2222',@UnitPrice=0.657400,@PackingMethod=N'Bags (2)',@PackingQty=2.0000,@DueDateValue=NULL,@Modifi edDate=''2008-05-19 15:06:37:610'',@LineTotal=@p16 output
select @p1, @p16

View 2 Replies View Related

Using Computed Column Specification

Dec 13, 2012

I have a question about Computed Column Specification which you can specify as a formula for each column inside a table.

I have now columns named Age and Class.

Classes are "Kids" (ID #1) , "Junior" (ID #2) and "Senior" (ID #3)
Kids, which is for age of 6 till 12
Junior, which is for 12 till 16
Senior, 16 and above.

I have already searched for hours (I really did) on the internet for a solution, but ended with more questions because of the complicated solutions.

Now the Age is shown as a result of a formule of DOB (Date of Birth column), now I want the exact same thing, but the age must specify which Class the user is in.

Example, when I add a user with the birthdate 25/03/1988 (DD/MM/YYYY) he/she gets 24 as age.

With this formula : (datediff(year,[age],getdate()))

Now I want that the user gets "Senior" as Class (same table).

Senior is ID 3 in this case.

Now I do know how Case, When and Then works, but the validation fails. After reading some forums I understood that I should use a create function method. I am not really experienced with creating functions. Also the coding looks more different as I am used to. How to link the Computed Column to a created formula.

View 3 Replies View Related

Using Computed Column In The Query

Apr 14, 2004

need help ,

i have a table called "Loans" where i need to compute a column i.e. NoofDays based on which other calculation like interest calculation needs to be done.
my query goes like this

"select datediff(dd,VDate, MDate) as NoOfDays ,NoOfDays * Principal * Rate /100 * 365 from Loans".

if i run the above query it says

"Invalid column name 'NoOfDays'".

this executes fine if i use Access but not in SQL Server.

can anybody say what might be the problem and how i can solve it.

regards

Rajesh :)

View 1 Replies View Related

Problem With Computed Column

May 19, 2008

hi
i want to create a table that has a computed column like this :
create table resources(
id int identity(0,1) primary key,
currentDate int not null,
currentMonth int default 0,
monthBefore as (currentMonth - (select top 1 currentMonth from resources where (currentDate - resources.currentDate) = 1)))

as u can see, monthBefore is computed column, and i want get currentMonth value of previous month, for this work, i define a column as currentDate that hold only year+month (like 971,082,083,...) and by this expression, i want to get currentMonth of previous record (previous month), but the following error has shown me :
Subqueries are not allowed in this context. Only scalar expressions are allowed.
how to solve this problem to get currentMonth of previous record ?
thanks

View 3 Replies View Related

Formula For Computed Column

Jan 17, 2007

I have downloaded the SQL Server Books online and found the section on Computed columns. In my small banking program I have columns named Deposit/Withdrawel and Balance. If I am reading this right I need to set the Computed Column Specification of my Balance Column in order to perform the calculation. My problem now is that I have no idea how to word the formula and other than telling me what a formula is the SQL Server Books online is no help whatsoever, so any help would be awesome.

If i've misunderstood what I am meant to do somebody please please tell me. Thanks

View 3 Replies View Related

Null And Computed Column

Oct 1, 2007

how do you change the allow null on a column to be 0,
so instead of having null when empty, it would be 0,
I tried default value or binding to 0 to -1, and to 1
and in 0 or -1, it stays null, in 1 it puts the 1
for example if is quantity, it puts as if I would have 1 quantity,
so it's either null, or whathever number I put visides -1 or 0
how can I make it be 0 as defult?
I'm using it to add and substract with computed column, formula;
and the problem is that if it's the first time it's used it does not add or substract, becsause it does not add null with a number, if it has 0 than it works,
whata I had to do is add it manually, but of course it doesn't suppoesed to be that way
any suggestions will be appriciated:

View 2 Replies View Related

Computed Column Calculations

Jul 23, 2005

If i want to split a computed column into two or more columns based onthe the length (its a varchar) of the computed column, how often willsql server determine what the computed column is? roughly along thelines ofselect comp_col,'comp_col 2'=case when datalength(comp_col)<=100 then comp_colelse left(comp_col,99) + '~' end,datalength(comp_col)from aTableAs you can see, in this scenario we use the computed coulumn,comp_col, in a few places, so does SQL server need to calculate thiseach time? I'm playing with this on the basis that it does and thustrying to shift the computed column out to a variable and thenmanipulte and return from their, but that has its own problems whenyou throw in additional parameters (trying to join table udf's) so ifSQL server is smart enough to not calculate the column each time Iwould save a lot of hassle?Cheers Dave

View 9 Replies View Related

Index Computed Column?

Jul 28, 2006

Can I create an index on a variation of a column that isn't actually inthe table?I have a ParcelNumber column, with values like123 AB-67012345ABC 000-00-04012-345-67AP34567890The blanks and non-alphanumeric characters cause problems with users,because sometimes they're there, and sometimes they aren't. So I wouldlike to create an index based on this column, with the non-alphanumericcharacters squeezed out. Of course I can add such a column to thetable and index it, but I'm wondering if it can be done withoutactually adding the column.Thanks,Jim

View 6 Replies View Related

Computed Column Specification

Sep 10, 2006

Hello,I want to assign a column a computed value, which is the multiplicationof a value from the table within and a value from another table.How can I do that?Say the current table is A, column1; and the other table is B, column3.What should I write as formula?I tried someting like;column1 * (SELECT column3 FROM B WHERE A.ID = B.ID)but it didn't work.

View 2 Replies View Related

Problem With Computed Column

May 19, 2008

hi
i want to create a table that has a computed column like this :

Code Snippetcreate table resources(id int identity(0,1) primary key,currentDate int not null,currentMonth int default 0,monthBefore as (currentMonth - (select top 1 currentMonth from resources where (currentDate - resources.currentDate) = 1)))

as u can see, monthBefore is computed column, and i want get currentMonth value of previous month, for this work, i define a column as currentDate that hold only year+month (like 971,082,083,...) and by this expression, i want to get currentMonth of previous record (previous month), but the following error has shown me :

Code SnippetSubqueries are not allowed in this context. Only scalar expressions are allowed.
how to solve this problem to get currentMonth of previous record ?
thanks

View 11 Replies View Related

Computed Column And Cursor

Dec 26, 2007

Is it possible to use a cursor in computed column?

I have two table (Table1 and Table2). Table 2 is child of Table 1.

Table 2 has more than one record for each record in table 1. What I want is, to concat records from table 2 and show it as a column value for Table1.

e.g.


Code Block
Table 1:
Col1 Col2
1 ABC
1 DEF

Table 2
T1Col1 Col2
1 A
1 -
1 B
1 1
1 -
1 C
I want to see the Results as
Table 1
Col1 Col2 Col3
1 ABC A-B1-C


Is it possible to add computed column for the table1 to get the result as shown in above block.

Thanks

View 3 Replies View Related

It's A Computed Column Processing Bug?

Sep 25, 2007



Hello everybody!

I have question about indexed and not indexed Persisted columns on sql server 2005. It's a bug?

First?, my version of SQL Server is
Microsoft SQL Server 2005 - 9.00.3186.00 (Intel X86) Aug 11 2007 03:13:58 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)


Now I create two tables and try four select queries:


Code Snippet
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
GO
create table t1 (id int primary key, id_bigint as cast(id as bigint))
GO
create table t2 (id int primary key, id_bigint as cast(id as bigint) persisted)
GO
select * from t1 -- (1)
-- Clustered index scan with two times Compute Scalar
GO
select * from t2 -- (2)
-- Clustered index scan with one times Compute Scalar
GO
create index IX_t2 on t2 (id_bigint)
GO
select * from t2 -- (3)
-- Index Scan with one times Compute Scalar
GO
select * from t2 where id_bigint = 0 -- (4)
-- Index Seek with one times Compute Scalar
GO
drop table t1
GO
drop table t2
GO
SET ANSI_PADDING OFF



1. I don't understand why access to computed column raise scalar computation wto times?
2. I don't understand why access to persisted computed column raise any scalar computation?
3. I don't understand why access to persisted computed column over index required any scalar computations?

Can anyone from Microsoft SQL Server Team told me about this mistake?
It's a BUG or I incorrect understand value of the "PERSISTED" word?


--
Thanks with avanced.
WBR, Roman S. Golubin
grominc[at]gmail.com

View 3 Replies View Related

Computed Column Formula

Aug 1, 2006

Is it possible to retrieve the formula associated with a computed column using t-SQL? I can use COLUMNPROPERTY( id, column, 'IsComputed') to find the computed columns, but how do I get the formula itself?

Thanks,

Mable

View 3 Replies View Related

Division By Zero And Computed By Column

Apr 5, 2007

I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved