Important features in T-SQL Server 2005

Important features in T-SQL Server 2005

What are the new keywords in SQL Server 2005?

-          Replicate()

Repeat the characters into specified times.

Specifically this functionality is use for the padding and formatting the result.

SELECT REPLICATE ('JENA', 2) AS "MyLastName Twice"

-          Grouping()

This function use in the case of use POLLUP or CUBE that moment if this two operators are not return the result it will return 0 otherwise 1

-          Rollup

This operator help to generate the total and subtotal of the result set of query based on the specified column.

SELECT

   CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

        ELSE ISNULL(Item, 'UNKNOWN')

    END AS Item,

    CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'

        ELSE ISNULL(Color, 'UNKNOWN')

    END AS Color,

    SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item,Color WITH ROLLUP

-          Cube

This operator is use to get the result in the cross tabulation of all the possible combination of dimension.

 SELECT Item, Color, SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH CUBE

-          Compute

This clause provides the grand total of the result set of a query.

SELECT SalesOrderID, UnitPrice, UnitPriceDiscount

FROM Sales.SalesOrderDetail

ORDER BY SalesOrderID

   COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount)

-          Compute By

This clause provides the grand total and subtotal of result set of a query based on the specified column.

SELECT SalesOrderID, UnitPrice, UnitPriceDiscount

FROM Sales.SalesOrderDetail

ORDER BY SalesOrderID

COMPUTE SUM(UnitPrice), SUM(UnitPriceDiscount) BY SalesOrderID

-          Row_ Number() over (Partition by  ... Order by  ...)

This method returns the sequential number of each records based on the order by column.

SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'RowNumber',c.FirstName, c.LastName

FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID

JOIN Person.Address a ON a.AddressID = c.ContactID

-          Rank()  over (Partition by  ... Order by  ...)

This method return the rank of each row based on the partition and order by clause .If two rows having the same rank it will return the same number instead of two numbers. For the next rank number will come with the previous rank plus one number.

SELECT RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK,

i.ProductID, p.Name, i.LocationID, i.Quantity

FROM Production.ProductInventory i JOIN Production.Product p

ON i.ProductID = p.ProductID

ORDER BY p.Name

Locking Mechanism -- How locking is intergrated with the ISOLATION Level

Locking Mechanism –

In SQL Server whenever multiple uses are trying to access the piece of data at the same moment locking mechanism provides the data in synchronies ways to all the uses. Specifically Locking mechanism is using in programs at the time of deal with isolation levels.

Locking is specifically integrated with the ISOLATION LEVEL.

Types of mode of Locking –

-          Shared  (S)

Shared locks apply on the read (SELECT) operation. Whenever one transaction is reading the data on same moment other transaction cannot modify the data. Once read operation is completed shared lock released from the transaction.

-          Update (U)–

At a single moment only one transaction can modify the data. Other transaction still waits to complete the current transaction.

-          Exclusive(X) –

No other transaction can’t modify the data and read operation can be take place with the help of NOLOCK hints.

At the time of both the operation read and modify, first read operation access the data before performing the modification operation in exclusive lock.

-          Schema

-          Bulk update

-          Key range

-          Intent

Isolation Level – How isolation level affect the transaction

Isolation Level –

At the time of fetching and updating the data from database, By default SQL Server maintaining the isolate levels .But some business point of time need to get other transaction modified data and also restrict other transaction to complete first priority of transaction.

How to set the transaction in SQL server program –

SET TRANSACTION ISOLATION LEVEL {READCOMMITTED | READUNCOMMITED | Serialization | Repeatable | Snapshot}

Types of Isolation Level --

-          Read Uncommitted

-          Read Committed

-          Serialization

-          Repeatable

-          Snapshot

Read UnCommitted –

-          Does not issue shared lock to prevent other transaction from modifying data read by current transaction

Read Committed –

-          Can’t read data that has been modified but not committed. Data can be changed by other transaction between individual statements within the current transaction.

Repeatable Read –

-          Can’t read data that has been modified but not yet committed by other transaction .prevents other transaction that has been read by the current transaction until the current transaction completed.

About Transaction and Distribution Transaction

About Transaction – A C I D

What is SQL Server transaction?

A block of or single statements execute in the database in sequence ways perform all the statement   or none of them. A transaction must satisfy four properties

-          Atomicity

Transactions either commit or roll back  

-          Consistency

It should maintain the state data from start of transaction to the end of transaction.

-          Isolation

It should isolate the modification by the two transactions.

-          Durability

If any disaster occurred in the database before the transaction it rolled back all data otherwise it will store the data in place.

Begin Transaction | Tran trans1

-          Start point of transaction.

Commit Transaction trans1

-          Store the data permanently in the database.

Rollback Transaction trans1

-          Rolled back all the data from begin transaction statements.

Save Transaction trans1

-          Store the some part of transaction data in the database.

BEGIN TRAN T1;

UPDATE table1 ...;

BEGIN TRAN M2 WITH MARK;

UPDATE table2 ...;

SELECT * from table1;

COMMIT TRAN M2;

UPDATE table3 ...;

COMMIT TRAN T1;

MSDTC – Microsoft distribution transaction coordinator

Whenever do the operation on the multiple server that time need to use the distrinbuted trancation.

BEGIN DISTRIBUTED TRANSACTION;

-- Delete candidate from local instance.

DELETE AdventureWorks.HumanResources.JobCandidate

    WHERE JobCandidateID = 13;

-- Delete candidate from remote instance.

DELETE RemoteServer.AdventureWorks.HumanResources.JobCandidate

    WHERE JobCandidateID = 13;

COMMIT TRANSACTION;

About Partition Tables and Indexes

Partition Tables and Indexes –

To manage and control the large tables and indexes required to partition the table data into the different file groups.

Following object need to create for the partitioning the table and index.

-          Partition Function

-          Partition schema

CREATE PARTITION FUNCTION fnName

AS RANGE  LEFT FOR VALUE(int)

CREATE PARTITION SCHEMA scName

AS PARTITION fnName

To(Filegroup names)

CREATE TABLE (ID INT NOTNULL)

ON ScName(ColName)

CREATE UNIQUE CLUSTERED  INDEX ixName

ON TblName(ColName) ON ScName(ColName)

Steps to create the partition table –

-          Create different file group for store the large database.

-          Create Partition function and define by LEFT and RIGHT ranges.

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000);

CREATE PARTITION FUNCTION myRangePF2 (int)

AS RANGE RIGHT FOR VALUES (1, 100, 1000);

CREATE PARTITION FUNCTION myRangePF3 (char(20))

AS RANGE RIGHT FOR VALUES ('EX', 'RXE', 'XR');

-          Attach file groups to the Partition schema based on the partition function.

CREATE PARTITION SCHEME myRangePS1

AS PARTITION myRangePF1

TO (test1fg, test2fg, test3fg, test4fg);

CREATE PARTITION SCHEME myRangePS3

AS PARTITION myRangePF3

ALL TO ( test1fg );

-          Create table on the partition schema.

CREATE TABLE PartitionTable (col1 int, col2 char(10))

ON myRangePS1 (col1) ;

-          Create index on partition table with the schema.

CREATE NONCLUSTERED INDEX IX_PartitionTable_col1

ON PartitionTable(col1)

ON myRangePS1 (col1);

-          Alter the partition table

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );

GO

--Split the partition between boundary_values 100 and 1000

--to create two partitions between boundary_values 100 and 500

--and between boundary_values 500 and 1000.

ALTER PARTITION FUNCTION myRangePF1 ()

SPLIT RANGE (500);

CREATE PARTITION FUNCTION myRangePF1 (int)

AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );

GO

--Merge the partitions between boundary_values 1 and 100

--and between boundary_values 100 and 1000 to create one partition

--between boundary_values 1 and 1000.

ALTER PARTITION FUNCTION myRangePF1 ()

MERGE RANGE (100);

ALTER PARTITION SCHEME MyRangePS1

NEXT USED test5fg;

-          Insert data into the partition table.

-          Retrieve data from the partition table.

SELECT $PARTITION.RangePF1 (10) ;

SELECT $PARTITION.RangePF1(Col1) AS Partition,

COUNT(*) AS [COUNT] FROM PartitionTable

GROUP BY $PARTITION.RangePF1(Col1)

ORDER BY Partition ;

SELECT * FROM PartitionTable

WHERE $PARTITION.RangePF1(Col1) = 5 ;

Full Text Search -- Architecture and Implementation

Full Text Search --

 Architecture –

-          Microsoft full text engine for sql server (MSFTESQL)

-          Microsoft Full text engine filter daemon(MSFTEFD) – that compromise the following s

     Filter

     Protocol handler

     Word breaker

-          MSFTESQL is a window services that is tightly integrated component of SQL Server 2005 which is used as a search engine. Its providing three functionality.

-          Implementing full text catalogs and indexes for the database

-          Querying the database with words, phrases and words with close approximately.

-          Managing the full text catalogs that are stored in SQL Server

Implementing Full text search –

n  Adding Full text catalog

-          Management studio -> storage node -> full text catalog -> add a new catalog

n  Adding Full text indexing table

-          Select database -> select table -> Right click option of full text index and define full text index ->full text indexing wizard

-          - Full text indexing wizard-> Select unique index -> next step ->select the column -> select option how to track the change ---- Automatic – For full population of indexing / Do not track change – for avoid a population and clean the start full population when index is created check box - > Add catalog or create new catalog -> create a table scheduler and catalog scheduler and scheduler them in the time.

Four Type of predicated used in SQL Server

1.       Free text – Similar meaning word not the exacting word

2.       Free text Table

3.       Contains – it can search for a word prefix of a word, synonyms of a word, word formed from another word.

4.       Contains Table

SELECT
FROM Production.ProductReviewWHERE CONTAINS(Comments, ' "learning curve" '); Comments

Cursor – About cursors

Cursor – About cursors

What is cursor?

Cursor occupies some memory location for the execution. After completion of cursor execution it will release the memory for the other resource.

-          Avoid to using the cursor

Type of cursor –

-          Static

-          Keyset

-          Dynamic

-          Fast – Forward

Both keyset and static cursor use the work tables build in the tempdb database.

Static cursor store the data in the work table to compare the data,

Keyset cursor stores the keysets to identify the records through keys.

Dynamic cursor reflect all the changes in the result set when cursor scrolling through the rows.

Fast-Forward only cursor fetches the row serially from start to end .It is not supporting the scrolling.

Steps of writing the Cursor –

-          Declare

-          Open

-          Fetch

-          Close

-          De allocate

DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName

FROM AdventureWorks.HumanResources.vEmployee

WHERE LastName like 'B%';

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0

BEGIN

    FETCH NEXT FROM Employee_Cursor

END;

CLOSE Employee_Cursor;

DEALLOCATE Employee_Cursor;

@@FETCH_STATUS Description

Return value                      Description

0                                           The FETCH statement was successful.

-1                                          The FETCH statement failed or the row was beyond the result set.

-2                                          The row fetched is missing.