Wednesday, March 31, 2010

Sp_Configure Options


EXAM Objective: Installing and Configuring SQL Server 2008

Sub Objective: sp_configure

Sp_Configure Options

In a previous post I discussed sp_configure and how to use it. In this post I will be covering some of the options and their meanings. Many of the meanings are pretty straight forward but a few of them are a bit obscure. The options I will be looking at now are listed below. These are either options I have come across in preparation for the SQL Server 2008 MCITP certification or that I felt might be of practical value to know.
  • cost threshold for parallelism
  • max degree of parallelism
  • max server memory (MB)
  • min server memory (MB)
  • index create memory
  • min memory per query
  • AWE enabled
  • ad hoc distributed queries
  • filestream access level
  • backup compression default

Thursday, March 11, 2010

Using MAX(datetime) and Sub-Queries to Find the Most Recent Item


Today I was preparing a query that pulled some information from the inventory and sales tables of our ERP. The query itself was pretty easy to craft, but the VP of Sales threw a wrench into the mix; it should only contain information regarding the most recent sales order. Let's imagine my query was providing output like this table. Of course the actual query was more complex, but this gives the important information to understand the problem.

Part_No Rev On_Hand Safety_Stock SO_No SO_Date 
12345 10 15 S1234 12/14/2009
12345 10 15 S1233 10/01/2009 
12345 10 15 S1232 08/02/2009 
12346 S1231 08/01/2009 
12347 S1230 10/20/2009 
12347 S1229 07/15/2009 


What I actually needed the query to deliver were lines 1, 4, and 5. These are only the most recent sales orders associated with each part number.

Friday, March 5, 2010

Removing Duplicate Items from an Array in PowerShell and C#


As a DBA I sometimes have to do some programming or scripting. One of the things that I find very useful is knowing how to remove duplicate items from an array. Most recently I had a C# application I had built that was generating duplicate items (they weren't really duplicates the part numbers were the same, however the revision was different. But the fact that the rev was different was irrelevant to the people consuming the data. Now one of the fields I needed to select was of the text data type, so I could not include DISTINCT in the query's select clause. I had to remove the duplicates from the array holding the data set. The .NET Framework 3.5 had made this a snap. You used to have to loop through the array and perform a comparison, but now you can do it with just a single line of code!