DB Design :: Steps To Extend Table Partitions
Jun 1, 2015
SQL 2014 I've inherited a db that has several partitioned tables. They are partitioned by month. We're approaching the last partition, 11-30-2015, so we need to extend the tables. My question is how do I do this? There are Partition Schemes and Partition Functions setup at the db level. I've figured out how to ALTER those. Next I go to a table that I know is partitioned, right-click Storage and select Manage Partitions.
My only option is to "create a staging table for partition switching". Not knowing what switching is, I'm not sure if this is what I want to do. All I want to do is add new partitions to the table - and remove some of the old ones since they are empty due to archiving of data.
So, what is the proper steps to adding new partitions to a table that is already partitioned?
View 4 Replies
ADVERTISEMENT
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
Nov 7, 2007
Hi all,
Does anyone know how to setup the "table details" to take up all the space in a page (extend the boarder to the end of the page) even there is only one record in the table details?
Normally, for example, if you got a table in a report, if the table has only one record, the boarder of table will only take up maybe 1/4 of the page space (header, 1 line details, footer). if the table has 5 records, it may take up half of the page, but the boarder of table neven extend to the end of the report and i wwant it happen!!!!!!! Thank you.
Cheers,
Bryan
View 8 Replies
View Related
Dec 2, 2015
This question is extension from the topic Updating table Rows with overlapping dates: [URL] .....
I am actually having a table as following:
Table Name: PromotionList
Note that the NULL in endDate means no end date or infinite end date.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
Flag = 1, success merge row. Flag = 2, fail row
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.
Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.
Original table:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL
Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL
Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).
By taking the min date and max date of the same group, the following table should be achieve after the table is updated.
Final outcome:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL
View 2 Replies
View Related
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
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
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
Jun 4, 2007
Hi guys , assuming right now I already create partition function (PF_Date) and partition scheme (PS_Date). Let say I would like to implement the partition on the existing tables ( eg: transaction table which is in PRIMARY filegroup), how am I switch it from PRIMARY to PS_Date ? Is it I have to re-create the particular table then only able to put in the partition scheme? Hope can get any assistance here. Thanks alot.
Best Regards,
Hans
View 8 Replies
View Related
Dec 31, 2011
I have a table that contains records of transactions with ID column is primary key
I use partition follow ID column, each partition have 1 million records.
CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)
CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])
But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?
View 9 Replies
View Related
Jan 29, 2008
Guys,
First off, I'm not very familiar with SQL Server. I need some guidance on what the best path to take is for this as it may not even be table partitions.
I have a huge table (155 million rows) and it's gotten so large than I can't even delete a large set of rows from it (i.e. delete everything older than 6 mo, which would be ~100 million rows). When trying to run a delete like this, it just goes for a LONG time and then just eventually runs out of memory.
The current data in this table can actually be completely cleared out soon (after Feb 1st) and I plan to do this with TRUNCATE TABLE, or just DROP and recreate. Once I do this, I want to create a way to keep this table moderately sized so it never grows that large again and it seems table partitions may be the way to go for this?
I'd like to keep the last 6mo of data in it (I have a datetime column to keep track of this). Anything older I'd like automatically removed. Can I do this with table partitioning? Create 6 partitions that store the 6 most recent months of data and everything older automatically gets dropped off?
If not partitions, what do you suggest to keep this DB modest size?
Thank you.
View 9 Replies
View Related
Apr 13, 2015
Perhaps this task is not for MDS.... But another tool for rapid development & startUp - we don't have. And nevertheless....
We created table managers_plan in MDS :
year
month
id_manager (domain attr)
POS (domain attr)
plan_sum_USD
plan_unit
----------------------------
Entities:
Managers ~ 800 records
POS ~ 100 000 records
managers_plan Total records for
1 year = 100K x 12 = 1 200 000
managers_plan - table partitions
- Will bemade ?
View 3 Replies
View Related
Nov 20, 2007
Hi all,
I have the following function:
Code Block
create function udf_CountRows(@pTableName sysname)
returns int
as
begin
declare @ret int
select @ret = SUM(p.rows)
from sys.partitions p
inner join sys.objects o
on p.object_id = o.object_id
and o.[name] = 'Well'
return @ret
end
Can I trust sys.partitions to always return the correct value or does it suffer the same issue as sysindexes prior to SQL2005?
Thanks
Jamie
View 5 Replies
View Related
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
Sep 15, 2015
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cd permit_nbr reference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008
View 3 Replies
View Related
Oct 29, 2005
Good Evening!
First and foremost any help would be greatly appreciated!My question is how to use a temp table within two (2) seperate called stored procedures?
I am using MS ReEporting Services and I call a stored proc that creates a temp table and executes some summarization code - so the scenario is as follows:
1. I select the first MS REporting services from a menu item - which creates the Temp Table (within a stored procedure) and the report is displayed
2. Within option (1) (MSRE - I have defined a navigation link to call another report - which uses a stored procedure) which I would like to access the temp table created in step 1.I know that #Temp_Table will only be available for step 1 and I have tried to use##Temp_Table which should be available for my session - which maybe I am not understanding correctly in that I thought a ##Temp_Table would be available for my session until no other T-Sql scripts are trying to access or I sign off!I don't really want to create a table and have it "hang around" until someone selects the menu option again to select the program again which then the T-Sql will then Drop the table.Any insight or help is sincerely appreciated!Best regards
View 2 Replies
View Related
Oct 8, 2015
I have table having around 100 million rows.Everyday we have an ETL process in which table will be trucnated and relaoded. Will creating a partition on the table increase the inserting speed?
View 4 Replies
View Related
May 16, 2007
Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp
View 1 Replies
View Related
Jun 6, 2001
Hi,
I have two job J1 and J2, each one has 10 steps. Now I want J2 to be the 11th step of J1 and I did not want manually type all the steps of J2 to be 11-20 step of J2. Is there an easy way through TSQL to do this ?
sp_add_jobstep only works when the step is OS command or a script but not a job
View 2 Replies
View Related
Aug 18, 2015
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name :
Nestle milk |
Rainbow milk
packages :
CTN,OTR, NOs |
CTN, NOs
Price:
50,20,5 |
40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos
| CTN=8 Nos
View 3 Replies
View Related
Mar 6, 2007
HelloIn my website I have a textbox to search some products, but it would like to extend my search-function so that it could find special characters. For example:When I search Mexico, I would like to find México. Is there an easy way to do this?Thanks in AdvanceWesley
View 1 Replies
View Related
Oct 4, 2007
I have a database that is in production. It is almost full. How would I go about extending the size of it? Would it involve an outage?
View 4 Replies
View Related
Sep 6, 2001
Hi
Had a weird error yesterday, can anybody shed any light on it.
I got the following error in a SQL 7(sp2) database.
'The log file for database 'IT2PROD' is full. Back up the transaction log for the database to free up some log space..
Error: 9002, Severity: 17, State: 2'
The Transaction Log has unrestricted file growth and auto grow file set and the disk has over 20gb of free space.
The only explanation I can think of is that the Log tried to extend during the hourly backup. Would this cause the above error ??? The backup file is usually about 80mb but was over 300mb when this happened.
Any ideas ????
Thanks in advance
Gary
View 4 Replies
View Related
Jun 8, 2001
Hi All,
I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.
TIA,
Shamil
View 4 Replies
View Related
Jul 8, 2015
I found this script for dependencies and want to extend it:
I want to add the obj_typ (U, FN, P, V, TR...) for the dependency
How can I do this?
Here is the script:
WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel, obj_type )
AS
(
SELECT
o.[object_id] AS referenced_id ,
o.name AS referenced_name,
[Code] ...
View 3 Replies
View Related
Jun 27, 2007
Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?
Thanks,
David
View 9 Replies
View Related
Jun 6, 2007
Hi,
I'm totally new to SSRS2005 and I was wondering if I could extend the existing report items. For instance, I'd like to add a few extra properties to the textbox report item. I tried wrting a custom Textbox control that inherits from System.Windows.Forms.Textbox. When I imported the dll in VS, the new textbox showed up in the toolbox, but it was grayed out.
Any idea what I'm missing here?
Thanks,
Phil
View 3 Replies
View Related
Jan 14, 2008
Howdy folks, first time poster.
In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?
-- The basic query
SELECT column
FROM table
WHERE clause
-- Additional clause, only appended to query
-- if expression evaluates to true
IF @parameter <> default_value
BEGIN
AND additional_clause
END
Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?
-- The basic query
SELECT column
FROM table
WHERE clause
-- Additional clause, should only effect result
-- set when the parameter is not default_value
AND table.column =
CASE @parameter
WHEN default_value THEN
-- identity, table.column=table.column,
-- should have no effect other than just a long query
table.column
ELSE
@parameter
END
View 8 Replies
View Related
Jun 2, 2006
Hi,
I have a number of functions which I'd like to be able to add to the expression language to avoid having to use numerous script components, but I can't find any information on how this is possible. Can anyone provide any advice, please?
Thanks in advance,
Lawrie
View 1 Replies
View Related
Mar 2, 2005
Hello,
I am managing a sqlserver 6.5 database in my company. I get the message that the datafiles should be expanded but whenever I try to expand it the following message appears:
Could not find enough space on disks to extend the database. Meanwhile, I have about 6 gigabytes free space on my disks. Please help me out.
Thanks,
Albert
View 2 Replies
View Related
Oct 27, 2006
Using MS Access as a front end and SQL as a back end how can i get past the 4000 character limmit per table?
View 9 Replies
View Related
May 6, 2015
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
View 7 Replies
View Related
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
