PIVOT & UNPIVOT Operators in SQL Server 2008
PIVOT is a new T-SQL operator introduced in SQL Server 2008 which can be used to rotate rows into columns and can create a crosstab query. You can specify the PIVOT operator in FROM clause of your query.
Using PIVOT
Specify the desired values that you want to see in SELECT statement and FROM clause contains the PIVOT statement. Let us take an example
Related Posts:
No commentsDate & Time Data Types in SQL Server 2008
The four new data types for storing dates and times in SQL Server 2008 are
- Date
- Time
- DateTime2
- DateTimeoffset
This post discuss about new date time data types and new date time related functions introduced in SQL Server 2008.
Related Posts:
No commentsT-SQL TIP in SQL Server 2008
You love this if you write a lot of T-SQL code. This tip I can say as T-SQL language syntax enhancement. The following are the enhancements in T-SQL
- Now We can declare and initialize the variables with single statement.
Related Posts:
No commentsTable Expressions in SQL server
Table Expressions are sub-queries where a table is expected. There are two types of table expressions. This post explains how to use the Table expressions and their advantages.
- Derived tables
- Common Table Expressions
Related Posts:
No commentsSQL server Joins or Subqueries?
Select statements that use join operator and join statements can be written in Subqueries and similarly Subqueries can be written in Joins. Writing the Select statements with Join operator can help database engine to find more efficient strategy for retrieving the appropriate data. However each having their own advantage over other. Some problems can be easily solve with Subqueries and some can be solve with Joins. More about SQL Joins can read here.
Related Posts:
No commentsSQL Server Reports Integration with SharePoint 2007
This post explains how to Deploy the SQL server Reports to Office SharePoint Server 2007. SQL Server Reporting Services can operate in two modes, either in Native Mode or Share Point Integration Mode.
Related Posts:
No commentsReport Builder 2.0 in SQL Server 2008
Report builder 2.0 is a report authoring tool that we can use to design and publish reports. We can specify the data source, what data to display on report and which layout you prefer to see the report. When you run the report, the report processor takes all the information you specified and retrieves the data and generates each page as you view it. This post explains step-by-step details of creating the report using Report Builder 2.0.
Using Report Builder you can
Related Posts:
No commentsJoins in SQL server
Fundamentals of joins in SQL server
By using joins, we can get the data from two or more tables based on logical condition between the tables.
The two tables in a query related by
- specifying a column from each table used in the join.
- specifying a logical operator to be used in the comparing the columns.
The following are joins by classification
- Inner join
- Outer join
- Left outer join
- Right outer join
- Cross join
- Self join
Inner join: is a join which returns only the rows for which there is
an equal value in the join column. Inner joins can be specified in either the
FROM or WHERE caluses.
Recommended method is specify the join condition in FROM clause which helps in specifying the other conditions in the WHERE clause.
FROM <First_Table> <Join_Type> <Second_Table>
[ON <join-condition> ]
Example: SELECT *
FROM Employee AS e
INNER JOIN Department AS d
ON e.DepartmentID = d.DepartmentID
Inner join becomes an Equi join when you specify equality condition in JOIN clause.
Outer join: is a join which returns all the rows from the at least one of
the tables mentioned in the FROM clause and meeting the condition in the WHERE clause.
Outer joins can be specified in the FROM clause only.
All rows are retrieved from the left table referenced with a left outer join
and all rows retrieved from the right table with a right outer join.
All rows from the both tables are returned in a full outer join.
Left outer join example:
SELECT
Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName FROM Employee LEFT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
Right outer join example:
SELECT
Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName FROM Employee RIGHT OUTER JOIN Department ON Employee.DepartmentID = Department.DepartmentID
Cross join: A cross join is join which does not have a WHERE clause and
produces the cartesian product of the tables involved in the join.
The size of the cartesian product result set is the number of rows in the
first table multiplied by the number of rows in the second table.
Example:
SELECT p.SalesPersonID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t
Self-Joins: A table can be joined to itself in a self-join. For example, you can use a self-join to find the products that are supplied by more than one vendor.
Example:
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID
Related Posts:
4 commentsSQL server 2005 Features
Development Features
- CommonTableExpressions
Which returns a record set in a statement.
CTE can be thought of as a temporary result set which can be defined in the SELECT,INSERT,UPDATE and DELETE statements. CTE can be self-referencing and can be referenced multiple times in the same query.
The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,...n] ) ]
AS ( CTE_query_definition )
- CLR support To build business objects
(stored procedures,triggers, functions, etc) which enables you to execute .NET code in the DBMS. - ServiceBroker handles the messaging between a sender and receiver in a loosely coupled manner.
- Data Encryption
Native supported functions to encrypt the data in user defined databases. - Microsoft incorporates SMTP mail to improve the native mail capabilities.
- Multiple Active Result Sets(MARS) Allow a persistance database connection to server more requests from a single client.
- SQL server Integration Services (SSIS) has replaced
DTS(Data Transformation Services) as the primary ETL tool and ship with the SQl server for free. - XML enhancements
- Try Catch Functionality
- Data Partioning
Management Features
- Backup enhancements
- Better Email handling
- Full 64-bit support
- Database Mirroring
- Dedicated administrator connection
- Database snapshots
Related Posts:
1 comment
![Recommend [kalyanms1]](http://s3.amazonaws.com/arkayne-media/img/badge/logo-recommend-badge-medium.png){kind=icon}
