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
  

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	A	10	15	S1234	12/14/2009
12345	A	10	15	S1233	10/01/2009
12345	A	10	15	S1232	08/02/2009
12346		5	0	S1231	08/01/2009
12347	-	0	0	S1230	10/20/2009
12347	-	0	0	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.

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!