SQL 2012 :: Partitioning Large Table On Nullable Date

May 15, 2014

I have a very large table that I need to partition. Ideally the table will write to three filegroups. I have defined the Partition function and scheme as follows.

CREATE PARTITION FUNCTION vm_Visits_PM(datetime)
AS RANGE RIGHT FOR VALUES ('2012-07-01', '2013-06-01')
CREATE PARTITION SCHEME vm_Visits_PS
AS PARTITION vm_Visits_PM TO (vm_Visits_Data_Archive2, vm_Visits_Data_Archive, vm_Visits_Data)

This should create three partitions of the vm_Visits table. I am having a few issues, the first has to do with adding a new clustered index Primary Key to the existing table. The main issue here is that the closed column is nullable (It is a datetime by the way). So running the following makes SQL Server upset:

ALTER TABLE dbo.vm_Visits
ADD CONSTRAINT [PK_vm_Visits] PRIMARY KEY CLUSTERED
(
VisitID ASC,
Closed
)
ON [vm_Visits_PS](Closed)

I need to define a primary key on the VisitId column, but I need to include the Closed column in order to partition on it.how I would move data between partitions on a monthly basis. Would I simply update the Partition function, or have to to some sort of merge, split, or switch function?

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Table Partitioning Based On Date Column

Aug 25, 2014

We have a database and have 6-7 growing tables. All the tables have Primary and foreign key relation. I want to do partition based on the date column.

I need 3 partitions

First partition has to hold present data
second partition need to hold the previous year data (SAS storage)
Third partition need to hold all the old data and need to be in the archive database

I understand that first we need to disable the constraints (Indexes PK & FK)
Then create partition function and partition schema
Then Create the Constraints again

View 9 Replies View Related

SQL 2012 :: Partitioning A Large Dimension?

May 9, 2014

The SQL CAT team's recommendation is to avoid partitioning dimension tables: URL.....I have inherited a dimension table that has almost 3 billion rows and is 1TB and been asked to look at partitioning and putting maintenance in place, etc.I'm not a DW expert so was wondering what are the reasons to not partition dimensions?

View 7 Replies View Related

Partitioning A Large Table - How Much Is Too Much?

Nov 14, 2007

Hi folks! I'm looking for advice on partitioning a large table. In the DDL below I've changed names to protect the guilty.

My table has this schema:


CREATE TABLE [dbo].[BigTable]
(
[TimeKey] [int] NOT NULL,
[SegmentID] [int] NOT NULL,
[MyVal] [tinyint] NOT NULL
) ON [BigTablePS1] (TimeKey) -- see below for partition scheme

alter table [dbo].[BigTable] add constraint [PK_BigTable]
primary key (timekey asc, SegmentID asc)

-- will evaluate whether this one is needed, my thinking is yes
-- based on the expected select queries.
create index NCI_SegmentID on BigTable(SegmentID asc)


The TimeKey column is sort of like a unix time. It's the number of minutes since 2001/01/01, but always floored to a 5 minute boundary. so only multiples of 5 are allowed.

Now, this table will be rather big. There are about 20k possible SegmentIDs. For every TimeKey from 2008/01/01 to 2009/01/01 (12 months), I'll have on the order of 20000 rows, one for each SegmentID.

For the 12 month period, there are 365*24*60/5=105120 possible TimeKey values. So the total rowcount is over 2 billion. (20k * 105120)

Select queries are expected to be something like this:


-- fetch just one particular row...
select MyVal from BigTable
where TimeKey=5555 and SegmentID=234234

--fetch for a certain set of SegmentID and a particular time...
select
b.SegmentID
,b.MyVal
from BigTable b
join OtherTable t on t.SegmentID=b.SegmentID
where b.TimeKey=5555
and t.SomeColumn='SomeValue'


Besides selects, also I need to be able to efficiently issue update statements against the table with new values in the MyVal column based on a range of TimeKey values (a contiguous span of a few days) and sets of about 1000 SegmentID. updates would always look like this:


