3. Architecture

.Net 3.0 runtime
Contains the core pillars (WPF, WWF etc) and runtime components. If we wish only to run any .net 3.0 application, installing .net 3.0 runtime is enough.

While the .NET Framework 2.0 class library is partially superseded by the new components (WF, WCF, and WPF) added in version 3.0, many portions of the original class library are still crucial to developers. The technologies of version 2.0 (ASP.NET, WinForms, ADO.NET, XML etc.) largely remain the elementary part of the new release; however, .NET framework 3.0 developers may now prefer using WPF over Windows Forms for writing a native Windows GUI.



Windows Workflow Foundation (WF)
WF broadly comprises of the following assorted components:

Activity: A unit of work ranging from very simple to quite complex.
Workflow: A group of activities that partially or completely implements a business process.
WF Designers: The graphical tools to create and modify workflows and activities.
WF Base Activity Library: A fundamental group of activities (IfElse, While, Listen etc. design constructs) used to create workflows. These are quite similar to the BizTalk Orchestration shapes.
WF Runtime Engine: A library that executes workflows. It also provides other services, such as communicating with software outside the workflow.
Host process: A Windows application that hosts the Windows Workflow Foundation runtime engine, workflows, and runtime services for persisting a workflow's state, handling transactions, etc.

WF provides the Workflow Designer, a Visual Studio-hosted graphical tool for creating workflows. The activities in a workflow can be drawn using the Base Activity Library (BAL) provided with WF.

Windows Communication Foundation (WCF)
However applications are built (using workflow or otherwise), most of them need to communicate with each other. This inter-application communication has taken a big leap forward in the last few years. After a perpetual era of disagreement, all of the major vendors agreed to support SOAP based Web services which makes interoperability between applications built on different technology platforms, such as J2EE and the .NET Framework, significantly simpler. So, this also makes the idea of service-oriented architecture much more plausible for most organizations.

A lot of communication approaches exist in the .NET Framework 2.0 such as ASP.NET Web Services, .NET Remoting, System.Messaging supporting queued messaging through MSMQ, Web Services Enhancements (WSE) - an extension to ASP.NET Web Services that supports WS-Security etc. However, instead of requiring developers to use a different technology with a different application programming interface for each kind of communication, WCF provides a common approach and API. "WCF is actually an old wine in new bottle", and was previously called as Indigo. In the .NET Framework 3.0 environment, most applications that might have used one of the communication technologies listed above will instead use WCF.

WCF provides strong support for interoperable communication through SOAP. This includes support for several specifications, including WS-Security, WS-ReliableMessaging, and WS-AtomicTransaction. WCF doesn't itself require SOAP, so other approaches can also be used, including optimized binary protocol and queued messaging using MSMQ. WCF also takes an explicit service-oriented approach to communication, and loosens some of the tight couplings that can exist in distributed object systems, making interaction less error-prone and easier to change. Thus, WCF addresses a range of communication problems for applications. Three of its most important aspects that clearly stand out are:

Unification of Microsoft�s communication technologies.
Support for cross-vendor interoperability, including reliability, security, and transactions.
Rich support for service orientation development.
To sum up, communication between applications, whether within an organization or across, is a fundamental part of any modern software. The .NET Framework 3.0 attempts to address enduring communication challenges by using the service-oriented approach of WCF.

Windows CardSpace (WCS) Digital identities are the way how people electronically represent themselves today on the Internet. In the majority of cases, a person's digital identity is expressed as a simple username, and when its combined with a password, it is used to access web sites, email servers, e-merchants, online banks etc. Yet, despite their simplicity and popularity, usernames and passwords haunt people. Many of us have a hard time remembering all of the usernames and passwords of different sites. Thus, some people use the same values for different sites, easing the memory problem but increasing the security risk.

Usernames, passwords, and other personal information can be stolen by phishers. By sending delusory emails, phishers entice their victims to log in to their Web site that looks just like, say, the site of the victim's bank. So once the victim enters his username and password, the phisher can use this information to masquerade as the user on the real site.

Reducing occurrences and severity of these problems requires an entirely new approach to managing digital identities. WCS (originally called InfoCard) provides this. It helps people keep track of their digital identities as distinct information cards. If a Web site accepts WCS logins, users attempting to log in to that site will see a WCS selection. By choosing a card, users also choose a digital identity that will be used to access this site. Rather than remembering a plethora of usernames and passwords, users need only recognize the card they wish to use.

