Archive For The “SQL Server” Category
On my quest for DDD/TDD nirvana, I’m going to start documenting moments of insight on real projects when design problems are solved, principles suddenly make sense for the first time, or pitfalls are avoided. I want to do it partly for posterity, and partly to help others who are also learning. Here’s the first one. [...]
Last week I wrote a SQL query to estimate how many columns are missing from foreign or primary keys. This works because of our naming convention for database keys: We use a Code suffix for natural keys e.g. CountryCode = NZ We use an ID suffix for surrogate keys e.g. EmployeeID = 32491 This script [...]
So, it seems Service Pack 1 for Visual Studio 2008 adds some support for SQL Server 2008, in that you can now connect and browse SQL Server 2008 servers in the Server Explorer. This’ll let you do cool stuff like generate code with LINQ to SQL, but there’s one important feature missing: Where’s the SQL [...]
Imagine you have a list of unrelated items in your .NET application, and you need SQL Server to do something for each one. For example: A customer has a shopping cart containing a list of 10 product IDs. The shopping cart is stored in ASP.NET session memory on the web server. How can you retrieve [...]
During my foray into XML SQL queries this week, I was presented with another challenge. Instead of getting just the immediate children of a category, I now needed to recursively select all children from a tree – to an unlimited depth. A Common Table Expression (CTE, aka WITH statement) can also be called recursively, but [...]
