Materialized Views in SQL Server

Materialized Views

1. In SQL, a view is a virtual table based on the result-set of an SQL statement.
2. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more actual tables in the database.
3. You can add SQL functions, WHERE and JOIN statements to a view and present the data as if the data were coming from one single table.
4. But if a view performs an operation on millions of records, performance of the view will decrease dramatically and if this operation is performed frequently then the view will access millions of records repeatedly. 
5. To resolve this issue, materialized view comes into picture. 
6. A materialized view is defined same as a regular view but the output is stored in a physical table which is updated frequently whenever the actual tables change. 
7. They are useful to aggregate data in business intelligence applications with complex queries.

Design Patterns

Design patterns are recurring solutions to software design problems found during application development.Design patterns are important building blocks for designing and modelling applications on all platforms.

Design patterns was first presented by Erich Gamma, Richard Helm, 

Ralph Johnson and John Vlisside. They are also called as “Gang 

Of Four”(GoF).

They wrote a book on design patterns titled 

"Design Patterns:Elements of Reusable Object-Oriented Software"

Most of the design patterns are grouped into the following three

types:

1) Creational Patterns

2) Structural Patterns

3) Behavioral Patterns

1) Creational Patterns:

Creation Patterns abstracts the object creation process from the

client. It creates objects for the client rather than having 

clients instantiate objects directly.

Some of the design patterns under this group are:

a) Factory Method

b) Singleton

c) Microsoft Factory

d) Abstract Factory 

2) Structural Patterns:

Structural Patterns are concerned with class and object 

composition. They compose interfaces using inheritance and define 

ways to compose objects to achieve a new functionality.

Some of the design patterns under this group are:

a) Decorator

b) Facade 

c) Adapter 

d) Bridge

3) Behavioral Patterns:

Behavioral Patterns are basically concerned with communication 

between objects.

Some of the design patterns under this group are:

a)Memento

b)Strategy

c)Template Method

d)Command

Difference between Delete and Truncate commands in SQL Server

Delete	Truncate
DELETE is a DML command	TRUNCATE is a DDL command
We can delete all records of the table or only specific records based on filter criteria	Truncate deletes all records of the table and there is no provision to delete specific records
A log is maintained in case of delete and hence it is possible to roll back the transaction	No log is maintained in case of truncate and hence it is not possible to roll back the transaction
We can execute a trigger in case of delete	A trigger cannot be executed in case of truncate
Indexes like identity are not initialized in case of Delete	Indexes like identity are initialized in case of Truncate
Example: Delete from where	Example: Truncate table

Get month list at runtime in SQL Server

WITH CTE
AS 
( SELECT 1 AS MonthNumber
UNION ALL
SELECT  MonthNumber + 1 -- add month number to 1 recursively
FROM CTE
WHERE  MonthNumber < 12 -- just to restrict the month number upto 12
)
SELECT
MonthNumber,
DATENAME(MONTH,DATEADD(MONTH, MonthNumber ,0)- 1)[MonthName]                                                                                                        -- function to list the monthname.
FROM CTE

Difference between Layer and Tier

Layer corresponds to logical separation of code. A layer typically live within the same process and machine.
Typical Layers include:
1) Presentation Layer
2) Business Layer
3) Data Access Layer

Tier corresponds to physical separation of components i.e, different components like exe, dll, etc are deployed on the same server or multiple servers. Hence it can be said that Tiers are physical deployment of Layers.
Typical Tiers include:
1) Presentation Tier
2) Business Logic Tier
3) Data Tier

Remove time part from date in SQL Server

If we want to remove time part from the date without converting the date to varchar.

Example:

SELECT GETDATE() gives the Current Date.
i.e, 2012-01-26 23:38:21.983

Now f I want only the date part i.e, 2012-01-26 00:00:00.000.
But I don't want to convert it to varchar.

Below is the code.

SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

Output:
2012-01-26 00:00:00.000