SQL Server 2008 :: Partitioning - Left Or Right?
Jul 1, 2015
Normally it is recommended to leave an empty partition on both the front and back ends of a table to avoid data movement when merging/splitting. But I have some questions based on my scenario, which is a table partitioned by a load date, so all records in a partition contain the same date, not a range of dates.
If I use range left, once I switch out the first partition it would become empty, so would there be data movement when I merge it into the next partition? The real issue though is that we will not just be removing the first partition, but "random" partitions throughout the table. Will this work?
If I use range right, when I split the last partition to create a new one it doesn't seem there would be any data movement there either. Am I missing something?
Basically I'm wondering if I should use range left or right. Most recommend using right, but then the boundary value is not the value in the partition. This could potentially result in someone deleting the wrong data if they are not careful. So is there any reason not to use left in my scenario?
I did a test of removing a partition in the middle and it worked just fine; this was using range right. I have about 6 million rows per partition. I also tested splitting at the end and it worked fine. I'll rebuild it with range left and test.
View 0 Replies
ADVERTISEMENT
Apr 1, 2015
I've yet to use partitioning in a production environment, and pretty much last ran any partitioning related code a few years back when looking at certification; so I'm definitely not an expert on the matter and only loosely clued up on the concepts.
I've recently started with a new employer, and they have just implemented a new system for sms messaging. The database tables tracking the sms messages being sent are going to get big and so they have created decided to implement partitioning on some of the tables using a partition scheme on the CreatedDate column; the DBA involved in designing the partitioning has left and I'm picking this up.
The relevant DDL for the table is below:-
CREATE TABLE [Message].[Sms](
[SmsId] [bigint] IDENTITY(250000001,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_Sms:CreatedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Sms:SmsId] PRIMARY KEY NONCLUSTERED
[code]....
There are some issues with the above that I will be addressing seperately (e.g. the clustered index should be unique as it contians the unique key, and the fillfactors are daft), but my concerns for this post are below.
1) How to define the Primary Key and enforce it's uniqueness whilst trying to ensure it's aligned with the partition in order to be able to switch out old data once an as yet undefined retention period has passed. In books online it states:- "If it is not possible for the partitioning column to be included in the unique key, you must use a DML trigger instead to enforce uniqueness. " Books online - Special Guidelines for Partitioned Indexes. However, I'm not sure what this means, nor how I create the primary key to use the partition function seeing as it doesn't have the CreatedDate in the unique key?
2) The original partition function was envisaged as the following:-
CREATE PARTITION FUNCTION [DateFunction](datetime) AS RANGE
LEFT FOR VALUES (N'2014-01-01T00:00:00.000'
, N'2014-04-01T00:00:00.000'
, N'2014-07-01T00:00:00.000'
, N'2014-10-01T00:00:00.000'
, N'2015-01-01T00:00:00.000'
, N'2015-04-01T00:00:00.000'
, N'2015-04-02T00:00:00.000'
, N'2015-04-03T00:00:00.000'
, N'2015-04-04T00:00:00.000'
, N'2015-04-05T00:00:00.000')
GO
There is a procedure that has been created and scheduled daily that will create a new partition for each day, and then merge these together at the end of the quarter. My understanding of partitioning is that this is a bad idea, as it will result in merging several populated partitions together. Is my understanding correct? If so, I'm planning on removing the day partitions at the end of the function, and simply adding quarterly partitions, maintaining a spare empty partition at the end of the table. Would this make more sense?
View 9 Replies
View Related
May 8, 2013
we planning to create partitioning on existing tables. The partitioning is on date column, there should be one partition for each year.
Creating of new partitions should be automated, and also we dont have any plans of archiving old data, all we want is that new partition creation should be automated.
View 6 Replies
View Related
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
Mar 30, 2015
SELECT LEFT(InstanceName,CHARINDEX(' ',InstanceName)-1)
FROM ServerDB
Returns the following error.
Invalid length parameter passed to the SUBSTRING function.
View 2 Replies
View Related
Oct 20, 2015
I have imported a txtfile to access and have some problem making the data appear in my wanted way. I want to shift the cells in the first row one step to the left, i.e to overwrite the zeros with the left value. Then I want to assign the first row values as field names.
Current data
Field1 Field2 Field3 Field4 Field5
0 Name Major Sector Hi
karl
per
anna
Wanted outcome
Name Major Sector Hi
karl
per
anna
View 8 Replies
View Related
Mar 8, 2015
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.idthis now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
View 9 Replies
View Related
Jun 16, 2015
I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
View 2 Replies
View Related
Jun 25, 2015
Is there a way to leave the graphical 'Include Execution Plan' on by default in SSMS? I don't know how many times I run a long-running query, say to myself, "wow, that took a while; I wonder what the execution plan looks like?" only to realize that I left it turned off. Now I have to turn it on, and wait for the query to run again. I'm guessing there's a setting in the options somewhere to always leave it on, but I'm not sure where
View 2 Replies
View Related
Jul 19, 2006
i'm using sql server 2005 partitioning feature, when i update a record in 100 record partition it takes the same time as 1000000 records partition why, could any one help
View 1 Replies
View Related
Jan 8, 2007
Ananth writes "A new feature is established in SQL Server 2005. That is Partitioned Table. I went through several articles in net regarding partioned table. But I did not get any generalized version of Partitioning.
For example I partitioned a table into 4 Filegroups based on Transaction Date. So I used range function to give ranges for 4 quarters of one year. It works fine. But I would like to generalize this scenario. It will work for one year. If new year comes ,I have to write new script and execute the same.
The Partitioned function should work for 12 months in a given year. So here year is the paramter and then that function should be construct based on the year automatically.
Can anybody give me answer asap."
View 1 Replies
View Related
Dec 2, 2006
Is there any benefit in creating seperate file groups for a partitionedtable on a multi-processor server with RAID5 and 1 Logical Drive?
View 8 Replies
View Related
Jan 23, 2004
I was wondering if Microsoft SQL Server support any partitioning method (like range, list, hash or composite partitioning in Oracle) or you have to partition all tables manually?
Thanks in advance,
Bracia
View 1 Replies
View Related
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
Oct 28, 2015
I have a table PLACE with a character column CODE
[Code] [nchar](4) NULL
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
   Â