The identities represented by these cards are created by one or more identity providers. These identities will typically use stronger cryptographic mechanisms to allow users to prove their identity. WCS itself also includes a self-issued identity provider that runs on client machines. With this provider, users can create their own identities that don't rely on passwords for authentication.

So if passwords aren't used to log in to a site, phishers can inflict no harm either. Well, not really! Phishers, if somehow, can trick a user to log into a bogus site, might be still able to acquire personal information of the user, such as sensitive medical information etc. Preventing this requires that users be able to distinguish real sites from the look-alike fakes created by phishers. To allow this, the organization that owns a Web site can get a high-assurance certificate. Unlike today's simple SSL certificates, acquiring this new kind of certificate involves a much more rigorous process, including stronger proof that the organization applying for it actually is who it claims to be. A high-assurance certificate can also carry a company's logo and other information to help the user correctly determine whether a site using this certificate is legitimate. When a user accesses a new site, WCS always displays the information in that site's certificate using a standard screen. Based on the strength of the certificate received, this screen will indicate different levels of assurance of the site's identity.

Windows Presentation Foundation (WPF)

User interfaces are an important part of most Windows applications. No matter how much software evolves, traditional menu-driven GUIs are here to stay for some more time. Similarly, the need to display video, run animations, use 2/3D graphics, and work with different document formats also cannot be superseded. And all of this must be possible whether the application is a stand-alone desktop client or is accessed through a Web browser.

So far, all of these aspects of the user interface have been provided in different ways on Windows. For example, a developer needs to use Windows Forms to build a Windows GUI, or HTML/ASPX/Applets/JavaScript etc. to build a web interface, Windows Media Player or software such as Adobe's Flash Player for displaying video etc. The challenge for developers is certainly clear: building a coherent user interface for different kinds of clients using diverse technologies isn't a simple job.

A primary goal of WPF (originally called Avalon) is to address this challenge! By offering a consistent platform for these entire user interface aspects, WPF makes life simpler for developers. By taking a more modern approach, including support for video, animation, 2/3D graphics, and various kinds of documents, WPF can let users work with information in new ways. And by providing a common foundation for desktop clients and browser clients, WPF makes it easier to build applications that address both.

Another challenge that has long faced the creators of user interfaces stems from the different roles required for building effective interfaces. Software developers are needed to create the logic behind the interface, and Designers are required to define the interface's look and feel. Yet older technologies such as Windows Forms are focused entirely on the developer. There's no truly effective way for developers and designers to collaborate. To address this issue, WPF relies on the eXtensible Application Markup Language (XAML). An XML-based language, XAML allows specifying a user interface declaratively rather than in code. This makes it much easier for user interface design tools like MS Expression Blend (originally branded as MS Expression Interactive Designer and code named as Sparkle) to generate and work with an interface specification based on the visual representation created by a designer. Designers will be able to use such tools to create the look of an interface and then have a XAML definition of that interface generated for them. The developer imports this definition into Visual Studio, then creates the logic the interface requires.

Developers can also build a XAML browser application (XBAP) to create a remote client that runs inside a Web browser. Built on the same foundation as a stand-alone WPF application, an XBAP allows presenting the same style of user interface within a downloadable browser application. The best part is that the same code can potentially be used for both kinds of applications, which means that developers no longer need different skill sets for desktop and browser clients. The downloaded XBAP from the Internet runs in a secure sandbox (like Java applets), and thus it limits what the downloaded application can do.

