Partitioning Tables And Filegroup Stuff

Jan 17, 2007

Hi everyone,
 
Primary platform is 64-bit on A-P cluster.
 
Our needs are on yearly basis and on monthly basis. We're forced to keep up five years for the majority of the production tables.
In terms of years, I see three ways:
 
1.Create all the ranges for a FILEGROUP with a only NDF
 
2004, 2005, 2006 => FG1 =>  ONE.NDF
 
2.Create all the ranges for a FILEGROUP along with more than one NDF.
 
2004, 2005, 2006 => FG1 =>  ONE.NDF
                                                       TWO.NDF  
 
3.Create each range to a FILEGROUP where there will be  one NDF or  (n) NDF
 
2004 => FG0=> ZERO.NDF
2006 => FG1 => ONE.NDF
2005 => FG2 => TWO.NDF
 
What is the best approach in terms of availability, performance and best practices? Maybe is a silly question, I'm sorry if it is.
 
As usual, thanks a lot for your time.
 

View 3 Replies


ADVERTISEMENT

Partitioning Schema And Filegroup Management

Jan 17, 2008

Hi everyone,
I have successfully managed to create filegroup Function and partitioning
scheme.
My question is how then Next month incoming increaze 2 functions.
Next will be 200812...how I made an add to the logic?

I found an example that explain me strategy...but with a fixed number of
Filegroup, making the switch IN / OUT. My case necessarily the filegroup
file are always on add


Thanks,
Alen Italy


PARTITION CREATE FUNCTION [] (DateCreazioneLogIIS datetime) AS
RANGE LEFT FOR VALUES (
DATEADD (ms, -3,'20070901 ')
DATEADD (ms, -3,'20071001 ')
DATEADD (ms, -3,'20071101 ')


)


CREATE PARTITION SCHEME [RaggruppamentiDateLog]
AS PARTITION [DateCreazioneLogIIS] TO (
[200709]
[200710]
[200711]
[PRIMARY]
)

View 2 Replies View Related

Partitioning Tables

May 4, 2007

How to partition tables in dynamic way?

