I had an interesting discussion with some members of a class I am teaching right now about how paging is done. It looks like there are four Functions that allow for paging and using Common Table Expressions. They all can optionally include a separate ORDER BY clause to specify how they are sorted (so your result set can be sorted differently than it is ranked).
I got thinking about writing custom paging code with the new SQL Server 2005 ROW_NUMBER, RANK, and DENSE_RANK functions. I started playing around with code and ended up with this simple ASP.NET 2.0 example. It works with SQL Server 2005 and the Adventure Works sample database. The example uses ad-hoc SQL so it would be easy to show how it works, but moving it into stored procs would be simple.
For those of use who have spent any time in the sample AdventureWorks database, I just found a handy Visio (or HTML) database diagram of the database. Its clean and explains some of their ideas about using schemas in SQL Server. Worth a look if you've ever looked at the AW database.
If you upgrade to RC1 be aware that SQL Server 2005 requires SP2 before it will work with Vista RC1. Yeah, I know there is no SP2...but that's the case. It just doesn't work. I am trying to hack around the problem so I'll let you know if I find a solution...
If you're in Atlanta and want to see me talk about the SQL Server CLR Integration, come by the Atlanta C# Users Group on May 1st (That's today). Meeting starts at 6pm. Here's a link to the User Group's Site:
In a current project I am working on with a distributed team, we use a set of detached database (mdf/ldf) files to keep current the latest version of the database we're working on. I use SQL Server 2000 and 2005 on my local dev-box, but since this customer is going to use SQL Server 2000 I've been trying to keep the work on SQL Server 2000.
I was talking with a bright guy inside the ADO.NET team today when he told me that the DbCommandBuilder supports a new option called ConflictOption. This option alerts the DbCommandBuilder to use one of three methods for detecting concurrency conflicts:
Recently I posted about Timestamps and CommandBuilders and I got a well informed reply by Luciano Evaristo Guerche concerning a related approach of using BINARY_CHECKSUM in SQL Server to do the same thing. I think Luciano's response means to say that if you can't use Timestamp's in the database (like you don't have control over the schema) then BINARY_CHECKSUM is an improvement over the brute force concurrency that CommandBuilders do by default.
I have been attempting to try out some new software from Microsoft (including Glidepath and Visual Studio Database Edition). Both of these require SQL Server Express installed. Problem is that I install a Developer Edition of SQL Server 2005 (as well as 2000) because it is more feature friendly than SQL Server Express. Why does Microsoft insist I have a third Database server? Why can't it prompt me to tell it what database to use, or at least attempt to find SQL Server 2005 as the default instance on the current machine? Just stoopid in my opinion. It's keeping me from trying out and possibly exhaulting these new interesting projects.
"There I said it..."
Hey everybody look...its not a Silverlight post ;)
Looks like MS is going after the 'hobbyist' with their new “Express” line of development products. Wonder if this will really stem the MySQL tide or not. Perhaps the SQL Server Express edition will be a better solution than MSDE.
Evidently MS slipped on thier pledge to deliver the betas at the conference. This article implies that they are claiming that the betas are “imminent“...wonder what that really means? Q3? Q4?
In response to Sahil Malik's recent post on CLR Types as UDT's in Yukon, I have to say I prefer the Typed Xml in Yukon to CLR Types.
I am very impressed with RS at the moment. I looked briefly at this stuff a year ago when it was to be part of Yukon and liked what I saw a lot. Now the Report interface is done via Visual Studio and it is very powerful. My days of trudging through Crystal Reports or creating ad-hoc aspx pages may be over. The feature I really like are:
I have so many SQL Server instances on my local machine others in my home office that I wanted one place to start and stop them all. I liked the start-stop functionality in the SQL Server agent, but I have MSDE instances and SQL Server 2005 instances running too, so a single place to do it all from an icon tray was my goal. So here I've created a simple .NET 2.0 application. I would have done it with 1.1 to make it more accessible for users, but there were some features I needed in 2.0 to make the app work. So if you have the .NET 2.0 Framework installed, check out this new app to control multiple instances of SQL Server a mouse-click away:
Thanks to Bill Booth via the Windows Off Topic Mailing List, I was concerned that the DTC was being used for intra-database transactions with SQL Server 2000. I was looking at timing and using System.Transactions with SQL 2000 is through the roof slower than using traditional client-side transactions. Interestingly SQL 2005 doesn't have this limitation. Lazar Florin has a great blog post that explains what is happening here (found here).
Forget everything I told you. It's all changed in the April CTP. I hope to re-give this talk soon in the Atlanta area with the new bits.
I've been digging deep into SQL Server 2005's CLR integration over the last few days. I am surprised by several specific omissions. My guess is that they have been dropped because of time constraints:
In addition, the current release of the InProc Managed provider has made some interesting choices that are different from earlier version:
|Implementing and Securing an API with ASP.NET Core (new)|
|Building a Web App with ASP.NET Core, MVC6, EF Core and AngularJS|
|Building a Web App with ASP.NET5, MVC6, EF7, and AngularJS (Retired)|
|Best Practices in ASP.NET: Entities, Validation, and View Models|
|Front-End Web Development Quick Start|
|Lessons from Real World .NET Code Reviews|
|Node.js for .NET Developers|
|Application Name||WilderBlog||Environment Name||Production|
|Application Ver||188.8.131.52||Runtime Framework||.NETCoreApp,Version=v1.1|
|App Path||D:\home\site\wwwroot||Runtime Version||.NET Core 4.6.24628.01|
|Operating System||Microsoft Windows 6.2.9200||Runtime Arch||X86|