Link Server

Link Server

Linked Servers is a concept in SQL Server by which we can add other
SQL Server to a Group and query both the SQL Server dbs usig T-SQL Statements.
With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, 
joined and combined with local data.
Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server. 

Increase SQL Server stored procedure performance

 Increase SQL Server stored procedure performance



1. SET NOCOUNT ON
This help to stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.

This can reduce network traffic.

2.Use return values


3.Don't write select * from [tablename]

write select [columnname1],[columnname2] from [tablename]
This helps to speed of the query.


4.Don't use Prefix "Sp_" in your store procedure.

Becoz if you use "Sp" then SQL Server looks in the master database then your database.


5.Use sp_executesql stored procedure instead of the EXECUTE statement.


6.Avoid using temporary tables inside your stored procedure.

Becoz Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.


7.Avoid using DDL (Data Definition Language) statements inside your stored procedure.

Using DDL statements inside stored procedure reduces the chance to reuse the execution plan. 

LINQ Samples

I will cover few examples which are using query operators. You can find list of query operators Here

Refer Player class

Code

 

public class Player

  {

      public string FirstName { get; set; }

      public string LastName { get; set; }

      public string Country { get; set; }

      public int[] Scores { get; set; }

      public int ManOfTheSeries { get; set; }

  }

 

Type Filtering

Filtering based on the type.

Code

// FILTERING

          Console.WriteLine("List of batsmans who have scrored more than 50 runs in their First Match");

          foreach (Player s in query)

              Console.WriteLine(s.FirstName + " " + s.LastName + ": " + s.Scores[0]);

           // Keep the console window open in debug mode.

           //TYPE FILTERING

           //GET the int data from the type which contains both string and int.

           object[] sampleObject = {1,2,"Three",4,5,"Six","Seven",

                                           new Player{

                                                       FirstName = "Virat",

                                                       LastName="Kohli",

                                                       Country="India", 

                                                       Scores = new int[] { 107, 119, 125, 114 }

                                                   } 

                                   };

           //Get the string 

           var queryTypeFilteringString = sampleObject.OfType<string>();

           Console.WriteLine("");

           Console.WriteLine("String Type");

           foreach (var item in queryTypeFilteringString)

           {

               Console.WriteLine(item);

           }

           //Get the Int

           var queryTypeFilteringInt = sampleObject.OfType<int>();

           Console.WriteLine("");

           Console.WriteLine("Int Type");

           foreach (var item in queryTypeFilteringInt)

           {

               Console.WriteLine(item);

           }

           //Get Player

           var queryTypeFilteringPlayer = sampleObject.OfType<Player>();

           Console.WriteLine("");

           Console.WriteLine("Player Type");

           foreach (var item in queryTypeFilteringPlayer)

           {

               Console.WriteLine(item.FirstName + " "+ item.LastName);

           }

Output

String Type

Three

Six

Seven

Int Type

1

2

4

5

Player Type

Virat Kohli

Press any key to exit.

Compound Form

Below query will provide list of batsmans who have scored century (100) and above.

Code

            //Compound

            Console.WriteLine("List of batsman who have scored Century (100) in their Matches");

            var queryFilter = playerList

                              .SelectMany(

                               p=>p.Scores,

                              (p,s) => new {Player=p,Score=s})

                             .Where(p => p.Score > 100)

                             .OrderBy(p=>p.Player.FirstName)

                             .Select(a => a.Player.FirstName +" "+a.Player.LastName + " " + Convert.ToString(a.Score) );

            foreach (string s in queryFilter)

                     Console.WriteLine(s);

Output

Hashim Amla 165

Mahela Jayawardene 185

Ricky Ponting 180

Ricky Ponting 142

Sachin Tendulkar 200

Sachin Tendulkar 190

Sachin Tendulkar 170

Sachin Tendulkar 150

Press any key to exit.

Sorting
The OrderBy() and OrderByDescending() Methds returns IOrderedEnumerable<TSource>.

Code