I want to partion a table based on the client specific id and I want these values (client id's) to be passed dynamically to the create partition function.
I am not familiar with partitioning so it will be great if someone guides me (I am also reading some articles on partitioning, but it will be easier with some help)

The table I am trying to partition has like 80 million rows with four client's data as of now and will be more once we implement new clients.

I also think Partition will help, because before we load a client's data, we remove the data that is already out there (we flush previous qtr data before we insert this qtr data)

Any help will be appreciated.

Thanks
Raj

View 4 Replies View Related

Partitioning Tables Vs San Hardware

Feb 26, 2008

Guys,

I am trying to use table partition feature from Sql Server 2005 enterprise edition.

I have Names table with columns FNAME, LNAME and DISPLAYNAME (concatenation of FNAME and LNAME) which I partitioned across 2 drives and 4 file groups based on the below criteria.

CREATE PARTITION FUNCTION pfNameRange(varchar(200))
AS RANGE RIGHT FOR VALUES ('F', 'I', 'S');

Currently there are 5 mill rows in this partitioned tables - partitioned table has clustered index on ID (identity property) and LNAME.

I also created another table with the same data without partition on the table.

When I run the following query I get the same response time of 10secs from both tables.

Names - partitoned table with clustered index on ID and Lname
NameSEARCH - with no partition and no index

select * from names where lname = 'smith'
select * from namesearch where lname = 'smith'

Is it safe to assume that if the data files are on San it doesnt give any advantage of table paritioning?

How can paritioning be made effective with data files on San

Any suggestions and inputs would help.

Thanks

View 4 Replies View Related

Historical Tables, Partitioning Or What?

Apr 4, 2007

I have about 45000 records in a CSV file, which I am using as HTTP request parameters to query a website and store some results in a database. This is the kind of application which runs 24/7, so database grows really quickly. Every insert fires up a trigger, which has to look for some old records based on some criteria and modify the last inserted record. My client is crazy about performance on this one and suggested to move the old records into another table, which has exactly the same structure, but would serve as a historical table only (used to generate reports, statistics, etc.), whilst the original table would store only the latest rows (so no more than 45k at a given time, whereas the historical table may grow to millions of records). Is this a good idea? Having the performance in mind and the fact that there's that trigger - it has to run as quickly as possible - I might second that idea. Is it good or bad? What do you think?



I read a similar post here, which mentioned SQL Server 2005 partitioning, I might as well try this, although I never used it before.

View 5 Replies View Related

SQL 2005 - Partitioning Tables

Oct 29, 2005

I want to know how to partition a table using two columns (Example: Salesman, OrderDate).

View 8 Replies View Related

Tables Partitioning -- Performance Boost

Nov 13, 2007

Hi

I have a question about the partitioning a table.

I have a database with more 50 tables and 25 tables are having more than 10 lakhs records which includes history records.I have two data files for this database under PRIMARY FILE GROUP.Now i want to transfer these history records to some other database.
I wanted to know if this kind of activity will boost the database performance?.If yes how should i configure my new database.
On what factors of partitioning my performance will boost.

Thanks in advance

Regards
Arvind

View 1 Replies View Related

SQL Server Admin 2014 :: Partitioning Master And 4 Child Tables

Jul 5, 2014

I have 6 tables which are very huge in row count and need to be partitioned for better manageability.

Little info: Every day, 300 Million records are inserted and 300 million records are deleted in below 7 tables. we maintain only 8 days worth of data in below tables which is the reason records which are older than 8 days are continuously deleted.

Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106

Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table.
dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235

I would like to partition the above child tables based on the IDs that are inserted every 4 hours. Meaning, All IDs that are inserted in 4 hours window should be in a partition.

View 1 Replies View Related

SQL Server 2008 :: Retrofitting Partitioning To Existing (large) Tables

Feb 9, 2015

We have an existing BI/DW process that adds large chunks of data daily (~10M rows) to an existing table, as well as using Deletes to remove stale data. This scenario seems to beg for partitioning to support switching in/out data.

After lots of reading on this, I have figured out the mechanics of the switching, bit I still have some unknowns about the indexes needed to support this.

The table currently has several non-clustered indexes, including one on the partitioning column - let's call that column snapshotdate. Fortunately there are no FKs involved, and no constraints.

Most of the partitioning material I see focuses on creating a clustered PK to assist with switching. Not sure if this is actually necessary, but assume I create one using an Identity column (currently missing) plus snapshotdate.

For the other non-clustered, non-unique indexes, can I just add the snapshotdate to the end of the index? i.e. will that satisfy the switching requirement?

View 1 Replies View Related

Listing Tables From A Filegroup Using Sql Statement

Jul 30, 2001

Hi everybody,
Is there anyway we can find the list of tables in each filegroups by writing a sql query..

Thanks
muthu

View 1 Replies View Related

How To Move Internal Tables To Another Filegroup

May 25, 2007

I mange several large databases with multiple files and filegroups. During some maintenance I was trying to remove a filegroup and its files and noticed that I was unable to. It appears that some service broker objects were created on my user defined filegroup.

Looking at the system tables I see several system tables on my user defined filegroup

SELECT o.name +'.' +i.name FROM sysobjects o inner join sysindexes i on i.id = o.id where groupid = 2

queue_messages_1255675521.queue_clustered_index
queue_messages_1255675521.queue_secondary_index
queue_messages_1287675635.queue_clustered_index
queue_messages_1287675635.queue_secondary_index
queue_messages_1319675749.queue_clustered_index
queue_messages_1319675749.queue_secondary_index

my normal tricks do not work as I am unable to even select from these tables.

How to I move them or get rid of them. I am not even sure what is created them as we are not using service broker in our application.

View 3 Replies View Related

Do Tables Using A Different Filegroup Log Trans To The Same Transaction Log Used By Primary?

Sep 26, 2007

we like some of the advantages filegroups offer but wonder if the purpose is somewhat defeated if it is true that tables sitting on other filegroups still have to work with the same transaction log that the db's primary tables work with.

View 4 Replies View Related

AWE, /3gb And /pae Stuff

Jul 4, 2007

Hi all,



I'm running under Windows Server 2000 datacenter sp4 with sql server 2000 Sp4

on 16GB Ram.



I enabled the /PAE in the boot.ini file and also enabled the AWE and set the SLQ Server maximum memory to 6144.



Do I need to include the /3GB switch in the boot.ini file?



I'm a little bit confused with the article found on the site. http://support.microsoft.com/default.aspx/kb/274750

Here are the lines.

Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 3 GB of memory.
or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to use up to 3 GB of available memory.


Regards,

Larry

View 5 Replies View Related

Easy Stuff

Jul 1, 2004

hi.. guys this is simple for you guys how can i make ms access to display the current date in a col.... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??????

View 3 Replies View Related

Locale Stuff

Jan 25, 2005

Hi,

You might just be able to save me downloading and installing SQL Server French...

I'm having localization problems with some .NET code - and I might be able to solve the problem if I know how SQL is parsed in different locales. Say my table MyTable contains a float column FloatColumn. In English, I would say
SELECT * FROM MyTable WHERE FloatColumn = 2.3

If my locale was, say for argument's sake, French, would I have to write
SELECT * FROM MyTable WHERE FloatColumn = 2,3
???

Thanks for your help,
T

View 1 Replies View Related

Datetime And Other Stuff

Dec 12, 2005

Overview. Machine data is fed into DOWNTIMELOG via a Cimplicity. I do not have the ability to change the way that it goes in. It contains the time that a machine has stopped and where the stoppage has occurred. Via a web page I want to give the operator the amount of time the machine stopped. From that they will provide some more detail. As they enter time, I need to subtract what they entered from the total (DOWNTIMELOG). That is the reason I created ENTERED_TIME. [PDNTOTAL_TEMP_VAL0] hold the total time for each stoppage, [TOTTIME] is intended to hold the value that has been accounted for. [DWNCATEGORY_TEMP_VAL0] holds the category and matches up with [DWNCATEGORY]. Also, I need to match Date and Shift. The problem is that DOWNTIMELOG does not capture shift. However, 1st shift occurs between 700 and 1500, 2nd shift occurs between 1500 and 2300, 3rd shift occurs between 2300 and 700. 3rd shift presents a problem as it occurs across 2 dates. Could someone please help me with a query that provides the net between the two tables that is matched by date, shift and category?
CREATE TABLE [dbo].[DOWNTIMELOG] (
[timestamp] [datetime] NOT NULL ,
[DWNTIMESTAMP_VAL0] [varchar] (25) NULL ,
[UPTIMESTAMP_VAL0] [varchar] (25) NULL ,
[PDNHOUR_VAL0] [int] NULL ,
[PDNMIN_VAL0] [int] NULL ,
[PDNSEC_VAL0] [int] NULL ,
[PDNTOTAL_TEMP_VAL0] [int] NULL ,
[DWNMSG_TEMP_VAL0] [int] NULL ,
[DWNCATEGORY_TEMP_VAL0] [varchar] (50) NULL

CREATE TABLE [dbo].[ENTERED_TIME] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DWNCATEGORY] [varchar] (50) NULL ,
[DWNMSG] [int] NULL ,
[ENTRYDATE] [smalldatetime] NULL ,
[SHIFT] [int] NULL ,
[TOTTIME] [int] NULL

View 4 Replies View Related

Sql Automated Task Stuff

Jan 14, 2008

Where can i learn about making the sql server execute task when a certain situation happens. Like lets say make the server delete all entrys older than 40 days? Or have a entry deleted automatically when a certain situation happens like lets say it take 4 votes and a entry get deleted how do i make it do that automatically

View 2 Replies View Related

T-SQL (SS2K8) :: Matching Value After Using STUFF

Aug 22, 2014

This is my code, table and result,

declare @t_JobNoticeID table (cvid int, JobNoticeID int)
insert into @t_JobNoticeID values(2456, 24);
insert into @t_JobNoticeID values(4000, 124);
insert into @t_JobNoticeID values(245, 9);
insert into @t_JobNoticeID values(2456, 19);
insert into @t_JobNoticeID values(4000, 904);
insert into @t_JobNoticeID values(4000, 11);
insert into @t_JobNoticeID values(24, 19);

[Code] ....

My question is -

How update statement looks like compare RESULT A1 with x_JobMatching?

So, my FINAL RESULT shown as follow,

CVIDJobNoticeIDisMatch
1925450
6590690
459130
2456191
4569110
40009041
24560
900240
24191

View 5 Replies View Related

CHARINDEX And STUFF/REPLACE

Jul 23, 2005

Hello,I need to be able to replace only the first occurance of a space characterin a column.Reason being is the data in the column I am trying to replace seems to haveumpteen space characters after each entry, so a simple replace functionwill replace all the spaces after it with what I want!I have thought of RTRIM to get rid of the spaces after and then replace, Ihave also thought of CHARINDEX to find the first occurance of a space andSTUFF to replace it.I have done my homework on these functions!But I am having trouble writing such a statement,I've never written a query which would use more then one function on onecolumn you see and I am getting confused!I'll tell you what I want to do in simple stepsReplace only the first found space in a name column, but then if a name hasa middle initial that will be a problem,Replace that with a dot.then concatanate '@emailaddress;@emailaddress2' after itso when SQLServer does the select it will bring back something likejoe.bloggs@emailaddress;emailaddressBut I guess I'd also need joe.n.bloggs@emailaddress;emailaddressThe data in the column looks like this at the momentjoe bloggsBut I guess there may come a time when we havejoe n bloggs, just to complicate things!What is your advice, and how do I write a query like thisI have been playing around with it in Query Analyser but as I said I amgetting confused and need some help if you don't mindThanks a lot to all who reply :-)RegardsJayne

