Ranking functions in SQL

Ranking functions introduced with SQL Server 2005 allow us to assign a number to each row returned from a query. They allow us to rank each row in respect to others in several different ways. Ranking functions can be used only with the SELECT and ORDER BY statements. They cannot be used directly in a WHERE or GROUP BY clause, but can be used in a CTE or derived table.

The syntax for ranking functions is shown as follows:

<function_name>() OVER([PARTITION BY <partition_by_list>]
ORDER BY <order_by_list>)
  • function_name: Can be one of ROW_NUMBER, RANK, DENSE_RANK, and NTILE
  • OVER: Defines the details of how the ranking should order or split the data
  • PARTITION BY: Details which data the column should use as the basis of the splits
  • ORDER BY: Details the ordering of the data
There are four ranking functions in SQL Server:
  1. ROW_NUMBER – It allows us to provide incrementing sequential integer values to the rows in the result-set of a query based on logical order that is specified in the ORDER BY subclause of the OVER clause. The ROW_NUMBER function contains the OVER clause, which the function uses to determine the numbering behavior. The ORDER BY option, which determines the order in which the function applies the numbers, must be included in the query. We have the option of starting the numbers over whenever the values of a specified column change, called partitioning, with the PARTITION BY clause.

    Example Query 1: Basic use of ROW_NUMBER()
    SELECT CustomerID, FirstName + ' ' + LastName AS Name,
    ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS Row
    FROM Sales.Customer AS c
    INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;

    Example Query 2: Using ROW_NUMBER() in a CTE
    WITH customers AS(
      SELECT CustomerID, FirstName + ' ' + LastName AS Name,
      ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS Row
      FROM Sales.Customer AS c
      INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID
      )
    SELECT CustomerID, Name, Row
    FROM customers
    WHERE Row > 50
    ORDER BY Row;

    Example Query 3: Using PARTITION BY option in ROW_NUMBER()
    SELECT CustomerID, FirstName + ' ' + LastName AS Name, c.TerritoryID,
    ROW_NUMBER() OVER (PARTITION BY c.TerritoryID ORDER BY LastName, FirstName) AS Row
    FROM Sales.Customer AS c
    INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;

  2. RANKRANK assigns the same number to the duplicate rows and skips numbers not used. It assigns an ascending, nonunique ranking number to a set of rows, giving the same number to duplicate rows; numbers are skipped for the number of rows that have the same value.
    If rows 2 and 3 are duplicates, RANK will supply the values 1, 3, 3, and 4.

    Example Query 1: Using RANK
    SELECT ROW_NUMBER() OVER (PARTITION BY SUBSTRING(LastName,1,2) ORDER BY LastName) AS RowNum,
    RANK() OVER(ORDER BY SUBSTRING(LastName,1,2) ) AS Ranking, CONCAT(FirstName,' ',LastName) AS CustomerName, UnclearedBalance
    FROM CustomerDetails.Customers
    WHERE UnclearedBalance is not null
    ORDER BY Ranking

  3. DENSE_RANK - This is similar to RANK, but each row number returned will be one greater than the previous setting, no matter how many rows are the same. DENSE_RANK doesn’t skip numbers.
    If rows 2 and 3 are duplicates, DENSE_RANK will supply the values 1, 2, 2, and 3.

    Example Query:
    SELECT ROW_NUMBER() OVER (PARTITION BY SUBSTRING(LastName,1,2) ORDER BY LastName) AS RowNum,
    DENSE_RANK() OVER(ORDER BY SUBSTRING(LastName,1,2) ) AS Ranking, CONCAT(FirstName,'',LastName) AS CustomerName, UnclearedBalance
    FROM CustomerDetails.Customers
    WHERE UnclearedBalance is not null
    ORDER BY Ranking

    Note: Both RANK and DENSE_RANK are similar to the ROW_NUMBER function, but they produce the same ranking value in all rows that have the same logical ordering value.
    The difference between RANK and DENSE_RANK is that RANK indicates how many rows have a lower ordering value, whereas DENSE_RANK indicates how many distinct ordering values are lower.
    For example, a rank of 9 indicates eight rows with lower values. A dense rank of 9 indicates eight distinct lower values.

  4. NTILE – The NTILE function assigns buckets to groups of rows. It allows us to associate the rows in the result with tiles (equally sized groups of rows) by assigning a tile number to each row. This takes the rows from the query and places them into an equal (or as close to equal as possible) number of specified numbered groups, where NTILE returns the group number the row belongs to. The value in parentheses after NTILE defines the number of groups to produce, so NTILE(25) would produce 25 groups of as close a split as possible of even numbers.

    Example Query:
    SELECT NTILE(10) OVER (ORDER BY LastName) AS BatchNumber, CONCAT(FirstName,' ',LastName) AS CustomerName, UnclearedBalance
    FROM CustomerDetails.Customers
    WHERE UnclearedBalance is not null
    ORDER BY BatchNumber
