Common Table Expression(CTE)

This is a new Enhancement of SQL server 2005(as my knowledge).we can think CTE's as a simple and more powerful alternative to derived Tables.In my case Now I am using CTE's where I used Temporary Tables before and I replaced so many views.

A CTE can be defined as a temporary named result set, which is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It is important to note that the scope of a CTE is just the statement in which it is declared. The CTE named result set is not available after the statement in which it is declared and used.

Here I am putting a code sample for CTE witch I have taken from AdventureWorks Database then you all can check this SQL script in your SQL server 2005(if you have installed AdventureWorks DB.)

WITH YearlyOrderAmtCTE(OrderYear, TotalAmount)
AS
(
SELECT YEAR(OrderDate), SUM(OrderQty*UnitPrice)
FROM Sales.SalesOrderHeader AS H JOIN Sales.SalesOrderDetail AS D
ON H.SalesOrderID = D.SalesOrderID
GROUP BY YEAR(OrderDate)
),
SalesTrendCTE(OrderYear, Amount, AmtYearBefore, AmtDifference, DiffPerc)
AS
(
SELECT thisYear.OrderYear, thisYear.TotalAmount,
lastYear.TotalAmount,
thisYear.TotalAmount - lastYear.TotalAmount,
(thisYear.TotalAmount/lastYear.TotalAmount - 1) * 100
FROM YearlyOrderAmtCTE AS thisYear
LEFT OUTER JOIN YearlyOrderAmtCTE AS lastYear
ON thisYear.OrderYear = lastYear.OrderYear + 1
)
SELECT * FROM SalesTrendCTE
GO

Did you Try to Create Reports in SQL Server 2005

I just went through Report Option in SQL server 2005. It looks good, can do a lot of things on it just like crystal report.But we can't compare it. b'coz Crystal Reports only for Reporting isn't it?.So Here is some screens which I did.i think You have to Use Business Intelligence Development Studio which is in SQL server 2005. If you need more information about Business Intelligence and Data Warehousing in SQL Server 2005 just click here



How is it? you can Set Server URL for Report Engine In project properties.Then You can view the Report in Internet Explorer

It's interesting to work with sql Server 2005

I imported Some of my sql server 2000 Databases In to new Sql server 2005.i saw something new in sql TOP Key word.The TOP operator has been around since SQL Server 7.0( i think so.), in the past it only accepted a constant as the number of rows to return or as the percentage of the rows to return. The TOP statement in SQL Server 7.0 and SQL Server 2000 was also limited to SELECT statements. Thanks to improvements in the TOP operator for SQL Server 2005, the TOP statement can now accept variables and subqueries for the number of rows to return or the percentage of the rows to return. The TOP operator has also been enhanced so that it can be used with INSERT, UPDATE, and DELETE statements in addition to the SELECT statement.

DECLARE @intRows INTEGER;
DECLARE @startAfter INTEGER;
SET @intRows = 12 ;
SET @startAfter = 10;
SELECT TOP (@intRows) * FROM HumanResources.Employee
WHERE EmployeeID > @startAfter
ORDER BY EmployeeID;
GO

And we Can set Varible Values programicaly.

We can still use the PERCENT keyword to return a percentage of rows instead of a fixed number of rows. And sql server 2005 we can use it with Top keyword with a variable value.

DECLARE @intPercRows INTEGER
DECLARE @intPage INTEGER
SET @intPercRows = 12
SET @intPage = 10
SELECT TOP (@intPercRows) PERCENT * FROM HumanResources.Employee
WHERE EmployeeID > @intPage
ORDER BY EmployeeID;
GO


In addition to passing an expression, we can also use a subquery to satisfy the TOP statement value requirement.

DECLARE @startAfter INTEGER;
SET @startAfter = 0;
SELECT TOP (SELECT COUNT(*)/11 FROM HumanResources.Employee) *
FROM HumanResources.Employee
WHERE EmployeeID > @startAfter
ORDER BY EmployeeID;
GO

First Day in Sql 2005

First time i Enter in to sql 2005.Where is that Enterprise Manager,Query Analyzer and Analysis Manager Short cuts?? They have remove them and introduce a new interface call SQL Server Management Studio.its a new integrated tool that combines the functionality of three tools: Query Analyzer, Enterprise Manager, and Analysis Manager. In addition, it allows us to manage all other components, such as Reporting Services, SQL Server Integration Services (SSIS; formerly known as DTS), Notification Services, and SQL Server Mobile. WOW.......


and it has Template Explorer.we can can lot of Templates for various sql functions from this Template Explorer. So what else we need??? .let me go through and find out ;)

working with Sql server 2005

I installed sql server 2005 Work group Edition at my home compuater last december. but Still i could not work on it well.i thot to start it today.yeah it has great new features with a reporting Server. so ?? lets move

Some ADO.NET Tricks

Passing Null as a Parameter Value

When sending a null value as a Parameter value in a command to the database, you cannot use null Instead you need to use DBNull.Value.
For example:

VB.NET
Dim param As SqlParameter = New SqlParameter("@Name", SqlDbType.NVarChar, 20)
param.Value = DBNull.Value

C#

SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
param.Value = DBNull.Value;


Optimizing Connections with the DataAdapter

The Fill and Update methods, of the DataAdapter, automatically open the connection specified for the related command property if Specific Connection is closed. If the Fill or Update method open the connection, Fill or Update will close it when the operation is complete. For best performance, keep connections to the database open only when required. Also, reduce the number of times you open and close a connection for multiple operations.

when performing transactions, explicitly open the connection before beginning the transaction and close the connection after you commit


Public Sub RunSqlTransaction(ByVal MyDA As SqlDataAdapter, ByVal myConn As SqlConnection, ByVal MyDS As DataSet)

myConn.Open()
Dim myTrans As SqlTransaction = myConn.BeginTransaction()
myCommand.Transaction = myTrans

Try
MyDA.Update(MyDS)
myTrans.Commit()
Console.WriteLine("Update successful.")

Catch e As Exception

Try
myTrans.Rollback()

Catch ex As SqlException

If Not myTrans.Connection Is Nothing Then
Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
" was encountered while attempting to roll back the transaction.")
End If

End Try

Console.WriteLine("An exception of type " & e.GetType().ToString() & " was encountered.")
Console.WriteLine("Update failed.")
End Try

myConn.Close()

End Sub

Searching About Object-relational mapping

Creating Classes was becoming a Headache .But then I start to find a way to go beyond from this Coding. if we can Create Classes according to our Tables,Stored procedures and views then it will be Easy. I had never heard about this term call ORM before. Thanks my buddy thusitha giving this term.So I started to Search about ORM and I found this NHibernate. Mmmmmmm…… wanna learn about ORM now. :)

Help me ..........................