update t
set t.MyVal=p.MyVal
from BigTable t
join #myTempTable p on t.TimeKey=p.TimeKey
and t.SegmentId=p.SegmentId


where #myTempTable would have order of 1000*24*60 rows in it, all with contiguous TimeKey values, and about 1000 different SegmentID values. #myTempTable also has a clustered pk on (timekey asc, SegmentId asc).

After the table is loaded, it would never get any inserts or deletes. only selects and updates.

Given the size, and the nature of the select and update queries, this table seems like a good candidate for partitioning. I'm thinking it makes sense to partition on TimeKey.

So my question is, is it stupid to create a separate partition for each day in the year long span of TimeKeys this table covers? That would mean 365 partitions in the partition function and partition scheme. Something like this:


CREATE PARTITION FUNCTION [BigTableRangePF1] (int)
AS RANGE LEFT FOR VALUES
(
3680640 + 0*1440, -- 3680640 is the number of minutes between 2001/01/01 and 2008/01/01
3680640 + 1*1440,
3680640 + 2*1440,
3680640 + 3*1440,
...snip...
3680640 + 363*1440,
3680640 + 364*1440,
3680640 + 365*1440
);
GO

CREATE PARTITION SCHEME [BigTablePS1]
AS PARTITION [BigTableRangePF1]
TO
(
[PRIMARY],[PRIMARY],[PRIMARY],
...snip...
[PRIMARY],[PRIMARY],[PRIMARY]
);
GO


does anyone have any experience with partitioned tables with so many partitions? Is a few hundred partitions too many? From my understanding of partitions, seems like having so many will be ok. Is it somehow worse than having hundreds of tables in a database?

Even with one partition for each day, I'll still have 24*60*20000/5 ~ 5m rows in each one.

5m seems like a manageable number. 2b does not.



elsasoft.org

View 2 Replies View Related

SQL 2012 :: Dynamic Partitioning Of Table Server

Dec 3, 2014

I need Dynamic Partition of SQL Table.

1. What is the best practice for partitioning (on date column)

2. The project on which i am working correctly have a case where in i get the update of my status flag after few days (Say 15 - 30) in that case if my data got into partition table how to update and how to search which partition has my data

3. Is creating partition occupies more disk space?

4. Is every partition would need index?

View 7 Replies View Related

SQL Server 2012 :: Table Partitioning For 500gb In Size

Feb 25, 2014

Script to do the table partitioning for a 500gb in sliding window technique?

View 9 Replies View Related

SQL 2012 :: Table Partitioning - Switch Out / Merge Range

Mar 6, 2014

If the partitioning MERGE command attempts to drop historic data at the wrong boundary point then data movement between file groups may be necessary before or during the next index rebuild. The script below creates 2 test tables, one using a range right function and the other using range left. The partitioning key is a number between 0 - 59, an empty partition is maintained at the start and end of ranges, 4 partitions contain data in the ranges between 0-14, 15-29, 30-44, 45-59. Data in the lowest range (0 - 14) is switched out and a merge command is run, edit the script to try the different merge boundaries, edit the variables at the start to suit runtime environment 'Data Drive' & 'Log Drive' paths.Variables are redeclared but commented out at the start of code blocks to allow stepping through if desired.

--=================================================================================
-- PartitionLabSetup_20140330.sql - TAKES ABOUT 1 MINUTE TO EXECUTE
-- Creates a test database (workspace)
-- Adds file groups and files
-- Creates partition functions and schema's
-- Creates and populates 2 partitioned tables (PartitionedRight & PartitionedLeft)

[Code] ....

The T-SQL code below illustrates one of the problems caused by MERGE at the wrong boundary point. File Group 3 of the Range Right table is empty according to the data space views, it cannot be dropped though. File Group 2 contains data according to the views but you are allowed to drop it's file.

USE workspace;
DROP TABLE dbo.PartitionedRightOut;

USE master;
ALTER DATABASE workspace
REMOVE FILE PartitionedRight_f3 ;
--Msg 5042, Level 16, State 1, Line 2
--The file 'PartitionedRight_f3 ' cannot be removed because it is not empty.

