SQL SERVER - Intorduction to Service Broker and Sample Script

SQL SERVER - Intorduction to Service Broker and Sample Script


Service Broker in Microsoft SQL Server 2005 is a new technology that provides messaging and queuing functions between instances. The basic functions of sending and receiving messages forms a part of a "conversation." Each conversation is considered to be a complete channel of communication. Each Service Broker conversation is considered to be a dialog where two participants are involved.

Service broker find applications when single or multiple SQL server instances are used. This functionality helps in sending messages to remote databases on different servers and processing of the messages within a single database. In order to send messages between the instances, the Service Broker uses TCP/IP.

This transaction message queuing system enables the developers to build secure and reliable applications, which are scalable. The developers can design applications from independent components known as "services." If the applications need to avail the functionality of these services, then it sends message to the particular "service."

Loosely coupled applications (programs that exchange messages independently) are supported by the Service broker. The three components of the Service broker are as follows: conversation components (which consist of the conversation groups, conversations and messages); service definition components (which define the conversations); and networking and security components (defines the infrastructure used for exchanging messages between instances)

The maintenance of Service Broker is easy and it is a part of the routine database administration procedure. This is because this functionality forms a part of the Database Engine. Service Broker also provides security by preventing unauthorized access from networks and by message encryption.

Let us understand Service Broker with simple script. Script contains necessary comments to explain what exactly script is doing.
---------------------------- Service Broker -----------------------
In this exercise we will learn how to cofigure Servie Broker and send and recieve messages.
-------------------------------------------------------------------

CREATE DATABASE ServiceBrokerTest
GO
USE ServiceBrokerTest
GO
-- Enable Service Broker
ALTER DATABASE ServiceBrokerTest SET ENABLE_BROKER
GO
-- Create Message Type
CREATE MESSAGE TYPE SBMessage
VALIDATION = NONE
GO
-- Create Contract
CREATE CONTRACT SBContract
(SBMessage SENT BY INITIATOR)
GO
-- Create Send Queue
CREATE QUEUE SBSendQueue
GO
-- Create Receive Queue
CREATE QUEUE SBReceiveQueue
GO
-- Create Send Service on Send Queue
CREATE SERVICE SBSendService
ON QUEUE SBSendQueue (SBContract)
GO
-- Create Receive Service on Recieve Queue
CREATE SERVICE SBReceiveService
ON QUEUE SBReceiveQueue (SBContract)
GO
-- Begin Dialog using service on contract
DECLARE @SBDialog uniqueidentifier
DECLARE @Message NVARCHAR(128)
BEGIN DIALOG CONVERSATION @SBDialog
FROM SERVICE SBSendService
TO SERVICE 'SBReceiveService'
ON CONTRACT SBContract
WITH ENCRYPTION = OFF
-- Send messages on Dialog
SET @Message = N'Very First Message';
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Second Message';
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
SET @Message = N'Third Message';
SEND ON CONVERSATION @SBDialog
MESSAGE TYPE SBMessage (@Message)
GO
-- View messages from Receive Queue
SELECT CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
-- Receive messages from Receive Queue
RECEIVE TOP(1) CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
-- Receive messages from Receive Queue
RECEIVE CONVERT(NVARCHAR(MAX), message_body) AS Message
FROM SBReceiveQueue
GO
-- Clean Up
USE master
GO
DROP DATABASE ServiceBrokerTest
GO


You can download the above script from here<http://www.pinaldave.com/sql-download/scripts/Service_Broker.zip>.

Let me know what do you think of this script and how simply one can configure service broker.

Reference : Pinal Dave (http://blog.sqlauthority.com<http://blog.sqlauthority.com/>)
http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/

Learn English

http://www.englishclub.com/


http://www.englishclub.com/pronunciation/tongue-twisters.htm

SQL Server 2008 - HierarchyID - Part I

http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx

SQL Server 2008 - HierarchyID - Part I

I am excited by the cool new features that SQL Server 2008 is binging up !
SQL Server 2008 is bringing some commendable new features one of the nice feature is HierarchyID. This a new data type that is available in the in the latest July CTP of the SQL Server 2008 Developer Edition.
Organizations have struggled in past with the representation of tree like structures in the databases, lot of joins lots of complex logic goes into the place, whether it is organization hierarchy or defining a BOM (Bill of Materials) where one finished product is dependent on another semi finished materials / kit items and these kit items are dependent on another semi finished items or raw materials.
SQL Server 2008 has the solution to the problem where we store the entire hierarchy in the data type HierarchyID. HierarchyID is a variable length system data type. HierarchyID is used to locate the position in the hierarchy of the element like Scott is the CEO and Mark as well as Ravi reports to Scott and Ben and Laura report to Mark, Vijay, James and Frank report to Ravi.
Scott
|
Mark <- > Ravi
| |
Ben<-> Laura Vijay <-> Frank <-> James
This tree can expand to n nodes.
The average number of bits that are required to represent a node in a tree with n nodes depends on the average fanout (the average number of children of a node). For small fanouts (0-7), the size is about 6*logAn bits, where A is the average fanout. A node in an organizational hierarchy of 100,000 people with an average fanout of 6 levels takes about 38 bits. This is rounded up to 40 bits, or 5 bytes, for storage. It supports the insertion and deletion of nodes.
The HierarchyID can be indexed in two ways
1) Depth First strategy: A depth-first index, rows in a subtree are stored near each other. For example, all employees that report through a manager are stored near their managers' record.
[cid:image001.gif@01CD383A.42FF5220]<http://blogs.msdn.com/blogfiles/manisblog/WindowsLiveWriter/SQLServer2008HierarchyID_13519/DF.gif>
2) Breadth First Strategy : A breadth-first stores the rows each level of the hierarchy together. For example, the records of employees who directly report to the same manager are stored near each other.
[cid:image002.gif@01CD383A.42FF5220]<http://blogs.msdn.com/blogfiles/manisblog/WindowsLiveWriter/SQLServer2008HierarchyID_13519/BF.gif>
We can change the strategy as and when we want but the index needs to be dropped and then a new index has to be rebuilt. If if the data is huge and the index is clustered then the table is converted to heap and then indexed again.
The following are the methods in the SQL Server 2008 Database Engine to support HierarchyID data type.
1) GetAncestor
2) GetDescendant
3) GetLevel
4) GetRoot
5) IsDescendant
6) Parse
7) Read
8) Reparent
9) ToString
10) Write

GetAncestor()
This method is useful to find the (nth ancestor of the given child node.
Syntax: child.GetAncestor ( n )
GetDescendant()
This method is very useful to get the descendant of a given node. It has a great significance in terms of finding the new descendant position get the descendants etc.
Syntax: parent.GetDescendant ( child1 , child2 )
This function returns one child node that is a descendant of the parent.
1. If parent is NULL, returns NULL.
2. If parent is not NULL, and both child1 and child2 are NULL, returns a child of parent.
3. If parent and child1 are not NULL, and child2 is NULL, returns a child of parent greater than child1.
4. If parent and child2 are not NULL and child1 is NULL, returns a child of parent less than child2.
5. If parent, child1, and child2 are all not NULL, returns a child of parent greater than child1 and less than child2.
6. If child1 or child2 is not NULL but is not a child of parent, an exception is raised.
7. If child1 >= child2, an exception is raised.
GetLevel()
This method is useful to find the Level of the current node.
Syntax: node.GetLevel ( )
This function will return an integer that represents the depth of this node in the current tree.
GetRoot()
This method will return the root of the hierarchy tree and this is a static method if you are using it within CLR.
Syntax: hierarchyid::GetRoot ( )
It will return the data type hierarchyID.
IsDescendant()
This method returns true/false (BIT) if the node is a descendant of the parent.
Syntax: parent.IsDescendant ( child )
Parse()
Parse converts the canonical string representation of a hierarchyid to a hierarchyid value. Parse is called implicitly when a conversion from a string type to hierarchyid occurs. Acts as the opposite of ToString(). Parse() is a static method.
Syntax: hierarchyid::Parse ( input )
Read()
Read reads binary representation of SqlHierarchyId from the passed-in BinaryReader and sets the SqlHierarchyId object to that value. Read cannot be called by using Transact-SQL. Use CAST or CONVERT instead.
Syntax: void Read( BinaryReader r )
Reparent()
This is a very useful method which helps you to reparent a node i.e. suppose if we want to align an existing node to a new parent or any other existing parent then this method is very useful.
Syntax: node.Reparent ( oldRoot, newRoot )
ToString()
This method is useful to get the string representation of the HierarchyID. The method returns a string that is a nvarchar(4000) data type.
Syntax: node.ToString ( )
Write()
Write writes out a binary representation of SqlHierarchyId to the passed-in BinaryWriter. Write cannot be called by using Transact-SQL. Use CAST or CONVERT instead.
Syntax: void Write( BinaryWriter w )
Sample Code
Create Table and Index
Use AdventureWorksLT
Go
--Scheme Creation
Create Schema HumanResources
Go
--Table Creation
CREATE TABLE HumanResources.EmployeeDemo
(
OrgNode HIERARCHYID,
EmployeeID INT,
LoginID VARCHAR(100),
Title VARCHAR(200),
HireDate DATETIME
)
Go
--Index Creation
CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo
ON HumanResources.EmployeeDemo (OrgNode,EmployeeID)

Data Insertion
--Insert First Row
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (hierarchyid GetRoot(), 1,'adventure-works\scott', 'CEO', '3/11/05') ;
Go
--Insert Second Row
DECLARE @Manager hierarchyid
SELECT @Manager = hierarchyid GetRoot() FROM HumanResources.EmployeeDemo;
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-works\Mark',
'CTO', '4/05/07')
Go
--Insert Third Row
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = hierarchyid GetRoot() FROM HumanResources.EmployeeDemo;
Select @FirstChild = @Manager.GetDescendant(NULL,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-works\ravi',
'Director Marketing', '4/08/07')
Go
--Insert the First Descendant of a Child Node
DECLARE @Manager hierarchyid
SELECT @Manager = CAST('/1/' AS hierarchyid)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(NULL, NULL),45,
'adventure-works\Ben','Application Developer', '6/11/07') ;
Go
--Insert the Second Descendant of a Child Node
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = CAST('/1/' AS hierarchyid)
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild, NULL),55,
'adventure-works\Laura','Trainee Developer', '6/11/07') ;
Go
--Insert the first node who is the Descendant of Director Marketing
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = CAST('/2/' AS hierarchyid)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(NULL, NULL),551,
'adventure-works\frank','Trainee Sales Exec.', '12/11/07') ;
Go
--Insert the second node who is the Descendant of Director Marketing
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
SELECT @Manager = CAST('/2/' AS hierarchyid)
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild, NULL),531,
'adventure-works\vijay','Manager Industrial Sales', '12/09/06') ;
Go
--Insert the third node who is the Descendant of Director Marketing
--in between 2 existing descendants
DECLARE @Manager hierarchyid
DECLARE @FirstChild hierarchyid
DECLARE @SecondChild hierarchyid
SELECT @Manager = CAST('/2/' AS hierarchyid)
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)
SELECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543,
'adventure-works\james','Manager Consumer Sales', '12/04/06') ;

