Thursday, March 19, 2015

CASE Expressions

There are two types of CASE expressions: simple and searched. The terminology is a little confusing for me. Here's how each type is used.


The simple CASE expression
  CASE @var
     WHEN 'some literal' -- comparing some value to the value next to CASE
       THEN 'YES'
    WHEN 'some other literal'
      THEN 'NO'
    ELSE 'MAYBE'
END

The searched CASE expression
  CASE
    WHEN @var = 'some literal' -- any Boolean
       THEN 'YES'
    WHEN @var = 'some other literal'
      THEN 'NO'
    ELSE 'MAYBE'
  END

The simple CASE expression
  • Allows only a Boolean expression.
  • Evaluates each WHEN statement in order
  • Returns the results of the first WHEN statement that results in TRUE 
  • If none of the WHEN statements is TRUE, the ELSE clause is used (if it is there)
  • If none of the WHEN statements is TRUE and there is no ELSE clause, returns NULL
The searched CASE expression
  • Evaluates each WHEN statement in order.
  • Returns the results of the first WHEN statement that results in TRUE 
  • If none of the WHEN statements is TRUE, the ELSE clause is used (if it is there)
  • If none of the WHEN statements is TRUE and there is no ELSE clause, returns NULL
From MSDN:  CASE (Transact-SQL)

Wednesday, February 4, 2015

Alter Schema

Database objects and types can be moved between schemas using the ALTER SCHEMA statement.  I have never had occasion to use this statement - that's why it's on this blog.  If I blog it, I usually remember it.

To move a database object TableA from schemaOne to schemaTwo,
ALTER SCHEMA schemaTwo TRANSFER schemaOne.TableA

To move a database type TypeA from schemaOne to schemaTwo
ALTER SCHEMA schemaTwo TRANSFER TYPE::schemaOne.TypeA

This can be found on MSDN Alter Schema (Transact-SQL)

Friday, November 4, 2011

Error Installing SQL 2005 on New Multi-Core Server

We encountered an unusual error when installing SQL 2005 x64 Ent Edn on a Windows 2003 R2 x64 Ent Edn server (non-clustered) running on a new server with 48 core and 32Gb RAM.  Other than installing software that was back a couple of versions on new hardware, there seemed to be nothing unusual or difficult about the install (the age should have given me the first clue as to the problem).  The installation process seemed to go well - all the required information was entered and the installer seemed to be nearing the end of its jouney by instlaling the SQL Engine and starting the engine. 

The error message displayed was:
The service did not respond to the start or control request in a timely fashion.
The installer then uninstalled all the components.

Looking through the summary.txt and other install log files SQL Server generates usually gives me the best information about an installation failure.  In this case, the useful information was in application event id 4097 from Dr Watson which stated:
The application, E:\Program Files\MSSQL.1\MSSQL\Binn\sqlservr.exe, generated an application error The error occurred on 11/03/2011 @ 15:25:56.962 The exception generated was c0000005 at address 0000000001B323F0 (sqlservr!DmpGetClientExport)

The problem is that SQL Server 2005 does not install / start correctly on some of the newer hardware configurations.  This problem is discussed in MS Support Blogs.  Per KB954835, you may not be able to install Microsoft SQL Server 2005 on a computer that has a multi-core processor, and one of the following conditions is true:
  • The ratio between logical processors & physical sockets is not a power of 2.
  • The number of physical cores is not a power of 2.
  • The total number of CPUs is an odd number. For example, there are seven or nine CPUs.
The solution is simple enough:  reconfigure the server to use only 1 core when installing.  Then reconfigure the server to use all desired core.  This is done by using the misconfigure command, setting the numcore in the advanced area of the boot.ini tab and rebooting. 

This problem does not occur when installing SQL 2008 or SQL 2008 R2.

Saturday, October 15, 2011

SQL Server Code Name "Denali" is Officially SQL Server 2012

It's official! SQL Server Code Name "Denali" is Officially SQL Server 2012. Release Candidate 0 (RC0) will be available at the end of the year, with the RTM version available the first half of next year. Microsoft MVP Dan English gives us the scoop on his blog.

Wednesday, October 12, 2011

Comparison of SQL Server 2008 R2 Features by Edition

Feature comarison of SQL 2008 R2 by Edition.  I am comaring only the Datacenter, Enterprise, Standard and Express Editions.