ALTER DATABASE workspace
REMOVE FILE PartitionedRight_f2 ;

-- Works surprisingly although contains data according to system views.

If the wrong boundary point is used then the system 'Data Space' views show where the data should be (FG2), not where it actually still is (FG3). You can't tell if data movement between file groups is pending and the file group files are not protected from deletion by the OS.

I'm not sure this is worth raising a connect item for but it would be useful knowing where data physically resided after a MERGE RANGE and before an INDEX REBUILD, the data space views reflect the logical rather than the physical location if a data movement is pending.

View 0 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

Nullable Date Fields ?

Dec 7, 2007

Is it bad design to allow nulls on a date field ? I can think of one case such as a sale of an item and populating a field for the date of purchase, only when the purchase took place (and null until then).

comments ?

View 5 Replies View Related

SQL Server 2012 :: Purge Process On A Large Table

Jan 9, 2014

I am attempting to do a rather simple purge task on a very large table. This task will need to take place daily and delete records older than 6 months out of the database. On first pass this will delete well over 130 million rows. I thought the best way to handle this is create a proc and call the proc from a SQL Agent Job that runs nightly. Here is an example of the script:

CREATE PROCEDURE usp_Purge_WCFLogger
AS
SET NOCOUNT ON
EXEC sp_rename 'dbo.logs', 'logs_work'
GO
SELECT * INTO dbo.Logs_Backup FROM dbo.Logs_Work WHERE TIMESTAMP < DATEADD(month, -6, GETDATE())

[Code] .....

View 3 Replies View Related

SQL 2012 :: Create Clustered Index On A Very Large Table (500 GB)

May 7, 2014

I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.

Here is my script:

CREATE CLUSTERED INDEX CI_IndexName
ON TableName(Column1,Column2)
WITH (MAXDOP= 4, ONLINE=ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION=PAGE)
ON sh_WeekDT(Day_DT)
GO

View 9 Replies View Related

SQL Server 2012 :: How To Batch Delete Large Table

Jun 16, 2015

I have a table with about 466 Million rows. In this table there is a int column called WeeksToRetain as well as a EventDate column containing the date the row was inserted. I am trying to delete all the rows that that should be deleted according to the WeeksToRetain. For example, if the EventDate is 5/07/15 with a 1 in the WeeksToRetain column the row should be removed by 5/14/15. I am not sure what days SQL considers the beginning and end of the week. However the core issue I am having is the sheer mass of deletions I must do and log growth.

So I am trying to do the delete in batches. More specifically I want to load a temporary table with a million rows, then use the temporary table to load a sub temporary table with 100,000 rows and join this temporary table to the table I want to delete from looping through 10 times to get 1 million. The Logging.EvenLog table which is the table I'm trying to purge has a clustered index on EventDate (ASC). I would like to run this in a schedule job with enough time between executions for log backups to run.

