New Sql Server 2008 Query environment

There's something new in sql server 2008 query environment.

See the pic below


it's just like you make a code at Visual studio environment. If you specify which table you will select than the columns name in the table will be display. No need to worry that you will write wrong columns name.

Only want to share this one for the moment. wait for my next explore of Sql server 2008.

Have a nice day.

row_number function

a new function in sql server 2005. It's function returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

syntax :

ROW_NUMBER ( ) OVER ( [<partition_by_clause>] <order_by_clause> )

example :

SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0

(source : Sql server documentation )

great, doesn't it ?

have a try.

Have a nice day.

Pivot Table in Sql Server 2005 ( Part 2 )

Here my logic to make the field more fleksible. So you don't have to fix the field. Of course this method used when you need to make your report fleksible and don't need to add the new field if there's a new data inserted into master table.

declare @field varchar(max)
declare @select varchar(max)
declare @employeeid int

set @field = ''

declare cr cursor for
select employeeid
from HumanResources.Employee

open cr

fetch next from cr into @employeeid

while @@fetch_status = 0
begin
if @field <> ''
begin
set @field = @field + ','
end

set @field = @field + '[' + convert(varchar(max),@employeeid ) + ']'

fetch next from cr into @employeeid
end

close cr
deallocate cr

set @select =
'SELECT VendorID, ' + @field + '
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @field + ' )
) AS pvt ORDER BY VendorID;'

exec (@select)

Have a try. Ask if you still confuse.

Have a nice day.

Pivot Table in Sql Server 2005

New but not new since sql server 2008 have been launch. I will share a little bit about it.

Here's the sample query from Sql Server 2005 Documentation:

USE AdventureWorks
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID;

here is the result :

VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5

if your database was upgraded from sql 2000 to sql 2005 , then you may set the compatiblel level to 90 to make the feature availeble.

sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ]

example : sp_dbcmptlevel 'databasename', '90

remember : if you use pivot features you have to :
1. one column as the value to aggregate
2. one column as the column name . From the example , the column name would be employeeid.
Unfortunately, You have to define it.
I thought i can use

FOR EmployeeID IN
( select employeeid from HumanResources.Employee )

but it doesn't work, that will make my query more fleksible if it's work. I will find other way to make it. May be a little trik for it. I will share it on other post.
3. you are in sql 2005 - > there's must be obsolute.
4. the feature is on. use sp_dbcmplevel to change the level to 90.

How cool isn't it ? Have a try and have a nice day.

Mass Transfer Data

Here my tips for newbie in sql server to transfer massive data from table A to table B.

example using adventuresworks from sql server 2005 database sample.

select * into table1
from HumanResources.Employee
where employeeid between 1 and 20

i create table1 with the same field as HumanResources.Employee. Above query just an example.

--- use it if you need to update some field of records in table1 that may need to be updated if
-- there's exists on destination table.
update HumanResources.Employee set vacationhours = 24
from HumanResources.Employee a
inner join tabel1 b on a.employeeid=b.employeeid

-- here the main massive insert. the order of the field may the same as the destination table.
-- in case there's different, but some of the them have the same field than you need to add
-- (nmfield1, nmfield2 , .. before select statement ) before the select statement.
-- Its better if you defined the field that you want to insert

insert into tabel1
select a.*
from HumanResources.Employee a
left join tabel1 b on a.employeeid=b.employeeid
where b.employeeid is null

Have a try and good luck .

Happy New Years 2009

Using Default Parameter in Store Procedure

Sometime you need to create parameter with default value in your store procedure. Here is a simple example of how to make it:

create proc dbo.sp_getemployee
@employeeid char(10)= ''
as
select * from master_employee
where employeeid = case when @employeeid = '' then employeeid else @employeeid end

In here, i make a default value which is an empty string. I'm using it to manipulate the data that if the value is an empty string than return all employee data from employee table.

Now i can use it as below :

exec sp_getemployee

which is return all the record from master_employee

or

exec sp_getemployee @employeeid = '000000001'

which is return record with employeeid = '000000001'

How owsome isn't it ? but be carefule, it doesn't work if you create a report using Crystal report. Because, the crystal report will still ask to fill the value into the paramater.

Ok, now you know how to make default value into parameter in store procedure. Try it ok. Have a nice day

Store Procedure ( The Beginning )

Stored Procedure ( next i call it SP ) is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

Syntax

CREATE PROC [ EDURE ] procedure _name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
(source SQL Help documentation)

That's a brief description and syntax about Stored Procedure.

Simple sample code :

/*
created : 10-12-2008 by Wihendro
Note : here you explain about the procedure function and purpose
*/
Create Proc dbo.spr_Test
as
set nocount on

select * from orders

That's all the brief SP best on my experience.
The /*...*/ ( a comment )filled in SP use to help us to monitor the changes made by the programmer. It may help us to track back the purpose of the SP. In application development using sql server as database, it may contain a lot of SP. After a decade of the development, if you need to change your code, you may encounter some difficulty about the purpose of the SP. So, in my opinion, you should use a comment above your SP.

"set nocount on" syntax use to set the message from sql server to off. every single statement either success or failed to execute, the sql server may send the message as part of the result execution. I used set nocount on to set it off. despite of receiving it, you may hide it. because you don't need it from your SP.

Ok, that's all for this time. For whom that doesn't know the SP , at least now know how to create it. and for others, may use my tips to add the comment and "set nocount on" on your SP. Wait for my next post more about Stored Procedure.

Have a Nice Day

Return XML in Sql Server 2005

one of the feature in sql server 2005 is returning the query in XML format.

Syntax :
[ FOR { BROWSE | } ]
::=
XML
{ {
RAW [ ( 'ElementName' ) ] | AUTO }
[
[ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] }
] [ , ELEMENTS [ XSINIL | ABSENT ] ]
| EXPLICIT
[ [ , XMLDATA ]
]
| PATH
[ ( 'ElementName' ) ]
[

[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
Source: SQL Help Documentation

simple example :

select top 100 contactid, firstname, middlename, lastname
from Person.Contact
FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL

At .net application we only have to consume the xml result. How to do it, i will post it later because i still exploring it :D

Taxed Income Function: Case Indonesia

Base on UU PPh pasal 21 in Indonesia, every working citizen have to pay income tax with progressive calculation tariff. The detail of this UU PPh will not discuss in here. What i will try to discuss is how to implemented it into sql server function dan store the tariff into tabel.

Even the progressive calculation tariff may stay for along time, it may possible that the rate may change or the placement. Real case is, the change of PPh 21 before 2008 change from 5 placement into 4 placement on 2009. You can imagine how you have to change all your calculation in your salary system if you use "fix code" method in your calculation system.

This is my idea to make the function and store the tariff into table. I Hope it will help you to efficient your job.

Here is some step you need to consider:
1. The function is for progressive tariff policy
2. There is a table to store the rate. it will be explain later.
3. The calculation is for Taxed Income, i will used PKP as the variable.

Firstly, we create the table:

CREATE TABLE [dbo].[Master_Tarif_Pajak](
[Tahun] [char](4) NOT NULL,
[Tingkat] [int] NOT NULL,
[Lower] [decimal](18, 0) NULL,
[Upper] [decimal](18, 0) NULL,
[Pajak] [decimal](5, 2) NULL,
CONSTRAINT [PK_Master_Tarif_Pajak] PRIMARY KEY CLUSTERED
(
[Tahun] ASC,
[Tingkat] ASC
)ON [PRIMARY]
) ON [PRIMARY]

The Requirement is :
Entry the record from the first placement to the fifth and the last one the upper we leave it Zero
Your data my looks like this :

Tahun Tingkat Lower Upper Pajak
2008 1 0 25000000 0.05
2008 2 25000000 50000000 0.10
2008 3 50000000 100000000 0.15
2008 4 100000000 200000000 0.25
2008 5 200000000 0 0.35
2009 1 0 50000000 0.05
2009 2 50000000 250000000 0.15
2009 3 250000000 500000000 0.25
2009 4 500000000 0 0.30

(9 row(s) affected)

Next step is the function :

/*
created : 29-08-2008 by wihendro

*/
create function dbo.UDF_Calculate_Tax
(
@pkp money , @tahun char(4)
)
returns money
as
begin
declare @tingkat int , @pajak money, @lower money, @upper money, @pajak1thn money
, @terhitung money, @denda money

set @tingkat = 1
set @pajak1thn = 0
set @terhitung = 0

while exists( select tingkat from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat
)
begin
select @lower = [lower], @upper = [upper] , @pajak = pajak
from master_tarif_pajak with (nolock )
where stsrc = 'A' and tahun = @tahun and tingkat = @tingkat


if @pkp > @upper
begin
if @upper = 0
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung)*@pajak)
break
end
else if @lower is not null
begin
if (@terhitung + ( @upper - @terhitung )) <= @pkp begin set @pajak1thn = @pajak1thn + ((@upper - @terhitung)*@pajak) set @terhitung = @terhitung + (@upper - @terhitung ) end else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
end
else if (@terhitung + ( @upper - @terhitung )) > @pkp
begin
set @pajak1thn = @pajak1thn + ((@pkp - @terhitung)*@pajak)
set @terhitung = @terhitung + (@pkp - @terhitung)
break
end
else if @terhitung = @pkp
begin
break
end
end
else
begin
set @pajak1thn = @pajak1thn + ((@pkp-@terhitung) * @pajak )
break
end
set @tingkat = @tingkat + 1
set @lower = null
set @upper = null
end

return @pajak1thn
end

Example:

select Pajak = dbo.UDF_Calculate_Tax(200000000,'2008' )

Result :
Pajak
---------------------
36250000.00

(1 row(s) affected)

