Stories Tagged with 'SQL Server'


ROW_NUMBER, RANK, DENSERANK and NTILE

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).

ROW_NUMBER()

SQL Server 2005 Paging Demo

Url: http://wilderminds.blob.core.windows.net/downloads/pagingdemo.zip

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. 

AdventureWorks Database Diagram (Visio or HTML)

Url: http://www.microsoft.com/downloads/details.aspx...

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.

Vista RC1 Doesn't work with SQL Server 2005

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...

 

Atlanta C# Users Group - SQLCLR Presenation

Url: http://www.atlantacsharp.org/NextMeeting.aspx

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:

SQL Packager and SQL Server MDF Formats

Url: http://www.red-gate.com/products/SQL_Packager/i...

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. 

SQL Server Timestamps and ADO.NET Concurrency

Url: http://msdn2.microsoft.com/en-US/library/system...

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:

BINARY_CHECKSUM and Database Concurrency

Url: http://wildermuth.com/viewrant.aspx?id=2053

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.

Microsoft Requiring SQL Server Express Sucks...

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..."

Interesting Discussion with Leader of SQL Server and WinFS

Url: http://channel9.msdn.com/showpost.aspx?postid=3...

Hey everybody look...its not a Silverlight post ;)

Extending Reporting Services

VS.NET and SQL Server "Express"?

Url: http://news.com.com/Microsoft+reaches+beyond+pr...

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.

No Yukon and VS.NET Betas at TechEd Europe?

Url: http://news.com.com/Microsoft+reaches+beyond+pr...

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?

Shelved...

Using Data in Reporting Services Footers

Url: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-se...

Yukon and User Defined Types (UDTs)

Url: http://codebetter.com/blogs/sahil.malik/archive...

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. 

Yukon and Whidbey Slip!

Url: http://news.com.com/2100-1012_3-5172166.html?ta...

SQL 2000 Reporting Services

Url: http://www.microsoft.com/sql/reporting/howtobuy...

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:

New SQL Server 2005 Drop on MSDN...September CTP?

Url: https://msdn.one.microsoft.com/Subscriber/1033/...

SQL Server 2005 Database Mirroring Slips to 1H 2006

Url: http://www.microsoft.com/sql/2005/productinfo/l...

SQL Server Monitor - Download Now!

Url: http://wilderminds.blob.core.windows.net/downloads/LocalSqlServerM...

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:

Slow Transactions with System.Transactions and SQL Server 2000

Url: http://blogs.msdn.com/florinlazar/archive/2005/...

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).

If You Attended my SQL Server 2005 CLR Talk Recently...

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.

SQL Server 2005 Omissions

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:

New Article: "SQL Server CLR Integration: Part 1"

Url: http://www.devsource.com/article2/0,1759,179167...


Application Name WilderBlog Environment Name Production
Application Ver 1.1.0.0 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