SQL server : WITH TIES

WITH TIES allows adding one or more rows along with the rows limited by the TOP or similar statements. It works only when you use it alongside the ORDER BY expression.

Table data:

Query result:

SPARSE Column in sql server

What is a SPARSE Column?

Sparse columns provide a highly efficient way of managing empty space in a database by enabling NULL data to consume no physical space. For example, sparse columns enable object models that typically contain numerous null values to be stored in a SQL Server 2008 database without experiencing large space costs.

What is a Column Set?

Tables that contain sparse columns can define a column set to return the data in all of the sparse columns in the table. Similarly to a computed column, it is not physically stored in the table; however, unlike computed columns, the data in it is directly editable. The column set returns an untyped XML representation of the data. Column sets are useful when you have a large number of sparse columns in a table and working with them is awkward.

How to use SQL Server Sparse Columns ?

We can define sparse columns by using either the CREATE TABLE or ALTER TABLE statement, using the SPARSE keyword for the columns that you require to be sparse columns. 

Notes :

Sparse columns require more storage space for non-null values than a standard column.

Data types like geography, geometry, image, ntext, text, and timestamp cannot be used for sparse columns. 

Example:

The Fastest Way To Loop In C#

These are different ways to loop over a collection:

• do()
• do/while()
• for()
• foreach()
• ForEach()
• For()
• For(MaxDegreeOfParallelism)
• ForEach()
• ForEach(MaxDegreeOfParallelism)
• ForEachAsync()
• ForEachAsync(MaxDegreeOfParallelism)

Which one of these ways to loop over a collection do you think is the fastest? First, let’s look at a few of these ways to loop over an array. This is an example of using Parallel.For() with MaxDegreeOfParallelism:

var collection = personArray;
ParallelOptions options = new() {
    MaxDegreeOfParallelism = 6
};
_ = Parallel.For(fromInclusive: 0, toExclusive: collection.Length, parallelOptions: options, body: (index) => {
    base.Update(collection[index]);
});

C#

Benchmark Results

Here are the benchmark results that shows using Parallel.For() for a reference, value and record type.

Here is an example of using Parallel.ForEachAsync().

var collection = _personRecordArray;
await Parallel.ForEachAsync(collection, (person, token) => {
    base.Update(person);
    return new ValueTask();
});

C#

Benchmark Results

Here are the benchmark results that shows using Parallel.ForEachAsync() for a reference, value and record type.

As you can see from these benchmarks, using Parallel.ForEachAsync() is a lot slower than using Parallel.For() (around 6.86 times slower).

So, what is the fastest way to loop over an array? Well, according to my benchmark tests, it can actually vary depending on the type used in the array. If the array contains a reference or value type, then using for() is the clear winner! If the array contains the record type, then using Parallel.For() is the fastest (around 1.13 times faster) Here is an example:

var collection = personRecordArray;
_ = Parallel.For(0, collection.Length, (index) => {
    base.Update(collection[index]);
});

C#

In all of my benchmark tests, value types (structures) loops the slowest when compared to the reference and record type. I recommend to not use them in collections.

Using Spargine to Speed Up Arrays

I am currently making changes to my OSS project Spargine based on the findings in the latest edition of my code performance book. To make processing collections as fast as possible easy, I’m adding extension methods called PerformAction(). Here is how I unit test this method:

var words = RandomData.GenerateWords(count: 10, minLength: 10, maxLength: 100).ToArray();
var sb = new StringBuilder();
words.PerformAction((word) => {
    sb.Append(word);
});
var result = sb.ToString();

C#

This method also verifies there are items in the array to prevent exceptions. I will be using this method from now on when processing arrays because if the .NET team comes out with an even faster way to loop over an array, I can just change the code in PerformAction() and instantly all projects that use it get the performance boost! Code reuse is a good thing! I will revisit these methods whenever I do a major run of my benchmark tests so make sure to keep the Spargine NuGet packages up to date. You can add Spargine to your projects by going here: http://bit.ly/dotNetDaveNuGet.

SingleOrDefault vs FirstOrDefault

 SingleOrDefault returns a SINGLE element or null if no element is found. If 2 elements are found in your Enumerable then it throws the exception you are seeing. Just like Highlander... with Single - there can be only one.

FirstOrDefault returns the FIRST element it finds or null if no element is found. so if there are 2 elements that match your predicate the second one is ignored.

deliveryGroup.DeliveryMethods.SingleOrDefault(x => x.Id == methodId)

deliveryGroup rd = this.db.Details
    .FirstOrDefault(x => x.TId == Id && x.TypeId == TypeId);

its use very careful because when found any duplicate SingleOrDefault it will throw error.

