Category Archives: SQL Server

When adding a new column with a default constraint to an existing table, keep in mind what you want your existing records to contain.  If you add the new column and set it to be allow nulls, then all of the existing records will contain a NULL, and any inserted records will have the default value.  However, if you set new column’s nullability to be NOT NULL, then the existing records will get back-filled with the default value.

Here is a quick sql snippet to show the behavior:

CREATE TABLE Test (Id INT  IDENTITY(1,1) NOT NULL, Name  VARCHAR(10) NULL)
INSERT Test (Name) VALUES ('you')
INSERT Test (Name) VALUES ('me')
SELECT * FROM Test

ALTER  TABLE Test ADD  FlagNull BIT NULL CONSTRAINT  DF_Test_FlagNull DEFAULT ((0))
ALTER TABLE Test  ADD FlagNotNull BIT NOT NULL CONSTRAINT  DF_Test_FlagNotNull DEFAULT ((0))
SELECT * FROM Test

INSERT  INTO Test (Name) VALUES ('us')
SELECT * FROM  Test

Id  Name
1   you
2   me

Id  Name  FlagNull  FlagNotNull
1   you   NULL      0
2   me    NULL      0

Id  Name  FlagNull  FlagNotNull
1   you   NULL      0
2   me    NULL      0
3   us    0         0

As shown the FlagNull column has a NULL for the first 2 records as opposed to the FlagNotNull column has the default value for the same records.

Recently, I had the opportunity to brush up on my SQL Server memory tuning skills.  It’s been a while since I needed them, but I thought I’d throw out what I used for someone else’s future benefit.

First, some background knowledge:
There are different physical memory limits for each Windows release.
The virtual address space (set of virtual memory addresses) is limited to 4 GB for 32-bit Windows.
The virtual address space is divided into two 2 GB partitions: one for use by the processes and other reserved for use by the system.
The address space (amount of physical memory) is limited to 2 GB for a 32-bit process.

In tuning SQL Server, there are 3 key things to look at: Physical Address Extension (PAE), Address Windows Extensions (AWE), &  4 Gigabyte Tuning (4GT).

Physical Address Extension (PAE) is a switch (/PAE) that is added to the boot.ini that allows an x86 processor to access and use more than 4GB of physical memory.  It’s only used for 32-bit versions of Windows, as x64 processors can access more than 4 GB natively.  Most versions of Windows since 2000 Advanced Server support it.

4 Gigabyte Tuning (4GT) is a switch (/3GB) that is added to the boot.ini that increases the processes portion of the virtual address space from 2 GB to 3 GB.  4GT will limit the available RAM to 16GB, so if the server has more than 16GB RAM, 4GT should not be used.

Address Windows Extensions (AWE) is a set of APIs that allows applications to access more than 4GB of RAM.

Which ones to use is determined by how much RAM your server has.  Here is a general rule of thumb for what to use and when:

Of course, doing research is part of the fun, so here are some references to keep handy:
Memory Limits for Windows Releases:  http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx
4GT:  http://msdn.microsoft.com/en-us/library/bb613473(VS.85).aspx
4GT Technical Reference:  http://technet.microsoft.com/en-us/library/cc778496(WS.10).aspx
AWE:  http://msdn.microsoft.com/en-us/library/aa366527(VS.85).aspx
PAE:  http://msdn.microsoft.com/en-us/library/aa366796(VS.85).aspx

Happy tuning!

During the course of one of my last blog posts on Cursors, I ran across a niche piece of information that I didn’t realize.  I had always read that FAST_FORWARD CURSORs were the fastest and least locking of the different CURSOR types.  What I didn’t realize was that if the SELECT statement joins on one or more tables with a trigger table (INSERTED/DELETED), the CURSOR is converted to a STATIC CURSOR!  Unfortunately, most of the tables that I work with have some sort of trigger.  So this was really important information.  Read more about it in this MSDN article.

