Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wiliambalan
Starting Member
2 Posts |
 Posted - 2014-10-06 : 13:03:21
|
| Hello I am new on SQL SERVER development and would like to know if you have a resource that I've used in other DBMS (Oracle).The feature is Clustered Tables "Together", see the scenario below:DEPT table: ID_DEPT NAME_DEPT 10 Contabil 20 FinancialEMP table: ID_DEPT ID_EMP NAME SALARY 10 1 FULANO 100.00 10 2 BELTRANO 200.00 20 3 CICLANO 300.00 20 4 TEOBALDO 400.00 20 5 Marinette 500.00 If these two tables are clustered, that is physically stored together in the data blocks would be thus: Block 1: 10 Department FULANO 1 100.00 2 200.00 BELTRANO Block 2: 20 3 department CICLANO TEOBALDO 300.00 4 400.00 5 500.00 GretelIn clustering does not duplicate the keys for storage, or stored only be ID_DEPT.Another point, which would be a disadvantage of clustering would be the DML command. When we INSERTs into the table, the DBMS can not just do as you normally do and inserting data in the first blank of the first block to see ahead, he HAS to analyze the key and put the data being inserted into the exact block to which it belongs . If you have no space then the DBMS will have to move data to other blocks in order to free up space and insert a NEW given the exact sequence where the key is.This solution is only for tables that are accessed together and not suffering almost never UPDATS OR INSERTS.It looks bad for OLTP environments, but for Datawarehouse would be interesting because the data loads are not updated daily and is from time to time.Needed to know if in SQL SERVER to implement this, does anyone have any tips?[] 's Wiliam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-10-06 : 13:39:13
|
| This feature does not exist in SQL Server. You can add a clustered index to each table, but you can't cluster them together.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-10-07 : 12:36:20
|
| To confirm for you, tkizer is 100% right.You could put both of them into a single filegroup with no other tables, which should give you some I/O benefits, but it's not nearly the extent of combined tables as in Oracle. |
|
|
|
|
|
|
|
|
Clustered Tables