How to manage partition

Partition are useful function for managing large data in database. I have posted an article on how to create partition. Now we will look into how to manage partitions.

First we will create partition as given in my past article.


USE bi

CREATE PARTITION FUNCTION part_test_func(datetime) AS range LEFT FOR VALUES ('2008-09-10', '2008-09-20', '2008-09-30')

/* so it has range values like

<= '2008-09-10' > '2008-09-10 and <= '2008-09-20' > '2008-09-20 and <= '2008-09-30' >'2008-09-30'

*/

/* Now we need to map file group to each of this partition

Here first , second and third are filegroups */

CREATE PARTITION scheme part_test_scheme AS PARTITION part_test_func TO(FIRST, SECOND, third, FIRST)

/*Now we will cretae a table on this partition scheme */

CREATE TABLE part_test

(

id INT IDENTITY(1, 1),

data VARCHAR(100),

DATE DATETIME

)

ON part_test_scheme(DATE)

INSERT INTO part_test

VALUES ('a',

'2008-09-05')

INSERT INTO part_test

VALUES ('a',

'2008-09-15')

INSERT INTO part_test

VALUES ('a',

'2008-09-25')

INSERT INTO part_test

VALUES ('a',

'2008-10-05')

CREATE TABLE part_test_archive

(

id INT IDENTITY(1, 1),

data VARCHAR(100),

DATE DATETIME

)

ON part_test_scheme(DATE)

--Now as part of maintenance we want to move all data of september to archive table.

--We need to switch partition for range between '2008-09-01' and '2009-08-30'

DECLARE @partition_no_start INT

DECLARE @partition_no_last INT

SET @partition_no_start = bi.$partition.Part_test_func('2008-09-01')

SET @partition_no_last = bi.$partition.Part_test_func('2008-09-30')

SELECT @partition_no_start,

@partition_no_last

WHILE @partition_no_start <= @partition_no_last

BEGIN

Alter TABLE part_test switch PARTITION @partition_no_start TO part_test_archive PARTITION @partition_no_start

SET @partition_no_start = @partition_no_start + 1

END

--Lets verify movement of data

SELECT Min(DATE),

Max(DATE)

FROM part_test_archive

SELECT Min(DATE),

Max(DATE)

FROM part_test


Like- SQL Server

Recently I got a query on how to use like clause. As we all know we can use to find word with pattern matching.
Here we will look a sample for it.


CREATE TABLE user_id_list (

user_id VARCHAR(256))

/*If you are using SQL Server 2008*/

INSERT INTO user_id_list

VALUES ('amish'),

('amish123'),

('amish.shah'),

('123456'),

('123amish')

/*If you are using SQL Server 2005*/

INSERT INTO user_id_list

SELECT 'amish'

UNION ALL

SELECT 'amish123'

UNION ALL

SELECT 'amish.shah'

UNION ALL

SELECT '123456'

UNION ALL

SELECT '123amish'

-- To get ID which has only alphabet in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z]%'

-- To get ID which has only alphabet and '.' in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,.]%'

-- To get ID which has only alphabet and numbers in it

SELECT user_id

FROM user_id_list

WHERE user_id NOT LIKE '%[^a-z,0-9]%'


Except command -SQL Server 2005

Except command can be used when we want rows from table1 which are not in table2.
This command is supported in SQL 2005 and later versions.
Let see an example for this


CREATE TABLE #temp (

id INT,

data VARCHAR(100),

code INT)

INSERT INTO #temp

VALUES (1,'a',1)

INSERT INTO #temp

VALUES (1,'b',2)

INSERT INTO #temp

VALUES (2,'a',1)

INSERT INTO #temp

VALUES (2,'b',2)

CREATE TABLE #temp1 (

id INT,

data VARCHAR(100),

code INT)

INSERT INTO #temp1

VALUES (1,'a',1)

INSERT INTO #temp1

VALUES (1,'b',2)

INSERT INTO #temp1

VALUES (3,'a',1)

INSERT INTO #temp1

VALUES (3,'b',2)


Now we want rows from #temp which are not in #temp1

In SQL 2000 we can do this by matching all columns


SELECT *

FROM #temp t

WHERE NOT EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data

AND t.code = t1.code)


In SQL 2005 we can do it easily by except


SELECT *

FROM #temp

EXCEPT

SELECT *

FROM #temp1


Interesect Command(SQL Server 2005)

One of new feature of SQL Server 2005 is except and intersect commands.
Lets look how it can be useful.


CREATE TABLE #temp (

id INT,

data VARCHAR(100))



INSERT INTO #temp

VALUES (1,'a')

INSERT INTO #temp

VALUES (1,'b')

INSERT INTO #temp

VALUES (2,'a')

INSERT INTO #temp

VALUES (2,'b')

CREATE TABLE #temp1 (

id INT,

data VARCHAR(100))

INSERT INTO #temp1

VALUES (1,'a')

INSERT INTO #temp1

VALUES (1,'b')

INSERT INTO #temp1

VALUES (3,'a')

INSERT INTO #temp1

VALUES (3,'b')


Now we want matching row from #temp and #temp1

In SQL 2000 we can do this by this way


SELECT *

FROM #temp t

WHERE EXISTS (SELECT *

FROM #temp1 t1

WHERE t.id = t1.id

AND t.data = t1.data)


In SQL 2005 we can do it easily by intersect


SELECT *

FROM #temp

INTERSECT

SELECT *

FROM #temp1


Table Value Constructors

Table Value Constructors for Insert Statement

CREATE TABLE test (

id INT,

name VARCHAR(100))


To insert data into this table here is the standard way.

INSERT INTO test
VALUES (1,‘id1’)

INSERT INTO test
VALUES (2,‘id2’)

INSERT INTO test
VALUES (3,‘id3’)

If you are fan of union all you can try this one also.

INSERT INTO test

SELECT 1,‘id1’
UNION ALL
SELECT 2,‘id2’
UNION ALL
SELECT 3,‘id3’

IN SQL 2008 using Table Value Constructor you can write it more easily with less code.
When number of rows will grow , we will feel benefit of it because of less code.

INSERT INTO test

VALUES (1,‘id1’),
(2,‘id2’),
(3,‘id3’)

Even you can use it to create temporary derived table/CTE as shown below.

SELECT *
FROM (VALUES (1,‘id1’),
(2,‘id2’),
(3,‘id3’)
) testable(id,name)

Note:- You can not insert more than 1000 rows using this method in a single insert command.