The following SQL query uses all the ranking functions in a single query:
 
SELECT orderid, custid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rank,
DENSE_RANK() OVER(ORDER BY val) AS dense_rank,
NTILE(100) OVER(ORDER BY val) AS ntile
FROM Sales.OrderValues
ORDER BY val;

Differentiate between WCF and ASMX Web Service

  • ASMX Web Services (ASP.NET Web Service) are designed to exchange messages only using SOAP over HTTP.
    However, WCF can exchange messages using any format (SOAP is the default format) over any transport protocol (HTTP, TCP/IP, MSMQ, NamedPipes, etc).
  • ASMX Web Services can only be hosted in IIS over HTTP.
    However, WCF service can be hosted in IIS, WAS (Windows Process Activation Services), Console Application, Windows NT Services, WCF provided host.
  • There is limited security in ASMX Web Services. IIS is responsible for authentication and authorization and ASP.NET security configuration, and transport layer security. WSE can be used for message layer security.
    WCF supports many of the capabilities provided by IIS and WS-* security models. It provides a consistent security programming model for any protocol. The security provided by WCF is consistent regardless of the host that is used to implement the service. WCF also supports claim based authorization, that provides much better control over resources than role based security.
  • ASMX Web Services uses XmlSerializer for serialization while WCF uses DataContractSerializer which is far better in performance than XmlSerializer. Major issues with XmlSerializer is as given below:
    a) Only public fields or properties can be translated to XML.
    b) The classes which implements IEnumerable interface can only be translated.
    c) Classes that implement IDictionary such as Hashtable cannot be serialized.
  • Web Services are not as flexible as WCF services. If you make a new version of the service then you need to just expose a new end. Therefore, services are agile and which is a very practical approach looking at the current business trends.

    Note: WebService supports Early Binding while WCF supports Late Binding.

    A major advantage of WCF over WebServices is that it can exchange messages in the following patterns:

    1)    Request – Reply Message Exchange Pattern
    2)    One – Way Message Exchange Pattern
    3)    Duplex Message Exchange Pattern

    WebServices only supports Request – Reply Message Exchange Pattern.

Differentiate between Server.Transfer() & Response.Redirect()

Both Server.Transfer and Response.Redirect are ASP.NET objects and are used for navigation between web-pages. However, there are noticeable differences between these two techniques:

Response.Redirect()
  • Response.Redirect() redirects the user to another web-page which may or may not be on the same server. It can redirect the user to an external website on a different server.
  • Response.Redirect() updates the address bar and adds the updated URL to the browser history. User can click back on the browser to navigate to the previous page.
  • Response.Redirect() terminates the request with HTTP 302 status and client-side roundtrip. Client then navigates to the new address and the browser address bar and history updates. The client pays the cost of additional round-trips to the server on each request.
  • Form variables are are not transferred upon a call to Response.Redirect().

Server.Transfer()
  • Server.Transfer() quits current execution of the web-page and redirects the user to another web-page on the same server. It cannot send the user to an external website on a different server.
  • Server.Transfer() keeps the URL unchanged in the address bar. It happens entirely on the server side and the client browser’s address bar remains constant. User cannot click on back button on the browser to navigate to the previous page
  • Server.Transfer() reduces the server request and conserves server resources. It simply changes the focus on the Web Server and transfers the request. With Server.Transfer() there are less number of HTTP requests, which eases pressure on the Web Server and makes the application execute faster.
  • Developer can transfer Query Strings and form variables with a little bug-bashing. The Server.Transfer() method has a second parameter – preserveForm. If this is set to True, the existing query string and form variables will be available to the transferred page. Ex – Server.Transfer(“webpage2”, True);

