Getting to know SQL 2005 Service Broker

Looks likes I’ve got to do a presentation on Service Broker for the SQL 2005 launch – so I’ve been doing some home work on the subject. The best primer I’ve found is http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp?frame=true this is refernenced in the webcast http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032263311&EventCategory=5&culture=en-us&CountryCode=US.  Once you’ve got your head round the basics you’ll want to create your ow broker service…

0

SQL 2005: Making sense of the new Optimistic Concurrency Controls and the readers that block writers

For a long time now Oracle enthusiasts have bashed SQL Server for not having Optimistic Concurrency Controls.  They would demonstrate a SQL Server reader blocking a writer and possibly a writer blocking a reader.  Thus, having appeared to demonstrate that SQL can’t handle these two operations concurrently, they would therefore conclude that SQL can’t scale…

0

SQL 2005 – UPSERT: In nature but not by name; but at last!

SQL Server has long been criticised for not having an UPSERT command, well now in SQL 2005 we have some good news, we have two new TSQL set operators that give us the complex insert/update logic that constitute an UPSERT.  These are INTERSECT and EXCEPT e.g. — UPDATE Destination FROM (Source INTERSECT Destination) — INSERT…

1

Are you still using @@IDENTITY?

Are you still using @@IDENTITY?  It seems many people are.  If you are, are you aware that it might not always return what you expect?  No? then read on.  @@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session.  This means that if a trigger, or similar, were to insert…

1

Cool new OVER Clause (Transact-SQL) in SQL Server 2005

Cool new OVER Clause (Transact-SQL) in SQL Server 2005 to circumvent the not so efficient correlated subquery. Imagine a table: create table grades( StudentID int not null,StudentName varchar(10) not null,Subject varchar(10) not null,Score  int not null) With some values: insert into grades values(1,’John’,’Math’,87)insert into grades values(1,’John’,’Geography’,76)insert into grades values(1,’John’,’History’,98)insert into grades values(1,’John’,’Science’,85)insert into grades values(2,’Sally’,’Crafts’,89)insert into…

1