Change SQL Server 2008 R2 from partition count from 1000 to 15000

exec sp_db_increased_partitions '', 'ON'

SQL Server Management Studio (SSMS) 2005/2008/2012 Auto Recovery Files

When I started my PC today, I observed it has restarted forecefully due to windows auto update. I quickly opened Sql Server Management Studio (SSMS) wondering if my files will be recovered automatically as SSMS prompts usually. But It didn't.

I was wondering if I need to rewrite the script. but thought of sniffing temp folders to try my luck. A windows search of "*.sql" pointed me to some temp files in folder "C:\Users\\Documents\SQL Server Management Studio\Backup Files\Solution1".

That's it, SSMS keeps unsaved scripts in this folder by default. You can disable this feature if you would like.

There is no direct option to enable/disable Auto Recover settings in 2005/2008. If you are too keen you can do so by tweaking registry setting, here are the steps:

-- SSMS 2005/2008

- Open registry editor (type regedit in windows run)
- For SSMS 2005, Go to the key HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover
- For SSMS 2008, Go to the key HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\General\AutoRecover
- Change DWORD "AutoRecover Enabled" to 1 for Enabling and 0 for Disabling Auto Recovery

-- SSMS 2012

There is a better way to change this setting in SSMS 2012

- Open SSMS 2012 and click on Tools >> Options

- In Options dialog box click on Environment >> AutoRecover

- Check/Uncheck "Save AutoRecover information every" checkbox to enable or disable Auto Recovery
- You can modify the Auto Save interval between 1 to 60 minutes
- There is an setting for the number of days Auto Recover information can be persisted.

It is possible to modify registry setting for SSMS 2012 as well. The location of this registry key has changed to HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\General\AutoRecover

How To Retrieve SQL Server Database Table Size

Today, while working on my data warehouse performance tuning and cleanup activity, I wanted to identify the large space hungry tables in my database so that they can be compressed to save some space. I was aware of system stored procedure sp_spaceused which can be used to retrieve space usage details of individual tables. I came up with this little script to list the space details of all tables in my database so that this information can be shared in a report.

---------------------------------------------------------

