SQL Server Performance

Como Particionar las tablas, indices (como PK)

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by cipriano, Jan 10, 2008.

  1. cipriano New Member

    Buenos dias,
    Señores soy una persona neófita en SQL SERVER. Pero SI tengo conocimiento en INFORMIX. Y como estamos Migrando de SQL a INFORMIX, ESTOY PROBANDO ALGUNAS COSAS, como es la fragmentacion de Tablas.
    Por tal motivo quisiera hacer las siguientes preguntas:
    1. En INFORMIX puedo Particionar una tabla de dos manera: Una llamada fragment by round robin in datadbs1 datadbs2, esto quiere decir que la información se almacenará en forma paralela en las dos espacio de almacenamiento(datadbs1 y datadbs2) y el otro tipo se ????? (no recuerodo el nombre) , pero se trata por condición.
    En Sql existe solo existe un tipo de Particionar por CONDICION????
    Crea primero Partition Functio
    Crea Partition scheme.
    Pregunta : Solo existe en SQL este tipo de Particionamiento. Lo que yo quiero es particionar la tabla en dos o mas FIle group, y creo que solo existe el tipo de particionamiento de esquema.
    2. Cuando creo la tabla en SQL con dos medios de almacenaminento distinto(partition schema y por File Group):
    create table prueba
    (dni char(8),
    nombre char(30)
    constraint pk_prueba primary key(dni) with (opc1,opc2,...) on idxdbs
    ) on nombre_esquema(campo)
    Como el ejemplo anterior, la tabla se crea en el file group IDXDBS, y no se crea en el otro: particion Schema.
    Aque se debe esto????????
    3. En informix cuando Yo creo una tabla, Yo puedo indicarle que tipo de bloqueo va a tener: por Pagina(Default) o por Registro.
    Tambien le indicamos con que tamaño inicial de Extent va a contar (1 extent es igual a 8 pagina) y el tamaño de Extent próximo .
    Ejemplo:
    Create table pruebita
    (dni char(8),
    nombre char(30)
    ) lock mode row o page, Fentent=250, Nextent=50
    Estaré en la espera de su respuesta.
    Saludos
    Anibal
    PD. Mi correo es cipri002@marina.mil.pe
  2. Luis Martin Moderator

    <p>&nbsp;Bienvenido al Forum!.</p><p>Dado que son varias las preguntas te suguiero leer la siguiente información obtenida de los libros en línea. Te dejo además un link.</p><p>&nbsp;</p><h1><a class="" name="_creating_a_partitioned_view"></a>Creating a Partitioned View</h1><p>A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table. Microsoft® SQL Server™ 2000 distinguishes between local and distributed partitioned views. In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server. In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server. In addition, SQL Server 2000 differentiates between partitioned views that are updatable and views that are read-only copies of the underlying tables.</p><p>Distributed partitioned views can be used to implement a federation of database servers. A federation is a group of servers administered independently, but which cooperate to share the processing load of a system. Forming a federation of database servers by partitioning data is the mechanism that enables you to scale out a set of servers to support the processing requirements of large, multitiered Web sites. For more information, see <a href="javascript:hhobj_1.Click()">Designing Federated Database Servers</a>.</p><p>Before implementing a partitioned view, you must first partition a table horizontally. In designing a partitioning scheme, it must be clear what data belongs to each member table. The original table is replaced with several smaller member tables. Each member table has the same number of columns as the original table, and each column has the same attributes (such as data type, size, collation) as the corresponding column in the original table. If you are creating a distributed partitioned view, each member table is on a separate member server. For the greatest location transparency, the name of the member databases should be the same on each member server, although this is not a requirement. For example: <b>Server1.CustomerDB</b>, <b>Server2.CustomerDB</b>, <b>Server3.CustomerDB</b>.</p><p>You design the member tables so that each table stores a horizontal slice of the original table based on a range of key values. The ranges are based on the data values in a partitioning column. The range of values in each member table is enforced by a CHECK constraint on the partitioning column, and ranges cannot overlap. For example, you cannot have one table with a range from 1 through 200000, and another with a range from 150000 through 300000 because it would not be clear which table contains the values from 150000 through 200000. </p><p>For example, you are partitioning a <b>Customer</b> table into three tables. The CHECK constraint for these tables is:</p><pre><code>-- On Server1:<br>CREATE TABLE Customers_33<br> (CustomerID INTEGER PRIMARY KEY<br> CHECK (CustomerID BETWEEN 1 AND 32999),<br> ... -- Additional column definitions)<br><br>-- On Server2:<br>CREATE TABLE Customers_66<br> (CustomerID INTEGER PRIMARY KEY<br> CHECK (CustomerID BETWEEN 33000 AND 65999),<br> ... -- Additional column definitions)<br><br>-- On Server3:<br>CREATE TABLE Customers_99<br> (CustomerID INTEGER PRIMARY KEY<br> CHECK (CustomerID BETWEEN 66000 AND 99999),<br> ... -- Additional column definitions)<br></code></pre><p>After creating the member tables, you define a distributed partitioned view on each member server, with each view having the same name. This allows queries referencing the distributed partitioned view name to run on any of the member servers. The system operates as if a copy of the original table is on each member server, but each server has only a member table and a distributed partitioned view. The location of the data is transparent to the application.</p><p>You build the distributed partitioned views by: </p><ul type="disc"><li>Adding linked server definitions on each member server containing the connection information needed to execute distributed queries on the other member servers. This gives a distributed partitioned view access to data on the other servers.<br><br></li><li>Setting the <b>lazy schema validation</b> option, using <b>sp_serveroption</b>,<b> </b>for each linked server definition used in distributed partitioned views. This optimizes performance by ensuring the query processor does not request meta data for any of the linked tables until data is actually needed from the remote member table.<br><br></li><li>Creating a distributed partitioned view on each member server. The views use distributed SELECT statements to access data from the linked member servers, and merges the distributed rows with rows from the local member table. </li></ul><p>To create distributed partitioned views for the preceding example, you must: </p><ul type="disc"><li>Add a linked-server definition named <b>Server2</b> with the connection information for <b>Server2</b>, and a linked server definition named <b>Server3</b> for access to <b>Server3</b>.<br><br></li><li>Create this distributed partitioned view: <pre><code>CREATE VIEW Customers AS<br> SELECT * FROM CompanyDatabase.TableOwner.Customers_33<br>UNION ALL<br> SELECT * FROM Server2.CompanyDatabase.TableOwner.Customers_66<br>UNION ALL<br> SELECT * FROM Server3.CompanyDatabase.TableOwner.Customers_99<br></code></pre></li><li>Perform the same steps on <b>Server2</b> and <b>Server3</b>. </li></ul><h5>Updatable Partitioned Views</h5><p>If a local or distributed partitioned view is not updatable, it can serve only as a read-only copy of the original table. An updatable partitioned view can exhibit all the capabilities of the original table.</p><p>A view is considered an updatable partitioned view if: </p><ul type="disc"><li>The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each individual SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query). </li></ul><p>The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables. </p><h5>Table Rules</h5><p>Member tables are defined in the FROM clause in each SELECT statement in the view definition. Each member table must adhere to these rules: </p><ul type="disc"><li>Member tables cannot be referenced more than once in the view.<br><br></li><li>Member tables cannot have indexes created on any computed columns.<br><br></li><li>Member tables must have all PRIMARY KEY constraints on an identical number of columns.<br><br></li><li>Member tables must have the same ANSI padding setting. For more information about the ANSI padding setting, see <a href="javascript:hhobj_2.Click()">SET ANSI_PADDING</a>. </li></ul><h5>Column Rules</h5><p>Columns are defined in the select list of each SELECT statement in the view definition. The columns must follow these rules. </p><ul type="disc"><li>All columns in each member table must be included in the select list. SELECT * FROM &lt;member table&gt; is acceptable syntax.<br><br></li><li>Columns cannot be referenced more than once in the select list.<br><br></li><li>The columns must be in the same ordinal position in the select list <br><br></li><li>The columns in the select list of each SELECT statement must be of the same type (including data type, precision, scale, and collation). For example, this view definition fails because the first column in both SELECT statements does not have the same data type: <pre><code>CREATE VIEW NonUpdatable<br>AS<br>SELECT IntPrimaryKey, IntPartNmbr<br>FROM FirstTable<br> UNION ALL<br>SELECT NumericPrimaryKey, IntPartNmbr<br>FROM SecondTable<br></code></pre></li></ul><h5>Partitioning Column Rules</h5><p>A partitioning column exists on each member table and, through CHECK constraints, identifies the data available in that specific table. Partitioning columns must adhere to these rules: </p><ul type="disc"><li>Each base table has a partitioning column whose key values are enforced by CHECK constraints. The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table. Any given value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, AND, OR, &lt;, &lt;=, &gt;, &gt;=, =.<br><br></li><li>The partitioning column cannot be an identity, default or <b>timestamp</b> column.<br><br></li><li>The partitioning column must be in the same ordinal location in the select list of each SELECT statement in the view. For example, the partitioning column is always the first column in each select list, or the second column in each select list, and so on.<br><br></li><li>Partitioning columns cannot allow nulls.<br><br></li><li>Partitioning columns must be a part of the primary key of the table.<br><br></li><li>Partitioning columns cannot be computed columns.<br><br></li><li>There must be only one constraint on the partitioning column. If there is more than one constraint, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view.<br><br></li><li>There are no restrictions on the updatability of the partitioning columns. </li></ul><p>A partitioned column that meets all these rules will support all of the optimizations that are supported by the SQL Server 2000 query optimizer. For more information, see <a href="javascript:hhobj_3.Click()">Resolving Distributed Partitioned Views</a>.</p><h5>Data Modification Rules</h5><p>In addition to the rules defined for updatable partitioned views, data modification statements referencing the view must adhere to the rules defined for INSERT, UPDATE and DELETE statements.</p><p class="indent"><!--NOTE--></p><img src="http://sql-server-performance.com/Community/forums/mk:mad:MSITStore:C:%5CProgram%20Files%5CMicrosoft%20SQL%20Server%5C80%5CTools%5CBooks%5Ccreatedb.chm::/Basics/note.gif" style="margin-left: 0em;" alt="" border="0" height="11" width="12"><p style="margin-top: -1.1em; margin-left: 1.5em;"><b>Note</b>&nbsp;&nbsp;You can modify data through a partitioned view only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft. SQL Server 2000 Developer Edition.</p><!--/NOTE--><h6>INSERT Statements</h6><p>INSERT statements add data to the member tables through the partitioned view. The INSERT statements must adhere to these rules: </p><ul type="disc"><li>All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.<br><br></li><li>The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.<br><br></li><li>INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.<br><br></li><li>INSERT statements are not allowed if a member table contains a column with an identity property.<br><br></li><li>INSERT statements are not allowed if a member table contains a <b>timestamp</b> column.<br><br></li><li>INSERT statements are not allowed if there is a self-join with the same view or any of the member table. </li></ul><h6>UPDATE Statements</h6><p>UPDATE statements modify data in one or more of the member tables through the partitioned view. The UPDATE statements must adhere to these rules: </p><ul type="disc"><li>UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table<br><br></li><li>The value of a column with an identity property cannot be changed: however, the other columns can be updated.<br><br></li><li>The value of a PRIMARY KEY cannot be changed if the column contains <b>text, image </b>or <b>ntext</b> data.<br><br></li><li>Updates are not allowed if a base table contains a <b>timestamp</b> column.<br><br></li><li>Updates are not allowed if there is a self-join with the same view or any of the member tables.<br><br></li><li>The DEFAULT keyword cannot be specified in the SET clause of the UPDATE statement. </li></ul><h6>DELETE Statements</h6><p>DELETE statements remove data in one or more of the member tables through the partitioned view. The DELETE statements must adhere to this rule: </p><ul type="disc"><li>DELETE statements are not allowed if there is a self-join with the same view or any of the member tables. </li></ul><h5>Distributed Partition View Rules</h5><p>In addition to the rules defined for partitioned views, distributed (remote) partition views have these additional conditions: </p><ul type="disc"><li>A distributed transaction will be started to ensure atomicity across all nodes affected by the update.<br><br></li><li>The XACT_ABORT SET option must be set to ON.<br><br></li><li><b>smallmoney</b> and <b>smalldatetime</b> columns in remote tables are mapped as <b>money</b> and <b>datetime</b> respectively. Consequently, the corresponding columns in the local tables should also be <b>money</b> and <b>datetime</b>.<br><br></li><li>Any linked server cannot be a loopback linked server, that is, a linked server that points to the same instance of SQL Server. </li></ul><p>A view that references partitioned tables without following all these rules may still be updatable if there is an INSTEAD OF trigger on the view. The query optimizer, however, may not always be able to build execution plans for a view with an INSTEAD OF trigger that are as efficient as the plans for a partitioned view that follows all of the rules.</p><!--RELATEDTOPICSLIST--><p class="clear-graphic"></p><h5><b>See Also</b></h5><span id="LinKs"><p><a href="javascript:hhobj_4.Click()">CREATE VIEW</a></p><p><a href="javascript:hhobj_5.Click()">Designing Partitions</a></p><p><a href="http://sql-server-performance.com/Community/forums/cm_8_des_06_9mlv.htm">Scenarios for Using Views</a></p><p><a href="javascript:hhobj_6.Click()">Using Partitioned Views</a></p><p>&nbsp;</p><p>http://www.sql-server-performance.com/faq/table_partitioning_p1.aspx&nbsp;</p></span><p>&nbsp;</p>

Share This Page