TechBubbles

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

Read more

Related Posts:

No comments

Date & 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.

Read more

Related Posts:

No comments

T-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

  1. Now We can declare and initialize the variables with single statement.

Read more

Related Posts:

No comments

Table 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

Read more

Related Posts:

No comments

SQL 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.

Read more

Related Posts:

No comments

SQL 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.

 

Read more

Related Posts:

No comments

Report 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.

image

Using Report Builder you can

Read more

Related Posts:

No comments

Joins 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

  1. Inner join
  2. Outer join
  3. Left outer join
  4. Right outer join
  5. Cross join
  6. 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 comments

SQL 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