-- Author : Irfan Sheikh (http://iirfan.blogspot.com/)

-- Date : 10/01/2012

-- Description :  Script to list space used by individual database tables

---------------------------------------------------------

USE [DatabaseName]

GO

-- Declare table variable to store table space information

DECLARE @tabSpace TABLE

(

name varchar(50),

[rows] decimal,

reseved varchar(50),

data varchar(50),

index_size varchar(50),

unused varchar(50)

)

-- Declare table variable to store table names

DECLARE @Tabs TABLE (ID INT IDENTITY(1,1), TableName VARCHAR(50))

-- Populate list of tables in current DB

INSERT INTO @Tabs (TableName)

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE @Ctr INT

DECLARE @TabName VARCHAR(100)

-- Initialize counter for Looping

SELECT @Ctr = MIN(ID) FROM @Tabs

SELECT @TabName = TableName FROM @Tabs WHERE ID = @Ctr

WHILE (@TabName IS NOT NULL)

BEGIN

      -- Populate output of sp_spaceused in Table variable

      INSERT INTO @tabSpace

      EXEC sp_spaceused @TabName

     

      SET @Ctr = @Ctr + 1

      SET @TabName = NULL

      SELECT @TabName = TableName FROM @Tabs WHERE ID = @Ctr

     

END

-- Final Select

SELECT * FROM @tabSpace ORDER BY CAST(REPLACE(DATA,'KB','') AS DECIMAL) DESC

Here is sample output of this script:

name	rows	reseved	data	index_size	unused
DatabaseLog	1591	6672 KB	6528 KB	72 KB	72 KB
spt_values	2506	336 KB	152 KB	152 KB	32 KB
spt_monitor	1	16 KB	8 KB	8 KB	0 KB
MSreplication_options	3	16 KB	8 KB	8 KB	0 KB
ProductCostHistory	0	0 KB	0 KB	0 KB	0 KB
ProductDescription	0	0 KB	0 KB	0 KB	0 KB
ShoppingCartItem	0	0 KB	0 KB	0 KB	0 KB

Circular Queue of Integers Using a Simple Array

Q#2. Implement a circular queue of integers of user-specified size using a simple array. Provide routines to initialize(), enqueue() and dequeue() the queue. Make it thread safe.

Ans.

Analysis:

A queue is a First in First Out (FIFO) type Data Structure. The element which is added first is removed first. In order to implement a circular queue, we will have to keep track of the queue start index, queue end index and a variable to track the number of elements in queue.

Representation of circular array:

A circular array with six elements, Total queue capacity 8 elements:

Start Index = 0		End Index = 5		No of Elements = 6
1	2	3	4	5	6

After dequeing 2 elements

Start Index = 2		End Index = 5		No of Elements = 4
		3	4	5	6

After enqueing 3 elements

Start Index = 2		End Index = 0		No of Elements = 7
9		3	4	5	6	7	8

Approach:

As required we have to define three methods Initialize, Enqueue and Dequeue. Where Initialize will initialize/reset the Queue Array, Enqueue will add elements and Dequeue will remove elements from the queue. Further, we will implement locks on Array and variables to make this implementation thread safe.

Pseudo Code:

DECLARE QueueStart=0, NoofElements=0, QueueArray                                // Declare Queue Variables

FUNCTION Initialize(INT ArraySize) :

QueueStart=0, NoofElements=0, QueueArray  = NEW INT[ArraySize] // Initiate Queue Variables

FUNCTION Enqueue(INT NewElement) :

QueueArray[NewPosition] =   NewElement                        // NewPosition method returns new Index

NoofElements +=1

FUNCTION Dequeue() :

RETURN QueueTopElement                                                        // Return element from top of Queue

NoofElements -=1

QueueStart = NewQueueStart                                                  // QueueStart is set to new Start Index

C# Code:

 public class CircularQueue  
 {  
      // Variable Declaration  
      private int _noofElements = 0;  
      private int _queueStart = 0;  
      private int[] _queueArray;  
      private object _lock;  
      public int Length  
      {  
           get  
           {  
                return _noofElements;  
           }  
      }  
      //Function to get Next Index in circular array  
      public int NextIndex()  
      {  
           int extendedSize = _queueStart + _noofElements;  
           // Restart new index from start of array if end of array is reached  
           return extendedSize >= _queueArray.Length ? extendedSize - _queueArray.Length : extendedSize;  
      }  
      //Function to Initialize Queue Array and Reset Variables  
      public void Initialize(int Size)  
      {  
           // Initialize Circular Queue Array        
           lock(_lock)  
           {  
                _queueArray = new int[Size];  
                _queueStart = 0;  
                _noofElements = 0;  
           }  
      }  
      public void Enqueue(int NewMember)  
      {  
           if(_queueArray.Length == _noofElements)  
                throw new Exception("Queue is Full");  
           // Set member at new circular index  
           lock(_lock)  
           {  
                _queueArray[NextIndex()] = NewMember;  
                _noofElements++; // Increment element counter  
           }  
      }  
      public int Dequeue()  
      {  
           if(_noofElements == 0)  
                throw new Exception("Queue is Empty");  
           int temp = _queueArray[_queueStart]; // Store position value in temp variable  
           lock(_lock)  
           {  
                _queueArray[_queueStart] = 0; // Reset position value  
                _queueStart = (_queueStart == _queueArray.Length - 1) ? 0 : _queueStart + 1; // Get new start position  
                _noofElements--; // Decrement element counter  
           }  
           return temp;  
      }  
      public void Print()  
      {  
           foreach(int i in _queueArray)  
           {  
                Console.Write("{0:D2} | ", i);  
           }  
           Console.WriteLine();  
      }  
 }  

Algorithm Problem : Sort an Array of Three Color Balls

I have been practicing Algorithm problems for a while and thought it would be a good idea to post it for people with similar intrested. I will post new questions as often as I get time from my work schedule. Your feedback is welcome :)
---------------------------------------------------------------------------------------------------------

Q#1. Consider an Array of balls. A ball can be of one of the three colors Red/Green/Blue. Ask is to sort this array.

Follow up Question, Perform this operation in O (n) time.

Solution:

Analysis:

Let’s reduce some ambiguities from the problem.

n  Is there a weightage associated to colors of these balls?
No, consider all balls to have equal weightage.

n  By sorting is there a particular ordering required of these colors?
Yes, Red followed by Green and then Blue

We can consider the array of balls as a char array of length n with characters R, G and B. The array has length ‘N’

Unsorted array can be depicted as:

G

R

B

R

G

R

B

G

G

B

Sorted array can be depicted as:

R

R

R

G

G

G

G

B

B

B

Approach:

Being specific to this problem, we should try to arrange Red color at the start of the array, Blues at the rear of the array, Green’s will automatically be arranged in between.

