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.

What is the maximum allowable size of a row in SQL SERVER?

• If a table does not contain any sparse column, then the maximum allowable row size is 8060 bytes.
• However, if a table contains sparse columns, then the maximum allowable row size is 8018 bytes.

How is a column changed from sparse to nonsparse or nonsparse to sparse

• When a column changed from sparse to nonsparse or nonsparse to sparse, the storage format of the column is changed. This is accomplished by the SQL SERVER Database Engine using the following procedure:
• The SQL SERVER Database Engine adds a new column to the table in the new storage size and format.
• For each row in the table, the SQL SERVER Database Engine updates and copies the value stored in the old column to the new column.
• The SQL SERVER Database Engine then removes the old column from the table schema.
• It then rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.

List down the limitations for using SPARSE Columns

• A sparse column must be nullable and cannot have the ROWGUIDCOL or IDENTITY properties.
• A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.
• A sparse column cannot have a default value or bound to a rule.
• A computed column cannot be marked as SPARSE although it can contain a sparse column.
• A sparse column cannot be part of a clustered index or a unique primary key index.
• A sparse column cannot be used as a partition key of a clustered index or heap, but it can be used as the partition key of a nonclustered index.
• A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.
• Sparse columns cannot be added to compressed tables, nor can any tables containing sparse columns be compressed.
• When a non-sparse column is changed to a sparse column, the sparse column will consume more space for non-null values. When a row is close to the maximum row size limit, the operation can fail.
• If a table contains a sparse column, the maximum size of a row in a table will be decreased from 8060 bytes to 8012 bytes.

What are the datatypes which cannot be specified as SPARSE?

Given below are the data types which cannot be specifies as SPARSE:

• geography
• geometry
• image
• ntext
• text
• timestamp
• user-defined data types

Explain the COLUMNS_UPDATED function and its relation to Sparse Column

The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. The bits that are returned by the COLUMNS_UPDATED function are as follows:

• When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.
• When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.
• For insert operations, all bits are set to 1.