Thursday, May 26, 2016

คนค้นคนตอน พฤติกรรมของ Left Join

RDBMS: MSSQL2008 R2

เวลาเราทำการ Left/Right Join ร่วมกับ Where Condition เราอาจจะเจอปัญหาที่ทำให้เรางงกับวิธีการประมวลผลของคิวรี
ลองดูตัวอย่างข้างล่าง

1. สร้างตารางทดสอบชื่อ DEPT กับ EMP ด้วยสคริปต์ข้างล่าง EMP.DEPT_ID เป็น FK ที่ชี้ไปหา DEPT.DEPT_ID

create table dept (dept_id int,dept_name varchar(20))
create table emp (emp_id int,emp_name varchar(20), dept_id int)

insert into dept values (10,'IT')
insert into dept values (20,'FINANCE')
insert into dept values (30,'HR')

insert into emp values (1,'SOMCHAI',10)
insert into emp values (2,'TANAKORN',20)
insert into emp values (3,'PETER',30)
insert into emp values (4,'PHAIBOON',null)
insert into emp values (5,'SUKWAN',null)

2. ถ้าเรา Left Join สองตารางนี้ปกติ
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id 



3. และถ้าเราเพิ่ม Condition ลงในส่วน On ของ Join
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id and e.emp_name = 'PETER'




4. ลองเปลี่ยน Condition จากบน On มาเป็น Where
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id
where e.emp_name = 'PETER'


ผลที่ได้จากการรันคิวรีหลายตัวข้างบนแสดงให้เห็นว่ากรณีการทำ Outer Join การใช้เงื่อนไขบนส่วนที่เป็น On กับ Where Clause นั้นให้ผลต่างกันซึ่งอธิบายได้ดังนี้

1. Left Join กับ Condition ใน ON
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id and e.emp_name = 'PETER'

ความหมายของคิวรีข้างบนคือ
1. ให้ Left Join โดยเอา EMP เป็น Base (ตัวตั้ง) เพราะฉะนั้นจะ Return ทุก ๆ Rows ของ EMP
2. เอา DEPT มา Left Join โดยใช้ DEPT_ID และเอาเฉพาะ Rows ที่มีค่า EMP_NAME เป็น 'PETER' มา Join เท่านั้น ตัวที่ไม่ตรงตามเงื่อนไขนี้ให้ถือว่า Join ไม่เจอกับตาราง EMP
3. การที่ EMP Left Join กับ DEPT ถ้าแถวใด ๆ ของ DEPT ไม่สามารถ Map กับเงื่อนไขใน On ได้ ก็จะถือว่า Map ไม่เจอดังนั้นคอลัมน์ใด ๆ ที่เป็นของ DEPT ในผลการ Join จึงเป็น NULL


2. Left Join กับ Condition ใน WHERE
select * 
from emp e
left join dept d 
on d.dept_id = e.dept_id
where e.emp_name = 'PETER'

ความหมายของคิวรีข้างบนคือ
1. ให้ Left Join โดยเอา EMP เป็น Base (ตัวตั้ง) เพราะฉะนั้นจะ Return ทุก ๆ Rows ของ EMP
2. เอา DEPT มา Left Join โดยใช้ DEPT_ID แถวที่ไม่สามารถ Map กันระหว่าง EMP กับ DEPT ได้ คอลัมน์ของ DEPT จะเป็น NULL (เป็นลักษณะของ Outer Join โดยปกติ)
3. เอาผลที่ได้จากข้อ 2 มาตัดเอาเฉพาะ Row ที่มีค่า EMP_NAME เป็น PETER

Sunday, February 14, 2016

การใช้ Roles บน MSSQL

RDBMS: MSSQL2008 R2

ใน SQLServer มีการใช้ Role เช่นเดียวกับ Oracle 

1. สร้าง Role

use test
create role test_exec_role

2. กำหนดสิทธิ์ให้กับ Role ข้างล่างนี้เป็นการกำหนดสิทธิ์ในการ Execute Stored Procedure ให้กับ Role ที่เราสร้างไว้ เราสามารถสร้าง Script ได้โดยใช้ SQL Statement เพื่อช่วยให้ไม่ต้องเขียนทีละ Statement

use test 
select 'grant exec on dbo.'+name+' to  test_exec_role' from sys.procedures

นำผลที่ได้ มารันเพื่อ Grant สิทธิ์ให้กับ Role

use test
grant exec on dbo.sp_procedure1 to  test_exec_role
grant exec on dbo.sp_procedure2 to  test_exec_role
grant exec on dbo.sp_procedure3 to  test_exec_role


3. สร้าง Users
สร้าง Login ใน SQLServer การจะสร้าง User จะต้องสร้างทั้ง Login ซึ่งเป็น User ที่ใช้ในการ Login เข้า Instance หลังจากนั้นจะต้องสร้าง Database User อีกทีหนึ่งเพื่อให้สามารถเข้า Database ตัวที่ต้องการได้ คำสั่งข้างล่างกำหนดให้ Login ไม่มีวันหมดอายุ และไม่กำหนดเงื่อนไขใด ๆ ในการสร้าง User

use [master]
create login [testuser] with password=N'testpwd123', default_database=[test], check_expiration=OFF, check_policy=OFF

สร้าง DB User

use [test]
create user [testuser] for login [testuser] with default_schema=[dbo]

การลบ Login และ DB User
use [master]
drop login [testuser]

use [test]
drop user [testuser]

4. Grant Role ที่เราสร้างไว้ให้กับ User

use [test]
exec sp_addrolemember 'test_exec_role', [testuser]

5. Grant Role เพิ่มเติม Role, เหล่านี้เป็น Role ที่มีอยู่ใน MSSQL อยู่แล้ว โดย db_reader เป็นการ Grant สิทธิ์อ่านอย่างเดียว และ db_reader เป็นสิทธิ์ให้เขียนอย่างเดียว

use [test]
exec sp_addrolemember 'db_datareader', [testuser]
exec sp_addrolemember 'db_datawriter', [testuser]