Managed Heap

Address spaces, created by processes at run time.

Work based on automatic memory management.

Microsoft’s .NET framework uses a managed heap model as part of its automatic memory management process.

When you create an object using the new operator in C#, the .NET run time allocates memory in the "managed heap" (simply a heap managed by the .NET run time + the garbage collector).

Automatic memory management is one of the services that the Common Language Run time provides during Managed Execution.

The Common Language Run time's garbage collector manages the allocation and release of memory for an application. For developers, this means that you do not have to write code to perform memory management tasks when you develop managed applications. Automatic memory management can eliminate common problems, such as forgetting to free an object and causing a memory leak, or attempting to access memory for an object that has already been freed.

When you initialize a new process, the run time reserves a contiguous region of address space for the process. This reserved address space is called the managed heap. The managed heap maintains a pointer to the address where the next object in the heap will be allocated. Initially, this pointer is set to the managed heap's base address. All reference types are allocated on the managed heap. When an application creates the first reference type, memory is allocated for the type at the base address of the managed heap. When the application creates the next object, the garbage collector allocates memory for it in the address space immediately following the first object. As long as address space is available, the garbage collector continues to allocate space for new objects in this manner.

Allocating memory from the managed heap is faster than unmanaged memory allocation. Because the run time allocates memory for an object by adding a value to a pointer, it is almost as fast as allocating memory from the stack. In addition, because new objects that are allocated consecutively are stored contiguously in the managed heap, an application can access the objects very quickly.

The garbage collector's optimizing engine determines the best time to perform a collection based on the allocations being made. When the garbage collector performs a collection, it releases the memory for objects that are no longer being used by the application. It determines which objects are no longer being used by examining the application's roots. Every application has a set of roots. Each root either refers to an object on the managed heap or is set to null. An application's roots include static fields, local variables and parameters on a thread's stack, and CPU registers.

Objects that are not in the graph are unreachable from the application's roots. The garbage collector considers unreachable objects garbage and will release the memory allocated for them. During a collection, the garbage collector examines the managed heap, looking for the blocks of address space occupied by unreachable objects. As it discovers each unreachable object, it uses a memory-copying function to compact the reachable objects in memory, freeing up the blocks of address spaces allocated to unreachable objects. Once the memory for the reachable objects has been compacted, the garbage collector makes the necessary pointer corrections so that the application's roots point to the objects in their new locations. It also positions the managed heap's pointer after the last reachable object. Note that memory is compacted only if a collection discovers a significant number of unreachable objects. If all the objects in the managed heap survive a collection, then there is no need for memory compaction.

To improve performance, the run time allocates memory for large objects in a separate heap. The garbage collector automatically releases the memory for large objects. However, to avoid moving large objects in memory, this memory is not compacted.

Struct

• Struct is an encapsulated entity. Struct doesn't uses complete oops concept but are used for user defined data type. All the member of the struct has to be initialized, as it is value type.
•  A struct is a simple user-defined type, a lightweight alternative to a class.
•  Similar to classes, structures have behaviors and attributes. As a value type, structures directly contain their value so their object or instance is stored on the stack.
•  Struts support access modifiers, constructors, indexers, methods, fields, nested types, operators, and properties.
•  Whenever you have a need for a type that will be used often and is mostly just a piece of data, structs might be a good option. In the other words when we have small and frequent use of some work use structs over classes.
•  Structs reside on the stack, so we should keep them small.
•  The default constructor of a structure initializes each field to a default value. You cannot replace the default constructor of a structure.
•  You can't define destructor for structs

E.G.

public struct Student { 

    int id; 

    int zipcode; 

    double salary; 

}

  

Q: Is Structure reference type?

A: No, it is value type

Q: Is Structure support default (parameter less) constructor?

A: No

Q: Is Structure support parameterized constructor?

A: Yes

Q: Is Structure support uninitialized valued (attributes)?

A: No, all values must be initialized within constructor. We cannot initialize value during declaration.

Q: Can Structure be inherited?

A: No, they are sealed.

Q: Can class be inheriting to Structure?

A: No. Struct can't derive from any class.

Q: Can Interface be inheriting to Structure?

A: Yes. Struct can be derive from interface only.

Q: Can Structure member be declared protected?

A: No, as Structure cannot inherited.