Convert json date in jQuery

 The date format js is give in the below section, please go below and see the date.format.js and copy and use it.

Table of content for JSON date format

1. JSON date
2. Parse JSON date to date
3. Date format types
4. Demo link for live output
5. JavaScript code for date format
6. Full example code for multiple date format

 The JSON date is

        /Date(1297246301973)/

The JQuery Code is

        var jsonDate = "\/Date(1297246301973)\/";

        var date = new Date(parseInt(jsonDate.substr(6)));

Tuple in C#

Tuple Tuple has max limit up to 7 permeameter.

The Tuple<T> class was presented in .NET Framework 4.0. A tuple is an information structure that contains an arrangement of components of various information types. It very well may be utilized where you need to have an information construction to hold an article with properties, however you would prefer not to make a different sort for it. 

Tuple<T1, T2, T3, T4, T5, T6, T7, TRest>

Example:

Tuple<int, string, string> per = 
                        new Tuple <int, string, string>(1, "Darshan", "Mitesh");

How to access tuple:

var person = Tuple.Create(1, "Darshan", "Jobs");

person.Item1; // returns 1

person.Item2; // returns "Darshan"

person.Item3; // returns "Jobs"

var numbers = Tuple.Create("One", 2, 3, "Four", 5, "Six", 7, 8);

numbers.Item1; // returns "One"

numbers.Item2; // returns 2

numbers.Item3; // returns 3

numbers.Item4; // returns "Four"

numbers.Item5; // returns 5

numbers.Item6; // returns "Six"

numbers.Item7; // returns 7

numbers.Rest; // returns (8)

C# 8.0 new features :

1) Default Interface Methods

interface IWriteLine 
{ 
 public void WriteLine() 
 { 
 Console.WriteLine("Wow C# 8!"); 
 } 
} 

2) Nullable reference type

string? nullableString = null; 

Console.WriteLine(nullableString.Length);

3) Advanced Pattern Matching

Example
var point = new 3DPoint(1, 2, 3); //x=1, y=2, z=3 
if (point is 3DPoint(1, var myY, _)) 
{ 
  // Code here will be executed only if the point .X == 1, myY is a new variable 
  // that can be used in this scope. 

}

4) Async streams

await foreach (var x in enumerable) 
{ 
  Console.WriteLine(x); 

}

5) Ranges

Index i1 = 3; // number 3 from beginning 
Index i2 = ^4; // number 4 from end 
int[] a = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }; 

Console.WriteLine($"{a[i1]}, {a[i2]}"); // "3, 6" 

6) Caller Expression Attribute

public static class Verify { 
    public static void InRange(int argument, int low, int high, 
        [CallerArgumentExpression("argument")] string argumentExpression = null, 
        [CallerArgumentExpression("low")] string lowExpression = null, 
        [CallerArgumentExpression("high")] string highExpression = null) { 
        if (argument < low) { 
            throw new ArgumentOutOfRangeException(paramName: argumentExpression, message: $ " {argumentExpression} ({argument}) cannot be less than {lowExpression} ({low})."); 
        } 
        if (argument > high) { 
            throw new ArgumentOutOfRangeException(paramName: argumentExpression, message: $ "{argumentExpression} ({argument}) cannot be greater than {highExpression} ({high})."); 
        } 
    } 
    public static void NotNull < T > (T argument, 
        [CallerArgumentExpression("argument")] string argumentExpression = null) 
    where T: class { 
        if (argument == null) throw new ArgumentNullException(paramName: argumentExpression); 
    } 
} 
// CallerArgumentExpression: convert the expressions to a string!   
Verify.NotNull(array); // paramName: "array"   
// paramName: "index"   
// Error message by wrong Index:   
"index (-1) cannot be less than 0 (0).", or 
// "index (6) cannot be greater than array.Length - 1 (5)."   

Verify.InRange(index, 0, array.Length - 1);

7) Default in deconstruction

(int x, string y) = (default, default); // C# 7 

(int x, string y) = default;               // C# 8

8) Using declarations

// C# Oldy Style 
using (var repository = new Repository())   
{   
} // repository is disposed here!   
   
// vs.C# 8   
   
using var repository = new Repository();   
Console.WriteLine(repository.First());   

// repository is disposed here! 

9) Generic attributes

public class GenericAttribute<T> : Attribute { } 
public class ValidatesAttribute<T> : Attribute {} 
[Validates<string>] 
public class StringValidation {} 
[Validates<int>] 

public class IntegerValidation{} 

10) Static Local Functions

int AddFiveAndSeven() 
{ 
  int y = 5; int x = 7; 
  return Add(x, y); 
  static int Add(int left, int right) => left + right; 

}