MS SQL Server :: Can The Match Index Of A Fz Lookup Table Be Partitioned?

Fuzzy Lookup Match Index Maintenance

May 29, 2007

Hello,

For one of my SSIS projects that does a fuzzy lookup on a table, I opted to create an index and

to maintain the stored index. The index got created and subsequent project execution was able to

use that index.

Now I want to update certain rows in that table. When I run the update statement I get the following error.

How can I retain the index and still be able to update the table?

update location_stage set batchid = 'APR07N'

where batchid is null and eventid = '20070528020041';

Msg 6549, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInvoke, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':

System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.SqlInternalConnectionSmi.ExecuteTransaction(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction)

at System.Data.SqlClient.SqlInternalTransaction.Rollback()

at System.Data.SqlClient.SqlTransaction.Rollback()

at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)

. User transaction, if any, will be rolled back.

The statement has been terminated.

View 3 Replies View Related

Lookup Finds Match On Empty Reference Table

Jul 6, 2006

Hi all,

In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "

I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.

Does anyone have an idea why? I could'nt find anything about that in BOL.

Sébastien.

View 4 Replies View Related

Row Yielded No Match During Lookup When Using 2 Columns In Lookup

Jul 24, 2007

I am doing a lookup that requires mapping 2 columns in the column mapping section. When I do this, I get the error "Row yielded no match during lookup" . The SQL that I captured in SQL profiler does find the record when I run it in Management Studio. I have already tried trimming everything to no avail.

Why is this happening?

I tried enabling memory restrictions but then I my package hangs and I get a SQLDUMPER_ERRORLOG.log file with the following logged:

07/24/07 13:35:48, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5952
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:Program FilesMicrosoft SQL Server90SharedErrorDumpsSQLDmpr0033.mdmp
07/24/07 13:35:49, ACTION, DtsDebugHost.exe, Watson Invoke: No

Why am I getting this error with "Enable Memory Restriction"?

View 12 Replies View Related

Row Yielded No Match During Lookup While There Is No Row Going Through The Lookup

Sep 29, 2006

Hi all,

I don't understand what's happening here.

I have a Conditional Split with 3 outputs. On the first output I have a lookup, when I execute the package I have 56 rows going through the Conditional Split, all rows are then going to the 2nd and 3rd output but the lookup on the first output generates an error "Row yielded no match during lookup".

I don't understand why the lookup is generating an error while there is no row going through it.

Any idea ?

Sébastien.

View 6 Replies View Related

Fuzzy Lookup Index Table

Feb 26, 2008

Can anybody tell me when the index table is created? I have a Ole DB Command transformation that deletes rows from a table, and the pipeline continues on to a fuzzy lookup. The fuzzy lookup is returning matches to some of the rows which were delted in the aformentioned Ole DB Command. If the index table is created during pre-execute, this would make some sense to me since those rows which get deleted still exist before the pipeline reaches the Ole DB Command transformation which deletes those rows. If this is the case, is there a way to delay the index table creation? If this is not the case, has anybody else ran into anything like this...is there some solution? Thanks for any help.

View 1 Replies View Related

Difference Between Index Seek && Index Scan && Index Lookup Operations?

Oct 20, 2006

please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio

thank you in advance

View 3 Replies View Related

Row Yielded No Match During Lookup

Sep 8, 2006

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

View 9 Replies View Related

Row Yielded No Match During Lookup

Sep 14, 2006

I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.

Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.

Or else any other way to send an email after generating the text file ocntaining the non matching rows.

Please suggest using steps or example

View 4 Replies View Related

Fuzzy Lookup Match Issue

May 29, 2007

Hello,

I have a peculiar problem in my project. My project design is like this

The number in (...) are count of records.

File feed (1000)
|
|
Fuzzy Lookup
against Table2
|
|
Split Fz Lookup results
(_Similarity >= 0.60 && _Confidence >= 0.85)
| |
| |

| Write matches to Table1 (250)

|

Fuzzy Group

Remaining rows (750)

|

|

Split Fz Group results

| |

| |

Write Canonicals Write Dupes

to Table2 to Table1

(300) (450)

This is basically a customer de-dupification project.

The Table2 has the canonicals and Table1 has the dupes (of the canonicals).

I already have some data in these tables and the new data is matched against the existing data

in these tables and classified as new customers and duplicate customers.

In the above process one could notice that the rows identified as dupes of already exsting canonicals

by the Fuzzy Lookup task are written into the dupes table (Table1) and will not be processed further down

the line in the project.

But in my case I see that those matches identified by Fuzzy lookup are further being included in the

Fuzzy Grouping also.

When I run this in debug mode in BIDS, it shows the correct numbers as I have depicted in the

illustration above. But, after execution, when I query the tables it shows that all 1000 rows

went through Fuzzy Grouping.

Any thoughts?

Btw, is there anyway to upload attachments to the postings here?

View 1 Replies View Related

Fuzzy Lookup Misses Possible Match

Apr 10, 2008

I have a fuzzy lookup task that compares a source list of contacts to a reference list of contacts with the default settings. I did some testing by adding seed data that I knew would produce somewhat high similarity hits. All of the seeded contacts but one came back with the expected high sim values. When I looked for the one that didn't, I noticed another match had come up but it had a very low similarity of .17. I then did some research and discovered the reason was the MaxOutputMatchesPerInput setting which was set to 1. I then set it to 3 and reran the package and sure enough my seeded contact that was missing before now showed up. I thought the best match would show up if the MaxOutputMatches was set to 1? That is not the case in my testing.

For example, Donna Mizeman was in the reference list. I added Don Miseman to the source list to seed it. The only match that came back was something like Dieman Abdul .... So the initial match had a similarity of .17 but when MaxOutputMatchesPerInput is set to 3 the best match (seeded) has a similarity of .72.

Anyone have an explanation for this?

-Mike

View 1 Replies View Related

Lookup Transform Not Finding Blank Match

Mar 14, 2006

I am having problems with a lookup transformation. I have a row in my lookup table for blank ('') source data. If I test the join using SQL the match is made, but the Lookup transform doesn't consider it a match and sends it to error output. Is there a property that I don't have set correctly or something else I am forgetting?

View 5 Replies View Related

SQL Equivalent For Excel Match + Lookup Formula

Nov 29, 2007

Hi there,

Not sure if this is the right thread to post this on but here goes:

I currently have an excel file, we will call it price for now. In this file I have details similar to those below:

100 200 300 400

100 $4 $5 $6 $7
200 $20 $30 $40 $50
300 $100 $200 $300 $400
400 $15 $15 $15 $15

At present in Excel, one uses the match and lookup function to determine the price of the length and height dimension. Example: 300 x 200 = $40

How can one perform this same task in SQL 2005? Is this even possible?

Your responses would be greatly appreciated.

Thanks,
chris_kzn

View 3 Replies View Related

How To Drop Lookup Rows That Have No Exact Match?

Nov 7, 2007

I have a very basic Lookup in my SSIS package that looks up against two columns and outputs a row to a table. Now currently if there is no exact match, it writes a null in my destination table. How do I simply drop all those rows that dont produce an exact match? I tried using the 'Ignore' error output, but with that it writes NULLS into my destination table. With the 'Redirect' it is looking for a place to redirect the error (NULL) rows, and I dont want to deal with the hassle or writing these NULL values to a file or table just to delete them afterwards. I just simply want to forget about all those rows that dont produce an exact hit and only fill in the destination table with those that do produce a hit. How can I drop these lookup rows that dont produce an exact match?

View 5 Replies View Related

Partitioned Index Column Order

Aug 22, 2007

