Monday, June 28, 2010

creating the composite index with more than 900 bytes



Creating the composite index with more than 900 bytes:
Sql server is having some constraints while creating the index. The index entry will not allow if the data size is more than 900 bytes. It gives the error while inserting the data.

There is a way to insert the more than 900 bytes into composite index. we see that in this article.

Intial setup:

Creating the table with two columns and total size is more than 900 bytes.

use tempdb
create table tmp
(
c1 varchar(10),
c2 varchar(1000)
)


Index creation:

The create index statement gives the warnings. Because total size is havng more than 900 bytes.

create index idx_tmp on tmp(c1,c2)

Warning! The maximum key length is 900 bytes. The index 'idx_tmp' has maximum length of 1010 bytes. For some combination of large values, the insert/update operation will fail.

Test data Length:
select len('

test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
')





Insert statement with more than 900 bytes:

The toltal size of the insert is 1010 bytes here. It is not allowing to insert and giving the error.


insert into tmp values( 'hai',
'test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data'
)

INCLUDE clause:

There is another way to avoid this error and create a index on both the columns.

drop index idx_tmp on tmp

By using the INCLUDE clause of the CREATE INDEX statement, the index key could be defined as (c1) and c2 defined as a nonkey column. In this way, the index key size would be 10 bytes.


CREATE INDEX idx_tmp2
ON tmp (c1)
INCLUDE (c2);

Insert statement with more than 900 bytes:

The toltal size of the insert is 1010 bytes here. This time it is allowing to insert

insert into tmp values( 'hai',
'test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data
test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data test_data'
)



The index has included both the columns. The below query proves that.

select b.name index_name,a.name column_name from sys.columns a
inner join sys.indexes b
on a.object_id=b.object_id
where b.name ='idx_tmp2'





Addition to that we verify the explain plan. It goes for the index scan.
select * from tmp where c1='10'


select * from tmp where c2='10'



Tuesday, June 22, 2010

Good to Know in SQL server 2008

Good to Know in SQL server 2008

1. Insert values:

Many times we use “INSERT INTO tab_name VALUES (….) “way for inserting the data into table. There is a simple way in the SQL server 2008 to avoid multiple INSERT clauses for more than one record. It is possible in single statement.


use tempdb

Create table Dept(Id int, DName varchar(100))

Insert into Dept
values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D')

select * from Dept




2.Declare and initiate in same lines:

Old style ( SQL 2000 and 2005) :

declare @i int
set @i = 10
declare @dt datetime
set @dt = GETDATE()
select @i as Col1, @dt as Col2




New Style (SQL server2008):

declare @i int = 10
declare @dt datetime = getdate()
select @i as Col1, @dt col2
go

Monday, June 21, 2010

Pivot in SQL server

PIVOT:

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

CREATE TABLE Sales (Make varchar(8), Year int, Sales int)
GO
Insert into Sales values ('Honda',2005,20000)
Insert into Sales values ('Honda',2005,60000)
Insert into Sales values ('Mahindra',2005,40000)
Insert into Sales values ('Honda',2006,30000)
Insert into Sales values ('Mahindra',2006,30000)
Insert into Sales values ('Mahindra',2006,60000)
Insert into Sales values ('Mahindra',2007,8000)
GO

Actual Group by, get Number of cars sold for each make in each year.
Select Make,YEAR,SUM(Sales) from Sales
group by Make,Year

Transposed of above query

Select * from Sales pivot(Sum(sales) for Year in ([2001],[2002],[2003],[2004],[2005],[2006],[2007])) t
go


Restricting the columns which we need to show.

Select * from Sales pivot(Sum(sales) for Year in ([2005],[2006],[2007])) t



List sum of each product sold for each year and product should have been sold all the years and not just 1 year.

Select * from Sales pivot(Sum(sales) for Year in ([2005],[2006],[2007])) t
where [2007] is not null


Thursday, June 10, 2010

Difference in Correlated Update

Difference in Correlated Update :



I came across the Correlated update statement with below format. It updates the address filed in the “Main” table and takes it from the “sub” table. The NVL function has given outside the sub query.

Outer NVL Query:
update main m
set address = nvl((select address from sub s where m.id=s.id),-1);

We can give the NVL function with the sub query (specifically for that column). Then what is the difference between “Outer NVL Query” and “Inner NVL Query”.
Inner NVL Query:
update main m
set address = (select nvl(address,-1) from sub s where m.id=s.id)

Initial setup:
This step creates the tables and populates it.

create table main ( id number, Address varchar2(10));

insert into main values (1,'NNNNNNN');
insert into main values (2,'NNNNNNN');
insert into main values (3,'NNNNNNN');
insert into main values (4,'NNNNNNN');

create table sub ( id number, Address varchar2(10));

insert into sub values (1,'A');
insert into sub values (2,null);
insert into sub values (4,'C');

select * from main;

select * from sub

Inner NVL Query:

update main m
set address = (select nvl(address,-1) from sub s where m.id=s.id);

It is applying the NVL function if NULL value comes from the “SUB” table. It is putting the NULL value in the “Main” table if it does not find the match in the “Sub” table.

We think that the outer NVL query works in the same way. But there is some difference.
Outer NVL Query:
update main m
set address = nvl((select address from sub s where m.id=s.id),-1)

We can see the difference in below result. It is applying the NVL function in the both the situation.

This is small Difference in the Correlated Update.

Tuesday, June 8, 2010

Shell script to find the latest file

The below shell script will help to find the latest file and name starting with “sales”.

Example the below files are available in UNIX box.
sales_2007_02_23_0010.dat
sales_2007_02_24_0024.dat
sales_2007_02_24_0714.dat
sales_2007_02_25_0010.dat
sales_2007_02_25_0525.dat
sales_2007_02_25_0835.dat

There are 3 files on 25th with different time stamp. The aim is identify the latest file. So the script check the latest file based on the time when that file has created in the UNIX box.

Script : latest_file.sh

#!/usr/bin/ksh
rm -f /home/hb/ms/hb/karthi/sales.dat
val=`ls -lrt sales* tail -1 awk '{print $9}'`
echo $val
ls /home/hb/ms/hb/karthi/$val
cp /home/hb/ms/hb/karthi/$val /home/hb/ms/hb/karthi/sales.dat

Monday, June 7, 2010

Cumulative Sum

Ways to get a Cumulative Sum:

Initial setup:
Creating the table and populating the table.

CREATE TABLE #Sales (DayCount smallint, Sales money, RunningTotal money)


INSERT INTO #Sales (DayCount, Sales, RunningTotal)
SELECT 1, 10, 0
union all
SELECT 2, 7, 0
union all
SELECT 3, 2, 0
union all
SELECT 4, 6, 0

GO

SELECT * FROM #Sales ORDER BY DayCount

GO





Method 1:
DECLARE @RunningTotal money
SET @RunningTotal = 0

UPDATE #Sales
SET @RunningTotal = RunningTotal = @RunningTotal + Sales

GO

SELECT * FROM #Sales ORDER BY DayCount

GO




Method 2:

SELECT
a.DayCount
, a.Sales
, SUM(b.Sales) AS 'Running Total'
FROM
#Sales a
JOIN #Sales b ON b.DayCount <= a.DayCount GROUP BY a.DayCount , a.Sales ORDER BY a.DayCount , a.Sales

Wednesday, June 2, 2010

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)