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

Using NOLOCK and READPAST table hints in SQL Server

//About Lock in SQL Server
URL-1:http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/6185492

URL-2:http://blog.sqlauthority.com/2011/05/08/sql-server-what-kind-of-lock-with-nolock-hint-takes-on-object/