rapid-DEV.net

Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.

Syntax

ALTER DATABASE database_name ADD FILEGROUP file_group_name

Example

ALTER DATABASE mdbSales ADD FILEGROUP [FG1]

After files and filegroups have been created, you are ready to define the partition function and the partition scheme. To verify your files and filegroups, use sp_helpfile and sp_helpfilegroup, respectively.

USE your_database_name
go

sp_helpfilegroup
exec sp_helpfile
go

here we go …

UPDATE table1
SET a1=a2,b1=b2,c1=c2
FROM table1 INNER JOIN table2 WHERE table1.id = table2.id

The logic is

a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file

In the SQL Server Management Studio

1. Highlight the database-> Tasks->Detach..-> Click OK
2. Rename the _log.ldf to be like _log.ldf–,
3. Highlight Databases->Attach -> Click Add -> add the database, highlight the log file and click the Remove button. This means you only attach .mdf
4. After this is done, you can verify the contents of the attached database and then delete the log file.

Example: create a function to get total UnitPrice of a particular OrderID from Northwind2 database

use Northwind2
go
create function getOrderTotalPrice
(
	@OrderId int
)
return int

as
begin 

declare @TotalPrice int
set @TotalPrice = 0

select @TotalPrice sum(UnitPrice) [Order Details] where OrderId = @OrderId

return @TotalPrice 

end

Create a new stored procedure to get Order Detail from Northwind2 database

create proc selOrderDetail
(
	@OrderId int
)
as
select OrderID, dbo.getOrderTotalPrice(OrderID) as TotalPrice, OrderDate, RequiredDate, ShippedDate, ShipName, ShipAddress, ShipCity,
ShipRegion, ShipPostalCode, ShipCountry
from Orders where OrderId = @OrderId

Usage: exec stored procedure selOrderDetail which takes OrderId as input parameter

exec selOrderDetail 10248