SELECT Â RIGHT(RTRIM('0000'+ISNULL([Code],'')),4) Â Â Â
  FROM [Place]
  WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
View 10 Replies
View Related
Feb 25, 2014
Script to do the table partitioning for a 500gb in sliding window technique?
View 9 Replies
View Related
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
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
Oct 24, 2013
How to remove space left to right and right to left
If I give limit >60 for first 60 character; limit 60< second 60 character
Result would be check if space at 60 character if yes remove and go the 59 character check then space remove and 58 character check if there is charater then display
As well as after 60 character to till 120 for right space
View 5 Replies
View Related
Apr 16, 2008
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
View 5 Replies
View Related
Mar 31, 2008
There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.
Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?
Jim
View 1 Replies
View Related
Apr 23, 2008
Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
Any pointers to documentation or any advice?
Thanks,
A Brown
View 1 Replies
View Related
Feb 25, 2008
Hello!
Recently, I set up server with Windows Web Server 2008 RC1, SQL 2008 Express beta, .NET 3.5, IIS 7.
I'm running ASP.NET web application with SQL database. Everything works fine until the first application state on the server expires. After that, any postback that starts a new application state on the server and connects to the database, results in the following error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Is this a bug that will be fixed in release of Windows / SQL or am I doing something wrong?
Many thanks for help,
Jan
View 1 Replies
View Related
Mar 7, 2008
Hi all,
New to this so please be patient and please help.
I have developed an SQL 2005 Express command line option install for our company which has been working seamlessly for the last 18 months.
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
I downloaded the €œMicrosoft SQL Server 2008 Express CTP, February 2008€? from http://www.microsoft.com/downloads/details.aspx?FamilyId=749BD760-F404-4D45-9AC0-D7F1B3ED1053&displaylang=en
I simply replaced the 2005 file €œSQLEXPR.EXE€? with the 2008 file €œ€?, recompiled the installation and tested only for it to fail. I than read the 2008 books online and noted the change in command line options.
http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en
http://msdn2.microsoft.com/en-us/library/ms144259(SQL.100).aspx
I then changed the command line to suit the Microsoft 2008 books online, recompiled the installation and tested only for it to fail once more.
Interestingly I tested the install from the default GUI and at the point of adding the €œsa€? login credentials it fails to allow the installation to proceed. Strangely by selecting the windows authentication credentials, €œnext€? than €œback€? it now allows me to add the €œsa€? login credentials and continues to install correctly as required.
I hope I have explained this clearly enough.
1. Is this a bug in the €œMicrosoft SQL Server 2008 CTP, February 2008€? installation?
2. If so is this causing the command line install options to fail?
3. How do I obtain a version of €œMicrosoft SQL Server 2008 Express€? that will work installing from the command line?
Thanks in advance.
View 1 Replies
View Related
Jan 25, 2015
-- Why is the left table in a LEFT JOIN limited by the where clause on the right table?eg
DECLARE @LeftTable TABLE (LeftID INT NOT NULL IDENTITY(1, 1), LeftValue INT NULL)
INSERT @LeftTable (LeftValue)
VALUES (111)
INSERT @LeftTable (LeftValue)
VALUES (222)
[code]....
View 2 Replies
View Related
Dec 20, 2006
hello
for MS SQL 2000, i cannot get it
i am having names like AB_12 I want to get all rows with left part similar , AB im that case
SELECT id, name
FROM Users
WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN
(
SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
FROM Users
GROUP BY LEFT(name, CHARINDEX('_', name) - 1)
HAVING (COUNT(*) > 1)
)
does not work
is there any way to use a variable ?
declare @nm nvarchar
set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns
FROM Users
thank you for helping
View 4 Replies
View Related
Aug 25, 2006
We have a table that contains a column named "Left". (Yea, I know, this was a bad idea - but it is not within my power to change it.)
Our stored procedures access it using [Left] without any problems in Sql Server 2000 and Sql Server Express Edition. HOWEVER, it does NOT work with SQL Server 2005. It generates the error: Column 'Left' is read-only.
Has anyone seen this? Is there a problem with SQL Server 2005 and reserved words using [ ] notation?
Thanks!
View 5 Replies
View Related
Sep 8, 2015
I need to join 2 tables but the join needs to account for 2 seperate columns.
for example:
select
a. type
a. prod_code
a. prod_type
b. division
from table1 a
left join table2 b
on a. prod_code = b. prod_code
and a. prod_type = b. prod_type
The issue is that you may have only the prod_code or prod_type and null value for the other in table1.
Ideally I want it to check for both then if 1 isn't available then it draws the division of the available. having both or one or the other determines the division it falls under.
View 5 Replies
View Related
Sep 1, 2005
Hi All,
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
View 2 Replies
View Related
Dec 6, 2004
Hello!
I am trying to run a query on an SQL-server database:
SELECT (Left(Comments, 20)) As shortComment FROM myTable
The Field Comments is a Text field (datatype is text), and I can't get the LEFT function to work with this datatype...
How can I bypass this problem? Can I convert the commentfield to varchar in the QUERY?
noccy
View 1 Replies
View Related
Nov 1, 2004
I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?
SQL with Left Outer join operator:
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#)
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#
SQL with *= operator
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat , CurrentWeekFinMetrics
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)
For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.
Thanks and Regards
mb
View 2 Replies
View Related
Apr 15, 2015
I'm attempting to improve a vendor created query.
Here is the original -
SELECT m.lID
FROM Message m
inner join Message_Cc mCC on m.lID=mCC.lMessage and mCC.lOfficeRecipient = 200321
INNER JOIN UserRole d on mCC.szRecipient=d.szRoleName
inner Join Map_UserAtOfficeToRole a2 on a2.lUserRole = d.lid AND d.nRecordStatus = 1
[Code] ....
If I run this without the LEFT OUTER JOIN and the is null statement I get 648 rows. But If I include it I get 0 rows. I can't understand why I get 0 rows with the outer join.
View 9 Replies
View Related
Aug 3, 2015
I have a query that has a left join with a large partitioned table. The partitioned table has 10s of millions of records, and each partition has about 100,000 records.
The left join is part of an insert that gets a column from the partitioned table, if the column exists. The query contains the partition ID and all other joined columns are part of a non-clustered index.
Through the profiler, I found that there were millions of reads and the execution plan was giving me a table scan on the partitioned table.
I changed the query to do the insert followed by an update with inner join. That did the trick, but it worries me that SQL Server 2014 behaves differently from 2012 or 2008R2, which can make upgrading very time consuming.
View 3 Replies
View Related