DECLARE @i int
DECLARE @RowCount int
DECLARE @NextBatchDate datetime
CREATE TABLE #BatchProcess
(
EventDate datetime,
ApplicationID int,

[Code] .....

View 9 Replies View Related

SQL 2012 :: Generating CREATE TABLE Scripts For Large Number Of Tables

Feb 11, 2014

Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?

Background: I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.

I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.

[URL] ....

View 7 Replies View Related

SQL Server 2012 :: How To Quickly Update / Insert 3M Records In Large Table

Mar 28, 2015

Our system runs a SQL Server 2012 DB, it has a table (table_a) which has over 10M records. Our system have to receive data file from previous system daily which contains approximate 3M updated or new records for table_a. My job is to update table_a with the new data.

The initial solution is:

1 Create a table (table_b) which structur is as the same as table_a

2 Use BCP to import updated records into table_b

3 Remove outdated data in table_a:
delete from table_a inner join table_b on table_a.key_fileds = table_b.key_fields

4 Append updated or new data into table_a:
insert into table_a select * from table_b

As the test result, this solution is very inefficient. Step 3 costs several hours, e.g. How can I improve it?

View 9 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Horizontal Partitioning By Date Range

Jan 17, 2001

HI, I'm testing out partitioning for an upcoming project and I'm a little confused over the query plan im generating.

I have a 13 million row table I want to break in to date ranges:
tables are calljrnl,calljrnl_1999 and calljrnl_2000. The calljrnl table contains data for the last six months. one view, calljrnl_view is a union all of these three tables.

alter table calljrnl add constraint dt_constraint check (cj_created_dt >= '2000-10-01 00:00:00')

alter table calljrnl_1999 add constraint dt_constraint_1999 check (cj_created_dt <='1999-12-31 23:59:59')

alter table calljrnl_2000 add constraint dt_constraint_2000 check (cj_created_dt >='2000-01-01 00:00:000')

alter table calljrnl_2000 add constraint dt_constraint_2000_2 check (cj_created_dt <='2000-09-30 23:59:59')


The problem I'm having is that when I query on any date range outside the constraints set on the calljrnl_2000 table, It still wants to scan the calljrnl_2000 table.
I'm assuming this has something to do with how the constraints are structured, but I am not able to find the right combination.

ALL HELP IS GREATLY APPRECIATED!!!!

View 4 Replies View Related

SQL 2012 :: Composite Key Partitioning In Tabular Model

Jun 17, 2014

I have a question about partitions in both SQL Server table and Tabular Model. I started to use Tabular Model recently.

I need to partition a table that collects daily rows for different clients.

The natural partition key is a combination of clientID+dateID (something like CL-YYYYMMDD)

I created a configuration table with a primary key PartitionID IDENTITY(1,1) , that contains also the field clientID and dateID

Every day I add a new row in it and I get a partitionID for the new client and date

Then I created a partitioned fact table using PartitionID as the partition field, using the partition function and the partition schema as well.

The daily client data is inserted in the partitioned table using the partitionID

Everything works fine, and the data are loaded correctly into the partitioned fact table.

Then I created a Tabular Model where the fact table is the partitioned table, and I created tabular model partitions using something like "select <field list> from PartitionedTable where partitionID = <partitionID>"

In this way, every day I load partitioned data in both sql server and tabular model. I have two dimensions, client and calendar

Now my question is: when I browse the Tabular Model, and I'm selecting a specific dimension date and dimension client, am I using the partitionID index correctly?

Or should I put in the tabular model partition query something like "select <field list> from PartitionedTable where clientID = <clientID> and dateID = <dateID>"? In this case is still working the partitionID index? How can I check it?

View 0 Replies View Related

Copy Table Leaving Nullable Columns

Nov 14, 2000

I would like to dynamically copy a table in Transact SQL, like this:

SELECT * into NEW_TABLE from MY_TABLE where 1 = 2

This creates an empty NEW_TABLE as desired. However, NEW_TABLE retains the column nullability of MY_TABLE; I would like NEW_TABLE to have all its columns nullable.

I tried to write Transact SQL logic to update the isnullable column in syscolumns for NEW_TABLE, but was told that the entire SQL Server would have to be reconfigured to allow this.

Does anyone know of another way to do this?

View 1 Replies View Related

ADOX - Access Table Creation With Nullable Columns.

Oct 29, 2007

I need to programatically create a mdb file which will contain nullable columns. I am using C++ with ADOX for the table creation and ADO to perform the table update.

Although ADOX seems to create the table ok, Table->Columns->Appends does not set the fields as adColNullable as expected.

When I insert data using ADO::Recordset->AddNew the following error occurs :- "The field 'MyTable.Column 2' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."

Am I on the right tracks here or do I need to adopt a different approach?

Code block to illustrate the problem :-



Code Block
ADOX::_CatalogPtr m_pCatalog;
ADOX::_TablePtr m_pTable;
ADOX::_ColumnPtr m_pCol1;
ADOX::_ColumnPtr m_pCol2;
ADODB::_ConnectionPtr m_pConn;
ADODB::_RecordsetPtr m_pRs;
//ADOX - Create Data Source
_bstr_t strcnn(("Provider='Microsoft.JET.OLEDB.4.0';Data source = C:\test.mdb"));
m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
m_pCatalog->Create(strcnn);

m_pTable.CreateInstance(__uuidof(ADOX::Table));
m_pTable->PutName("MyTable");

m_pCol1.CreateInstance(__uuidof(ADOX::Column));
m_pCol1->Name = "Column 1";
m_pCol1->Type = ADOX::adVarWChar;
m_pCol1->DefinedSize = 24;
m_pCol1->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol1->Name, ADOX::adVarWChar, 24);