How can you force Garbage Collection

The GC.Collect method of the garbage collection GC class can be used to force garbage collection. The GC.Collect method may be used if there is a significant reduction in the amount of memory being used at a defined point in an application's code.

The garbage collector suspends all threads that are currently in the process of execution, before it performs a collection. Frequent calls to GC.Collect method may create performance issues. In most cases the optimizing engine in the garbage collector (which determines the best time to run a garbage collection) is better at determining the best time to perform a collection. 

In general, calls to any of the collect methods should be avoided, and the garbage collector should be allowed to run independently.

Global Assembly Cache (GAC)

The Global Assembly Cache (GAC) is a machine-wide cache of code. It can be found on every computer where the CLR is installed. The GAC is a storehouse of public/shared assemblies and allows applications to share assemblies instead of having the assembly distributed with each application.

Assemblies must be strong named before being installed in the GAC. The GAC performs integrity checks on all files that make up the assembly (at the time of its installation) to ensure that the assembly has not been tampered with.

Note: Unless explicitly required, do not share an assembly. Keep assembly dependencies private, and locate assemblies in the application directory.

Mention the demerits of installation of an assembly in the GAC

The application can no longer be replicated or installed by using the xcopy command (used to copy the application directory), if one of the assemblies that make up an application is placed in the GAC. The assembly must be moved in the GAC as well.

Mention the benefits of installation of an assembly in the GAC
  • Shared Location – An assembly which has to be used by multiple applications should be placed in the GAC. Add a version policy statement to the Machine.config file which will redirect references to the assembly.
  • File Security – Since it is installed in the systemroot directory, the GAC GAC inherits the Access Control List (ACL). So, only Administrators should be allowed to delete files from the GAC.
  • Side-by-side versioning – The GAC can maintain multiple copies of assemblies with the same name but different version information.
  • Additional search location – Before using the codebase information in the configuration file, the CLR runtime checks the GAC for the requested assembly.

Mention the ways to deploy an assembly into the GAC
  • Use an installer designed to work with the GAC for installing assemblies.
  • Use Gacutil.exe (Global Assembly Cache tool (gacutil.exe) is a developer tool provided by the Windows Software Development Kit (SDK)) to deploy an assembly into the GAC.
    Gacutil.exe does not provide assembly reference counting and other features provided by the Windows Installer. So it should only be used in development environment.

Where is the GAC located?

.NET Framework 4 & above – The default location for GAC is %windir%\Microsoft.NET\assembly
Versions earlier to .NET Framework 4 – The default location for GAC is %windir%\assembly

Friend Assemblies

A friend assembly can access another assembly's Friend (Visual Basic) or internal (C#) types and members. If an assembly is identified as a friend assembly, the types and members no longer have to be marked as public in order to be accessed by other assemblies.

How can you identify friend assemblies for a given assembly?

The attribute InternalsVisibleToAttribute can be used to identify one or more friend assemblies for a given assembly. The InternalsVisibleToAttribute class specifies that types that are ordinarily visible only within the current assembly are visible to a specified assembly.

Note: When an assembly that will access internal types or internal members of another assembly is compiled, the name of the output file (.exe or .dll) must be explicitly specified by using the /out compiler option.
Explicit naming of output file is required because at the time of binding to external references, the compiler has not yet generated the name for the assembly it is building.

