Diff between SQL Server 2005 and SQL Server 2008

"What is the diff. between Sqlserver 2005 and Sqlserver 2008"?

Here I am going to describe only 3 differences which is related to Declare and Set Keywords of Sql.

Differences 

1. You can declare variable and assign the value without using Set keyword in sqlserver 2008. / but not in 2005

2. You can increment the value like c# @i+=1 in sqlserver 2008. / but not in sqlserver 2005

3. You can put the break point for debug query  in sqlserver 2008. / but not in sqlserver 2005

...many other diff but here is only 3 we describing 

Declare @I int = 1

while(@I<=10)

begin

print @i

set @i+=1

end

More Details:
http://www.c-sharpcorner.com/blogs/5451/difference-between-sql-server-2005-and-2008.aspx

Computed Column – PERSISTED and Storage

If we are creating one computed column in the database it won't take more size of that computed column until we give the PERSISTED keyword

It does not increase the data size of the original table as well as it does not turn the column into persisted. When we create an index on the column of the table, there is an additional space occupied by that index.

More Details:
http://blog.sqlauthority.com/2010/08/21/sql-server-computed-column-persisted-and-storage-part-2/

Simple DataSet Sorting in C#

           Simple DataSet Sorting in C# 

            DataSet dsTest = new DataSet();
            DataTable dtSample = new DataTable("SampleTable");
            DataColumn dcName = new DataColumn("Name",typeof(string));
            dtSample.Columns.Add(dcName);
            dtSample.Columns.Add("Age", (typeof(Int32)));
            dtSample.Rows.Add("Lawrence", 25);
            dtSample.Rows.Add("Dinesh", 30);
            dtSample.Rows.Add("Aruna", 24);
            dtSample.Rows.Add("Deb", 35);
            dtSample.Rows.Add("Satish", 42);
            DataView dvSample = new DataView(dtSample);
            dvSample.RowFilter = "Age <> 30";
            dvSample.Sort = "Age Asc";
            dsTest.Tables.Add(dtSample);

Adding One more column with existing DataTable

               object objCheckAmount;
                if (ReceivableDT.Columns.Contains("CheckAmountWithTax") == false)
                {
                    ReceivableDT.Columns.Add("CheckAmountWithTax", typeof(System.Double), "Paid + TaxPaid");
                }
                objCheckAmount = ReceivableDT.Compute("Sum(CheckAmountWithTax)", "Select = 'true' and ReceivableHeaderID='" + ReceivableHeaderID + "'");
                if (objCheckAmount != DBNull.Value)
                {
                    CheckAmount = Convert.ToDouble(objCheckAmount);
                }

Rounding Functions IN SQL Server, C# and JavaScript

/// SQL Query to Round the values in different format

 DECLARE @value decimal(18,5)
SET @value = 6.001
SELECT ROUND(@value, 1)
SELECT CEILING(@value)
SELECT FLOOR(@value)


/// C# code to Round the values in different format

decimal Value = Convert.ToDecimal(Console.ReadLine());
System.Console.WriteLine("RoundValue={0}\nCeilingValue={1}\nFloorValue={2}", Math.Round(Value),Math.Ceiling(Value),Math.Floor(Value));

/// JavaScript code to Round the values in different format

var ceilValue = Math.ceil(Value); 
var floorValue = Math.floor(Value);
var roundValue = Math.round(Value);
 

Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

I have not written about this subject for long time, as I strongly believe that Book On Line explains this concept very well. SQL Server 2005 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.

NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.

More Details:

http://blog.sqlauthority.com/2007/10/09/sql-server-2005-sample-example-of-ranking-functions-row_number-rank-dense_rank-ntile/ 

Difference between On clause and Where clause when used with left join in SQL Server

 Below Link has the Elaborate explanation. Please go through it.
http://www.codeproject.com/Articles/231132/Difference-between-On-clause-and-Where-clause-when

Use of PARSEONLY Keyword in SQL Server

Syntax:

SET PARSEONLY { ON | OFF }

* When SET PARSEONLY is ON, SQL Server only parses the statement.
* When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.
The setting of SET PARSEONLY is set at parse time and not at execute or run time.
Do not use PARSEONLY in a stored procedure or a trigger. SET PARSEONLY returns offsets if the OFFSETS option is ON and no errors occur.

Examples


 Eg 1:   SET PARSEONLY ON;
            SELECT * FROM Lawrence_Setup
//This will parse the query. It wont compile and execute. It will check the syntax error only.


  Eg 2:  SET PARSEONLY OFF;
            SELECT * FROM Lawrence_Setup  
//This will compile and execute the query and also It will return the output vales based on the query