Sunday 30 March 2014

Query to display names of all columns and data types in a table - SQL

Following is the query that returns column name and data type in a table in SQL:


Replace the string 'Table Name' with the actual table name

select first.name[Column Name],

case when second.name IN ('char', 'varchar', 'nchar', 'nvarchar') then second.name + 
' (' + convert(nvarchar(10),first.max_length ) + ')' 
when second.name IN ('decimal','numeric') then second.name + ' (' + convert( nvarchar(10),first.precision ) + 
', ' + convert( nvarchar(10),first.scale ) + ')'
else second.name end [Data Type],
case first.is_nullable when 1 then 'Yes' else 'No' end [Allow Nulls]
from sys.columns first
inner join sys.types second on first.system_type_id=second.system_type_id
where object_name(first.object_id)='Table Name'
order by first.column_id


Population by Country - Tableau

Ranking Functions in SQL Server with example

There are four types of Ranking Functions in SQL. All the Ranking Functions are used with Over Clause
  • Row_Number: The row_number function gives each a row in a table a serial number. 
Example: 

Consider below Table named 'EmpDetails', the table contains two columns 'EmpID' and 'EmpName'
  

Now we will implement Row_number to the above table:

Query:

select *, ROW_NUMBER() over (order by empid) as Rowno from dbo.EmpDetails 

The output of the query is as follows:


  • Rank: The Rank function gives rank to each rows in the result based on the over clause.

Example:
select *, Rank() over (order by empid) as Rowno from dbo.EmpDetails 

Ouput:

As seen above, the employees with empid as '1' has been given Rank 1, whereas the employee with empid '2' has rank 3, because there were two employees with empid 1 that has rank one.


  • Dense_Rank: This function is similar to Rank() function, the only difference is that it gives Rank without any gaps.
Example:



select *, dense_Rank() over (order by empid) as Rowno from dbo.EmpDetails 

Output:

If you observe the above result set, dense_rank() function has assigned rank 2 to the employees with Empid 2, unlike Rank() function which ranked them as 3.

  • Ntile: The Ntile function requires to specify a number in the parentheses and based on that the it divides the rows in the result set.
Example:


select *, Ntile(2) over (order by empid) as Rowno from dbo.EmpDetails 

Here, we have specified 2 in the parentheses, hence the result set with contain only two ranks i.e. 1 and 2

Output: