Difference between GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server

One of the common question on Microsoft SQL Server interview is, what is the difference between GETDATE(), SYSDATETIME(), and GETUTCDATE(). Even though all three SQL Sever function returns the current date time in SQL Server, there are some subtle differences between them. The main difference between GETDATE() and SYSDATETIME() is that GETDATE returns current date and time as DATETIME but SYSDATETIME returns a DATETIME2 value, which is more precise. The difference between GETDATE() and GETUTCDATE() is in timezone, the GETDATE() function return current date and time in the local timezone, the timezone where your database server is running, but GETUTCDATE() return current time and date in UTC (Universal Time Coordinate) or GMT timezone.

Read more »

How to Compare Date in SQL Server Query? Finding All Rows Between Two Dates

It's tricky to use dates in SQL server query, especially if you don't have good knowledge of how DateTime type works in SQL server. For example, one of the frequently asked SQL queries on the interview is to "select all rows where the date is 20151007?" How would you do that? Does following SQL Query will work correctly

select * from table where date = '20151007'

It may or may not, it entirely depends on upon data in your table. When you only provide date part of a DateTime variable, it uses '00:00:00.000' for time part.

Read more »

How to get just DATE or TIME from GETDATE() in SQL Sever

The GETDATE is one of the most popular built-in methods of  Microsoft SQL Server, but unlike its name suggest, it doesn't return just date, instead it returns date with time information e.g. 2015-07-31 15:42:54.470 , quite similar to our own java.util.Date from Java world. If you want just date like 2015-07-31, or just time like 15:42:54.470 then you need to either CAST or CONVERT output of GETDATE function into DATE or TIME data type. From SQL Server 2008 onward, apart from DATETIME, which is used to store both date and time, You also have a DATE data type to store date without time e.g. 2015-07-31, and a TIME data type to store time without any date information e.g. 15:42:54.470. Since GETDATE() function return a DATETIME value, You have to use either CAST or CONVERT method to convert a DATETIME value to DATE or TIME in SQL Server.

Read more »

Difference between row_number(), rank() and dense_rank() in SQL Server, Oracle.

Though all three are ranking functions in SQL, also known as window function in Microsoft SQL Server, the difference between rank(), dense_rank(), and row_number() comes when you have ties on ranking i.e. duplicate records. For example, if you are ranking employees by their salaries then what would be the rank of two employees of same salaries? It depends on upon which ranking function you are using e.g. row_number, rank, or dense_rank. The row_number() function always generates a unique ranking even with duplicate records i.e. if the ORDER BY clause cannot distinguish between two rows, it will still give them different rankings, though which record will come earlier or later is decided randomly e.g. in our example two employees Shane and Rick have the same salary and has row number 4 and 5, this is random, if you run again, Shane might come 5th.

Read more »

How to remove duplicate rows from a table in SQL

There are a couple of ways to remove duplicate rows from a table in SQL e.g. you can use a temp tables or a window function like row_number() to generate artificial ranking and remove the duplicates. By using a temp table, you can first copy all unique records into a temp table and then delete all data from the original table and then copy unique records again to the original table. This way, all duplicate rows will be removed, but with large tables, this solution will require additional space of the same magnitude of the original table. The second approach doesn't require extra space as it removes duplicate rows directly from the table. It uses a ranking function like row_number() to assign a row number to each row.

Read more »

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 »

How to Convert Result of SELECT Command to Comma Separated String in SQL Server

Sometimes, you need the result of SQL SELECT clause as a comma separated String e.g. if you are outputting ids of white-listed products. By default, the SELECT command will print each row in one line and you get a column of names or ids. If you need a comma separated String then you probably need to use a text editor like Notepad++, Edit Plus, or a tool like Microsoft Excel to convert that column of values into a big CSV String. I often use a text editor like edit plus to replace the \n to comma (,) to create such CSV string because it support regular expression based find and replace operation. Suddenly, a thought came to my mind, why not do this in T-SQL in the SQL Server. It would not only save the time but also give you options like create a sorted list of comma separated String using order by clause. It's a nice trick and useful in many scenarios especially if you are working with data drive application.

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 connect to Microsoft SQL Server database using Eclipse

Though I prefer SQL Server Management Studio to access and work with Microsoft SQL Server database, Sometimes, it's better to connect MSSQL database directly from Eclipse. This will save a lot of time which is wasted on switching between two applications, Eclipse and SSMS. It will also keep your PC fast enough because less application means less overhead. Eclipse IDE allows you to connect to almost all the database you have heard e.g. Oracle, MySQL, PostgreSQL, DB2 etc. All the steps are pretty much same, so once you know how to connect SQL Server database from Eclipse, you can connect Oracle or MySQL by yourself. Since Eclipse connects to the database using JDBC, you need to deploy JDBC drivers in your classpath. This is done part of New Database Connection Profile, one of the steps, which we will see later. In this article, I'll show you step by step guide to connect to Microsoft SQL Server database using Eclipse.

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 »

4 Ways to find Nth highest salary in SQL - Oracle, MSSQL and MySQL

One of the most common SQL interview questions is to find the Nth highest salary of employee, where N could be 2, 3, 4 or anything e.g. find the second highest salary in SQL. Sometimes this question is also twisted as to find the nth minimum salary in SQL. Since many Programmers only know the easy way to solve this problem e.g. by using SQL IN clause, which doesn't scale well, they struggle to write the SQL query when Interviewer keep asking about 4th highest, 5th highest and so on. In order to solve this problem effectively, you need to know about some key concepts like correlated subquery, window functions like ROW_NUMER(), RANK() and DENSE_RANK() etc. Once you know the generic logic to solve this problem, you can tackle all those variations by yourself.

Read more »