MS SQL Server :: Problem With PreComputed Partitions And Numeric Partition Key (HOST

SQL Server 2012 :: Partition Existing Table And Archive One Of The Partitions

Jun 22, 2015

I have some table that need to be partitioned and archive one of the partitions.

I did this in Oracle several years ago but not in SQL Server.

I'm looking for a basic example on how to do this.

I know the basic steps but the examples that I found on the Web were not quite what I'm looking for.

[url][/
Partition an existing SQL Server Table
url]

View 9 Replies View Related

Does SQL Server Move Records Between Partitions When Updating The Partition Key Column?

Apr 4, 2007

If I run an UPDATE query on a table which is partitioned by the column I am updating - will the records be moved to another partition?

ie. I have a table where Historical bit column marks whether a particular record should go to Partition1 (=0) or Partition2 (=1). Now, I update a record in that table and change the Historical column value from 0 to 1. What happens with that record?

View 3 Replies View Related

Precomputed Tables

Jul 20, 2005

Dear Friends,Suppose a database (SQL SEVER 2003) is consists of 500 Tables & 1000 Views.As I understand from the theory, that Views are nothing but the queries stored in the databse. Whenever a view is referenced than it starts fetching data from tha database. Thus , it will force the processor to do calculations.To reduce the processor burden and to ge the fast response: instead of keeping 1000 Views- I wish to keep precomputed tables in the databse.Would this be fair practice?Your suggestions & guidence required. Further Discussions are welcome.Thank You.SuryaPrakash****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...922abd573f7447e*****************************************

View 1 Replies View Related

Host_name

Nov 1, 2005

Hi,I have a question regarding host_name() and IP addresses of clients. I'mrunning on a shared server - so access to xp_cmdshell is barred which is thestandard response to questions about getting the IP address of a client fromsql server. My issue is this:For security reasons every user of our database system logs into our customsecurity system all under the *same* sql-server user name (who only hasaccess to a discrete set of stored procedures). This can't be changed as weare limited to 3 database users. I store the host_name that the user log'sin from when he logs in - and then check the host_name of any further callsto sp's under this login context. I have however just discovered thathost_name() is set in the connection string - so the client can pass prettymuch whatever he wants to - so all an imposter would have to do is *fake*the client name of an existing user. Is there anyway of detecting the *real*client's host? Is there any way of forcing a client to be limited to justone client machine? Can I get hold of the IP address in a reliable way?ThanksNick

View 4 Replies View Related

Replication With Host_name

Apr 13, 2006

Hello,

I am wanting to limit the amount of rows that are merged between the server and the wm device. Host_name() doesn't seem to be supported in sql mobile. I created a column called host and set the default value as host_name(). I get the table to replicate however when I try to insert into the table on the mobile side I get errors. Is there another option to filter like this or if I hard code a deviceID how can I add this to the filter rows options? Thanks.

John

Table script:

CREATE TABLE [dbo].[tstHost](

[peoplid] [numeric](18, 0) NOT NULL,

[image] [image] NULL,

[host] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_tstHost_host] DEFAULT (host_name()),

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_F9DD287E09FF4064813E154D2F0ACBC6] DEFAULT (newsequentialid()),

CONSTRAINT [PK_tstHost] PRIMARY KEY CLUSTERED

(

[peoplid] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Query on sql mobile side:

insert into tsthost (peopleid) values (2)

Error:
FAILED: insert into tsthost (peopleid) values (2)
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25503)
Description: The column name is not valid. [,,,Node name (if any),Column name,]
Interface defining error: IID_ICommand
Param. 0: 0
Param. 1: 0
Param. 2: 0
Param. 3:
Param. 4: peopleid
Param. 5:

View 1 Replies View Related

How To Get HOST_NAME Insted Of User_name()

Jun 7, 2001

All developers use same login to access database (I know is it is not the best solution)

We need track when user insert value or edit value in specific table
It is done by triger and work fine with USER_ID() or user_name()

