Q21. What is Temporary table in sql server?
Q22. What is the difference between Temporary table and Temporary variable?
Q23. Does indexing occupy any space in memory in sql server?
Q24. What are cursors. What is the disadvantage of cursors.
Q25. Difference between varchar and Nvarchar?
=====================================================================
Q21. What is Temp table in sql server?
Answer:
They are used to store any temporary data. They are created with pound(hash) keyword. In
SQL Server Management Studio you can see at the following path System Databases > tempdb > Temporary Tables
- In Temporary Table we can see entry for both ##GlobalTemporaryTable and #TemporaryTable
- Within current session you can query data from #temp table as many times. With in current session means, for same tab. if you query the #temp in second tab it will not be avaliable. once you close the initial tab, we can not get #temp again.
- #temp are auto dropped with end of execution of Stored Procedure.
- If you try querying from another connection or infect tab, it will give Invalid object name error.
- Global temporary tables are used to access across all connections. it remain active around all tabs. it will be deleted once we close the sql server instance.
=====================================================================
Q22. What is the difference between Temporary table and Temporary variable?
Answer:
Temporary Tables
1. There are two types of temporary tables. Local temporary table (#table) and Global Temporary table (##table)
2. Temporary Tables are physically created in the tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
3. Indexing can be done on Temporary tables.
4. Not allowed in User Defined functions.
5. Little slower in performance because they are physical tables
Table Variables:
1. Only single type.
2. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. It is created in the memory database but may be pushed out to tempdb.
3. Indexing can not be created on Table variable.
4. Allowed in UDF
5. Little faster because they are made in memory tables.
=====================================================================
Q23. Does indexing occupy any space in memory in sql server?
Answer:
YES
=====================================================================
Q24. What are cursors. What is the disadvantage of cursors.
Answer:
Cursors are used to fetch the data from a table in a row by row manner.
Disadvantages of using Cursor:
- Cursor in SQL is temporary work area created in the system memory, thus it occupies memory from your system that may be available for other processes. So occupies more resources and temporary storage.
- Each time when a row is fetched from the cursor it may result in a network round trip. This uses much more network bandwidth than the execution of a single SQL statement like SELECT or DELETE etc that makes only one round trip.
- Repeated network round trips can degrade the speed of the operation using the cursor.
=====================================================================
Q25. Difference between varchar and Nvarchar?
Answer:
Varchar stores 1 byte each character while Nvarchar stores two bytes.
Nvarchar is used to store different language characters like german, Chinese etc
=====================================================================