December 29, 2003 at 9:37 am
An Oracle DB can be divided into semi-independent 'table spaces', where one space contains several tables. Is there an equivalent capability in SQL Server 2000?
December 29, 2003 at 9:46 am
There are partitioned views, distributed across servers, which seem like a similar thing. Not as mature in SS2K.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 29, 2003 at 8:15 pm
Actually, (and this is from an Oracle guy), the equivalent functionality arrived in SQL Server 7 with filegroups, which are logical storage units with a given database and can consist of one or several data files. (A datafile can be a member of only one filegroup).
Using filegroups, you can achieve the same goal as Oracle users do with tablespaces, namely separating commonly-joined tables onto separate disks by creating them in separate filegroups whose constituent data files are located on separate disks.
The partitioned views mentioned by Steve Jones actually simulate a different feature of Oracle, namely partitioned tables. A partitioned table is a single logical object that is stored across one or more tablespaces using a partitioning algorithm. Probably the simplest example is data warehousing, in which a single table can be range-partitioned by month. As each month's worth of data is loaded into the table, it is stored into a different tablespace dictated by the partitioning instructions, thus spreading the data out over potentially hundreds of disk spindles.
The great value of partitioned tables (which is only partially realized by SQL Server's partitioned views) is that when your query includes a where clause that matches the table's partition clause ( i.e. MONTH='03' in the above example), then the query optimizer is smart enough to ignore all of the table's partitions that cannot logically contain rows targeted by the query. Thus, with intelligent partitioning, multi-million row tables can be queried as if they contained a mere fraction of the data they actually represent, delivering phenomenally-improved performance.
December 29, 2003 at 8:24 pm
Very good discription of files and filegroups Adrian. Often multiple files and filegroups are used to gain maximum performance from hardware and SQL server.
Wes
December 31, 2003 at 10:27 am
They can increase performance, but at an administration cost. In my working with DB2, one of the huge advanatages of SQL Server is the simplicity. there is less baby sitting required.
Not that it's a better or worse product, jsut a different thing.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply