If Row Exists Update, Else Insert in SQL Server

A user mailed me a block of C# code that updated a row if it existed and inserted, if the row was new. He wanted the same code to be done in SQL Server as well. If you too have a similar requirement, then here’s a sample query for you:

CREATE PROCEDURE usp_INSERTUPDATEEMP
(
 @EmpID AS INT,
 @LastName AS NVARCHAR(20),
 @FirstName AS NVARCHAR(20)
)
AS
BEGIN
    IF EXISTS (SELECT EmployeeID FROM Employees WHERE EmployeeID = @EmpID)
    BEGIN
        -- Write your update query
        UPDATE  Employees
        SET  FirstName = @FirstName, LastName = @LastName
        WHERE   EmployeeID = @EmpID  
    END
    ELSE
    BEGIN
       -- Write your insert query
       INSERT INTO Employees (EmployeeID, FirstName, LastName)
       VALUES ( @EmpID, @FirstName, @LastName )
    END
END

I am assuming here that there is a table called Employees which has three fields (EmployeeID, FirstName and LastName). If the EmployeeID already exists, then the First and LastName are updated. If there is a new record, it gets added to the table

Alternatively also check the MERGE statement which allows you to performs insert, update, or delete operations in a single statement.

Identify Tables that have Clustered index but no NonClustered indexes

I was recently helping a client determine tables in his SQL Server 2005/2008 which had Clustered Index but did not have any nonclustered index on them. I am sharing the query I tried out to achieve the same and will use the Northwind database as a sample to test the query on

SELECT obj.name as TableName
FROM sys.objects obj
INNER JOIN sys.indexes idx ON idx.OBJECT_ID = obj.OBJECT_ID
WHERE
 (obj.type='U'
 AND obj.OBJECT_ID NOT IN (
  SELECT OBJECT_ID
  FROM sys.indexes
  WHERE index_id > 1)
 AND idx.Index_ID = 1)

OUTPUT

Why is DISABLED returned when I run ServerProperty(‘LicenseType’) on a SQL Server 2005/2008 Installation

I was recently querying the database to track licensing information per seat/ per processor. This is the query I ran on my machine:

SELECT  ServerProperty('LicenseType') as LicenseType,
ServerProperty('NumLicenses') as ProcCount

However the query returned the following results.

When I dug into the reasons, I found out that it is by design that the query returns DISABLED. SQL Server 2005/2008 setup does not add licensing information in the registry during setup (unlike SQL Server 2000)

Here’s a very nice write up by Emma describing the issue and a resolution SAM Considerations for Tracking SQL Server modes

Convert From EST to GMT Time and vice versa in SQL Server

A user asked me if there were any functions to convert EST to GMT time in SQL Server. Well I do not think there is an existing function, but using the DATEADD() function, we should be able to achieve this easily. The difference between EST and GMT is -5 hours, so 4.00pm EST is will be 9.00pm GMT

The query would be like this:

DECLARE @ESTTime as DateTime
SET @ESTTime = '2010-02-13 11:30:55.293'
SELECT DATEADD(HOUR, 5, @ESTTime) as GMTTime

DECLARE @GMTTime as DateTime
SET @GMTTime = '2010-02-13 16:30:55.293'
SELECT DATEADD(HOUR, -5, @GMTTime) as ESTTime

OUTPUT

You can now easily create a user defined function using the above logic!

Mapping SQL Server 2000 System Tables to SQL Server 2005/2008 System Views

I keep emphasizing the need for SQL Server Developers and DBA’s to regularly check out SQL Server Books Online (BOL). This post is yet another example why you should be doing so.

If you have ever wondered to map the system tables that are in the master database in SQL Server 2000 to their corresponding system views or functions in SQL Server 2008, then here’s a very good article in BOL to look at

Mapping System Tables to System Views (Transact-SQL)

Here’s a snapshot of what the table looks like.

This table is extremely useful for developers migrating their code from SQL Server 2000 to SQL Server 2005 or higher. Check the entire table over here

Why should I use System Views now instead of System Tables?

Again, check the BOL to see why you should be using System Views/Catalog Views. Here’s the link: http://msdn.microsoft.com/en-us/library/ms174365(SQL.90).aspx

Determine the Next Identity number of a SQL Server Table

A user recently asked me a simple way to calculate the next identity number of a SQL Server 2005/2008 Table. With the SQL Server functions like IDENT_CURRENT and IDENT_INCR, this requirement becomes simple to achieve. We will take the example of the Orders table in the Northwind database to demonstrate the use of these functions. Here’s how:

USE Northwind
SELECT IDENT_CURRENT('Orders') as CurrentIdentity,
IDENT_INCR('Orders') as Increment,
IDENT_CURRENT('Orders') + IDENT_INCR('Orders') as NextIndentity

OUTPUT

Query between two versions of the Database at runtime

One of my clients was using two versions of the same database and wanted a simple way to be able to check a condition in a query and switch between the two versions at runtime. Here’s a simple way to do it

Note: For demonstration purposes, I am pretending to check a condition in the form of the variable @Chk

DECLARE @Chk As Bit = 1

SET @Chk = 1

IF  @Chk = 1
    USE New_DB 
ELSE 
    USE Old_DB 
     
SELECT custid,custname FROM Customers

Now if @Chk = 1, then the query will be executed against the New_DB. Similarly if @Chk is not 1, the Old_DB will be selected.

Convert Character Data to Binary and Vice-Versa in SQL Server 2008

I was recently working on a query where I had to convert a Character data to Binary and then later convert the Binary to Character data back again. Here’s how to do it.

We will assume that the character data with us is ‘SQLServer’

-- Convert Character to Binary
SELECT CONVERT(binary(9), 'SQLServer', 0) AS 'Char to Binary'

-- Convert Binary to Character
SELECT CONVERT(char(9), 0x53514C536572766572, 0) AS 'Binary to Char'

OUTPUT

When was my SQL Server Installed?

I was going through the SQL Server Security Catalog view and stumbled upon the sys.server_principals view. This view contains a row for every server-level principal. When you install SQL Server 2005/2008, the NT AUTHORITY\SYSTEM login gets created. Querying this view, we can easily retrieve the creation date. Here’s how:

SQL Server 2005/2008

SELECT create_date
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

SQL Server 2000

SELECT createdate
FROM sys.syslogins
where name='NT AUTHORITY\SYSTEM'

OUTPUT

If you know of a simpler way to retrieve the SQL Server installation date, make sure to drop a comment.

Note: This query will not give correct results, if in between your master database became corrupt and you had to rebuild it. I will update this post with other such exceptions(as and when I learn about them) when this query may not return expected results.

SQL Server 2008 R2 Developers Training Kit

The SQL Server 2008 Developers Training Kit has been updated with new content for SQL Server 2008 R2. As given on the site:

The SQL Server 2008 R2 Update for Developers Training Kit is ideal for developers who want to understand how to take advantage of the key improvements introduced in SQL Server 2008 and SQL Server 2008 R2 in their applications, as well as for developers who are new to SQL Server. The training kit is brought to you by Microsoft Developer and Platform Evangelism

The training kit offers the following benefits:

• Learn how to build applications that exploit the unique features and capabilities of SQL Server 2008 and SQL Server 2008 R2.
• Provides a comprehensive set of videos, presentations, demos and hands-on labs
• Contains new content for developers who are new to SQL Server.
• Contains new content for SQL Server 2008 R2.
• Contains all of the existing content from the SQL Server 2008 Developer Training Kit.
• Easy to download and install.

Download the SQL Server 2008 R2 Update for Developers Training Kit