View 2 Replies View Related

Why Does The Automatic Scripts That Are Generated Have So Much Stuff?

Dec 11, 2007

For example if I make a column not null the SQL Script generated.
Removes all indexes, keys, etc... than creates a temp tableThan moves the data into the temp tablethen deletes the old tablerenames the temp tableRecreate all the indexes, keys, etc...
Is there a structure reason for it? Like now that the DB is being alerted that the column is not null it wants to re insert all the data so it can re-organize it?
Because I can just write my own script to the effect of
ALTER [TABLE] [COLUMN] PARAM TYPE NOT NULLone line of script that seemingly does the exact same thing..
So I'm just curious when I change a column to non null why doesn't it just generate a simple one liner?

View 3 Replies View Related

Newbie - SQL Server Express And Other Stuff

Feb 16, 2008

Hi

View 1 Replies View Related

Use Stuff To Define Range Of Values?

Nov 13, 2014

I have this query that calculates the next possible shipping day, based on 3 conditions:

- It has to be a workingday (WORKTIMECONTROL: 1 workingday, 0 holiday) - marked green
- There might be extra days (@xdays) required by the process - marked blue
- Customer wants their goods to be shipped on special days - marked red:select TOP 1 Transdate
from WORKCALENDARDATE
where Transdate > @startday and WORKTIMECONTROL = 1 and DATEPART(WEEKDAY,TRANSDATE)-1 in (2,4) and