Structure VS Class

Structs	Class
Value Type	Reference Type
Stored in stack	Stored on managed heap
Doesn’t support inheritance with class	Support Inheritance
Cannot define default constructor	Constructor is fully supported
Cannot define default destructor	Destructor is fully supported
Uninitialized member not possible	Uninitialized member possible

Object Relational Mapping (ORM)

(ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm

An ORM library is a completely ordinary library written in your language of choice that encapsulates the code needed to manipulate the data, so you don't use SQL anymore; you interact directly with an object in the same language you're using

E.G.

book_list = new List();

sql = "SELECT book FROM library WHERE author = 'Linus'";

data = query(sql); // I over simplify ...

while (row = data.next())

{

     book = new Book();

     book.setAuthor(row.get('author');

     book_list.add(book);

}

With an ORM library, it would look like this:

book_list = BookTable.query(author="Linus");

Disadvantage of ORM

·         ORM libraries are not lightweight tools;

·         Performance is OK for usual queries, but a SQL master will always do better with his own SQL for big projects.

Example of ORM

Entity Framework

SQL Theory Interview Questions

1. Advantages and Disadvantages of Stored Procedure

Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required.

This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

2. What are different SQL statement

There are 3 types of SQL statements

1) DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.

Some of the DDL Commands are listed below

CREATE: It is used for creating the table.

CREATE TABLE table_name

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

ALTER: The ALTER table is used for modifying the existing table object in the database.

ALTER TABLE table_name

ADD column_name datatype

ALTER TABLE table_name

DROP COLUMN column_name

2) DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.

The Select statement is used as partial DML statement that is used to select all or relevant records in the table.

3) DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific user.

What are different JOINS used in SQL?

·         Right Join

·         Outer Join

·         Full Join

·         Cross Join

·         Self Join

3. What is SQL Injection?

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases.

4. What is the difference between DELETE and TRUNCATE?

The basic difference in both is DELETE is DML command and TRUNCATE is DDL

DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table

We can use DELETE with WHERE clause but cannot use TRUNCATE with it

5. What is the difference between DROP and TRUNCATE?

TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.

6. What is Trigger?

Ans. Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)

A SQL trigger is a special type of stored procedure. It is special because it is not called directly like a stored procedure. The main difference between a trigger and a stored procedure is that a trigger is called automatically when a data modification event is made against a table whereas a stored procedure must be called explicitly.

7. Advantages of using SQL triggers

SQL triggers provide an alternative way to check the integrity of data.

SQL triggers provide an alternative way to run scheduled tasks. By using SQL triggers, you don’t have to wait to run the scheduled tasks because the triggers are invoked automatically before or after a change is made to the data in the tables.

SQL triggers are very useful to audit the changes of data in tables.

Types of Trigger

DDL Triggers

In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and certain system defined stored procedures that perform DDL-like operations.

DML Triggers

In SQL Server we can create triggers on DML statements (like INSERT, UPDATE, and DELETE) and stored procedures that perform DML-like operations. DML Triggers are of two types

After Trigger (using FOR/AFTER CLAUSE)

This type of trigger fires after SQL Server finish the execution of the action successfully that fired it.

Instead of Trigger (using INSTEAD OF CLAUSE)

This type of trigger fires before SQL Server starts the execution of the action that fired it. This is differ from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

8. What are the properties of a transaction?