//Indian Players with their scores, descending order by LastName

           var sortQuery = playerList

                           .SelectMany(p => p.Scores,

                           (p, s) => new { Player = p, Score = s })

                           .Where(p => p.Player.Country == "India")

                           .OrderByDescending(p => p.Player.LastName)

                            .Select(p => p);

Output

Sachin Tendulkar India 200

Sachin Tendulkar India 190

Sachin Tendulkar India 170

Sachin Tendulkar India 150

Virender Sehwag India 225

Virender Sehwag India 170

Virender Sehwag India 184

Virender Sehwag India 102

Press any key to exit.

Group By

To Group query results based on a key value ,the  group clause can be used.

Code

// List of plyers count group by countries

            var countiresQry = from p in playerList

                               group p by p.Country into g

                               orderby g.Count() >= 2

                               select new

                                   {

                                       Country = g.Key,

                                       Count = g.Count()

                                   };

Above Query with Extension methods

var countiresQry = playerList.

                             GroupBy(p=>p.Country).

                             OrderByDescending(g=>g.Count()).

                             ThenBy(g=>g.Key).

                             Where(g=>g.Count()>1).

                             Select(g=>new {Country= g.Key, Count=g.Count()});

Output

India      2

Australia  2

Sri Lanka  2

South Africa 2

Press any key to exit.

Grouping With Nested Objects

Code

var countiresQry = playerList.

                              GroupBy(p=>p.Country).

                              OrderByDescending(g=>g.Count()).

                              ThenBy(g=>g.Key).

                              Where(g=>g.Count()>=2).

                              Select(g=>new 

                                   {

                                       Country= g.Key, 

                                       Count=g.Count(),

                                       Players = g.OrderBy(r => r.LastName).Select(r => r.FirstName + " " + r.LastName)

                               });

           foreach (var item in countiresQry)

           {

               Console.WriteLine("Country:{0,-10}  Plyer Count:{1}", item.Country, item.Count);

               foreach (var name in item.Players)

               {

                   Console.Write(" Player Name:{0}\n ",name);

               }

               Console.WriteLine();

           }

Output

Country:Australia   Plyer Count:2

 Player Name:Adam Gilchrist

  Player Name:Ricky Ponting

Country:India       Plyer Count:2

 Player Name:Virender Sehwag

  Player Name:Sachin Tendulkar

Country:South Africa  Plyer Count:2

 Player Name:Hashim Amla

  Player Name:Graeme Smith

Country:Sri Lanka   Plyer Count:2

 Player Name:Mahela Jayawardene

  Player Name:Kumar Sangakara

Press any key to exit.

LINQ Operators

LINQ Operators are key part of the Linq Query. operator uses Extension Methods to introduce these query methods to any type that implements the IEnumerable<T> interface.
Below table shows category wise Linq operator with description.

LINQ To ADO.Net

There are three separate ADO.NET Language-Integrated Query (LINQ) technologies: LINQ to DataSet, LINQ to SQL, and LINQ to Entities. 

LINQ to DataSet provides richer, optimized querying over the DataSet

LINQ to SQL enables you to directly query SQL Server database schemas, and 

LINQ to Entities allows you to query an Entity Data Model.

  LINQ to DataSet

The Dataset is one of the most widely used components in ADO.NET, and is a key  element of the disconnected programming model that ADO.NET is built on. Despite this prominence, however, the Dataset  has  limited query capabilities. 

LINQ to DataSet enables you to build richer query capabilities into DataSet by using the same query functionality that is available for many other data sources

LINQ to SQL

LINQ to SQL provides a run-time infrastructure for managing relational data as objects. In LINQ to SQL,  the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When you execute the application, LINQ to SQL translates language-integrated queries in the object model into SQL and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back into objects that you can manipulate.
LINQ to SQL includes support for stored procedures and user-defined functions in the database, and for inheritance in the object model.

LINQ to Entities

Through the Entity Data Model, relational data is exposed as objects in the .NET environment. This makes the object layer an ideal target for LINQ support, allowing developers to formulate queries against the database from the language used to build the business logic.