[code]...

So customer 123456 accepts shipping of goods only on tuesday and thursday as in the above example "... in (2,4)". Multiple shipping days means that the Subquery returns more than one record, which gives me a headache as I don't see how to integrate this portion into my query. I tried to use the stuff function as I formally need a result that can be provided that way; but the format is incorrect as it in varchar, while an array of integer is needed.

DATEPART(WEEKDAY,TRANSDATE)-1 in (select stuff((select ',' + CAST(DELIVERYDAY as nvarchar) from CollectiveShipment where custaccount = '123456'
for xml path('')),1,1,''))

View 2 Replies View Related

Separate Data Stuff From Applications

Jan 22, 2007



Hi,

This is probably for most of you a very basic question. My goal is to define in a seperate instance from application all infos related to DataSets and DataAdapters that are needed when connecting to a specific database. Why?

First, I would like to be able to have in a single instance everything needed when hooking to a SQL Server Express database and in an other everything needed when hooking to a SQL Server CE database. Then, from application, I would like to be able following a few basic configuration steps to choose from one or the other, or even both in specific cases.

Second, I want to be able to seperate Database related stuff from application so that I can re-use everything from any other application when needed.

I've read several books, articles on the subject and would like to have your opinion as several approaches were illustrated through them. What would you use for this:

- Component class?

- Class library?

- or else?

Any good articles on the subject?



Thanks in advance for sharing,

Stéphane

View 3 Replies View Related

Transact SQL :: Query Using Stuff Keyword

Jun 17, 2015

CREATE TABLE BILL_DETAIL
([objid] int,[x_billable_to] varchar(19), [x_bill_quantity] int,
     [x_billable_yn] int, [x_bill_rate] int,   [COST_TYPE] varchar(19) )
INSERT INTO BILL_DETAIL
([objid], [x_billable_to], [x_bill_quantity], [x_billable_yn], [x_bill_rate],[COST_TYPE])

[code]...

how to get records using stuff keyword as above i want to query using where condition with where objid=1 and should frame output as Parking, Toll only 1 input parameter need to given.

View 20 Replies View Related

SQL CE 3.1 Connection String And Some Other Basic Stuff

Oct 9, 2007

Since I couldnt find an easy way to connect Orcas Beta 2 with SQL CE 3.1

I'll try by a connection string

since im totally new at this
does anyone have any idea how to get started?

Thxs!

View 4 Replies View Related

Merge Replication - Weird Stuff

May 21, 2007

I'm implementing merge replication between SQL CE and sql server 2005 SP2. Here is the problem I'm facing



I have dynamic filters on some tables like

Select * from table1 inner join table2

ON table1.field1 = table2.field3

and table2.flag = true



When table2 flag turns true then all the matching records in table1 should be returned to the subscriber.



But that is not happening. The filter does not return any records to the subscriber even though i see records when i run the query on the server. My take on this is the publisher thinks there is no changes on table1 so there is nothing to update. So to fix that i run dummy updates like

update table1

set [name] = [name]

where table1.field1 in

(Select field2 from table2 where flag=true)



This updates the records and the publisher updates the subscriber the first time. When i set the flag to false, it is supposed to remove the data from the subscriber but i get this error.




The merge process failed because it detected a mismatch between the replication metadata of the two replicas, such that some changes could be lost leading to non-convergence. This could be due to the subscriber not having synchronized within the retention period, or because of one of the replicas being restored to a backup older than retention period, or because of the publisher performing more aggressive cleanup on articles of type download-only and articles with partition_options = 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199363)
Get help: http://help/MSSQL_REPL-2147199363

The common generation watermark is invalid at this replica since it does not exist or metadata for changes not yet propagated may have been cleaned up. (Source: MSSQLServer, Error number: 21800)
Get help: http://help/21800





I have no idea whats going on. Any clues, anyone? Thanks for your help.





Ramesh

View 13 Replies View Related

Renaming A Project... Some Minor Stuff

Apr 16, 2008

I have a project. Originally when I created this project, I called it "Project_A", (and I did not create a solution directory).

Doing this created a folder called "Project_A" with the following files within it. I guess these are meta data files and I shouldnt be messing around with them. Anyway, I see

(1) Package.dtsx (The default package that gets created with a new project)
(2) Project_A.database
(3) Project_A.dtproj
(4) Project_A.dtproj.user
(5) Project_A.sln
(6) Project_A.suo

When I rename my project in the Solution Explorer to "Project_B", the following happens in my folder.

Everything remains the same except Project_A.dtproj gets renamed to Project_B.dtproj and a new file called Project_B.dtproj.user gets created.

Now, my questions.

Question 1: What if I wanted my folder to have the same name as my project? Can I just change the folder name in Windows Explorer without having any ill-behaviors later on? I always have 1 project to 1 solution so I want to use the convention of keeping my folder named the same as my project.

Question 2: What about the files that are still named with Project_A dot blah blah. Is it bad if I renamed these to Project_B dot blah blah?



View 7 Replies View Related

