Introduction to Transactions

Transaction - a sequence of operations performed as a single logical unit of work.

ACID
Atomic - all the work in the transaction is treated as a single unit. Either it is all performed or none of it is.
Consistent – a completed transaction leaves the database in a consistent internal state.
Isolations – the transaction sees the database in a consistent state. This transaction operates on a consistent view of the data. If two transactions try to update the same table, one will go first and then the other will follow.
Durability - the results of the transaction are permanently stored in the system.

Multi-Statement Transactions

BEGIN TRAN

UPDATE 	...
SET	... = ''
WHERE	... = '...'

UPDATE 	...
SET	... = ''
WHERE	... = '...'

COMMIT TRAN

Rolling Back

BEGIN TRAN

UPDATE 	...
SET	... = ''
WHERE	... = '...'

UPDATE 	...
SET	... = ''
WHERE	... = '...'

IF @@ROWCOUNT = 5
  COMMIT TRAN
ELSE
  ROLLBACK TRAN

Stored Procedures

CREATE PROCEDURE TranTest1
AS
BEGIN TRAN

INSERT INTO [...]
([f1], [f2], [f3], [f4], [f5])
VALUES	('...', '...', '...', '...', ...)

UPDATE 	...
SET	... = ''
WHERE	... = '...'

COMMIT TRAN
GO

Source

A = Single Unit of Work
C = Consistent Internal DB State
I =
D =

Using Common Table Expressions

Common Table Expressions
- a temporary resultset that can be defined within the execution scope of a single SELECT/INSERT/UPDATE/DELETE/CREATE VIEW.
- not stored as an object and lasts for the duration of the query
- self-referencing; can be referenced multiple times within the same query

WITH <CTE_NAME>[columns...]
AS
(
<Query Definition>
)
SELECT <column list>
FROM <CTE_NAME>

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Structure of a CTE
A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE.

WITH Sales_CTE(SalesPId, NumOrders, MaxDate)
AS
(
SELECT SalesPersonID, Count(*), Max(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID

)
SELECT E.EID, OS.NumOrders, OS.MaxDate, E.MgrID,
OM.NumOrders, OM.MaxDate
FROM HR.Employee E
JOIN Sales_CTE OS ON E.EID = OS.SalesPId
LEFT OUTER JOIN Sales_CTE OM ON E.MgrID = OM.SalesPId
ORDER BY E.EID

Source

Abstract Class versus Interface

Abstract Class
An abstract class is a special kind of class that cannot be instantiated… An abstract class is only to be sub-classed/inherited from…
Core identity/objects of same/similar type
Can have fields and constants defined

public abstract class Employee {
...
}

Interface
An entity that … has no implementation; it only has the signature, just the definition of the methods without the body.
Peripheral abilities/IMovable inherited by Human and Vehicle
If implementations, only share method signatures
No fields can be defined

public interface IEmployee {
...
}

Source

MS SQL Server

Temporary tables vs. table variables¹

Normal tables = physical tables

Local Temp tables - available to the session that created them. #LOCALTEMP

CREATE TABLE #TibetanYaks(
  YakID int,
  YakName char(30)
)

Global Temp tables - available to all users and sessions. Rarely used.

CREATE TABLE ##TibetanYaks(
  YakID int,
  YakName char(30)
)

Table variables - stored part memory, part disk. Access time faster than a temp table

DECLARE @TibetanYaks TABLE (
  YakID int,
  YakName char(30)
)

Which command using Query Analyzer will give you the version of SQL Server and Operating System?
@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.³

¹ SearchSQLServer.com

² SqlTeam.com

³ dev.fyicenter.com

DataSet vs. DataReader

DataSet vs. DataReader¹

1. DataReaders have less overhead than DataSets and are more efficient.
2. DataReaders can be databound (i.e. to a DataGrid).
3. Iterated once through

but

1. DataSets are disconnected. They are like disconnected models of the database.
2. DataSets can be made into Session objects or files, for persistence
3. DataSets can provide access to more than one table and table relationships
4. DataSets can be bound to multiple controls
5. Reiterated through multiple times.
5. You can jump to a particular record or go backwards through a DataSet.
6. Ability to name your table

