SQL SERVER : Find the Size of Database file and Log file

Data and log information are never mixed on the same file, and individual files are used only by one database.

The database files (Primary / Secondary) are used for storing data. For each database file there will be a Log file associated, this is the transaction log file used to store the user transactions.

Please find the below code and Screenshot for finding the size of database and log file:

Code :

SELECT DB_NAME (database_id) AS Database_Name, Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = <databasename>


Scrennshot : 

SQL SERVER : Find out Nth Largest value

It is very common question for interview, to find out the 2nd or Nth largest salary of employee.

Please find the below methods to find out the answer :

Method 1 :

SELECT TOP 1 [Column_Name] FROM [Table_Name] 
WHERE [Column_Name] NOT IN (SELECT DISTINCT TOP (n-1) [Column_Name] FROM  [Table_Name] ORDER BY [Column_Name] DESC )
ORDER BY [Coumn_Name] DESC

Method 2 :

SELECT * FROM (SELECT *, DENSE_RANK() OVER(ORDER BY [Column_Name] DESC ) AS Name FROM [Table_Name]) DQ
WHERE DQ.Name = n

*n = for nth largest

SQL SERVER : Difference Between UNIQUE KEY and PRIMARY KEY

PRIMARY Key and UNIQUE Key enforce the Uniqueness of the values on the column[columns] on which it is defined.

•  By Default, UNIQUE Key creates a NON CLUSTERED INDEX while PRIMARY Key creates a   CLUSTERED  INDEX 
• You can create multiple UNIQUE Keys on a table but you can have ONLY One PRIMARY Key on a table.
• PRIMARY Key can be used in Foreign key relationship where as UNIQUE Key can not be used.
• PRIMARY Keys can not have nulls but UNIQUE Key allows null value (But only one null value).