We are using partitioned unique indexes on partitioned tables. When the Unique Index is built, should the column the index is partitioned by be the top (leftmost) column in the index? While this violates cardinality, it makes sense (at least to me) that the first thing the query execution would do is figure out which partition(s) contain the result set, then filter from there.

What do you guys think? Is there any documentation on optimizing partitioned indexes?

View 1 Replies View Related

Lookup Transformation Fails On DT_STR (3) String Match

Oct 9, 2006

The Lookup Transformation fails to match this datatype when full caching is enabled. When partial caching is activated (Edit > Advanced, Enable Memory Restrictions > Enable Caching) the lookup works.

This appears to be a bug to me.

View 4 Replies View Related

SQL Server 2014 :: Indexed View Not Being Used For Partitioned Clustered Column-store Index?

Oct 9, 2015

I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.

I have created a view with the following code:

ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key

I then created an index on the view as follows:

create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)

I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?

View 1 Replies View Related

Query Server To Find All Partitioned Tables, Partition Name, Column Used, Partitioned By

Dec 17, 2007

I want to find a way to get partition info for all the tables in all the databases for a server. Showing database name, table name, schema name, partition by (maybe; year, month, day, number, alpha), column used in partition, current active partition, last partition (for date partitions I want to know if the partition goes untill 2007, so I can add 2008)

all I've come up with so far is:

Code Block

SELECT distinct o.name From sys.partitions p
inner join sys.objects o on (o.object_id = p.object_id)
where o.type_desc = 'USER_TABLE'
and p.partition_number > 1

View 3 Replies View Related

Performance Expectations For Fuzzy Lookup Against 25mill Row Lookup Table

Oct 31, 2007

We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.

We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).

After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.

We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.

Does that performance seem inline with expectations? Any thoughts to improve performance?

View 4 Replies View Related

Index & FK Relationship On LookUp Tables

Jun 11, 2007

Hi!

It's been always said that it is best to put index on commonly joined fields in the table. But putting too much index on the table would cause the table to be slow on insert and update.

My question is, how do you deal with your fields that uses look up tables? Like for example for these fields

- CountryID(smallint)
- CreatedBy(int)
- ModifiedBy(int)
- Status(tinyint)

Those fields don't come a big part in the table, though when I query the table I always join them with their respective primary table to get their respective text value. Do I still need to put Index & FK relationships to these fields?

What fields are normally good candidates for index or fk relationships?

Thanks,
Enzo

View 6 Replies View Related

Challenge With Fuzzy Lookup Index Maintenance

Mar 5, 2008

Greetings
My Fuzzy Lookup task works beautifully when it generates the lookup index every time it runs, but as I'm planning on running this hundreds of times I'd like it to maintain the index via the trigger. However when it attempts to install the trigger via sp_FuzzyLookupTableMaintenanceInstall I get:

Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException:
Error number 8101 is invalid.

(I've not included the full stack trace as I figured this would be enough)

The table currently has an After Insert and After Update trigger. CLR integration is enabled in this database instance. Is there some other option I need to set somewhere?

Thanks!!

View 17 Replies View Related

How To Create Partitioned Table?

Jul 16, 2001

I'm running sqlserver 2000 enterprise edition on windows 2000 and I need
to know, how to create partition table. Please give me a small partition table example.

Thanks,
Ranjan

View 1 Replies View Related

Insertion Into Partitioned Table

Apr 8, 2008

hello
i want to ask if the insertion of a record into a partion is slower than insertion it into a non partitioned table or not?
cuz sql has to decide to wich partion the record has to insert according to the partitioning key and is this decesion process is making insertion slower ?

View 2 Replies View Related

Inserting In A Partitioned Table

Sep 14, 2007

Hello;

I have inserted 200m rows into a partitioned table using SSIS, the table has a [RecID] column which is an identity(1,1) primary key.
When I open the table, I see that RecId doesn't start from 1(its not ordered), it starts from 889823. But, when I query the table for RecID = 1, I can see that row.

Is it a typical behavior of a partitioned table? Or am I doing something wrong?

This is the query I used to create the partitioned table.

create partition function pf_LoadDate(Datetime)

as range right for

values ('01/01/1997','01/01/1999','01/01/2001','01/01/2002','01/01/2003',
'01/01/2004','01/01/2005','01/01/2006','01/01/2007')

--------------

create partition Scheme ps_RecBuyLoadDateScheme

as partition pf_LoadDate

to (FG1,FG1,FG1,FG1,FG2,FG2,FG2,FG3,FG3,FG3)

I'll appreciate any help.

Prok

View 15 Replies View Related

Northwind - Execution Plan Bug? Why Index Seek And No Bookmark Lookup?

Dec 10, 2006

If you display the execution plan and run the following:SET STATISTICS IO ONgoSELECT ProductID, SupplierIDFROM ProductsWHERE SupplierID = 1I don't understand how come there is noBookmark Lookup operation happening to get theProductID?I only see an Index Seek happening on SupplierID.There is no composite index SupplierID + ProductIDso what am I not understanding here?Thank you

View 3 Replies View Related

SQL Server 2008 :: Execution Plan Ignoring Index Insisting On Key Lookup

Sep 15, 2015

I have query with an expensive Key Lookup on a joined table. The predicate is the column that I'm joining on, and the output list contains two columns from the joined table.

I've created a basic non-clustered index covering the predicate column and include-ing the two output columns. However, the execution plan ignores this, and insists on using the primary key of the joined table to do the expensive key lookup. I've tried adding the included columns to the index directly and there's no change. I've also tried running dbcc freeproccache and no change.

View 3 Replies View Related

Replication And One Row Updates On Partitioned Table

Jul 27, 1998

Hi,

Has anyone had any problems on one row updates on a table where you have defined horizontal and vertical partitioning of the data to be replicated?
When I execute an update clause that modifies just one row the log reader misses the modification and it does not get replicated to the other databases.

If I do the same update clause but on several rows then all the modifications are read by the log reader and the replication task goes ok.

What might be wrong?

-janne

View 1 Replies View Related

Truncate A Partition In Partitioned Table

Nov 14, 2007

Hi all,

We have a table with 15 Partitions in SQL Server.Can i write a stored procedure or an SQL statement just to truncate a particular partition by passing the partition name.

Any Suggestions??????????

Thanks
Chow.

View 11 Replies View Related

256 Table Limit For Partitioned Views

Aug 8, 2005

I have a partitioned view sitting over several tables and I'm slowlyapproaching the 256 number. Can anybody confirm if there is such alimit for the maximum number of tables that a partitioned view canhold?If this is true, does anybody have any suggestions or ideas to workaround this max limit?TIA!

View 4 Replies View Related

SQL Server 2008 :: Filestream On Partitioned Table

Jun 12, 2015

I'm having a problem creating a partitioned table with a filestream column. I'm getting error: Cannot create table 'MyTable' since a partition scheme is not specified for FILESTREAM data

CREATE TABLE MyTable
(
IndexID BIGINT IDENTITY(1,1),
PartitionID SMALLINT,
IndexGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL

[code]...

I actually managed to get the table created. The table below gets created. I had to specifically indicate that the unique constraint is on [PRIMARY] (non-partitioned) and create a partition scheme in the filestram filegroup. However my problem now is with partition switching. I successfully created a non-partitioned staging table identical to the partitioned table, but the switching operation doesn't work.

CREATE TABLE MyTable
(
IndexID BIGINT IDENTITY(1,1),
PartitionID SMALLINT,
IndexGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
CONSTRAINT DF_MyTable_IndexGUID DEFAULT (NEWID())

[code]...

I get error:

'ALTER TABLE SWITCH' statement failed. The table 'MyTable' is partitioned while index 'UQNP_MyTable_IndexGUID' is not partitioned.

However, I can't drop the non-partitioned unique index on MyTable as it violates the filestream, and would make the table unusable.

View 0 Replies View Related

Sliding Window Scenario On Partitioned Table

Nov 13, 2007

I began with a partition function as follows:

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE RIGHT FOR VALUES (633294720000000000, 633320640000000000, 633347424000000000, 633374208000000000, 633399264000000000)

These numbers happen to correspond to the dates 11/1/7, 12/1/7, 1/1/8, 2/1/8 and 3/1/8 in ticks respectively.

I began with a partition scheme as follows:

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION [TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001], [FG_xxx_EventArchive00002], [FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004], [FG_xxx_EventArchive00005], [PRIMARY])

While running my “sliding window script�? , which I hoped would 1) roll off the oldest partition of my EventArchive table and 2) add a new partition with a tick boundary that equates to 3/5/8, I get an error related to my switch out table's index, the same table's Filegroup and Primary.

After getting the error, I scripted the partition function as a create in mgt studio and got…

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE RIGHT FOR VALUES (633320640000000000, 633347424000000000, 633374208000000000, 633399264000000000, 633402720000000000)

...which looks like what I had intended cuz the last boundary is the tick representation of 3/5/8 and the oldest has rolled off

scripting the scheme produced...

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION [TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001], [FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004], [FG_xxx_EventArchive00005], [PRIMARY], [FG_xxx_EventArchive00001])

which looks nothing like what I intended, I thought I’d end up with …00002,…00003,…00004,…00005,…00001,PRIMARY

the script steps that seem most relevant start at the 5th step as follows...

5) creates table [dbo].Switch on the switch out filegroup with columns, PK and indexes matching exactly those of [dbo].EventArchive

6) switches partition 1 of [dbo].EventArchive to [dbo].Switch

7) ALTER PARTITION FUNCTION TimeTicksRangePFN() MERGE RANGE (633294720000000000) --this was the oldest date corresponding to 11/1/7

8) truncates [dbo].Switch

9) drops all indexes on [dbo].Switch except a clustered index (IX_TimeTicks), leaves PK constraint alone

10) ships the new data whose values range from 3/1/8 to less than 3/5/8 to [dbo].Switch and deletes them from their source

11) recreates all non clustered indexes on [dbo].Switch

12)ALTER TABLE [dbo].[Switch] WITH CHECK ADD CONSTRAINT RangeCK CHECK ([TimeTicks] < the number of ticks represented by 3/5/8)

13)ALTER PARTITION SCHEME TimeTicksRangePScheme NEXT USED [FG_xxx_EventArchive00001] --fg isnt really hardcoded

14)ALTER PARTITION FUNCTION TimeTicksRangePFN() SPLIT RANGE (the number of ticks represented by 3/5/8)

15)ALTER TABLE [dbo].[Switch] SWITCH TO [dbo].[EventArchive] PARTITION 5

step 15 is the one that fails with message "ALTER TABLE SWITCH statement failed. index 'xxx.dbo.Switch.IX_TimeTicks' is in filegroup 'FG_xxx_EventArchive00001' and partition 5 of index 'xxx.dbo.EventArchive.IX_TimeTicks' is in filegroup 'PRIMARY'.

View 1 Replies View Related

DB Design :: Cannot Delete Data From A Partitioned Table

Sep 30, 2015

I have a very large table that I am trying to partition and use to reduce maintenance overhead as well as improve performance. The table contains about 12 years worth of data but only the most recent years is inserted/updated/deleted from thru the app. I created partitions on a computed(persisted) column which holds the "year" value derived from a date column. I have created the partitions with all the default set options, and the stored procedure which performs the delete against this table also was created with no special set options(basically database/session default). Yet, every time I try to run the proc to delete data thru the app, I get this error:

Msg 1934, Level 16, State 1, Procedure xxxx, Line 118
DELETE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

I've tried setting ANSI_WARNINGS on and off when creating the proc, inside the proc etc.., its always the same error whatever I set the option to.