TABLE 6.3: COMPARING DATAREADERS AND DATASETS2
DataReader	DataSet
A DataReader is specific to a data provider (for example, SqlDataReader, OdbcDataReader, and OleDbDataReader).	The DataSet class isn’t a part of any data provider. It’s specific to .NET only. However, the DataAdapter used to fill the DataSet with Fill() is specific to a data provider.
The data retrieved through a DataReader is read-only.	The data retrieved through a DataSet is read-write.
The data retrieved through a DataReader is forward-only. Once the data has been cycled through, the DataReader must be closed and re-created in order to reaccess the data.	You can work with data in a DataSet in any order you choose as many times as you like.
A DataReader presents data through a direct connection to the data source. Only one row of data is stored in Internet Information Services (IIS) memory at any one time.	A DataSet stores all the data from the data source in IIS memory at once.
A DataReader is fast.	A DataSet is slower than a DataReader.
A DataReader takes up few IIS and memory resources but annexes the databaseconnection until it’s closed.	A DataSet takes up a lot more memory/IIS resources to store all the data, but it doesn’t hold up a database connection until it’s closed. The connection needs to be open only when Fill() is called.
A DataReader lasts as long as the connection to the database is open. It can’t be persisted in a cookie or a session variable.	A DataSet lasts only until the page is reloaded (posted back) unless it’s somehow persisted (for example, in a session variable).
Fields in a DataReader are referenced by index number or name.	You can reference fields in a DataSet by name, but you must also name the DataTable and identify the row (index) that contains the field.
A DataReader has no concept of primary keys, constraints, views, or any other relational database management system (RDBMS) concept except row and field.	A DataSet contains DataTables. A primary key may be set for each DataTable, and relationships and constraints may be established between them. DataViews may be created over the DataSet.
You can’t update a data source through a DataReader.	You can make changes to data in a DataSet and then upload those changes back to the data source.
A DataReader connects to only onedata source.	A DataSet can be filled with Fill() from multiple data sources.

¹ My Library Syndication

² Beginning ASP.NET 1.1 Databases: From Novice to Professional

ASP.Net

Describe the role of inetinfo.exe, aspnet_isapi.dll andaspnet_wp.exe in the page loading process.
inetinfo.exe is theMicrosoft IIS server running, handling ASP.NET requests among other things. When an ASP.NET request is received (usually a file with .aspx extension), the ISAPI filter aspnet_isapi.dll takes care of it by passing the request to the actual worker process aspnet_wp.exe.

inetinfo.exe = IIS
aspnet_isapi.dll = ISAPI filter
aspnet_wp.exe = actual worker process

Page Load
Init, Load, PreRender, Unload, Disposed = ILPUD
ViewState is available after Init, OnLoad for a control.

Page Life Cycle Events²

• Page_Init
  The server controls are loaded and initialized from the Web form’s view state. This is the first step in a Web form’s life cycle.
• Page_Load
  The server controls are loaded in the page object. View state information is available at this point, so this is where you put code to change control settings or display text on the page.
• Page_PreRender
  The application is about to render the page object.
• Page_Unload
  The page is unloaded from memory.
• Page_Disposed
  The page object is released from memory. This is the last event in the life of a page object.
• Page_Error
  An unhandled exception occurs.
• Page_AbortTransaction
  A transaction is aborted.
• Page_CommitTransaction
  A transaction is accepted.
• Page_DataBinding
  A server control on the page binds to a data source.
• Process Request Method finally renders HTML Page

What’s a bubbled event?
When you have a complex control, like DataGrid, writing an event processing routine for each object (cell, button, row, etc.) is quite tedious. The controls can bubble up their eventhandlers, allowing the main DataGrid event handler to take care of its constituents.

When controls send their events to their parent control to be handled.

What are the different types of Session state management options available with ASP.NET?
ASP.NET provides In-Process and Out-of-Process state management. In-Process stores the session in memory on the web server. This requires the a “sticky-server” (or no load-balancing) so that the user is always reconnected to the same web server. Out-of-Process Session state management stores data in an external data source. The external data source may be either a SQL Server or a State Server service. Out-of-Process state management requires that all objects stored in session are serializable.

In-Process stores the session in memory on the web server.
Out-of-Process Session state management stores data in an external data source, requires that all objects stored in session are serializable.

Source: Mark Wagner’s .NET C# Cogitation¹

VbDotNetHeaven²

Windows Communication Foundation (WCF)

Windows Communication Foundation is a comprehensive layered framework for SOA programming.
[WCF = SOA Framework]
… you’re only responsible for modeling the communications of your products with the
outside world, and you don’t have to worry about actually implementing this model. This saves
you from having to deal with dirty little details of the underlying runtime components that implement
your model, and consequently allows you to focus on what matters to your application…

WCF Service
…you must not incorporate the logic that handles
the communications of the bug report manager system with the outside world into the bug report manager system itself. Instead, you must delegate the responsibility of the communications with the outside world to a different component. This component is known as a WCF service…

WCF Endpoint
Binding: The binding of an endpoint specifies how the endpoint communicates with the outside
world…
Address: The address of an endpoint specifies where the endpoint is…
Contract: The contract of an endpoint specifies what operations of the WCF service the clients
can access through that endpoint.

WCF Service Model
…, a WCF service is a wrapper around a piece of software enabling the software to
communicate with the outside world.
… developing a WCF service model boils down to developing its endpoints. Recall that
every endpoint has an address, a binding, and a contract. In general it takes two sets of tasks to develop a WCF service model: imperative (coding or development tasks) and administrative.
…anything that can be done declaratively in the configuration files should be done in the configuration files to allow administrators to modify the service without any code changes.

SQL Server Indexes

Clustered index
- reorders the way records in the table are physically stored
- a table can have only one clustered index
- the leaf nodes of a contain the data pages.

Nonclustered index
- the logical order of the index does not match the physical stored order of the rows on disk
- the leaf nodes of a nonclustered index do not consist of the data pages. Instead, they contain index rows.

Sources: FAQ: clustered vs nonclustered indexes