Solved

partition by a column of type datetime.

Posted on 2011-03-03
20
1,183 Views
Last Modified: 2012-05-11
this is my new table:
CREATE TABLE [dbo].tabla_demo
(  
id int identity,    
c varchar(200) NULL,
fecha datetime
)

Previous example I added the date column, it stores information from the year 2000, if you wanted to partition the table as it should modify the code?
0
Comment
Question by:enrique_aeo
  • 8
  • 6
  • 6
20 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 35027580
this tutorial shows how to do it:
http://weblogs.sqlteam.com/dmauri/archive/2005/07/18/7147.aspx

the key is the partitioning function. ...
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35029265
As the side note, if you have identity ID increasing the same way with fetcha date column, consider to partition by ID instead. Even if it introduces more work, in some cases it gives you some benefits. Mainly because you don't need to introduce another column in the clustered and non-clustered indexes. As the downside, it would not help SQL Server to eliminate partitions if you queries have fetcha as the filter there
0
 

Author Comment

by:enrique_aeo
ID: 35033677
ok, I understand
 1. we're on stage that the date and the id increases
 2. You should get the number of records per year, so that according to that made my partition function
 3. It is good practice to try to partition the primary key is the physical arrangement of a table?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:enrique_aeo
ID: 35033682
angelIII, thanks for the url. But the scenario I need is when a table has data, ie a non-partitioned table to a partitioned table
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35034211
you cannot change a table from non-partitioned to partitioned or vice-versa.

so, you have to rename + recreate the table, and reinsert the table's data from the renamed table.
0
 

Author Comment

by:enrique_aeo
ID: 35034979
Now if I'm confused. In the original example
 1. I think a table is not partitioned
 2. full data
 3. I think the partition function,
 4. I think the partition scheme,
 5. remove the primary key
 6. re-create the primary key
 7. and then the data appear in their respective partition
 What do you mean?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 35035008
no, not recreate the priamry key. you have to recreate the full table.
and in order not to loose data, you just rename the original table, create a NEW table with the same original name, but using the partitioning function.
then, you insert the data to the (new) table from the renamed table ...
0
 

Author Comment

by:enrique_aeo
ID: 35035028
ok, then
rename the original table = create a NEW table with the same original name, but using the partitioning function
something?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35035040
what exactly are you missing?
0
 

Author Comment

by:enrique_aeo
ID: 35035064
I believed that renaming a table is renamed, for example
 before: mytable
 Now: mitabla2

And another question
All partitions must be made using the primary key?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35035339
no, partitions can be made using any field, as it's actually the partitioning schema (function) the decides.
and indeed, the primary key is most often not the best for the partitioning
0
 

Author Comment

by:enrique_aeo
ID: 35036224
angell ok, then back to my question, as you would to partition a data table with a column of type datetime. In the example of the url is when the table is empty, but when it is full as I do?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35036578
the partitioning schema would depend on the needs and facts.
if you have only hundreds of calls per day, you could do a partitioning per month.
if you have thousands of calls her hour, the partitioning shall be by day
but that are only extreme examples...

and again: it does not matter if the table is empty or not: you cannot change it from non-partitioned to partition without dropping (aka renaming) it first. the only difference if the table is empty or not is that you don't need to rename it to keep the data. ...
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35036844
Angelii, why do you think he cannot simply drop and recreate clustered index? This would work without additional table.

Of course, this route (one table) would be slower on the large tables due extra table rebuild (when you drop clustered index). But it would work.

So in his scenario he can:

 1. I think a table is not partitioned
 2. full data
 3. I think the partition function,
 4. I think the partition scheme,
* Drop all non clustered indexes
 5. remove the primary key
 6. re-create the primary key
* Recreate non clustered indexes - make them aligned - i.e. include partitioned column to the index.
 7. and then the data appear in their respective partition
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 375 total points
ID: 35036903
the question is about how to partition the table ...
so, 2 scenarios:
* the table is empty (or the data does not matter)
  solution: drop the table, and recreate it with the proper (partitioning) code

* the table is not empty, and the data is to be kept:
  solution: rename the table, create a new one with the proper (partitioning) code, and copy over the data, drop the nenamed table

recreating a (clustered) index or primary keys does not matter, in that regards.

of course, one could consider to keep the table non-partitioned, and have a clustered index on the field "c" (url, I presume) instead, so you can check stats per url easier...
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35036950
See below
create table T
(
	ID int not null,
	Col int not null,
	Placeholder char(100),
	
	constraint PK_T
	primary key clustered(ID)
)
go

create partition function pfT(int) 
as range left 
for values(0, 100)
go

create partition scheme psT
as partition pfT
all to ([primary])
go

alter table T
drop constraint PK_T
go

alter table T
add constraint PK_T
primary key clustered(ID, Col)
on psT(Col)
go

Open in new window

0
 
LVL 13

Expert Comment

by:dwkor
ID: 35036979
AngelIII - I'm commenting this statement: "and again: it does not matter if the table is empty or not: you cannot change it from non-partitioned to partition without dropping (aka renaming) it first. the only difference if the table is empty or not is that you don't need to rename it to keep the data. ..."

You can change it from non-partitioned to partitioned. This is not optimal solution if table has a lot of data because of additional table rebuild but it could make sense in a lot of cases. Think about security, etc.
0
 
LVL 13

Assisted Solution

by:dwkor
dwkor earned 125 total points
ID: 35037101
Enrique, one other thing - why do you want to partition the table? This is probably the most important question. This is really useful in the case if you want to implement sliding window scenario although for the other cases it does not automatically means that it improves performance. It could, especially if parallelism would be involved, but same time it could hurt. For example, if you have CI (ID, Date) and partition by Date, query like: select  from T where ID > ? would introduce more IO than non-partitioned table.

Check http://aboutsqlserver.com/2010/12/22/sql-server-and-table-partitioning-part-2-when-partitioning-is-you-enemy/ if you need more details.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35037258
>You can change it from non-partitioned to partitioned.
I presume I missed the relevant info, then....
sorry that I posted wrong information. was this possible in sql 2005?
0
 
LVL 13

Expert Comment

by:dwkor
ID: 35037306
angelIII, yes. I would not recommend it on the huge tables (your way is faster) but it's available.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question