Example: The following example uses the InternalsVisibleToAttribute attribute in assembly A and specifies assembly AssemblyB as a friend assembly. This gives assembly AssemblyB access to all types and members in assembly A that are marked as Friend (Visual Basic) or internal (C#).

using System.Runtime.CompilerServices;
using System;

[assembly: InternalsVisibleTo("AssemblyB")]

// The class is internal by default.
class FriendClass
{
    public void Test()
    {
        Console.WriteLine("Sample Class");
    }
}

// Public class that has an internal method.
Public class ClassWithFriendMethod
{
    internal void Test()
    {
        Console.WriteLine("Sample Method");
    }
} 
Validation Rules for friend assembly name passed to the InternalsVisibleToAttribute attribute (If assembly A declares B as a friend assembly)
  • If assembly A is strong named, assembly B must also be strong named. The friend assembly name that is passed to the attribute must consist of the assembly name and the public key of the strong-name key that is used to sign assembly B (the assembly version, culture, architecture, or public key token should not be included).
  • If assembly A is not strong named, the friend assembly name should consist of only the assembly name.
  • If assembly B is strong named, you must specify the strong-name key for assembly B by using the project setting or the command-line /keyfile compiler option.

Hub-and-Spoke model & Satellite Assemblies

The .NET Framework uses a hub-and-spoke model to locate and retrieve localized resources, which requires that resources must be placed in specific locations so that they can be easily located and used.

If the resources are not compiled and named as expected, or not placed in correct locations, the CLR will not be able to locate them and will use the resources of the default culture instead.

The hub is the main assembly that contains the non-localizable executable code and the resources for a single culture, which is called the neutral or default culture. The default culture is the fallback culture for the application and is used when no localized resources are available. You use the NeutralResourcesLanguageAttribute attribute to designate the culture of the application's default culture.

Each spoke connects to a satellite assembly that contains the resources for a single localized culture but does not contain any code. Satellite assemblies are not part of the main assembly. The resources of satellite assemblies that correspond to a specific culture can easily be updated or replaced without replacing the main assembly for the application.

The .NET Framework Resource Manager, represented by a ResourceManager object, is used to automatically access localized resources and has the following requirements:
  • A single satellite assembly must include all the resources for a particular culture.
  • There must be a separate subdirectory in the application directory for each localized culture that stores that culture's resources. The subdirectory name must be the same as the culture name.
    Alternately, you can store your satellite assemblies in the GAC. In this case, the culture information component of the assembly's strong name must indicate its culture.
    If your application includes resources for subcultures, place each subculture in a separate subdirectory under the application directory.
  • The satellite assembly must have the same name as the application, and must use the file name extension ".resources.dll". Ex: If an application is named Example.exe, the name of each satellite assembly should be Example.resources.dll.
  • Information about the culture of the satellite assembly must be included in the assembly's metadata. To store the culture name in the satellite assembly's metadata, you specify the /culture option when you use Assembly Linker to embed resources in the satellite assembly.

What are the different types of assemblies in .NET

There are three different types of assemblies in .NET:

Private Assemblies – Private Assemblies are normally used by a single application and is stored in the application's directory (the folder containing the application's executable file). Private Assemblies are deployed with the application and is available for the exclusive use of that application (they are not shared by other assemblies). Private assemblies must be designed to work side-by-side with other versions of the assembly on the system.

Private assemblies can be installed by any installation method that can copy the assembly's file into this folder, such as the xcopy command.

Note that the steps for creating a private assembly are identical to those for creating a shared assembly with two exceptions:
  • A private assembly is not required to be signed, and publickeyToken is not required in the assemblyIdentity element of the assembly manifest.
  • Private assemblies can be installed into the application's folder using any installation technology. Private assemblies are not required to be installed using the Windows Installer.
Public / Shared AssembliesShared Assemblies are available for use by multiple applications on the computer. They have version constraint and are stored in the Global Assembly Cache (GAC). GAC is the storehouse of public/shared assemblies and is maintained by the .NET runtime. A shared assembly must be strongly named.

Satellite Assemblies An assembly with culture information is automatically assumed to be a satellite assembly. These assemblies contain resource files pertaining to a locale (Culture+Language). These assemblies are used in deploying Global application for different languages.

Satellite assemblies are used to build multi-linguistic applications. These language-specific assemblies work in side-by-side execution because the application has a separate product ID for each language and installs satellite assemblies in a language-specific subdirectory for each language.


Satellite assemblies are not part of the main assembly. The resources of satellite assemblies that correspond to a specific culture can easily be updated or replaced without replacing the main assembly for the application.

What is an Assembly Manifest

An assembly manifest contains all the metadata needed to specify the assembly's version requirements and security identity, and all metadata needed to define the scope of the assembly and resolve references to resources and classes. The assembly manifest can be stored in either a PE file (an .exe or .dll) with MSIL code or in a standalone PE file that contains only assembly manifest information.

