CREATING FILE GROUPS

hello  guys........
simple syntax for making of file groups in sql

create database test on primary
(NAME=TESTFG, FILENAME='D:\TEST.MDF', SIZE=500MB,MAXSIZE=700, FILEGROWTTH=20)



ALTER DATABASE TEST ON FILEGROUP TEST1
(NAME=TESTPTIMARY, FILENAME='D:\NDF', SIZE=700MB, MAXSIZE=900, FILEGROWTH=20)

V I SIVA RAO: Auto Updation--Merge Statement

V I SIVA RAO: Auto Updation--Merge Statement: The syntax is here:- merge itemorders as d using (select itemid,sum(b.qtyissued)as issuedqty from sales a, salesdetails b where a.orderno=...

Auto Updation--Merge Statement

The syntax is here:-
merge itemorders as d using (select itemid,sum(b.qtyissued)as issuedqty from sales a, salesdetails b where
a.orderno=b.orderno and a.orderid=11 and dbo.dateonly(a.orderdate)>'2011/12/1' group by itemid)c
on d.itemid=c.itemid
when matched and d.qty-c.orderqty<=0
then delete
when matched
then update set d.qty=d.qty-c.orderty
output $action,inserted.qty,inserted.itemid,deleted.qty,deleted.itemid;







-------------------
It will automatically update the stock of Itemorders Table

merge statement

syntax here:-

merge itemorders as d using (select itemid,sum(b.qtyissued)as issuedqty from sales a, salesdetails b where
a.orderno=b.orderno and a.orderid=11 and dbo.dateonly(a.orderdate)>'2011/12/1' group by itemid)c
on d.itemid=c.itemid
when matched and d.qty-c.orderqty<=0
then delete
when matched
then update set d.qty=d.qty-c.orderty
output $action,inserted.qty,inserted.itemid,deleted.qty,deleted.itemid;

how to know index fragmentation?

 pre requisites:-

create table fragmentation (schemaname varchar(250), tablename varchar(50),indexname varchar(50),fragmentation float)


syntax is here:-




declare @schemaname varchar(250),
@tablename varchar(250),
@indexname varchar(250),
@fragmentation float
begin
declare fragmentation cursor local for select schema_name(cast(objectpropertyex(a.object_id,'schema_id')as int)),object_name(a.object_id),a.name, b.avg_fragmentation_in_percent FROM sys.indexes a join sys.dm_db_index_physical_stats(db_id(),null,null,null,null) b on a.object_id=b.object_id and a.index_id=b.index_id where avg_fragmentation_in_percent>30
open fragmentation
fetch next from fragmentation into @schemaname,@tablename,@indexname,@fragmentation
while @@FETCH_STATUS=0
begin

insert into fragmentation values (@schemaname,@tablename,@indexname,@fragmentation)
fetch next from fragmentation into @schemaname,@tablename,@indexname,@fragmentation

end

end
close fragmentation
deallocate fragmentation
select * from fragmentation