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 »

SQL Server Veri Tabanındaki Tablo Adlarını Listelemek

SQL Server Veri Tabanındaki tablo adları nasıl listelenir?

use Veri_Tabanı_Adı
select * from sys.tables

veya

select * from sysobjects WHERE xtype='U'

SQL Server - Bir Tablodaki Verileri Diğer Bir Tabloya Eklemek

SQL Server'da daha önce oluşturmuş olduğunuz bir tablodaki verileri, aynı veritabanı veya farklı bir veritabanı içindeki tabloya ekleyebilirsiniz. Bunun için kullanacak olduğumuz kod bloğu aşağıdaki gibidir.

 INSERT INTO HedefTabloAdi  
 (SutunAdi1, SutunAdi2)  
 SELECT SutunAdi1, SutunAdi2  
 FROM KaynakTabloAdi  

Bir örnekle açıklıcak olursak;

Daha önceki bir projemizde "Araba" isminde bir veritabanımız ve veri tabanı içinde "ArabaBilgi" isminde bir tablomuz olsun. Bu tablodaki bilgileri başka bir proje için oluşturduğumuz "Araclar" isimli veritabanındaki, "AraclarBilgi" isimli tablonun ilgili kolonlarına eklemek istiyoruz.


ArabaBilgi Tablosu

AraclarBilgi Tablosu

Yazmamız gereken kod bloğu aşağıdaki gibidir;

 INSERT INTO [Araclar].[dbo].[AraclarBilgi]  
 (   [aracPlaka]  
    ,[aracMarka]  
    ,[aracFiyat]  
    ,[aracRenk]  
 )  
 SELECT [arabaPlaka]  
    ,[arabaMarkasi]  
    ,[arabaFiyat]  
    ,[arabaRenk]  
 FROM [Araba].[dbo].[ArabaBilgi]  

Kod bloğu çalıştığında kaynak tablodaki verilerin (ArabaBilgi), hedef tablodaki (AraclarBilgi) verilerin devamına eklendiğini görebiliriz.

 

AraclarBilgi Tablosu Son Durum