m_pCol2.CreateInstance(__uuidof(ADOX::Column));
m_pCol2->Name = "Column 2";
m_pCol2->Type = ADOX::adVarWChar;
m_pCol2->DefinedSize = 24;
m_pCol2->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol2->Name, ADOX::adVarWChar, 24);

m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));


//ADO - Data Access
m_pConn.CreateInstance (__uuidof(ADODB::Connection));
m_pConn->Open(strcnn,_bstr_t(""),_bstr_t(""),ADODB::adOpenUnspecified);
m_pRs.CreateInstance(__uuidof(ADODB::Recordset));
m_pRs->Open("MyTable", _variant_t((IDispatch *)m_pConn,true),ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);

// Define a SafeArray that contains field names.
SAFEARRAY * psaFields;
SAFEARRAYBOUND aDimFields[1];
aDimFields[0].lLbound = 0;
aDimFields[0].cElements = 1;
psaFields = SafeArrayCreate(VT_VARIANT, 1, aDimFields);

// Create a SafeArray for values.
SAFEARRAY * psaValues;
SAFEARRAYBOUND aDimValues[1];
aDimValues[0].lLbound = 0;
aDimValues[0].cElements = 1;
psaValues = SafeArrayCreate(VT_VARIANT, 1, aDimValues);
long ix[1];
_variant_t var;

//Insert Data
ix[0] = 0;
var = "Column 1";
SafeArrayPutElement(psaFields, ix, (void*) (VARIANT *) (&var));
ix[0] = 0;
var = "test data row 1 col 1 Only";
SafeArrayPutElement(psaValues, ix, (void*)(VARIANT *) &var);
_variant_t vtFields, vtValues;
vtFields.vt = VT_ARRAY | VT_VARIANT;
vtValues.vt = VT_ARRAY | VT_VARIANT;
vtFields.parray = psaFields;
vtValues.parray = psaValues;

m_pRs->AddNew(vtFields, vtValues); //!! Fails Here !!

m_pRs->Update();
m_pRs->Close();
m_pConn->Close();




View 3 Replies View Related

DB Design :: Table Partitioning Using Reference Table Data Column

Oct 7, 2015

I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster? 

I have think three ways to do it.
1. leave as it is.
2. 7 years partition on one server
3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)

View 3 Replies View Related

Partitioning A Table

Mar 27, 2008

i have a table named "user" in which user which are located at different places within a city are recorded.
i want to group user with respect to there location like users of northern region are recorded first then users of western region and so on.
tell me from horizontal and vertical partitioning wh technique is better or i should use some other technique.
thanks  for ur consideration.

View 5 Replies View Related

Table Partitioning

Nov 14, 2000

Hi,
I want to know more on table partitioning.I do not know where to get the right info.from.
I have a doubt - if a table is partitioned horizontally how does a query identifies where to pick up the data from i.e. from which part of partitioned table?

View 2 Replies View Related

Table Partitioning

Jun 16, 2008

i want to partition a table containing about 3 million rows. The partition column will be of datetime type.

following is the partition function i have used
create partition function MyPartFun
(datetime) as range left for values ('07/30/2007','09/30/2007','11/30/2007','01/30/2008','04/30/2008')


following is the partition scheme i have used
create partition scheme PartScheme as
partition MyPartFun all to ([primary])


i know how to add partition column while creating the table
But dont know how to add above partition scheme to an already populated table
Plz help...

View 2 Replies View Related

Table Partitioning

Apr 24, 2007