Pseudo Code:

DECLARE CtrRed=0, CtrBlue=N-1                                 // Initiate Red and Blue counter index

FOR I = 0 TO N-1                                               // Control Loop

IF CtrRed < CtrBlue                          // Red Index cannot be greater than Blue Index

IF Array[I] = ‘R’ THEN SWAP(Array[I], Array[CtrRed++])                  //Swap Red Color

IF Array[I] = ‘B’ THEN SWAP(Array[I], Array[CtrBlue--])                  //Swap Blue Color

I = I - 1                //Decrement the counter when Blue Ball is found, since swapped color may be Red

C# Code:

       public static char[] SortColors(char[] InputArray)

        {

            //Error Handler

            if (InputArray == null || InputArray.Length == 0)

                throw new Exception("Input Array Is Null or Empty");

            //Variable Declaration and Initialization

            int ctrRed = 0, ctrBlue = InputArray.Length - 1;

            for (int i = 0; i < ctrBlue; i++)

            {
                if (ctrRed < ctrBlue)

                {
                    if (InputArray[i] == 'R')
                    {
                        Swap(ref InputArray[i], ref InputArray[ctrRed++]);
                    }

                    else if (InputArray[i] == 'B')
                    {
                        Swap(ref InputArray[i], ref InputArray[ctrBlue--]);  
                        i--; 
                    }
                }
            }

            return InputArray;

        }

        public static void Swap(ref T InputA, ref T InputB)

        {

            T temp = InputA;
            InputA = InputB;
            InputB = temp;
        }

Time complexity for this algorithm is O(n)

Microsoft Dynamics CRM 4.0 -- Multi Currency Feature -- Transaction Currency

Microsoft Dynamics CRM 4.0 is a multicurrency system, in which each record can be associated with its own currency. This currency is called the transaction currency. The multicurrency features enable users to perform financial transactions like opportunities, quotes, orders, and invoices in multiple currencies. This feature also provides a currency choice to the end user when a financial transaction occurs.

Multiple records in different transaction currencies can be aggregated, compared, or analyzed with regard to a single currency, by using an exchange rate. This is known as the base currency. You first define a base currency for the organization and then define exchange rates to associate the base currency with transaction currencies. The base currency is the currency in which other currencies are quoted. The exchange rate is the value of a transaction currency equal to one base currency.

By using the transaction currency properties you can do the following:

• Select the currency in which you want to define and transact opportunities, quotes, orders, and invoices.
• Define currency exchange rates in relation to the base currency.
• Define transaction currencies and define an exchange rate to associate the base currency with the transaction currency.
• Capture the value of the transaction in the base currency and the transaction currency in all financial transactions.
• Define product pricelists for each currency.

To use multiple currencies, the base currency must be defined for an organization during server installation and organization setup. After the base currency is set for an organization during server installation, it cannot be changed. This value is stored in the organization.basecurrencyid property.

Transaction currencies are defined as a part of the system settings. There can be unlimited transaction currencies defined. Transaction currencies are related to the base currency with the definition of a currency exchange rate.

After the definition of base and transaction currencies, the pricelists must be defined for a currency. An organization can have multiple pricelists, which are also typically defined for a target geographical market in the local currency.

All entities that are involved in financial transactions support transaction currency. The currency is typically inherited from the account, opportunity, and so on, but can be changed as needed.

All money properties in an entity instance share the same transaction currency. For example, see the account.creditlimit property. For each money property in an entity, the system automatically creates a corresponding read-only, system calculated, money property that is called the "base". This is a money property that stores the value of the corresponding property in a base currency equivalent. For example, see the account.creditlimit_base property.

The following formula is used to calculate the base value.

creditlimit_base = creditlimit / exchangerate

Visit MSDN for more details.

SQL Server : Command to Reset Identity Column of a table

Many times we need to reset the Identity column of a table in Sql Server, we can do this by going to Sql Server Enterprise Manager and Reseting the Identity, Recently I came across a command that does the same..

Syntax:-
DBCC CHECKIDENT(strTableName, RESEED, intSeed)

where strTableName is the Name of the table for identity reset, intSeed is the Seed from which Identity should start now.


e.g. DBCC CHECKIDENT('tblUserDetails', RESEED, 10)

will reseed the Identity column in tblUserDetails table, now the new entry will have its Identity column value as 11.

-------------------------------------------------------------
Irfan Sheikh