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.

Feedback:

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

Feedback:

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

Feedback:

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!

Feedback:

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

Feedback:

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

Feedback:

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.

Feedback:

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

Feedback:

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.

Feedback:

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

Feedback:

Resolving “No Process is on the Other End of the Pipe” Error in SQL Server

While connecting to SQL Server 2008, you may encounter the error “Named Pipes Provider: No process is on the other end of the pipe.”

A common reason for this error is possibly that named pipes are not enabled on your server or the Named Pipes protocol is listed before TCP as shown below (Observe the Order):

To resolve this error, you can change the order in the Client Protocol Order as shown below:

Go to Start > All programs > SQL Server 2005/2008 > Configuration Tools > SQL Server Configuration Manager. Expand the SQL Native Client Configuration Node > Client Protocols

Now right click on the TCP/IP protocol > choose ‘Order’ and bring TCP/IP up in the client protocol order as shown below:

If the error still does not get resolved, use the same Configuration Management tool to make sure that the named pipes protocol is enabled.

Feedback:

Some Important links to help you upgrade your databases from SQL Server 2000 and 2005 to SQL Server 2008

Since SQL Server 2008 has been released, I find a lot of companies seriously considering the option to move their instances of SQL Server 2000 and 2005 databases to SQL Server 2008. However for a smooth upgrade, there are a couple of points that should be reviewed by a DBA, before carrying out the process. If you have already purchased SQL Server 2008 (SQL Server: How to Buy), then here are some important links from SQL Server Books Online to help you in the upgrade process:

Using Upgrade Advisor to Prepare for Upgrades - Upgrade Advisor is a tool that analyzes instances of SQL Server 2000 and SQL Server 2005 to identify known upgrade issues

Version and Edition Upgrades – A nice chart that shows all the upgrade paths to SQL Server 2008 from previous versions of SQL Server

Backward Compatibility - significant changes that might affect your applications after you upgrade to SQL Server 2008.

How to: Upgrade to SQL Server 2008 (Setup) - upgrade a stand-alone instance of SQL Server to SQL Server 2008

Hardware and Software Requirements for Installing SQL Server 2008 - the minimum hardware and software requirements to install and run SQL Server 2008

Some other important links that may prove helpful are:

Migrating a SQL Server 2000 Log Shipping Configuration to SQL Server 2008

How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances

Upgrading SQL Server 2005 Log Shipping to SQL Server 2008

Upgrading Business Intelligence Components

Considerations for Upgrading Analysis Services

Considerations for Upgrading Reporting Services

Considerations for Upgrading Integration Services

Considerations for Upgrading Data Transformation Services

Let me know if there any other important links you can share and I will update this post.

Feedback:

Syntax for Inserting New Rows with Default Values in SQL Server

Sometimes the simplest of t-sql approaches are often overlooked. Once such instance I came across recently, was a developer trying to insert default values in a SQL Server Table. Here’s a simple way to insert Default Values in a SQL Server table

CREATE TABLE #Employees
(
ID int Identity(1,1) PRIMARY KEY,
EName varchar(50),
Designation varchar(50),
ManagerID int NULL
)

INSERT INTO #Employees DEFAULT VALUES;

SELECT * FROM #Employees

Observe how the DEFAULT VALUES option of the INSERT statement is used to add rows without supplying explicit values. Simple!

OUTPUT

Feedback:

Get a Free copy of "Brad's Sure Guide to SQL Server Maintenance Plans" eBook

Here’s a chance to grab a free copy of Brad McGhee’s eBook Brad's Sure Guide to SQL Server Maintenance Plans. To get a free copy, all you need to do is enter your name and email over here and get an access to this eBook.

Here are some highlights of this eBook

Comprehensive tour of the SQL Server Maintenance Plan Wizard and Designer.

Find out how the Maintenance Plan tools let you configure and schedule core database maintenance tasks such as integrity checks, index reorganizations and rebuilds.

Learn the correct uses for the tool and find out where more advanced solutions, using T-SQL or PowerShell scripting, would be more appropriate.

This free eBook offer is offered by RedGate. RedGate creates some rock solid tools for .NET and SQL Server.

Feedback: