Difference between char, varchar, nchar and nvarchar data type in SQL Server database

What is difference between char and varchar in SQL, followed by nchar and nvarchar, is one of the popular SQL interview question, and surprisingly not every programmer knows this basic difference. If you go with name, which you should, than you can figure out that char is a fixed length data type while varchar should be variable length data type. Though all char, varchar, nchar, and nvarchar are used to store text or String data there are subtle differences between them. As I said char is fixed length, which means a variable or a column e.g. Zipcode char(10) will take only 10 bytes to store data, including space. On the other hand a varchar variable or column will take variable space, depending upon data you store + 2 additional bytes for storing length. For example a varchar column name varchar name(20) will take 6 bytes if you store "Jack" (4 + 2) and 7 bytes if you store "Jones" (5 + 2). In order to get better performance, you should use char for fixed length columns e.g. zipcode, where every row are under certain length e.g. 6 for India, and 5 + 4 digits postal codes for USA. On the  other hand, for a variable length column it's better to use varchar data type to save the space,  which is lost in case of char type, if actual data is always way less than capacity. In particular this question is next in series of couple of popular SQL interview question, e.g. difference between WHERE and HAVING clause and  writing SQL query to join three tables. If you come across any other interesting SQL queries than you can also share with us, if you don't know answer, we will try to find out together.

Difference between Clustered Index and Non Clustered Index in SQL Server - Database Interview Question

In SQL Server database there are mainly two types of  indexes, Clustered index and Non Clustered index, and difference between Clustered and Non Clustered index is very important from SQL performance perspective. It is also one of the most common SQL Interview question, similar to difference between truncate and delete,  primary key or unique key or  correlated vs non correlated subquery. For those, who are not aware of benefits of Index or why we use index in database, they help in making your SELECT query faster. A query with index is sometime 100 times faster than a query without index, of course depending upon how big your table is, but, you must index on columns which are frequently used in WHERE clause of SELECT query, or which forms major criterion for searching in database. For example in Employee database, EmployeeId or  EmployeeName are common conditions to find an Employee in database. As I said, there can be either clustered index or non clustered index in database, former is used to decide how data is physically stored in disk and that's why there can be only one clustered index in any table. In this article, we will explore more about both of this indexes and learn some key difference between clustered and non clustered index from interview and performance perspective. 

Difference between LEFT and RIGHT OUTER Joins in SQL - MySQL Join example

There are two kinds of OUTER joins in SQL, LEFT OUTER join and RIGHT OUTER join. Main difference between RIGHT OUTER join and LEFT OUTER join, as there name suggest, is inclusion of non matched rows. Sine INNER join only include matching rows, where value of joining column is same, in final result set, but OUTER join extends that functionality and also include unmatched rows in final result. LEFT outer join includes unmatched rows from table written on left of join predicate. On the other hand RIGHT OUTER join, along with all matching rows, includes unmatched rows from right side of table. In short result of LEFT outer join is INNER JOIN + unmatched rows from LEFT table and RIGHT OUTER join is INNER JOIN + unmatched rows from right hand side table. Similar to difference between INNER join and OUTER join, difference between LEFT and RIGHT OUTER JOIN can be better understand by a simple example, which we will see in next section. By the way joins are very popular in SQL interviews, and along with classic questions like finding second highest salary of employee, Inner join vs outer join or left outer join vs right outer join is commonly asked.

Migrating SQL Query from Oracle to SQL Server 2008 or Sybase

Oracle and Microsoft SQL Server are very different than each other and if you are migrating SQL queries or database, tables from Oracle 11g database to Microsoft 2008 SQL server than 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 sub queries 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 other 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. So if you are migrating from Oracle to Sybase or SQL Server its most likely same job and you should first start with either SQL Server or Sybase ASE and than later migrate them from each other. In this Oracle and SQL Server tutorial we will see couple of examples, where Oracle and SQL Server are different and how to change those SQL queries so that it can run on Microsoft SQL Server. By the way I have also written couple of post on queries like 10 ways to use SQL SELECT queries and Don’t delete, truncate it. If you like reading more on SQL queries than those are for you.

How to create and call stored procedure in MySQL with IN and OUT parameters

It's hard to remember exact syntax of, how to create stored procedure in MySQL, until you are creating and working on stored procedure frequently, simply because syntax is not a one liner. You need to remember exact syntax, if you are using MySQL database from command line. What help in this case is, quick examples. In last couple of MySQL tutorial we have seen How to find second highest salary and How to join 3 tables in one query; In this MySQL tutorial we will see couple of examples of creating stored procedure and calling stored procedure using IN and OUT parameters. All these MySQL examples are simple and help you to understand syntax of creating stored procedure with parameters in MySQL. These examples are tested in MySQL 5.5 database. We will also use following employee table to create and test these stored procedures :