Why Use SQL Over procedural?

http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=27

Structured Query Language (SQL) is a set-based language as opposed to a procedural language. It is the defacto language of relational databases.

The difference between a set-based language vs. a procedural language is that in a set-based language you define what set of data you want or want to operate on and the atomic operation to apply to each element of the set. You leave it up to the Database process to decide how best to collect that data and apply your operations. In a procedural language, you basically map out step by step loop by loop how you collect and update that data.

There are two main reasons why SQL is often better to use than procedural code.

• It is often much shorter to write – you can do an update or summary procedure in one line of code that would take you several lines of procedural.
• For set-based problems – SQL is much faster processor-wise and IO wise too because all the underlining looping iteration is delegated to a database server process that does it in a very low level way and uses IO/processor more efficiently and knows the current state of the data – e.g. what other processes are asking for the data.

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

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

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

1-2-3 Normal forms

1^st Normal Form – Eliminate Repeating Groups (ERG)
all occurrences of a record type must contain the same number of fields. No sets

2^nd Normal Form – Eliminate Redundant Data (ERD)
violated when a non-key field is a fact about a subset of a key.

3^rd Normal Form – Eliminate Columns Not Dependent on Key (ENDK)
violated when a non-key field is a fact about another non-key field.

All normal forms are additive.

Source