Partitioning in SQL server

Partitioning in SQL server with small sample:




Summary: Table partitioning can make very large tables and indexes easier to manage, and improve the performance of appropriately filtered queries.

Table Partitioning Components:


The partition function, partition scheme, and partitioned table or index form a dependency tree, with the partition function at the top, the partition scheme depending on the partition function, and then the partitioned table or index depending on being linked to a partition scheme.

Partition function:

The partition function defines the boundary values of the initial set of partitions and the data type of the partitioned column:

o It makes no reference to any tables or disk storage.
o It forms the basis for one or more partition schemes.

create PARTITION function PF_dept(int)
AS range right FOR values (10,20,30)

Partition scheme:

The partition scheme maps particular partitions to filegroups:

o A given partition scheme can be used for one or more partitioned tables, indexes, and indexed views.

create partition scheme ps_dept
as partition PF_dept
TO (FG1,FG2,FG3,FG4)

Partitioned table:

The partitioned table or index is tied to a particular partition scheme when it is created:

o The partition table has only an indirect relationship, through the partition scheme, to the partition function.

create table dept
(deptid int,
deptname varchar(10)
)
ON ps_dept(deptid)


CREATE TABLE EMP
(EMPID INT,
EMPNAME VARCHAR(10),
DEPT_ID INT)
ON PS_DEPT(DEPT_ID)

Read More

WITH TIES

WITH TIES in SQL server

Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.


create table sales( customer_name varchar(10), sales_amount int)

insert into sales values
('A',6000),
('B',6000),
('C',10000),
('D',12000),
('E',13000),
('F',15000),
('G',5000),
('H',4000)

Top 5:

The “Top 5” gives first 5 records based on the condition in query.

select top 5 * from sales
order by sales_amount desc

customer_name sales_amount
------------- ------------
F 15000
E 13000
D 12000
C 10000
B 6000

(5 row(s) affected)

Top 5 WITH TIES:

The “Top 5 WITH TIES” gives 6 records because the base result set with the same value in the ORDER BY columns appearing as the last of the TOP 5.

select top 5 WITH TIES * from sales
order by sales_amount desc

customer_name sales_amount
------------- ------------
F 15000
E 13000
D 12000
C 10000
A 6000
B 6000

(6 row(s) affected)

Read More