Procedure to insert Employee Record
--Use Serializable Transaction
CREATE PROCEDURE AddEmployee(@ManagerID hierarchyid, @EmpID int,
@LogID varchar(100), @JobTitle as varchar(200), @JoiningDate datetime)
AS
BEGIN
DECLARE @LastChild hierarchyid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo
WHERE OrgNode = @ManagerID
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)
VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate)
COMMIT
END ;

Hope you like this article I will write more about HierarchyID very soon. If you like the article or have any doubts then drop me an post.
I am attaching the sample SQL Script along so that you can test it in your test environment.

SQL: How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query : Good One

http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query

How Can I Sort A 'Version Number' Column Generically Using a SQL Server Query<http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query>

2down votefavorite<http://stackoverflow.com/questions/3474870/how-can-i-sort-a-version-number-column-generically-using-a-sql-server-query>
1
share [g+]share [fb]share [tw]


I wonder if the SQL geniuses amongst us could lend me a helping hand.

I have a column VersionNo in a table Versions that contains 'version number' values like

VersionNo

---------

1.2.3.1

1.10.3.1

1.4.7.2

etc.

I am looking to sort this, but unfortunately, when I do a standard order by, it is treated as a string, so the order comes out as

VersionNo

---------

1.10.3.1

1.2.3.1

1.4.7.2

Intead of the following, which is what I am after:

VersionNo

---------

1.2.3.1

1.4.7.2

1.10.3.1

So, what I need to do is to sort by the numbers in reverse order (e.g. in a.b.c.d, I need to sort by d,c,b,a to get the correct sort ourder).

But I am stuck as to how to achieve this in a GENERIC way. Sure, I can split the string up using the various sql functions (e.g. left, right, substring, len, charindex), but I can't guarantee that there will always be 4 parts to the version number. I may have a list like this:

VersionNo

---------

1.2.3.1

1.3

1.4.7.2

1.7.1

1.10.3.1

1.16.8.0.1

Can, does anyone have any suggestions? Your help would be much appreciated.



Answer:




If You are using SQL Server 2008

select VersionNo from Versions order by cast('/' + replace(VersionNo , '.', '/') + '/' as hierarchyid);

What is hierarchyid<http://msdn.microsoft.com/en-us/library/bb677290.aspx>

Edit:

Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here<http://www.sqlmag.com/article/sql-server/solutions-to-t-sql-sorting-challenge.aspx>.

The challenge<http://www.sqlmag.com/article/sql-server/t-sql-sorting-challenge.aspx>



Note:

In SQL Server 2008 things are drastically simpler. With slight adjustment of the dot separated list of values, you get the canonical string representation a HIERARCHYID value. Either replace all dots in the string with slashes and add a slash at the beginning and end of the string, or simply add a slash at the beginning and end of the string. Either way, convert the adjusted string to HIERARCHYID, and the values would naturally sort correctly based on the integer values in the string segments. Several people came with such a solution: Razvan Socol, Pawel Potasinski, Peter DeBetta and myself. Here are two examples based on this approach:
select val
from dbo.t1
order by cast('/' + replace(val, '.', '/') + '/' as hierarchyid);


select val
from dbo.t1
order by cast('/' + val + '/' as hierarchyid);