Hi,
I have a database created using Enterprise Manager Wizard.
For example datafile db1_data.mdf and log file db1_log file exists.
All the tables are created in datafile db1_data.mdf.
Now to improve performance I want to implement table partitioning.
Can anybody tell me howto implement it with existing strutcure.
Suppose there is table Mytable in which all update and delete actions are performed regularly.And it contains about 10,0000 records.
I want to partition the table so that it contains 5000 records.

Solution with example highly appreciated.

Satish

View 7 Replies View Related

Table Partitioning

Dec 13, 2007

Hi Experts,
I am new to Table Partitioning, Can any body guide me how to do table partitioning?
any way here is my scenario, we are having one database called "DATA" in SQL 2000 server and we have migrated to SQL 2005 by using backup and restore. and "DATA" is having about 15 tables and they are very very very big in size. and they dont have any index on a coulum name "DATETIME", but i want make table partition according to that perticular field "DATETIME" and right present we are having 6 months of data.
So, how to proceed further?
Your help will be appreciable..

View 1 Replies View Related

SQL Table Partitioning

Mar 19, 2007

 

i am trying to partition an sql table in sql server 2005, i created the partition schema and the data files that i want the data to be filled in after the partition. After the partition is finished sql gave me partition is successful , but i noticed that the size of data files i created has not increased and their sizes are the same.

notice: i have a clustered index on this table, so i dropped this index and recreated it 

 Bellow the script that i am using

 

 

and thank you for your help in advance

View 1 Replies View Related

What Does Mean Table Partitioning ?

Mar 7, 2008

Hello frnds....what does mean by this ?

View 1 Replies View Related

SQL 2012 :: Table Property For Retention Date?

Feb 21, 2014

I want to store data warehouse source tables and files in an Archive schema and then delete / drop them after a specified period of time.

Is there a table property that I can set (can't find one) or some other mechanism so that I can easily identify these tables with a script.

If there is no such property or feature within the database engine I will define a metadata table and record it there, but a property or similar that I can set at archive time would be very handy.

View 0 Replies View Related

SQL Server 2012 :: DATE As Dynamic Table Name

Jan 13, 2015

I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this

declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)

But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.

View 1 Replies View Related

[Table Partitioning] What Is The Best Method?

Oct 11, 2006

Hello,

I have a Sql Server 2005 database with many tables, each with millions of records within them.

They all have a Receive Date field, with records going back 10 years or so.

What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?

~Le

View 2 Replies View Related

SQLServer Table Partitioning

Jun 5, 2007

Hi!I have a question:I already have a DB that uses partitions to divide data in USCounties, partitioned by state.Can I use TWO levels of partitioning?I mean... 3077 filegroups and 50 partition functions that addressthem, but can I use another function to group the 50 states?Thanks!Piero

View 15 Replies View Related

Table,index Partitioning

Apr 30, 2007

We had data in tables for multiple users (Logins) .Each user data is identified by a one column named €œUSER€?. No user has direct access to tables and only through views .we have created views and stored proc .Views will perform DML operations on tables using condition WHERE USER=SUSER_SNAME() (i.e Logged in user).So no point of getting others user data.


Each table has a column USER and we are queering data based on login user .this is the foreign key of USER table. Each view contains user column in where clause .So for every query we are searching all records .instead of that is there any way to get data with out searching all records.

I heard about table Partitioning, index Partitioning, view Partitioning. Are they helpful to boost my query performance?

And also let me know is there any good way of designing apart from above options

View 3 Replies View Related

Table Locking In Partitioning

Aug 10, 2006

Hi,

I have the following doubt about table lockinglocking in case of partitioning:-

Say we have 5 partition on the table Employee on the key Joining_Date and when we run 5 select queries on each of the parition in parallel will there be locking on the table when the 1st query is running or all the 5 queries can run in parallel. Basically, I am trying to see if parallelism and partitioning can work in sync or there will be locking at the table level if I don't specify any query hints?

Any help in this regard is highly appreciated.

Thanks,
Ritesh





View 1 Replies View Related







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