How to add Primary key into a new or existing table in SQL Server

Since a primary key is nothing but a constraint you can use ALTER clause of SQL to add a primary key into existing table. Though it's an SQL and database best practice to always have a primary key in a table, many times you will find tables which don't have a primary key. Sometimes, this is due to lack of a column which is both NOT NULL and UNIQUE (constraint require to be a primary key) but other times purely due to lack of knowledge or lack of energy. If you don't have a column which can serve as primary key you can use identity columns for that purpose. Alternatively, you can also combine multiple columns to create a composite primary keys e.g. you can combine firstname and lastname to create a primary key name etc.

Read more »

Difference between close and deallocate cursor in SQL

Cursor in a database is used to retrieve data from the result set, mostly one row at a time. You can use Cursor to update records and perform an operation on a row by row. Given its importance on SQL and Stored procedure, Cursor is also very popular on SQL interviews. One of the popular SQL question on Cursor is close vs deallocate. Since both of them sounds to close the cursor, once the job  is done, What is a real difference between close and deallocate of Cursor in SQL? Well, there is some subtle difference e.g. closing a cursor doesn't change its definition. In Sybase particular, you can reopen a closed cursor and when you reopen it, it creates a new cursor based upon the same SELECT query. On the other hand, deallocation a cursor frees up all the resources associated with the cursor, including cursor name. You just cannot reuse a cursor name by closing it, you need to deallocate it. By the way, if you deallocate an open cursor, it's get closed automatically. Similarly terminating database connection from Server, also closes and deallocates any open cursors.

Read more »

Difference between Oracle SQL Query vs Microsoft SQL Server 2008 or Sybase

Oracle and Microsoft SQL Server are two of the most popular database but they are very different with each other and if you are migrating SQL queries or database, tables from Oracle 11g database to Microsoft SQL Server 2008 then you are bound to face some issues. Main reason of these porting issues are features which are supported and exists in Oracle database, but not available in Microsoft SQL Server 2008 like SEQUENCE, Order by clause in subqueries, and derived tables, derived table without name etc. I am sure there are few more and it will surface based upon different database objects you are using in your tables and queries. On another hand SQL engine for SQL Server and Sybase are very much similar, at least syntactically, and if you are migrating queries from SQL Server to Sybase you can do that without much hassle, of course, there will be slight changes but not as much like Oracle.

Read more »

How to increase length of existing VARCHAR column in SQL Server

You can increase the length of a VARCHAR column without losing existing data in SQL Server. All you need to do is that execute following ALTER TABLE statements. Though, you need to specify NULL or NOT NULL constraint explicitly, depending upon your data.

Here is the SQL command you can use to increase the length of a VARCHAR column in SQL Server:

ALTER TABLE Books ALTER COLUMN title VARCHAR (432)

This command increases the length of title column of Books table to 432 characters. You can use the same command to increase the length of CHAR, NCHAR or NVARCHAR columns as well.

Read more »

How to delete from table using JOIN in SQL Server

It's a little bit tricky to delete from a table while using any type of JOIN in SQL e.g. Inner Join, Left Outer Join, or Right Outer Join. The obvious syntax doesn't work as shown below:

delete from #Expired e INNER JOIN 
Deals d ON e.DealId = d.DealId
Where d.Brand = 'Sony'

here I have a table with a list of expired deals which I want to delete from the Deals tables, but only for Sony.

When I run this SQL Query in Microsoft SQL Server 2008, it gave me following error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'e'.

Now, I am puzzled, how to delete from a table while using INNER JOIN in SQL Server?

Read more »

How to add Columns to an Existing table in SQL Server

Adding a new column to an existing table with data is always tricky. You need to know what data is there, how much data is there, to gauge how long your query is gonna take to complete in production. Also, you cannot add NOT NULL columns into an existing table if they are not empty and you don't have a default value specified. If you know SQL then you probably know that you can add columns to an existing table in SQL Server using ALTER command. It not only allows you to add a column but to drop columns as well. You can also add or drop constraints using ALTER command. Btw, you need to be careful while doing anything with existing tables because of data inside, which presents some challenges while adding new columns or dropping existing ones.

Read more »