But all users (developers) use same login and USER_ID() and user_name()always the same.

Please help me how can get HOST_NAME of user who is trying to modify table and place it into triger insted of user_name()

Thanks a lot

View 2 Replies View Related

Using Host_name(): Crossing My Fingers...

Jul 20, 2005

I did this thing with host_name(). I'm hoping someone can tell me ifI'm gonna get into trouble with this scheme before it's too late...I have maybe 75 users.They all call on a table of appointments (many thousands) and I dumpthe ones being viewed into a temp table named TaskTEMP.I am using a custom function within a View to grab a bunch of rows inone table and slap them into a single column on the records returnedby the view.Here's where I'm concerned-When I insert the records into the TempTable I insert the valueHOST_NAME() into every row of a column named myMachine.So the row looks like:TaskID aName myMachine1234 Jim Smith Dell1011235 Fran Jones Dell1011235 Mary Cat Dell101When I run the view, I get:TaskID myNames1234 Jim Smith1235 Fran Jones, Mary CatWhat I'm concerned about is when user 2 is looking at the same recordat the same time and inserts the following into TaskTEMP:1234 Jim Smith CompaqXYZ1235 Fran Jones CompaqXYZ1235 Mary Cat CompaqXYZI use the HOST_NAME()filter in the example below to deal with this sothat both user one and user two get what they want instead of:TaskID myNames1234 Jim Smith, Jim Smith1235 Fran Jones, Mary Cat, Fran Jones, Mary CatIt works great in testing. The obvious problem is if two users havethe same machine name, but in this installation business rules preventthat.I hope someone can either validate this approach or improve or trashit before it gets too late with this project.thanks.lqThe view and function look looks like this:SELECT TaskID, dbo.fn_myNameGroup(TaskID) AS myNamesFROM dbo.TaskTEMPGROUP BY TaskIDIDThe function looks like this:CREATE function dbo.fn_myNameGroup(@TaskID as int) returnsnvarchar(500)asbegindeclare @ret_value nvarchar(500)SET @ret_value=''Select @ret_value=@ret_value + '; ' + aName FROM TaskTEMP whereTaskID=@TaskID AND myMachine=HOST_NAME()RETURN RIGHT(@ret_value,LEN(@ret_value)-2)end

View 3 Replies View Related

HOST_NAME() Filter Issue

Feb 5, 2007

I am using dynamic filtering on host_name() for SQL 2005 SP1 merge publication. It had been working fine, but something has caused the configuration to stop working.

Setting up the publication and a test subscription via the GUI works fine. However, if I script out the publication and subscription and attempt to run the script (with suitable password), then I get the error

��a value for the parameter @host_name was specified while publication does not use host_name() for dynamic filtering�?

I did some digging and was curious to find some differences in the information returned by sp_helpmergepublication and the content of dbo.sysmergepublications.

The sp_helpmergepublication output showed expected content after both GUI and script setup �� with a value of HOST_NAME() for validate_subscriber_info.

The content of dbo.sysmergepublications was as expected after setup via the GUI �� with a value of HOST_NAME() for dynamic_filters_function_list.

However, I noticed that if I performed setup using scripts the dynamic_filters_function_list value was null.

Further note that the subset_filter_clause value was correct for the filtered article with both GUI and script setup (<column name> = HOST_NAME())

After some experimentation, I found that if I executed sp_changemergearticle to set the subset_filter_clause again (after all the normal publication setup scripts had run) �� then the dynamic_filters_function_list value in dbo.sysmergepublications became correct �� and everything started working again.

This looks like a bug to me �� and although the workaround above seems to work �� it would be good to know if this is a known issue

If this is not a known issue, then I will attempt to put together a simpler repro than I currently have available.

aero1

View 6 Replies View Related

Problem With HOST_NAME Function With Linked View

Oct 30, 2005

I have an Access 2000 MDB file with a SQL 7 back end. I have a main tablewith 50,000 records; and I have a selections table with 50,000 records foreach machine that uses the database (about 25-50). This allows each user tohave their own set of selections.The selections table has three fields: ID (int), Sel (bit), MachName(varchar). ID and MachName comprise the primary key.I have a view that combines the main table and the entries for theselections table for the current machine (SQL below). The view works finewhen opened in EM and QA. And if I create a pass-through query from myAccess MDB file, the results are displayed fine.However, if I link the view to the Access MDB file, I get "#Deleted" inevery field of every record (which seems to indicate that the records werethere and then they were gone). However, if I hard-code the machine nameinto the same view instead of using HOST_NAME and then relink the view tothe MDB file, the linked view opens fine. Only when I use HOST_NAME as aparameter in the view is there a problem with it.Anyone have any idea what's going on here, or have heard of any issues withHOST_NAME and ODBC linked objects? SQL for the view is below.Thanks!NeilSELECT INVTRY.*, InvtrySelections.Sel, InvtrySelections.MachNameFROM dbo.INVTRY INNER JOINdbo.InvtrySelections ONdbo.INVTRY.ID = dbo.InvtrySelections.IDWHERE (dbo.InvtrySelections.MachName = HOST_NAME())

View 20 Replies View Related

T-SQL (SS2K8) :: Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Jun 10, 2014

when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)

set @a=99362600999900.0000

The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.

View 2 Replies View Related

Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Mar 21, 2006

Guys

I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000

When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)

Any ideas ??

View 6 Replies View Related

Removing Non-numeric Characters From Alpha-numeric String

Oct 24, 2007

Hi,

I have one column in which i have Alpha-numeric data like

COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.

I want to remove Non-numeric characters from above (space,'(',')',+,........)

i want to write something generic (suppose some function to which i pass the column)

thanks in advance,

Mandip

View 18 Replies View Related

TSQL Function To Return Numeric Value Of Non Numeric Field

Jul 20, 2006

I need to replace Access Val() functions with similiar function in sql.

i.e. Return 123 from the statement: SELECT functionname(123mls)

Return 4.56 from the satement: SELECT functionname(4.56tonnes)

Any one with ideas please

Thanks

George

View 1 Replies View Related

Pattern Matching - Searching For Numeric Or Alpha Or Alpha-Numeric Characters In A String

Aug 18, 2006

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.

Wildcard
Meaning

%

Any string of zero or more characters.

_

Any single character.

[ ]

Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).

Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA

View 1 Replies View Related

Raw Partitions

Jan 9, 2002

Does anyone have any statistics on the performance gains one can get using raw partitions. The database in question is very IO intensive and performs about 1,000,000 inserts/updates per select.

Thanks...

View 2 Replies View Related

SQL Installed On Both Partitions

Oct 12, 1999

Please help!

I have a server that has SQL Server installed on both C and D drives. The SQL Server software is currently running from the C drive and the live databases and backups are stored on the D drive.

I need to have everything on the D drive. Is there an easy way to make the registry point to the D drive without reinstalling SQL Server? The software will needs to run from the D drive because the C drive is running out of disk space. I will also need to delete the whole C:mssql directory.

Thanks.

View 2 Replies View Related

Moving Partitions From One DB To Another

Jun 19, 2008

assuming that you have two databases, the OLTP db and the OLAP db (take not that both have the same structure -- archiving purposes)... using table partitioning, is there a way where we can move 1 partition from the OLTP db to the OLAP db???

i'm actually trying to use this example with both tables in the DB.. I tried to modify to use two databases but sql server is unable to move the partition...

ALTER TABLE [Production].[TransactionHistory]
SWITCH PARTITION 1
TO [Production].[TransactionHistoryArchive] PARTITION 2;

SlayerS_`BoxeR` + [ReD]NaDa

View 9 Replies View Related

Configurations For Partitions.

Aug 23, 2007

Hi experts,

In SQL Server 2005 database we have partitioned a very big table into 30 partitions each holding few million of records.

Im just curious to know whether there are some configuration related to processors or system hardware in order to benefit from partitioning ? (Ex : If we have multiple processors Whether they need be configured to do a parallel processing ? )

Any real time experience (other than referring links) would be really helpful for me.

Thanks in advance,

Hariarul

View 1 Replies View Related

Merging Partitions

May 21, 2008

Hi There,

I have a quick question regarding merging cube partitions.
If I create partitions sliced by date (let's assume we have year level partitions like 2006,2007,2008...)
Later, if I want to merge selected partition to another partition , for example I have history partition and 2006 partition and I want to merge 2006 to history partition then I can simply merge them using ' merge partition' through Management Studio.

My question is that in script, History partition has condition which is where clause and restricted by year level (i.g. WHERE date < '01-01-2006' ) ; however after merging , script won't change like WHERE date <'01-01-2007').

If so, whenever I merge partitions then I have to alter the script as well based on selected merge partion?
I need to refresh history partition once a month;however even if I merge 2006 partition , once I reprocess history partition then it only process what it was wrote in script. So, after reprocessing 2006 data won't appear in this history partition.
So, wondering it's mentatory to alter the script once partition is merged.
Please give me some comments on this issue.
Thanks in advance.

View 4 Replies View Related

Horizontal Partitions - Not Working As Described

Apr 16, 2002

I am testing horizontal partitions to see whether it is a feasible
option for a project. IF I have a composite Primary Key and the
constraint column (a part of the Primary Key) that helps the
partitioned view is defined with DateTime Data Type, select on a restricted set of data through a partitioned view still tries to access all the tables instead of just one table that contains the data. Is this the case or am I
missing something ?

Any help on this is appreciated.

Here is what I am trying to do.

************************************************** **************

CREATE TABLE [dbo].[tst01] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL ,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tst02] (
[Dt] datetime NOT NULL ,
[TID] int NOT NULL,
[Nm] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [PK_tst01] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [PK_tst02] PRIMARY KEY CLUSTERED
(
[Dt], [TID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tst01] ADD
CONSTRAINT [CK_tst01] CHECK (Dt between '11/1/2002' and '11/30/2002')
GO

ALTER TABLE [dbo].[tst02] ADD
CONSTRAINT [CK_tst02] CHECK (Dt between '12/1/2002' and '12/31/2002')
GO

insert into tst01 values('11/1/2002', 1, 'SS')
insert into tst01 values('11/2/2002', 2, 'KK')
insert into tst01 values('11/3/2002', 3, 'DD')
Go

insert into tst02 values('12/1/2002', 1, 'LL')
insert into tst02 values('12/2/2002', 2, 'MM')
insert into tst02 values('12/3/2002', 3, 'GG')
Go

CREATE VIEW vtst AS
SELECT * FROM tst01
UNION ALL
SELECT * FROM tst02
Go

SELECT * FROM vtst WHERE dt = '11/1/2002'

************************************************** *********

When I look at the Execution Plan, it shows that clustered index seek
would be performed on both the tables.

StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Nov 30 2002
12:00AM' AND Convert([@1])>='Nov 1 2002 12:00AM')))
| |--Clustered Index
Seek(OBJECT:([test].[dbo].[tst01].[PK_tst01]),
SEEK:([tst01].[Dt]=Convert([@1])) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Dec 31 2002
12:00AM' AND Convert([@1])>='Dec 1 2002 12:00AM')))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[tst02].[PK_tst02]),
SEEK:([tst02].[Dt]=Convert([@1])) ORDERED FORWARD)

Thanks,
Sathish

View 1 Replies View Related

Update Horizontal Partitions

Feb 24, 2004

I'm considering using horizontal partitions to separate my data by year.
For example, SomeTable_2004, SomeTable_2003, etc. This works well for backups, maintenance, etc. because I'm working with 150+ GB of data. I'll be a partitioned view for queries.

However, I'm new at this and have a few questions. I would also like to do partitioned updates or inserts. But I need to make sure that the tables don't use similar primary keys. Does that make sense? I need to make sure that the primary keys from the first table are not used again in the second table.

SomeTable_2003
primary keys: 1,5,8,9,15

SomeTable_2004
primary keys: 2,3,4,10

I don't really care what keys are used on what table, as long as they are different. I have apps that already use this data, and I don't want to change the application logic.

Thanks,
T

View 1 Replies View Related

SQL Server 2014 :: How To Set Up The Partitions

Oct 1, 2015

how to set up the partitions.I have a transaction table with 50 million records that's very hard to query. it holds data for the last 4 years but the application only ever looks at the last 6 months so i believe this is and ideal candidate for partitioning.

Would it be better to

1) create a partition based on each year for all data so would have a 2015, 2014, 2013, 2012?
2) create 1 partition based on month for this years data then 3 based on year so would have jan,feb,march,april,may..., 2014, 2013, 2012

