Temporary Tables in SQL Server

Temp Table: 

1. Created in Temp database. Can treat as a normal table. 

2. Can be accessible outside the batch.

 

-- check object is exists in tempdb

IF OBJECT_ID('tempdb..#emptemp') IS NOT NULL 

  DROP TABLE #emptemp

CREATE TABLE #emptemp

(  

    empid int,  

    ename varchar(50),   

    address varchar(150)  

)  

GO  -- batch completed

SELECT * FROM #emptemp

INSERT INTO #emptemp VALUES ( 1, 'Scott','Newyork');  

GO  -- batch completed

SELECT * FROM #emptemp

 

3. Temp tables involves in Transactions, Logging, and Locking.

4. Can create Indexes on Temp tables.

5. Temp tables cannot be passed as parameters to procedures and functions.

6. We can perform schema modification on the Temp table run time.

7. Two types of temporary tables.

7.1. Local Temporary Tables: available in the session where they created. scope of these tables terminates when the session created is closed. Can create the same table in multiple sessions with the same name. 

       

CREATE TABLE #emptemp

(  

    empid int,  

    ename varchar(50),   

    address varchar(150)  

)

7.2. Global Temporary Tables: available for all sessions and all users. Scope of these table ends when the global temp table created session ends.

for example:

in session1 we created the below table, this table can be accessible in other sessions. If session1 terminates, we can not access this global temp table.

CREATE TABLE ##emptemp

(  

    empid int,  

    ename varchar(50),   

    address varchar(150)  

)

8. We cannot access local and global temporary tables in user-defined functions.

SQL Server - CTE and Recursive CTE

Common Table Expression (CTE): is a temporary named result set. It must be followed by single Select, Insert, Update, Delete statement that references some/all columns defined in the select statement. We can use CTE in CREATE VIEW statement as part of the SELECT Statement.

Order by, into, option clause with query hints cannot be used in CTE query definition.

Example:

with cte as
(
 select * from emp where deptno = 10
)

select * from cte;

Output:

Recursive Common Table Expression: is that references itself. Recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Anchor members must combine by one of SET operators (UNION ALL, UNION, INTERSECT or EXPECT). UNION ALL is the only set operator used between the last anchor member and the first recursive member. No of columns and data types in an anchor and recursive members must be the same. 

SELECT DISTINCT, GROUP BY, HAVING, TOP, LEFT, RIGHT, OUTER JOIN, Sub Queries cannot be used in CTE Query definition of a recursive member.

Query to list employees details and their level of reporting using recursive cte.

Example:

with cte as
(select emp.empno,emp.ename,emp.mgr,dept.dname ,0 lvl 
 from emp join dept on emp.deptno = dept.DEPTNO
 where mgr is null
 union all
 select emp.empno,emp.ename,emp.mgr,dept.dname,cte.lvl+1 
 from emp 
 join dept on emp.deptno = dept.deptno 
 join cte  on emp.mgr = cte.empno
)
select * from cte;

Output:

Example: Following query is used to display numbers from 1 to 110 using recursive CTE.

with cte as
(
select 1 lvl 
union all
select c.lvl+1 
from cte c where c.lvl<110
)
select * from cte;

When we run the above query, we will get an error (The statement terminated. The maximum recursion 100 has been exhausted before statement completion). By default, the maximum number of recursion allowed in CTE is 100. To overcome this error we use hint Maxrecursion. This hint specifies the maximum number of recursions allowed by query and the number of recursions is a positive integer between 0 and 32,767. 

Example:

with cte as

(select 1 lvl 
union all
select c.lvl+1 
from cte c where c.lvl<110
  )
select * from cte option(maxrecursion 110);


Output:

There is a scenario to display numbers up to 40,000. When we use maxrecursion (32767)

Example:

with cte as

(

select 1 lvl 

union all

select c.lvl+1 

from cte c where c.lvl<40000

)

select * from cte 

option (maxrecursion 32767);

Output:

After running the above query, we are getting an error (The statement terminated. The maximum recursion 32767 has been exhausted before statement completion). Because the output result has been crossed the maximum recursive limit of 32767. To overcome the above error, we can use option (maxrecursion 0).

option (maxrecursion 0): When 0 specified no limit is applied for recursion.

Example:

with cte as

(

select 1 lvl 

union all

select c.lvl+1 

from cte c where c.lvl<40000

)

select * from cte option (maxrecursion 0);

Output:

Now we can see the numbers up to 40,000.

Hive - Difference between order by and sort by

Order by: the actual ordering of data is performed in a single node.

hive> select * from emp order by ename;

can use asc/ascending for ordering.

data will be order by ename ASCENDING by default.

hive> select * from emp order by ename desc/descending;

data will be order by ename DESCENDING.

Sort by: if data is stored across more than one node, partial sorting is done node wise, but it can't be 100% perfect sorting. Can use ascending/asc , descending/desc for ascending and descending sorting.

Following will display the data sorted by ename in ascending order. 

hive>select * from emp sort by ename asc;

Hive - Database Commands

What is Hive?

Hive is a data warehousing infrastructure for Hadoop. That converts SQL queries to a MapReduce(MR) program, and this MR runs in the YARN (Yet Another Resource Negotiator)  framework. Supports analysis of large data sets stored in Hadoop Distributed File System (HDFS). It was built for Data Mining Applications, which takes to process data from several minutes to several hours. Which is also called Hive Query Language (HQL). 

The following are some commands that are used in HQL.

Show Database: List all installed databases in the system.

hive> Show Databases;

use <dbname>: To set current working DB name by default. 

hive>use siva;

we can see the change, instead hive (default), we can see the change hive (siva). Currently we are working using siva database.

Show Tables: List all tables in the selected database

hive>show tables;

To list all data from table emp in Siva database.

hive> select * from siva.emp;

Limit: Command is used to get 'n' number of records from the table.

To list 5 records from emp table.

hive>select * from emp limit 5;

Describe: Displays a basic structure of a given table.

hive>describe emp;

Describe Extended: To get additional details of table.

hive>describe extended emp;

Show Functions: To list all system functions.

hive>show functions;

Describe Function: To get function syntax and basic information of function. To get function XPath information.

hive>describe function XPath;

Describe Function Extended: To get function syntax and detailed information of function. 

hive>describe function extended Xpath;

!Clear: To clear the screen.

hive>!clear;

SQL Server - Generate Random number using User Defined Functions

Calling RAND function in a user defined Function:

RAND() function in SQL Server returns random number between 0 and 1.

What happens when we call RAND function in a Function?

Creating an user-defined function to generate Random value:

Create function dbo.Fn_GenRand(@a int)

returns float
as
begin
return(select rand())
end

Msg 443, Level 16, State 1, Procedure Fn_GenRand, Line 5
Invalid use of a side-effecting operator 'rand' within a function.

While creating above function, we get error message by using RAND() function. Because RAND() function is of Non-Deterministic function.

In SQL Server, USER defined functions are basically divided into 2 types. 

1. Deterministic Functions always returns same output when they called with specific set of input values in the same state of database.

SQRT,ISNULL,ISNUMERIC,POWER,DATEDIFF,CEILING,FLOOR,DATEADD,DAY,MONTH,YEAR,SQUARE etc.

2. Non-Deterministic Functions always returns different output when they called with specific set of input values in the same state of database.

GETDATE,ROW_NUMBER,RAND,@@MAX_CONNECTIONS,@@CONNECTIONS,RANK,NEWID, @@TOTAL_WRITE etc.

How to use get Random Value using Function?

1. Create a view:

Create view [dbo].[vw_genrandom]
as

SELECT RAND(convert(varbinary,NEWID()))*100  AS val

2. Create function:

Create function dbo.Fn_GenRand(@a int)
returns float
as
begin
return(select * from vw_genrandom)*@a

end

3. Generate Random Number:

select  dbo.Fn_GenRand(10)

Output

SQL Server - DDL operation on Linked Server

How to perform DDL Operations on Linked Server in SQL Server?

Linked server in SQL Server is a server object. Linked server are configured to enable database engine to execute T-SQL statements that includes tables in another instance of SQL server or cross platform databases such as Oracle.

How to create Linked Server in SQL server?

Following is the code to create linked server from SQL Server to Oracle.

BEGIN

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = 'LINKEDSERVER')

EXEC master.dbo.sp_dropserver @server='LINKEDSERVER', @droplogins='droplogins'

EXEC sp_addlinkedserver  @server='LINKEDSERVER',@srvproduct='Oracle',@provider='OraOLEDB.Oracle', @datasrc='<SourceServerName>'

EXEC sp_addlinkedsrvlogin 'LINKEDSERVER', 'false', NULL, '<Source db userId>', '<Source db Password>'

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'DynamicParameters', 1

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'NestedQueries', 1

EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'SqlServerLIKE', 1

EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'use remote collation', @optvalue=N'true'

END

user has to provide Oracle Server Name, UserId and Password of oracle instance in above code mentioned in <>.

How to get records counts for a given table in source database?

Following sql server script creates Tab_RowCount function in Oracle database using lnked server.

BEGIN

EXEC master.dbo.sp_serveroption 

                                                        @server=N'LINKEDSERVER', 

                                                        @optname=N'rpc out', 

                                                        @optvalue=N'true'

exec ('

create or replace

function Tab_RowCount( tablename in varchar2(128) ) return number

as

ColumnValue number default NULL;

begin

execute immediate

''select count(*)

from '' || tablename INTO ColumnValue;

return ColumnValue;

end;

') AT LINKEDSERVER

EXEC master.dbo.sp_serveroption 

                                                         @server=N'LINKEDSERVER', 

                                                         @optname=N'rpc out', 

                                                         @optvalue=N'false'

END

Execute function to get all tables count from oracle using LINKEDSERVER:

Following SQL Server script get all tables record count from oracle.

Select * from OPENQUERY (LINKEDSERVER,

                                              ' Select Table_Name, Tab_RowCount(User||''.''||table_name) Cnt

                                                from user_tables ')

Drop user created function using LINKEDSERVER

BEGIN

EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc out', @optvalue=N'true'

exec ('

drop 

function Tab_RowCount

') AT LINKEDSERVER

EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc out', @optvalue=N'false'

END

Hive - Analytical Functions

Lets work on emp table.

LAG: Displays preceeding employee number department wise. Default step value is 1. We can change this value by lag(<column>,<n>)

hive> select empno,e.deptno,d.dname,lag(empno) over (partition by e.deptno order by empno) followed_empno from emp e join dept d on e.deptno = d.deptno;

LEAD: Displays following employee number department wise. Default step value is 1. We can change this value by lead(<column>,<n>)

hive> select empno,e.deptno,d.dname,lead(empno) over (partition by e.deptno order by empno) followed_empno from emp e join dept d on e.deptno = d.deptno;

FIRST_VALUE: Returns first value of the window group.

hive>select empno,e.deptno,d.dname,first_value(empno) over (partition by e.deptno ) preceeding_empno from emp e join dept d on e.deptno = d.deptno;

LAST_VALUE: Returns last value of the window group.

hive> select empno,e.deptno,d.dname,last_value(empno) over (partition by e.deptno ) lastvalue from emp e join dept d on e.deptno = d.deptno;

Ranking Functions: These functions are used to find top(n) rows.

1. Row_Number(): Returns sequential number of a row within a partition of the result set without any gap.

hive> select empno,e.deptno,d.dname,e.sal,row_number() over(partition by e.deptno order by sal ) rownum from emp e join dept d on e.deptno = d.deptno;

For empno (7902,7788) salary is same (3000). In general for this scenario, same number should repeat both the records. 

2. Rank(): Returns rank of each row within the partition of a result set. Based on partition condition, it provides rank for each record. For employees (7902,7788) salary is repeated and these records got same rank. But the next record got rank of 14. In general we should get next rank value of 13. For rank 12, we got 2 records. For Rank() we will get the next rank 14 (12+no of same repeated values (i.e 2) ).

hive> select empno,e.deptno,d.dname,e.sal,rank() over( order by sal ) rank from emp e join dept d on e.deptno = d.deptno;

3. Dense_Rank(): We will overcome above problems using this function. Returns rank of rows with in the partition of result set without any gaps. Rank of a row is one plus the number of distinct rank.

hive>select empno,e.deptno,d.dname,e.sal,dense_rank() over( order by sal ) rank from emp e join dept d on e.deptno = d.deptno;

4. Ntile(): Distributes records in an ordered partition into specified no of groups. Groups are started from 1 for each group.

hive>select empno,e.deptno,d.dname,e.sal,ntile(3) over(partition by e.deptno order by sal ) rank from emp e join dept d on e.deptno = d.deptno;

5. Percent_Rank(): Calculates the relative rank of a row within a group of rows. Value between 0 and 1.

hive> select empno,e.deptno,d.dname,e.sal,percent_rank() over(partition by e.deptno order by sal) rank from emp e join dept d on e.deptno = d.deptno;

6. Cume_Dist(): Calculates the cumulative distribution of a value in a group of values.

hive>select empno,e.deptno,d.dname,e.sal,cume_dist() over(partition by e.deptno order by sal ) rank from emp e join dept d on e.deptno = d.deptno;