VS Express Free For Good And Other Fun Stuff

Apr 19, 2006

A whole bunch of exciting new stuff announced around the Visual Studio Express Editions today. VS Express is now free for good, not just 1 year (just like SQL Server!). Also lots of great new content and fun projects to do.

See Dan Fernandez's blog posting at http://blogs.msdn.com/danielfe/archive/2006/04/19/579109.aspx for lots of details.

Best, MJ

View 1 Replies View Related

Simple SQL Server Stuff For An Oracle Person...

Jul 27, 2006

I'm trying to do some basic stuff in SQL Server 2005 that I could do in Oracle.  I'm sure there's a way to do these things, I just don't know how.  My most immediate question is this:
Is there any easy way to run a SQL script and have it spool to HTML tables to a file?  The 'results to text' from SSMS is not adequate for display purposes.  I'm trying to display table/column descriptions.
Beyond that, does anyone know of any good resources that provide an 'Oracle to SQL Server' matrix so I can help figure out how to do some of these things in SQL Server?
Thanks in advance,
Mike

View 1 Replies View Related

Very NEW To This Database Server Stuff... Probably Simple For You, But Hard For Me...

Jan 4, 2008

Hi everyone,

BACKGROUND:
I just set up a network with 2 computers (direct cable network, no routers/hubs), one computer is running Windows 2000 Server and the other is running Windows XP Professional. They both recognize each other and I can access each computer on either computer. The WinXP computer has SQL Server 2005 express edition installed on it.

BASIC PROBLEM:
I am trying to install a database server on my Windows 2000 Server OS computer. This computer is a 350MHz, 256mb RAM computer. How would I go about doing this?

Thanks.

View 11 Replies View Related

SQL Server 2012 :: STUFF Function With HTML Tag

Jan 6, 2015

I have the following query that supposes to merge multiple result in a single one and put it into a temporary table:

SELECT DISTINCT [AlphaExtension],
STUFF((SELECT A.[NoteText] + '< BR />' FROM #temp A
WHERE A.[AlphaExtension]=B.[AlphaExtension]
FOR XML PATH('')),1,1,'') As [NoteText]
FROM #temp B
GROUP BY [AlphaExtension], [NoteText]

It is working fine unless by a simple detail. If you look at the second line of the query you will see that I am stuffing together a < BR /> tag (break line) because the contents of the field is going to be spitted directly to the screen and I want that the multiple results be displayed in different lines.

OK, the issue is that it is stuffing & lt ; BR / & gt ; instead < BR /> and therefore the browser is displaying the tag instead to break a line.

View 2 Replies View Related

T-SQL (SS2K8) :: Arithmetic Formula To Stuff Digits

Apr 6, 2015

I have a column which needs to be 9 digits long. The first four digits are independent from the other digits. The following digits need to be 5 and I have to add leading zeros if they're less than 5. This is an awful design and I'd love to change it but I can't.

SELECT Cg.Fiid,
--Original Formula
SUBSTRING( CONVERT( varchar, Cg.Fiid ),1, 4 ) + RIGHT( '00000' + SUBSTRING( CONVERT(varchar, Cg.Fiid ), 5, 10 ), 5 ),
--My solution
STUFF(Cg.Fiid, 5, 0, REPLICATE('0', 9 - LEN(Cg.Fiid)))
FROM (VALUES(71927),
(498932),
(498934),
(38061278))Cg(Fiid)

View 9 Replies View Related

Stuff Function Not Working With Left Or Right Join

Jan 1, 2015

I found something very strange...stufff function working with self join but not working with left or right join,. I have a table

**Id name**

1 samar

1 Harry

2 jack

I want the output as

**Id name**

1 samar Harry

2 jack

The below query works fine with self join

Select b.id, stuff ((select ` ` + a.name from #test a where a.id = b.id
for xml path (``)),1,1,``)

From #test b
Group by id

But when i do right join i get error _ invalid object name `b`. ....

Select b.id, stuff ((select ` ` + a.name from #test a
right join b on b.id = a.id for xml path (``),1,1,``)

From #test b
Group by id

View 4 Replies View Related







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