Functions of the Assembly Manifest
  • The manifest lists the files that make up the assembly.
  • It governs how references to the assembly's types and resources map to the files that contain their declarations and implementations.
  • It lists other assemblies on which the assembly depends.
  • It provides a level of indirection between consumers of the assembly and the assembly's implementation details.
  • It makes the assembly self-describing.

Contents of the Assembly Manifest

Given below are the details of the information contained in as Assembly Manifest:
  • Assembly Name – Name of the assembly.
  • Version numberA major and minor version number, and a revision and build number. The CLR uses these numbers to enforce version policy.
  • Culture – Information on the culture or language the assembly supports.
  • Strong name information – The public key from the publisher if the assembly has been given a strong name.
  • List of all files in the assembly - A hash of each file contained in the assembly and a file name.
  • Type reference information – Information used by the runtime to map a type reference to the file that contains its declaration and implementation. This is used for types that are exported from the assembly.
  • Information on referenced assemblies – A list of other assemblies that are statically referenced by the assembly. Each reference includes the dependent assembly's name, assembly metadata (version, culture, operating system, and so on), and public key, if the assembly is strong named.

Differentiate between Single-file assembly and Multifile assembly

When the components of an assembly is grouped in a single physical file, it is known as Single-file assembly.

When the components of an assembly is contained in several files, it is known as Multifile assembly. These files can be modules of compiled code (.netmodule), resources (such as .bmp or .jpg files), or other files required by the application. The files that make up a multifile assembly are not physically linked by the file system. Rather, they are linked through the assembly manifest and the CLR manages them as a unit.

Multifile assembly is usually created when there is a requirement to combine modules written in different languages and to optimize downloading an application by putting seldom used types in a module that is downloaded only when needed.

Components of an Assembly

There are four components of an assembly:
  • Assembly Manifest
  • Type Metadata
  • MSIL code
  • Resources
Out of the above four components, only assembly manifest is required. However, rest of the types or resources are needed to give the assembly any meaningful functionality.

Differentiate between Static and Dynamic Assemblies

Static Assemblies can include .NET Framework types (interfaces and classes), as well as resources for the assembly (bitmaps, JPEG files, resource files, and so on). Static assemblies are stored on disk in portable executable (PE) files.

Dynamic Assemblies are run directly from memory and are not saved to disk before execution. However, dynamic assemblies can be saved to disk later, after they have executed. These binaries are created on the fly in the memory using SYSTEM.REFLECTION.EMIT namespace.

Assembly

An assembly as a collection of types and resources that form a logical unit of functionality. It is a fundamental unit deployment, version control, reuse, activation scoping, and security permissions for a .NET application. Assemblies are the building blocks of the .NET Framework and can take the form of an executable (.exe, also known as process assembly) file or dynamic link library (.dll, also known as library assembly) file. Assemblies are the smallest versionable unit in the CLR.

An assembly can contain one or more modules. Assemblies provide the CLR with the information it needs to be aware of type implementations.

Properties of an Assembly
  • An assembly is implemented as .exe or .dll file.
  • An assembly can be shared between applications by putting it in the Global Assembly Cache (GAC). Assemblies must be strong-named before they can be included in the Global Assembly Cache.
  • Information about an assembly can be obtained programmatically by using Reflection.
  • Assembly contains code that the CLR executes.
  • MSIL code in a portable executable (PE) file will not be executed if it does not have an associated assembly manifest.
  • Each assembly can have only one entry point (DllMain, WinMain, or Main).
  • An assembly is the unit at which permissions are requested and granted. Thus, it forms a Security Boundary.
  • It forms a Type Boundary. Every type's identity includes the name of the assembly in which it resides.
  • An assembly forms a Reference Scope Boundary. The assembly's manifest contains assembly metadata that is used for resolving types and satisfying resource requests. It specifies the types and resources that are exposed outside the assembly. The manifest also enumerates other assemblies on which it depends.
  • It forms a Version Boundary. The assembly is the smallest versionable unit in the CLR; all types and resources in the same assembly are versioned as a unit.
  • It forms a Deployment Unit. When an application starts, only the assemblies that the application initially calls must be present. Other assemblies, such as localization resources or assemblies containing utility classes, can be retrieved on demand.
  • It is the unit at which side-by-side execution is supported.