For 1) would you have to perform some maintenance at the turn of each year for accommodating the next years data. For 2) although this would give better performance as query's are mostly in the last 6 months wouldn't this have more maintenance to move month data to year partitions come the turn of the year and then create the next years months partitions.

View 9 Replies View Related

Physical Disk Partitions

May 21, 2007

I just inherited a dev box, and need to do some performance analyzing on a 40 gig db for a client. Time is of the essence!

My question is that this dev box only has one disk partition (c: drive). Is it a huge deal that I don't have the db system files on one drive, with the data files on another, and tempdb on another,etc.....

View 1 Replies View Related

Table Partitions & RAID 5.

Aug 28, 2007

Hi experts,

We have a huge table with around 250 million records and have implemented SQL server 2005's new table partitioning feature. Now the data seems to be evenly spread across 20 different filegroups ( each 5 GB approx ) for the same table that was occupying 100 GB itself in the PRIMARY filegroup earlier.

Still the query response times have not come down drastically but we could see a good improvement in the execution plans now.

WE ARE USING RAID 5 IN OUR PRODUCTION ENVIRONMENT. ANY IDEA / THOUGHT ON HOW TO PLACE THE PARTITIONED FILEGROUPS AND THE LOG FILES IN THE RAID 5 (BTW , I'm very new to RAID concepts , any detailed instruction would be helpful ).

Any help would be greatly appreciated.

Thanks,

Hariarul

View 8 Replies View Related

Data Moving Between Partitions

Mar 9, 2008

I have a requirement that I need to reload the last seven days worth of data each night to ensure that we pick up late arriving and updated records. To avoid having to do updates we delete the last seven days data and reload.

I was wondering if it is possible to set up the table as a partition, paritioned on a value (OLD, NEW) or similar.

The job would set the last day in the NEW partition to be old, the theory being that this would cause the rows to move to the OLD parition, and then truncate the new partion rather than deleting. The last seven days data could then be inserted into the empty new partition.

My questions is 1. Is my theory about the data moving from one partition to another correct. 2. Can I actually truncate and individual parition, 3. Do you think it will perform any quicker. We would expect data in the range of 100K to 500K rows in the seven days and will store up to 4 years of historical data.

Thanks for your thoughts

Stapsey

View 1 Replies View Related

Better Table Management (partitions?)

Oct 31, 2006

Hi,

For my work I am now learning Sql server 2005 and I have been given a database that has been set up by someone else to work with. It is my job to get the database ready for use in reports.

My problem is that the current database has one huge table with almost 8GB of data. The table contains data from 2004 to present (and growing) from 14 different countries. The reports we use are mostly per country, but we also want to compare the 14 countries to eachother for say, whole 2006. At the moment the table is stored in one single file instead of using partitions.

I believe partitions can give a good performance boost when running the queries. But how do I do this? Currently the country codes are just plain text, can they be used for partitions?

Any advice would be welcome,

Thanks!

View 5 Replies View Related

Summarize Data Over Partitions

Oct 17, 2007

Hi champs!
The data i have is like this:

nr date value

------- -------------- --------
1 2007-10-03 45
1 2007-10-05 5
1 2007-10-11 -1
1 2007-10-30 23
2 2007-03-03 3
2 2007-03-13 -5
2 2007-03-03 6
3 2007-10-03 42
3 2007-10-03 11

.....

I want to summerize the value in each group and set the date to the 1'st og that month
i.e.
nr date value

------- -------------- --------
1 2007-10-01 72
2 2007-03-01 4
3 2007-10-01 53

any help is much appreciated.
thanks

View 3 Replies View Related

Moving Indexes To Seperate Partitions

Jul 8, 1999

I have been asked to move the indexes on our membership database tables to seperate partitions on the server. This is a new concept to me and thought I could use some advice on how to go about doing it.

Thanks in advance.

Brad Keck

View 2 Replies View Related

SOS - Table Partitions Or Indexed Views.

Feb 26, 2008

Hi Experts,

We have a very huge database that stores 12 years of data(120 Million records). But our application mainly accesses past 3 years data i.e , the queries would scan the 120 million records even when it actually has to scan 30 million records alone (for 3 years).

Since few other important applications needs access to all the 12 years data, we are in a position to have 12 years data in the same database.

Right now we are looking for an approach that would help us to efficiently access the 3 years data alone and boost the performance.

1. Will SQL server table paritioning help in this scenario ?

Or

2. Indexed views would help us ? Is it possible to create indexed views based on year range and access the views in the stored procedures ?

Any help would be greatly appreciated.

Thanks in advance,

Hariarul

View 4 Replies View Related

Analysis :: Aggregation Design - Using Partitions

Jul 31, 2015

I have 3 partitions using a year grouping. Current year, previous 4 years, older than 5 years. I have two measure groups, one is a distinct count, so I actually have 6 partitions.I also use usage based optimization to build my aggregations. Should each partition have a separate aggregation or should there be one for each measure group?

View 5 Replies View Related

Transact SQL :: Partitioning Performance With 15 Min Partitions

Oct 27, 2015

We are currently developing an OLTP application, which will need to purge data when it becomes older than 1 hour.Rather than having a process which deletes rows periodically (and risks locking the tables), I am considering using partitioning on a rolling 15 minute window.The idea is to have 5 active partitions, with the 5<sup>th</sup> one being swapped out, merged and a new one split in. This will allow data to live to a max of 1 hour 15 mins,which is acceptable.

Actually, I will have 8 partitions; there will be 4 partitions set in the future, just to ensure when the last partition is split, there isn’t any data movement, as the newest partition will be empty.I am wondering if there will be any performance issues due to partition swapping, merging and splitting every 15 minutes? The application will have a high volume of users when live. I think this should be a better option that continually deleting from the tables.

View 6 Replies View Related

Overlapping Partitions And Join Filters

Oct 2, 2006

I am using Sql 2005 and merge replication with push subscriptions. I have several dynamic join filters on some of my tables.

The join filters all use a central table that maps say a server location name (something that is returned from HOTNAME() in my case) to an for a store branch ID. This is a retail system database.

When I add a new new subscription I update this table with the new server location name and it's corresponding branch ID. My filtered tables all have a foreign key in them that is the branch ID. I can then effectively join from the server location name to a Branch ID.

What I have noticed is that if I update one row in the map table, sql server will re-generate all partitioned rows for all subscribers, even for rows that haven't been updated.

The net result is that when I add a subscription, my existing subscriptions all get about 52,000 row updates.

Am I seeing this because I said my partitions will overlap when I created the table articles?

Thanks for any help

Graham

View 2 Replies View Related

Problem With PreComputed Partitions And Numeric Partition Key (HOST_NAME())