http:paresh-sqldba.blospot.com

Working with Common Table Expression

Giving you example for Common Table expression (CTE) to get levels with members Hierarchy

Common syntax for CTE as following

--Syntax

With CTE_Name (Column_Name_1, Column_Name_2 … Column_Name_n )
As
(
cte_Anchor_Member_Query_Definition
UNION ALL or UNION EXCEPT OR INTERSECT
Cte_Recursion_Query_Definition (Must refer to CTE_Name)
)
--TSQL which uses the CTE

Below is the example id using CTE

WITH TreeCTE(ChildId, ParentId, level )
AS
(
-- Anchor Member
select col_ChildId,col_ParentId, 0 from TBL_Members where col_ParentId is null

UNION ALL
-- Recursive Member
select t.col_ChildId,t.col_ParentId, ct.level + 1 from TBL_Members t inner join
TreeCTE ct on ( t.col_ParentId= ct.ChildId)
)

-- Using the CTE
select * from TreeCTE order by level

By executing above query you can get result as following

From above example , first is the anchor statement where we fetch for root row
then using union all with join of CTE to get level and Hierarchy of every members.

You can see the anchor statement result

Example of indexed views

--Set the options to support indexed views.

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
WHERE od.SalesOrderID = o.SalesOrderID
GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1
ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev,
OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND ProductID BETWEEN 700 and 800
AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO


This example was taken from SQL Server online Help.

Backing up and Restoring the database with spiltting files to multiple disks

-- Backing up the database with spiltting files to multiple disks
BACKUP DATABASE SPLITFILEDISK TO
DISK = 'C:\BACKUPDIR\SPLITFILEDISK_1.BAK',
DISK = 'C:\BACKUPDIR\SPLITFILEDISK_2.BAK',
DISK = 'C:\BACKUPDIR\SPLITFILEDISK_3.BAK'


-- Feaching logical filenames from one of split backup
RESTORE FILELISTONLY
FROM DISK = 'C:\BACKUPDIR\SPLITFILEDISK_1.BAK'


-- Restoring database from multiple spilt files
RESTORE DATABASE SPLITFILEDISK_COPY FROM
DISK = 'C:\BACKUPDIR\SPLITFILEDISK_1.BAK',
DISK = 'C:\BACKUPDIR\SPLITFILEDISK_2.BAK',
DISK = 'C:\BACKUPDIR\SPLITFILEDISK_3.BAK'
WITH REPLACE ,
MOVE 'SPLITFILEDISK'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SPLITFILEDISK_COPY_DATA.MDF',
MOVE 'SPLITFILEDISK_LOG'
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SPLITFILEDISK_COPY_LOG.LDF'

Index was outside the bounds of the array

I get the problem with comparing database schema with Red Gate SQL Compare 6, after some time of the initialize the process of compare error is comming as following "Index was outside the bounds of the array"

You can view the error page also.

Put your valuable comments to get resolve this problem.

SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization

This is the best article posted by Ola for SQL Server Maintenance,Database Backup plan , integrity check and Index Optimization with Strored procedures and Functions. You can find from below link

http://ola.hallengren.com/

You can also find it's documentation and Script

http://ola.hallengren.com/Documentation.html

Backup Database with all Backup Types

Backing up a full database backup

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.bak'
WITH FORMAT;
GO

Backing up Full backup and differntial backup

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.bak'
WITH FORMAT;
GO

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB_Diff.bak'
WITH DIFFERENTIAL
GO

Backing up a full database backup and log backup

To take Log backup m it is require to set database to Full recovery mode.

alter database CompleteDB set recovery full

BACKUP DATABASE CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.bak'
WITH FORMAT
GO

BACKUP log CompleteDB
TO DISK = 'C:\BackupDir\CompleteDB.trn'
GO

Backing up full file backup of filegroups

First we are creating filegroup

alter database CompleteDB
add filegroup SecondaryFile

then we are adding file on that filegroup

alter database CompleteDB
add file
(
name = 'SecondaryFile' ,
Filename = 'c:\CompleteDB_Secondary_File.ndf'
)
TO FILEGROUP SecondaryFile

Now we are taking backup of filegroup

Backup database CompleteDB
filegroup = 'SecondaryFile'
to disk = 'C:\BackupDir\SecondaryFile.bck'

we can also take differntail backup of filegroup

Backup database CompleteDB
filegroup = 'SecondaryFile'
to disk = 'C:\BackupDir\SecondaryFile.bck'
WITH DIFFERENTIAL



Taking backup on BumpDevice

First we create dumpdevice

USE master
GO

EXEC sp_addumpdevice 'disk', 'BumpDevice',
'C:\backupdir\BumpDevice.bak';
GO

Then we take full database backup on that device

BACKUP DATABASE CompleteDB
TO BumpDevice
GO