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]
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
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.
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?
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.
Hello, i wanted to give the forum my current process flow to see if i am close or have some more work to do. The object is to import the data as fast as possible without loosing query responsiveness of the search on the web side. Any type of response will be greatly appreciated.
Current Process I receive multiple product inventory lists form multiple vendors. These inventory lists are in many different formats like .xls format, .txt format, .csv format and .dbf format. My server converts the file format to raw .csv. The table is very large and will consist of millions of rows. Inventory comes in alphanumeric format.
Each of the inventory lists are NOT in the same format. What i mean by this is that there are different header names for the users inventory list than what matches our database table. For Example, a user may have an excel document with the header name "Amt" but our database table field name is "Amount". In order to make this import process automated I make a single mapping file for each user. This mapping file relates the users field header in their inventory file to that of the database table.
Currently I have a process converting all files to .csv format. Every 15 minutes a routine runs that converts the .csv to XML and then performs the import Bulk Insert routine. The process deletes the users entire inventory then imports to a "tempinventory" table, which then updates the inventory table. During this 15 minutes we may get 20 inventory lists to import which could be a half a million records. I have this inventory table indexed using the primary key which is the inventory number. This is the search criteria used (Inventory Number) on the web side. The import routine to SQL works quickly ONLY if Full-Text Index is turned OFF. I assume i need the Full Text Indexing on so the queries of the full inventory lists a fast response on the web side.
Assumed Issues Right now if I were to turn off the Indexing for each import then we would have slow queries against the database on the web side.
To have one table with millions of rows that is constantly updated as well as queried at the same time is not efficient.
Assumed Corrections Right now it seems that partitioning the table into 10 numeric partitions, one for each number would leverage the import routine as well as the web side search routine. I would then partition the alpha partition in 1 letter increments so we would have 26 partitions, one for each letter for a total of 36 partitions.
If I have the partitions seperated then it will be easier to update the seperate index as well as perform maintenance on the index, i assume this is correct.
Future Plans I am upgrading the web side application to .NET 3.5 so we can take advantage of XLINQ and LINQ so our searches from the web side are faster and more efficient.
I am also looking into building a SilverLight application that will allow the user to install the application locally that will take their live database file and send updates to my server so they do not need to send in their inventory file at all and the inventory lists are live. This will alleviate the need to delete the users full listing in order to make a complete update. As stated above sometimes an update may just include updating the amount of only 2 of 40,000 rows.
I was also looking into db4o to see if it would be beneficial as well, http://www.db4o.com/, has anyone worked with this before in a similar manner?
Questions
I would like to make this process much more efficient from the import routine to the search routine. Is setting up the partitions as discussed a stable plan for both routines? Is the BULK INSERT using XML to SQL the most efficient way of importing the data to SQL?
How would i handle the full text indexing to allow fast import routines without slowing down the web side searches? After import of new data do i need to "update" the index as well? What is a good set of "preventative maintenance" standards should i follow when dealing with this many table updates as well as the catalogs and table data? I know there are benefits of using LINQ when querying the database from the web side but are there any other benefits that would fit into this current process? As for the SilverLight application would it be beneficial for the user as well as me to have the application poll their database file to find changes and send only the updated values of the list to the server via XML, which is then updated by SQL?
I am unsure what is the best way to make this process as easy and automated as possible giving the user the fastest experience possible when searching from the web side. Is this a smarter idea so i can track just the changes made by the user on their inventory list instead of importing the same redundant data they have? Would implementing something like db4o be beneficial for this process, http://www.db4o.com/? Please let me know if i am way off on this process or if there are some benefits that i am not using in this process. I have been doing a lot of research and this is what i have come up with so i wanted to ask the community what they thought about it as many heads are better than one. Please feel free to rip the process apart to, i take constructive criticism.
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...
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.
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.
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
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 ?
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
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.
-- 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
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.
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.
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
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
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:
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
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
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.
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
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
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?
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
I want to use an if statement to compute the value of a column in SQL Database using other columns. I am supposed to check if a column is null or not and do the computations accordingly. Can anyone help?
Hi. I have this method in a class, it produces a string value based on the value of another property in the object (which represents a field in the database). I would like to turn this into a computed column in SQL server... but need help converting the formula if this is even possible. Thanks in advance.public string GetVendorEvalRating(int vendorevaltotal) { string vendorevalrating = "";if (vendorevaltotal >= 26) {vendorevalrating = "Critical"; }else if ((vendorevaltotal >= 10) && (vendorevaltotal <= 25)) {vendorevalrating = "Material"; }else if ((vendorevaltotal >= 0) && (vendorevaltotal <= 9)) {vendorevalrating = "Minor"; }return vendorevalrating; }
I'm new to computed columns so heres the issue.I have four fields in particlur which I am trying to use: LeaseStart - smalldatetimeLeaseEnd - smalldatetimeLeaseDuration - Calulation = (datediff(year,[LeaseStart],[LeaseEnd]))YearlyExpense -TotalExpense - numeric(18,0) Now I am trying to divide the field in LeaseDuration by a numeric column (totalExpense) in the column YearlyExpense. convert(numeric(LeaseDuration)) / totalExpense Any help would be great!