10+ SQL Advance Theory Question , Sql Queries Interview Questions and Answers Dear “Developers” If you are looking to crack interview on SQL then you are at right place. here you shall learn lost of things about SQL Adavance Theory Quest
10+ SQL Advance Theory Question
Difference Between Local and Global Temporary Tables
1. Local Temp tables are prefixed with single pound (#) symbol, whereas global temp tables are prefixed with 2 hash (##) symbols.
2. SQL Server appends some random numbers at the end of the local temp table name, where this is not done for global temp table names.
3. Local temporary tables are only visible to that session of the SQL Server which has created it, whereas Global temporary tables are visible to all the SQL server sessions
4. Local temporary tables are automatically dropped, when the session that created the temporary tables is closed, whereas Global temporary tables are destroyed when the last connection that is referencing the global temp table is closed.
What are table variables?
Table variable is a special data type that can be used to store a result set for processing at a later time.
table is primarily used for temporary storage of a set of rows returned as the result set of a table-valued function.
Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures.
-10+ SQL Advance Theory Question
Syntax if interviewer ask to write :
DECLARE @userData TABLE(
name varchar(30) NOT NULL,
City varchar(30) NOT NULL
);
INSERT INTO @userData
SELECT name, city FROM Employees
What is CTE in SQL? Can you write a syntax to create a CTE?
A CTE is a temporary result set, that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE.
Syntax : 10+ SQL Advance Theory Question
WITH cte_name (Column1, Column2, ..)
AS
( CTE_query )
[Note : There can be some queries asked by interviewer where he can confuse with creation of CTE then some other query and then a query using CTE table. Please remember cte scope is only till next immediate statement.]
What are differences between Temporary tables, Table Variables and CTE?
| Temporary Table | Table Variable |
| Temporary table is created in the TempDB | Table variable is created in the memory |
| Temporary Table can be involved in transactions, logging or locking | Table Variables cannot be involved in transactions, logging or locking. This makes Table Variable faster than a Temporary Table |
| You can not pass Temporary Table as parameter to Functions and Stored Procedures | You can pass Table Variable as parameter to Functions and Stored Procedures |
| A temporary table can have indexes | Table Variable can have only a primary index |
If speed is an issue Table variables can be faster, but if there are a lot of records, or there is a need to search the temporary table based on a clustered index, then a Temporary Table would be better. If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won’t create statistics on table variables.
What is difference between INSERT INTO and SELECT INTO statements?
- Both the statements are use to copy data into the table.
- For insert into statement it is mandatory to create the table and then fire insert into query whereas for SELECT INTO statement table creation is not needed this query automatic generates the table and copy the data.
10+ SQL Advance Theory Question
Syntax for INSERT INTO :
- Insert into tblName values(col1Value, col2Value,…)
-
- Or
-
INSERT INTO @targetTblName
SELECT col1, col2 FROM sourceTblName
-[We need to create @targetTblName with required columns before firing this query otherwise it will going to through error.]
Syntax for SELECT INTO :
SELECT col1, col2 INTO targetTblName FROM sourceTblName
[This query automatically generates targetTblName with copied columns and data.]
What are Indexes? Types of Indexes? Advantages and Disadvantages of Indexes?
Indexes are used by queries to find data from tables quickly. Indexes are created on tables and views.
The existence of the right indexes, can drastically improve the performance of the query. If there is no index to help the query, then the query engine, checks every row in the table from the beginning to the end. This is called as Table Scan. Table scan is bad for performance.
There are 2 types indexes in SQL :10+ SQL Advance Theory Question
1. Clustered Index:
- A clustered index determines the physical order of data in a table. For this reason, a table can have only one clustered index.
2. Non Clustered Index:
- The data is stored in one place, the index in another place. The index will have pointers to the storage location of the data. Since, the non clustered index is stored separately from the actual data, a table can have more than one non clustered index.
Difference between Clustered and NonClustered Index:
| Clustered Index | NonClustered Index |
| Only one clustered index per table | have more than one non clustered index per table |
| Clustered index is faster than a non clustered index | Slow than Clustered Index |
| Clustered index determines the storage order of rows in the table. hence doesn’t require additional disk space. | Non Clustered index is stored separately from the table, additional storage space is required. |
Additional Disk Space: Clustered Index does not, require any additional storage. Every Non-Clustered index requires additional space as it is stored separately from the table. The amount of space required will depend on the size of the table, and the number and types of columns used in the index.
Insert Update and Delete statements can become slow: When DML (Data Manipulation Language) statements (INSERT, UPDATE, DELETE) modifies data in a table, the data in all the indexes also needs to be updated. Indexes can help, to search and locate the rows, that we want to delete, but too many indexes to update can actually hurt the performance of data modifications.
[Note : On the top of this Interviewer may ask you to write a syntax for creating an Index.]What is Covering Index?
If all the columns that we have requested in the SELECT clause of query, are present in the index, then there is no need to look up in the table again. The requested columns data can simply be returned from the index.
A clustered index, always covers a query, since it contains all of the data in a table. A composite index is an index on two or more columns. Both clustered and non clustered indexes can be composite indexes. To a certain extent, a composite index, can cover a query.
Subcribe My Channel Now 10+ SQL Advance Theory Question