The writing has been on the wall for some time, but a few days ago Microsoft made it official. OLE DB will no longer be supported with SQL Server after the Denali (SQL Server 11.0) release of SQL Server. Microsoft is "embracing" ODBC. "OLE DB will be supported for 7 years from launch" (of SQL […]
Bob Beauchemin
SQL Server Blog
Adieu, OLE DB
Posted on: August 31, 2011 5:19 pm
SQLCLR-based libraries available from TotallySQL
Posted on: August 30, 2011 11:07 am
I’ve always been interested in SQLCLR, and wondered about the potential for producing libraries of useful functions for a variety of specialist purposes. With the inclusion of the spatial and hierarchyid data types in SQL Server 2008 as well as a few SQLCLR-based functions in SQL Server Denali, you can’t help but wonder if the […]
Denali memory allocator changes and SQLCLR
Posted on: August 19, 2011 11:58 am
Once upon a time (well, now, currently), there were two memory allocators in SQL Server, the single-page allocator and the multi-page allocator. The single-page allocator was used for almost everything (data buffers, caches, etc); the multi-page allocator was used for somewhat more esoteric things. Like most of SQLCLR (that's CLR code that runs inside SQL […]
Correlating client and server-side trace in SQL Server Denali
Posted on: August 19, 2011 11:28 am
I'm not usually one for repeating stuff you can find in BOL. Usually, a waste of your time and mine. But this feature, for SQL Server Denali, is pretty well buried; well at least from me, and I knew what I was looking for. I've always been interested in (well, wrote a whitepaper on) using […]
Window clause, range specification, and missing values
Posted on: August 18, 2011 3:12 pm
In the previous post, I insinuated that allowing LAG/LEAD to be sensitive to value RANGEs might help with series of data with missing values. It won't do that unless there's some logic that allows the offset that LAG/LEAD uses to be calculated on a row-by-row basis (i.e. LAG/LEAD uses an expression based on the window […]
Remember: LAG function in SQL Server Denali uses rows
Posted on: August 17, 2011 4:52 pm
You've heard my rant before "measure what you think you are measuring". If not, follow the link. Here's an example using the LAG function, new in SQL Server Denali, to measure sales trends. We'll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity). CREATE VIEW dbo.EmployeeSalesByMonth AS SELECT EmployeeKey as [Employee], […]
Thanks to the Portland SQL Server User Group
Posted on: August 1, 2011 3:41 pm
A (couple of days late) thanks to all the folks that attended my "What's New in Denali" demo fest at Portland SQL Server User Group last Thursday. I hope the code-based explanation of the "zen" of Columnstore Index, Semantic Search/Fulltext Enhancements/FileTable, and Extended Event Graphic Session support was clear. Modulo the heat in the room […]
Denali window clause followup question
Posted on: August 1, 2011 3:04 pm
So, quickly on the heels of the first window clause and last_value() question, came a followup: OK smartie, why does last_value work fine here? I didn't have to change from the default window. What gives? select SalesPersonID, SalesOrderID, first_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as FirstOrderForSalesPerson, last_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as […]
An FAQ about the window clause in SQL Server Denali
Posted on: August 1, 2011 1:47 pm
I've been working with the new Denali T-SQL windowing functionality and ran into someone who asked about this "problem". It's almost sure to become an FAQ. Why does last_value not always "work right"? Take, as an example, the following query (against Adventureworks2008R2) select AccountNumber, SalesPersonID, SalesOrderID, first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID) as FirstOrderForAcct, […]