Differences between STATIC and FAST_FORWARD CURSORs:
They are both read-only concurrency model.  This means that no locks are held on the base table(s).  However, while fetching a cursor row, a Shared Lock will be acquired.  The Shared Lock can be avoided with a NOLOCK hint on the SELECT, or via isolation level.  However, once the row is fetched, the locks are released.
STATIC:  Think of this as a snapshot of the result set.  When it is opened, a temporary table is created in tempdb, which adds additional pressure on the database.  It does not reflect any types of changes to the underlying database, such as INSERTs, UPDATEs, or DELETEs.  This is a read-only cursor that can go forward and backwards.  There can be significant impact on server resources for creating, populating, and cleaning up the snapshot in the tempdb database.
FAST_FORWARD:  This will create a fast forward-only, read-only cursor with performance optimizations enabled.  It can only go from the first row forward to the last row.  It operates directly on the base table(s).  Unlike a static cursor, rows are not retrieved from the database until they are fetched.  Any changes to a row (from INSERT, UPDATE, DELETE statements) before the row is fetched, will be reflected in the fetched row.

Disclaimer:  If possible, avoid using cursors.

Here is a quick way to find the last reboot date and time of a SQL Server instance.

For SQL Server 2000 or 2005, use:

SELECT crdate FROM master.dbo.sysdatabases WHERE name = 'tempdb'

For SQL Server 2008, use:

SELECT sqlserver_start_time FROM master.sys.dm_os_sys_info

A few months back, we had a stored proc at work that was using CURSORs pretty heavily.  It had CURSORs inside of CURSORs.  This proc was one of our oldest, biggest, and most involved procs at over 2050 lines.  On larger clients, it could be called thousands of times a day.  All of the cursors were FAST_FORWARD types, which should have at least been somewhat performance friendly.  However they were SELECTing from tables that had triggers which implicitly converted the cursor type to be STATIC.  That’s not good, because it was locking up the SELECTed table and causing all kinds of headaches.

So the decision was made to rewrite it to use table variables and WHILE LOOPs.  This sped the proc up immensely, as measured by elapsed time, at the cost of using more in-process memory.  You see, when a CURSOR is used, it basically creates a temp table on TempDB and iterates through it.  With a STATIC cursor, the selected table has locks placed on it in the transaction tables.
The CURSOR alternative is to create a table variable containing the rows that would have gone in the CURSOR and use a counter variable to loop through it using the IDENTITY column on the table variable.  It’s still row-by-row processing, but it’s faster, more efficient, and doesn’t lock.  The only downside is that it will use more RAM.  So beware if you’re using SQL 2000, SQL Express, or an MSDE version as you may see performance degrade if the sqlservr process hits it’s memory ceiling.

I don’t think I need to say this, but will anyway.   Changing CURSORs into WHILE LOOPs are better, but CURSORs into a set-based query is best.  It’s always best to do a query in a single batch as opposed to row-by-row.

CURSOR code:

DECLARE @SomeID INT
DECLARE myCursor CURSOR LOCAL FAST_FORWARD  FOR
  SELECT DISTINCT  
    SomeID
  FROM dbo.TableA
  WHERE ID =  @ID

OPEN myCursor
FETCH NEXT FROM myCursor INTO  @SomeID

WHILE(@@FETCH_STATUS  <> -1)
BEGIN
  --do  activity
END

TABLE VARIABLE, WHILE/LOOP code:

DECLARE 
  @rowCount INT, 
  @currentRow  INT, 
  @SomeID  INT

DECLARE @tableVariable TABLE (RowID INT IDENTITY(1,1), SomeID INT NULL)

INSERT INTO  @tableVariable(SomeID)
  SELECT 
    SomeID
  FROM dbo.TableA
  WHERE ID = @ID

SELECT @rowCount = @@RowCount, @currentRow =  1

WHILE @currentRow<=@rowCount
BEGIN
  SELECT @SomeID =  SomeID FROM @tableVariable WHERE RowID =  @currentRow
  --do  activity
  SET  @currentRow = @currentRow + 1
END