Seputar Ilmu

Pertama mari kita mengenal SQL Server, SQL Server adalah sebuah aplikasi server untuk basis data yang dapat digunakan sekaligus pengelola back-end database. Saat menggunakan SQL Server, maka Anda membutuhkan software pendukung bernama SQL Server Management Studio. Software ini dapat membantu adminsistrator untuk memproses database di SQL Server. Dalam sebuah server SQL terdapat objek database berupa database, query, view, stored procedure, dan lain-lain. Dengan menggunakan SQL Server Management Studio, Administrator dapat melakukan berbagai aktifitas dalam mengelola database. Seperti membuat pengaturan akun pengguna, melakukan back-up database, mereplikasi database, serta melakukan pengiriman data antar database lainnya, dan masih banyak lagi fungsi yang lain. Penggunaan interface berupa GUI, memungkinkan administrator pemula dapat mengelola database dengan mudah. Sehingga semua orang mudah mengelola SQL Server tanpa harus pusing dengan kode script. Namun bagi administrator yang memili...

One of the frequently ask SQL questions in any programming interviews is what is the difference between VARCHAR and CHAR data type in SQL? particularly when your project is using Microsoft SQL Server. You might have seen this couple of times, but given its popularity, nowadays people are asking this question differently e.g. they will ask you how much space a column of VARCHAR(2) data type will take? How many characters can it take? How do you minimize the space? Is it better to use a CHAR variable instead of VARCHAR(2) and why? These are some of the really interesting question and more meaningful than classical SQL questions like the difference between char vs varchar data type question. Read more �

Did you ever face a situation where some of your innocuous looking action has cause production issue and that too big time? Well, I hope you have not because it's certainly, not a pleasant experience. One of such innocuous looking action is running SQL queries on production databases. I had that in past very early in my career where I removed some config as duplicates only to find after a week that it stopped publishing messages to one of the downstream. When you work in complex systems which has so many components, millions of lines of code, thousands of configuration and many databases with hundreds of tables, you have to be really careful with anything you do. Often there is on the real way to perform a production-like testing, hence the best bet is to keep your change as much isolated and limited as possible. Read more �

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 �

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 �

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 �

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 �

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 �

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 �

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 te...

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 �

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 >>

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 