So, if next year, the role change into 4 placement ( see the sample data that i have created for you ), you only have to fill the tariff to the table and in your only have to send the value 2008 as the year. More effectively if you make an entry modul for the tariff table. So prepare your income tax calculation program to overcome the new UU Pph next year.

Note: You need to entry the new tariff every new begging year. Just don't forget to maintain the table ok.

Go to try it.
Good Luck.

User Defined Function

In every sql statement you made, sometime there's sql statement that you use over and over. In a big project scale, there's a good idea to put it into Store Procedure or Function, depends on your needs. This time, i will discuss about Function,use to be konwn as a User-Defined Functions ( UDFs ).

User-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set. UDFs divide into 3 types, which are Scalar Functions, Inline table-valued and multiline table-valued

1. Scalar Functions
Syntax:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS return_data_type [ WITH [ ,...n ] ] [ AS ]
BEGIN
function_body
RETURN scalar_expression
END [ ; ]
(Source : SQL Help Documentation)

sample :

create function dbo.udf_hitung_score
(
@nilai numeric
)returns char(11)
as
begin
declare @grade char(1)

set @grade = case when @nilai between 85 and 100 then 'A'
when @nilai between 70 and 84 then 'B'
when @nilai between 60 and 69 then 'C'
when @nilai between 50 and 59 then 'D'
else 'F' end
return(@grade)
end

Example :

select dbo.udf_hitung_score(82)

You can use it as follow :
example, you have Table A with attribut Mark contain Student Mark with Numeric data type, than your query may become like this :

select nama_mahasiswa, nama_matakuliah, kelas , grade = dbo.udf_hitung_score(nilai)
from A
order by kelas

2. Inline Table-Valued Functions
syntax:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ]
) RETURNS TABLE [ WITH [ ,...n ] ] [ AS ]

RETURN [ ( ] select_stmt [ ) ] [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_Master_customers
(
@customerid nchar(10)
) RETURNS TABLE
as
RETURN (select * from customers
where customerid = @customerid )

example :

select * from dbo.udf_Master_customers('Alfki')

you can join it to with others table.

3. Multistatement Table-valued Functions
Syntax :
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS @return_variable TABLE <> [ WITH [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END [ ; ]

(Source : SQL Help Documentation)

using Northwind as database sample :

CREATE FUNCTION dbo.udf_order
(
@customerid nchar(10)
) RETURNS @order TABLE (contactname varchar(60) ,orderid int, orderdate datetime
, requireddate datetime )
as
begin
declare @contactname varchar(60)

select @contactname = contactname
from customers where customerid = @customerid

insert @order
select @contactname , orderid, orderdate, requireddate
from orders
where customerid = @customerid

RETURN
end

example

select * from dbo.udf_order('alfki')

multistatement table-valued used if you need more logical programming to manipulate the data before displaying it.

Each type of UDFs has their own unique function depends on your needs in your project. But surely, it's very helpfull in your project later.

After this, i will try to collect some logic and make it as a function that may usefull for you.

Have a try and good luck.

Set nocount on

Anyone know what is "set nocount on" ?
Its function to set the message from query result to off.

example:

select * from tabelA

Usually, come out the message besides the results, such as below:

(xx row(s) affected)

You use "Set nocount on" to make the message disappears. Depends on your needs. Sometimes, you use it to decrease the server peformance, that after displaying the result of the query, it may give you the message around the query results. For your concern, the error message still be display.

Base on my experience, I use "set nocount on" in my Store Procedure.The purpose is to lighten my Store Procedure performance that it decreased the server works to give the message to its caller.

Can you imagine when your Store Procedure need to insert a lots of records one by one. It may result one message every success inserted record. Plus, other message if you have select queyr in your Store Procedure.

Have a try and i hope it will useful for you.

Have a nice day

Left vs Right Join in SQL Language

We know Left and Right Join in Sql Server. Do you know the differences between them ??
I will try to explain the difference between them and the tips from me in the usage.

Left Join, it use to join between 2 table which the left one will be display all records even there is no match record with the right one. Record with no match field partner will be display Null

The same for Right Join, we use it to join between 2 table and the right one will be display all records even there is no match record with the left one. Record with no match field partner will be display Null.

Sound difficult to understand with out the sample do you ?

Example:
Table A comes with Nim and Nama field ( have 3 records )
Tablel B comes with Nim and NamaMatakuliah ( have 5 records )

Select * from A left join B on a.nim=b.nim

The the results are minimum have 3 records plus the match record with table A

but when we change it into : Select * from A Right join B on a.nim=b.nim

then the results are minimum have 5 records plus the match record with table B.

Tips from me:
- Usually people much easier to compare between 2 table from left to right, So its better to use Left join rather than Right join.
- Before you create the query statement, make sure that the left table is the most records or it is the one that you want to keep it display the records even there is no match records in the join statement.

How is it ?There's a little bit understanding from my explain ?? Have a try, and ask if you still confuse about it.

Have a nice day.