ACID (an acronym for Atomicity, Consistency Isolation, Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all these four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

SQL

• Normalization
• View
• Index
• SQL Theory Interview Questions
• SQL Queries Interview Questions

Index - SQL

Indexes are special lookup tables that the database search engine can use to speed up data retrieval.

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers.

An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements.

Single Column index

A single-column index is created based on only one table column. The basic syntax is as follows.

E.G.

CREATE INDEX index_name

ON table_name (column_name);

Unique Indexes

Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows.

E.G.

CREATE UNIQUE INDEX index_name

on table_name (column_name);

Composite Indexes

A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

E.G.

CREATE INDEX index_name

on table_name (column1, column2);

Implicit Indexes

Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

Drop Index

DROP INDEX index_name;

Clustered Index.

A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

E.G.

CREATE CLUSTERED INDEX IX_tblStudent_Gender_Score

ON student(gender ASC, total_score DESC)

NonClustered Index.

NonClustered Index does not alter the physical order of the table and maintains logical order of data.

A non-clustered index doesn’t sort the physical data inside the table.

In fact, a non-clustered index is stored at one place and table data is stored in another place.

This is similar to a textbook where the book content is located in one place and the index is located in another.

This allows for more than one non-clustered index per table.

Each table can have 999 nonclustered indexes.

E.G.

CREATE NONCLUSTERED INDEX IX_tblStudent_Name

ON student(name ASC)

Differences between clustered and non-clustered indexes.

There can be only one clustered index per table. However, you can create multiple non-clustered indexes on a single table.

Clustered indexes only sort tables. Therefore, they do not consume extra storage. Non-clustered indexes are stored in a separate place from the actual table claiming more storage space.

Clustered indexes are faster than non-clustered indexes since they don’t involve any extra lookup step.

View - SQL

·         A view is the result set of a stored pre-established which stored in the database dictionary.

·         It is a virtual table.

·         When the view is requested, it computed data dynamically from the database.

·         A view really doesn't do anything until you query it.

·         Views can hide the complexity of data.

·         Using view, we can display customized data as a virtual table from various physical tables.

·         Views take very little space to store, the database contains only the definition of a view, not a copy of all the data that it presents.

Limitations

·         Since a view is a virtual table – columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again.

·         The select statement on the view cannot contain ORDER BY or INTO TEMP.

·         When a table or view is dropped, any views in the same database are also dropped.

Operation with View

Data can be insert/update/delete using views if it is not read-only.

E.G. Updating view.

Update V_Employee Set Salary = 90000 WHERE EmpId = 9

To make any view readonly in sql we have to append some constant value in the view

E.G.

CREATE VIEW V_Employee

AS

SELECT * FROM Employee

UNION

SELECT 0,'',0,'' FROM Employee WHERE 1 <> 1 -- constant values

Index VIEW

Similarly, as adding index in table, we can also add index in the view. But the performance of maintaining the index in view is much higher than maintaining the index in the table.

We should need to add the index on the table or view which are not frequently inserted/updated (means non transactional). They are more commonly used in scenarios when data modification is less.

Basically we use indexes on reporting purpose.

The response time to a query is fast at the cost of space consumption.

To Create index view please follow below steps-

Session Options that must be on

·         ANSI_NULLS 

·         ANSI_PADDING

·         ANSI_WARNINGS

·         ARITHABORT

·         CONCAT_NULL_YEILDS_NULL

·         QUOTED_IDENTIFIERS

Session options that must be off

·         NUMERIC_ROUNDABORT

--Create a deterministic view with new SCHEMABINDING syntax.

CREATE VIEW Sales.vOrders  

WITH SCHEMABINDING  

AS  

    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,  

        OrderDate, ProductID, COUNT_BIG(*) AS COUNT  

    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o  

    WHERE od.SalesOrderID = o.SalesOrderID  

    GROUP BY OrderDate, ProductID;  

GO  

--Create an unique Clustered index on the view.  

CREATE UNIQUE CLUSTERED INDEX IDX_V1   

    ON Sales.vOrders (OrderDate, ProductID);  

GO  

Partitioned views

Partitioned views allow data in a large table to be split into smaller tables.

These small tables are called as member tables. The split is done based on range of data values in one of the columns.

E.G.

--- History

CREATE TABLE FACT.sales_history (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_history

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

--- Current year

CREATE TABLE FACT.sales_thisyear (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_thisyear

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

--- Current month

CREATE TABLE FACT.sales_thismonth (

    [date]    date NOT NULL,

    dim1      int NOT NULL,

    dim2      int NOT NULL,

    dim3      int NOT NULL,

    dim4      int NOT NULL,

    amount1   numeric(10, 2) NOT NULL,

    amount2   numeric(10, 2) NOT NULL,

    CONSTRAINT PK_sales_thismonth

        PRIMARY KEY CLUSTERED ([date], dim1, dim2, dim3, dim4)

) ON [PRIMARY];

CREATE VIEW FACT.sales

WITH SCHEMABINDING

AS

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_history

UNION ALL

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_thisyear

UNION ALL

SELECT [date], dim1, dim2, dim3, dim4, amount1, amount2

FROM FACT.sales_thismonth

Distributed Portioned View

In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.