Burp, GUI is a complex but an important part of the modern applications. Through WPF, the .NET Framework 3.0 presents a more complete and consistent solution to the challenges these interfaces present. The goal is to let people who create user interfaces (both developers and designers) effectively collaborate and do their jobs more expeditiously. So beware, because when your boss hears all this, she/he will certainly start expecting more from you.. : ( But I guess, that is the flip side of adopting any new technology.

Cocktails
�As an ideal meal cannot conclude without cocktails, so cannot a technology review without addressing criticism�.

Of late, I have came across some criticism of .NET 3.0 release and its branding on grounds that the new release doesn't accommodate .Net 2.0 bugs or language enhancements (like LINQ). It is also said that it does not add up anything substantial (other than WinFx lib) and that there shall be no performance gains too. Some even envisage the .Net 3.0 release as an improvised marketing ploy to hype up the forthcoming Vista release.

On the contrary, the re-branding from WinFx to .NET Framework and regrouping of the various technologies, "now", under a unified .NET Framework 3.0 banner, is a far better measure than it can be any later. This will not only avert another round of marketing confusion in future but also prevent the ever expanding branding namespace from getting further convoluted. After all, haven't we long forgotten the Longhorn for Vista (even though there was so much of brick-batting initially over it)? Plausibly, forgetting demands lesser efforts or time than remembering does.

The fact that .Net framework 3.0 is not introducing anything sour at this stage that will require redesigning or recoding the existing applications, is ostensibly welcome. It, otherwise, could have stirred the development community and their trust. Now that our lives are simplified (or at least are less complicated); bug fixes and fancy enhancements can shortly arrive (as patches/SPs). Similarly, major performance gains can be targeted with every new CPU/architecture release.

The aforesaid concerns and the resultant inertia, to a certain extent, were predictable at the time of announcement of the .Net release 3.0!! So, the ensuing confusion is obvious and is owing to the fact that from the very first release, till thus far; the .Net Framework has primarily grown vertically upwards and its now (with the new 3.0 release) attempting to expand horizontally outwards. This, however, indicates the overall maturity of the platform and is by far a healthy sign. So, instead of remaining dubious anymore, lets wisely step forward to accept the new version as an "additive" release (or more simply put the "production" release of WPF, WCF, WF, and WCS technologies), which is veritably colossal enough to be understated.

Basic Query

SQL SELECT statement has the widest variety of query options, which are used to control the way data is returned.

ORDER BY—A clause that returns the result set in a sorted order based on specified columns.
example:
SELECT * FROM Contacts ORDER BY first_name;
You are free to use ORDER BY with any select statement that might return multiple rows. You can also use it in conjunction with other clauses:

SELECT first_name, last_name FROM Contacts WHERE first_name BETWEEN ‘a’ AND ‘k’ ORDER BY last_name;

When you use DISTINCT, it applies to all requested columns. If you want a list of all the salespeople in your table and the companies they represent but not every sales entry, you can use the following statement. Note that this may return several entries from the same company, etc. DISTINCT applies to the entire requested result set.

DISTINCT—A keyword that returns only unique rows within a result set

SELECT DISTINCT company, last_name, first_name FROM Sales;

COUNT—A function that returns a numeric value which equals the number of rows matching your query

The COUNT function tells you how many rows are in a result set. As with all functions, it accepts one parameter. This basic example will tell you how many rows are in your table:
SELECT COUNT(*) FROM Sales;

You can also use it to count the number of rows in any result set.
SELECT COUNT(*) FROM Sales WHERE net_amount > 100;

AVG—A function that returns the numeric value that equals the average of the numbers in a specified column

AVG returns the average of all the fields in a column with a numeric data type. It accepts one column name as its parameter, and it will return “0” if it's used on a non-numeric column.

SELECT AVG(net_amount) FROM Sales;
SELECT AVG(net_amount) FROM Sales WHERE company LIKE ‘%ABCD Co%’;

SUM—A function that adds the numbers in a specified column
SUM works just like AVG, except it returns the sum of values in all fields in the result set.
SELECT SUM(net_amount) FROM Sales WHERE net_amount > 100;

MIN—A function that returns the lowest non-null value in a column
MIN returns the lowest, non-null value in the specified column. If the column is a numeric data type, the result will be the lowest number. If it's a string data type, it will return the value that comes first alphabetically.
SELECT MIN(net_amount) FROM Sales WHERE last_name = “Smith”;
SELECT MIN(last_name) FROM Sales;

MAX—A function that returns the largest value in a column
MAX works just like MIN, only it returns the highest non-null value. It too can be used on strings or numbers.
SELECT MAX(net_amount) FROM Sales;
SELECT MAX(company) FROM Sales WHERE net_amount > 100;
The MAX function is sometimes used on columns containing an auto-incremented key field to determine what the next entry’s key ID will be. Unless you’re running a nonpublic database, be wary of using this information to insert the next entry, in case another user beats you to the punch.

GROUP BY— Group by keyword is used to give records in same column rows in grouped manner. Group by can be used on one column name and more than one column name in SQL query

Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Example using the COUNT function

For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.

SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;

Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;
Joins
A join in SQL is a clause that allows` merging of records from one or more than one tables in database. The records from the tables are fetched based on some values that are common to each.

SELECT * from Employees e JOIN Salary s ON e.employeeid=s.employeeid;
Inner JoinTwo tables getting join result set display which is matched records only
Explicit type
SELECT *from shooters s INNER JOIN guntypes g ON s.guntype = g.guntype ;
Implicit type
SELECT *from shooters s , guntypes g WHERE s.guntype = g.guntype ;
Cross join
Is also called Cartesian join. Result of joining each row of the table with each row of the other table
Left outer join - unmatched left table records and matched right table records.
SELECT *from shooters s LEFT OUTER JOIN guntypes g ON s.guntype = g.guntype ;
Right outer join – unmatched right table and matched left table
SELECT *from shooters s RIGHT OUTER JOIN guntypes g ON s.guntype = g.guntype ;
Full outer join merged the result fetched from left and right
SELECT *from shooters s FULL OUTER JOIN guntypes g ON s.guntype = g.guntype ;

It is a simple sql join condition which uses the equal sign as the comparison operator.
Equi joins are SQL Outer join and SQL Inner join.
SQL Non Equi Join:
A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=) operator. Like >=, <=, <, >
SELECT first_name, last_name, subject
FROM student_details
WHERE subject != 'Maths

Keys, Cosntraints, index

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist
Entity Integrity ensures that there are no duplicate rows in a table.
Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.
Referential integrity ensures that rows cannot be deleted, which are used by other records (for example, corresponding data values between tables will be vital).
User-Defined Integrity enforces some specific business rules that do not fall into entity, domain, or referential integrity categories.

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity

CREATE TABLE employee(
EmployeeId INT NOT NULL CONSTRAINT pk_employee PRIMARY KEY (EmployeeId)
,
LName VARCHAR(30) NOT NULL,
FName VARCHAR(30) NOT NULL,
Address VARCHAR(100) NOT NULL,
HireDate DATETIME NOT NULL,
Salary MONEY NOT NULL CONSTRAINT check_sale CHECK (salary > 0)
)
Constraints Enhancements

When you attempt to update or delete a key to which existing foreign keys point. You can control it by using the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. For example, in the previous versions of SQL Server if you wanted to do a cascade delete from the referenced table when the appropriate record in the parent table is deleted, you had to create a trigger which executed on delete of the parent table, but now you can simply specify the ON DELETE clause in the REFERENCES clause.
CREATE TABLE Books (
BookID INT NOT NULL PRIMARY KEY,
AuthorID INT NOT NULL,
BookName VARCHAR(100) NOT NULL,
Price MONEY NOT NULL
)
GO

CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

ALTER TABLE Books
ADD CONSTRAINT fk_author
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID) ON DELETE CASCADE
GO

Constraints are the built-in mechanism for enforcing data integrity. Using constraints is preferred to using triggers, rules, and defaults because built-in integrity features use much less overhead and perform faster than the ones you can create. When you write your own code to realize the same actions the constraints can make you can make some errors, so the constraints are not only faster, but also are more consistent and reliable. So, you should use triggers and rules only when the constraints do not provide all the needed functionality.

Candidate Key - A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
Primary Key - A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.



Select a key that does not contain NULL
It may be possible that there are Candidate Keys that presently do not contain value (not null) but technically they can contain null. In this case, they will not qualify for Primary Key. In the following table structure, we can see that even though column [name] does not have any NULL value it does not qualify as it has the potential to contain NULL value in future
A table can have multiple Candidate Keys that are unique as single column or combined multiple columns to the table. They are all candidates for Primary Key. Candidate keys that follow all the three rules - 1) Not Null, 2) Unique Value in Table and 3) Static - are the best candidates for Primary Key. If there are multiple candidate keys that are satisfying the criteria for Primary Key

When you use the multiple-column constraint format, you can create a composite key. A composite key specifies multiple columns for a primary-key or foreign-key constraint.
The next example creates two tables. The first table has a composite key that acts as a primary key, and the second table has a composite key that acts as a foreign key.
CREATE TABLE accounts (
acc_num INTEGER,
acc_type INTEGER,
acc_descr CHAR(20),
PRIMARY KEY (acc_num, acc_type))

CREATE TABLE sub_accounts (
sub_acc INTEGER PRIMARY KEY,
ref_num INTEGER NOT NULL,
ref_type INTEGER NOT NULL,
sub_descr CHAR(20),
FOREIGN KEY (ref_num, ref_type) REFERENCES accounts
(acc_num, acc_type))
Cluster index & Non cluster index
Primary_Key column is a monotonically increasing integer and that the last three columns are VARCHAR columns. Let's also assume that there is a clustered index on the Primary_Key column, and that there are no nonclustered indexes on the table.

Now, let's assume we run the following query:

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Primary_Key = 1001

When the query optimizer analyzes this query, it will know that the Primary_Key column is the key to a clustered index. So the clustered index will (most likely) be used to locate the Primary_Key of 1001 very quickly using a clustered index lookup. In addition, since the other three columns are part of the clustered index, the values for these three columns are immediately available and can be immediately displayed after the query finishes executing. Keep in mind that all the columns in a clustered index are stored at the leaf level of the clustered index, so SQL Server, in this example, does not have to look elsewhere to find the data to be returned.

Non cluster
Now, let's take a look at the following query:

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Customer_No = 'ABC123'

In this case, when this query is analyzed by the query optimizer, there is no index on the column Customer_No. Because of this, SQL Server will perform a clustered index scan to look for the designated record to return. Since this column is not indexed or unique, every row in the table will have to be scanned until the record is found. Once it is found, the rest of the record (because it is a clustered index and each row's data is part of the leaf index), is immediately available and the data is returned.

If we need to perform the above query often, it would be to our advantage to add a nonclustered index to the Customer_No column. This way, when we run a query like the one above, instead of performing a time-consuming clustered index scan, it can use the nonclustered index on Customer_No to perform an index lookup, which is much faster than a clustered table scan. But is this all we need to do to optimize the above query? Actually, we have forgotten something. If we run the query, let's look at it again below, something else has to happen before our data is returned.

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Customer_No = 'ABC123'

When this query runs, it will use the nonclustered index on the Customer_No column to quickly identify the record using an index lookup. But unlike a clustered index, a nonclustered index only contains the data stored in the index, which is, in this case, only the Customer_No column. On the other hand, our query wants to return three columns: Customer_No, Customer_Name, and Customer_Address, not just key Customer_No. Because of this, SQL Server will now have to perform another step before it can return our data. Once it has located the correct row in the nonclustered index, then SQL Server must then look up the values of the other two columns from the clustered index, where this data is stored
Covering indexes, if used judiciously, can be used to speed up many types of commonly run queries. But covering indexes have some limitations. For example, they are limited to a maximum of 16 columns; they have a 900-character maximum width; certain datatypes cannot be included in them; and adding additional columns to an index makes the index wider, which in turn requires more disk I/O to read and write the rows, potentially hurting performance, especially if the table is subject to many INSERTs, UPDATEs, and DELETEs.

"nonkey column nonclustered indexes." This new feature is a variation of the standard covering index we have been talking about up to this point, but with some advantages over covering indexes.

Let's go back to our example query.

SELECT Customer_No, Customer_Name, Customer_Address
FROM Table_Name
WHERE Customer_No = 'ABC123'

Let's assume, as before, that there is a clustered index on Primary_Key. Now, instead of creating a covering index as described above, what we can do is create a nonclustered index on Customer_No, but instead of adding Customer_Name and Customer_Address as additional key columns to the index, we instead add Customer_Name and Customer_Address as nonkey columns to the nonclustered index.

nonkey column nonclustered index over using a covering index. The benefits include:

All data types are supported, except text, ntext, and image. So you have more datatype options than a covering index.
The maximum number of columns is 1,023, not 16 as with covering indexes.
Because the actual index is narrower, the key is more efficient and can offer better performance over a covering index where all of the columns are part of the key.
Yes, these are small differences, but lots of small differences add up and can help boost the performance of your SQL Server. In fact, you may want to review all of your currently existing covering indexes and change them to nonkey column nonclustered indexes to see if your query performance increases. Depending on the circumstances, performance could indeed increase.

Nonkey columns are created using the INCLUDE clause of the CREATE INDEX statement. For example:

CREATE INDEX IX_Table ON Table (Key_Index_Column) INCLUDE (Column1, Column2, Column3)

More space is required to store indexes with nonkey columns. Nonkey column data is stored at both the leaf level of the index and in the table itself.
Larger indexes mean fewer rows can fit on a page, potentially increasing disk I/O.
Index maintenance is increased for data modifications, potentially hurting performance if nonkey columns are large and the database experiences a high level of data modifications.