view vs materliazied view
Ans:-
1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
2) Another difference between View vs materialized view is that, when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.
3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) Performance of View is less than Materialized view.
5) This is continuation of first difference between View and Materialized View, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table
6) Last difference between View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.
Stored proc vs UDF
Basic Difference:-
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
Functions can have only input parameters for it whereas Procedures can have input/output parameters .
Functions can be called from Procedure whereas Procedures cannot be called from Function.
Advance Difference
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
We can go for Transaction Management in Procedure whereas we can't go in Function.
Delete vs Truncate
DELETE
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.
DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
1. DELETE is a DML Command.
2. DELETE statement is executed using a row lock, each row in the table is locked for deletion.
3. We can specify filters in where clause
4. It deletes specified data if where condition exists.
5. Delete activates a trigger because the operation are logged individually.
6. Slower than truncate because, it keeps logs.
7. Rollback is possible.
TRUNCATE
1. TRUNCATE is a DDL command.
2. TRUNCATE TABLE always locks the table and page but not each row.
3. Cannot use Where Condition.
4. It Removes all the data.
5. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.
6. Faster in performance wise, because it doesn't keep any logs.
7. Rollback is not possible.
DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as DELETE write records them in Log file in case it is needed to rollback in future from LOG files.
Seek perdicate vs predicate
Ans:-
Generally a predicate is a 'condition template' that returns TRUE if it satisfies the condition or FALSE if doesn't satisfies the condtion. For example EmployeeID = 1002, is a predicate applies to a WHERE condition (this can also be applied to table join conditions)
For example, let's say there is an Employee table with millions of data. Execute a query like SELECT Name FROM Employee WHERE EmployeeID = 1002. If there is no index on EmployeeID and Name column, the query engine will scan each row and check the 'predicate' (EmployeeID = 1002) against each row and if the row satisfies the condition, then return the row. This is what you are seeing in execution plan as 'Predicate:'
Let us say the table has a non clustered index on EmployeeID and Name columns. Then the query engine only need to seek the relevant index pages and rows to match the predicate to find the satifiying row. This is called 'Seek Predicates:'
An excellent series on scan and seek are in Craig Freedman's blog below:
http://blogs.msdn.com/b/craigfr/archive/tags/scans+and+seeks/
Seek Predicate is the operation that describes the b-tree portion of the Seek. Predicate is the operation that describes the additional filter using non-key columns. Based on the description, it is very clear that Seek Predicate is better than Predicate as it searches indexes whereas in Predicate, the search is on non-key columns – which implies that the search is on the data in page files itself.
Union vs Union all
UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
UNION ALL does not remove duplicates, and it therefore faster than UNION.
Index seek vs Index scan
http://saurabhsinhainblogs.blogspot.com/2014/01/difference-between-index-seek-vs-index.html
Index Scan --- will scan each row of Table and get query result
Index Seek:- will scan index leaf level pages for query result. So performance wise Index Seek is better
Scan
|
Seek
| |
1
|
When search definition can not point close on single row/ Range of rows to satisfy search predicates. It called as scan
|
When index definition can point close on single row/ Range of rows to satisfy search predicates. It called as Seek
|
2
|
SQL Server has to scan multiple pages to find range of rows which satisfy search predicates.
|
SQL Server knows which page / Range of rows to read
|
3
|
Scan is good if you have to retrieves all the rows from the table.
|
Seek is used if we need selected rows from the table
|
4
|
It reads every row in the table whether or not it qualifies
|
It reads rows that qualify and pages that contain these qualifying rows
|
5
|
Scan cost is proportional to the total number of rows in the table
|
The cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
|
6
|
If Scan is table scan means table doesn’t have index
|
Seek is always on index only
|
7
|
If table is having very small no. of rows and its not very frequently used, scan and seek will be almost same
|
If table is having huge no. of records then seek will give huge performance impact.
|
8
|
When SQL Server does a scan it loads the complete object which it wants to read from disk into memory, then reads through that object from top to bottom looking for the records that it needs.
|
It knows where in the index data is going to be, so goes directly to the part of the index that it needs and load to memory
|
9
|
You never want to convert seek to scan but if you just want it, then drop index
|
By addin column in where clause and select option in index, we can convert a scan into seek operation.
|
10
|
A scan means whole data irrespective of data you want
|
A seek means that you are looking for specific value(s) and the index provides you with the best way to do it
|
11
|
Scan can be possible with index or table
|
Seek is always with index , Db engine search data in B tree
|
12
|
Scan is of 3 types clustered index scan , non clustered index scan , table scan
|
Seek is of two types clustered index seek and nonclustered index seek
|
13
|
An Non Clustered index scan is a complete scan of all the leaf pages in B tree to to find index key of cluster index
|
An Non clustered index seek is a seek through the B-tree structure of a non-clustered index to find index key of clusterindex leaf page, from the root down to the leaf
|
14
|
A clustered index scan is a complete scan of all data pages at leaf in a clustered index
|
A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf
|
15
|
Nonclustered index scan means lot fewer pages then in clustered index scan
|
Nonclustered index seek means only pages required for data address, where as in clustered index seek it only reads data pages.
|
16
|
Order of data doesnt impact much
|
Search is fast beacause data is stored in order based on the clustered index key
|
17
|
Fragmented data affects scan most as whole data need to be read from disk
|
fragmented data affects but not as compare to scan, as sql engine reed minimal required data.
|
18
|
Scan is genreally not good , some queries it will use a table scan even when a useful index is present - this is usually because the data in the table is so small that it is more hassle to traverse the indexes (if this is the case you would expect the plan to change as the data grows, assuming the selectivity measure of the index is good).
|
seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows in the affected table.
|
19
| ||
20
|
Table scan only appears for a heap ie table without a clustered index. The first page in the heap is located based on info in the system tables, and then the pages are read one by one, using the next and, if necessary, previous pointers in the page headers. This is generally an expensive operation and should be avoided where ever possible
|
Seek is not possible without index
|
21
|
Clustered index scan is similar to table scan, just on a table that has clustered index. This operation reads the leaf pages of the clustered index, using the next and previous page pointers. Like with the table scan, this can be an expensive operation and should, wherever possible be avoided
|
Clustered index seek uses the clustered index’s b-tree structure. The seek starts at the root of the tree and navigates down the levels of the index until it reached the leaf page(s) with the desired data. This operation also appears when a partial scan of the table is done, when the index’s tree is used to locate a page, and the index is scanned from that point until another point in the table (possibly the end).
|
22
|
Index scan means reading all the leaf pages of a non-clustered index using the next and previous page pointers to navigate. Because non-clustered indexes generally have fewer pages in the leaf than a clustered index, this operation is usually cheaper than a clustered index scan
|
Non-clustered index seek is the same as the clustered index seek, just using a non-clustered index
|
23
|
This some times comes with lookups (Row \ Bookmark) , apart of scanning , Another idex is used to over result of scan
|
In a seak not all of the index is considered. Only one used index is enough
|
26
|
We can see scan is horizontal arrow
|
Seek is vertical arrow explain its nature of work
|
db mirroring vs log shipping VS Replication
Database Mirroring
|
Log-shipping
|
Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously.
|
Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondary’s for each primary database.
|
Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.
|
Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule
|
Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level
|
Log-shipping can work on database and server level. You can configure multiple databases in logshipping
|
Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred Server Limitation: Can be applied to only one mirror server Failover: Automatic Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds Role Change: Role change is fully automatic Client Re-direction: Fully automatic as it uses .NET 2.0/.Net 3.0 |
With Log Shipping:
Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required
|
Support only full recovery model
|
Supports full and bulk-logged recovery model
|
Mirror database is always in recovery mode. To read it you have use database snapshot.
|
You can use the stand-by option to read the database on standby server
|
Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages.
|
Not supported
|
ISNULL VS COALESCE
Ans:-
•ISNULL can accept only two arguments where as COALESCE can accept any number of input parameters
•ISNULL is not ANSI standard and is proprietary to TSQL.
•With ISNULL, the datatype of the second argument is converted to equal the data type of the first argument where as COALESCE converts according to data type precedence.
•ISNULL is generally accepted to be faster than COALESCE.
•COALESCE is a much cooler word and will usually earn you either an impressed glance or a blank confused stare when slipped into casual conversation.
charindex vs patindex
Ans:-
Similarity
Both the functions accepts two arguments to search the specified text filed in the given expression.
Both the function returns the starting position of the matching pattern given in the function.
Difference
The PatIndex function is used with the wildcard characters. You must enclosed the wildcard characters before (when searching at last) or after (when looking for first) the searching text.
But, the CharIndex function can not be used any wildcard characters with the specified searching pattern. In the CharIndex function will not work upon the wildcard characters.
Example of PatIndex
SELECT (PATINDEX('%Corner%', 'C-SharpCorner'))
Will result 8.
Example of CharIndex
SELECT (charindex('Corner', 'C-SharpCorner'))
Will also result 8.
ONLINE Index vs Offline Index
Ans:-
In ONLINE mode the new index is built while the old index is accessible to reads and writes.
any update on the old index will also get applied to the new index. An antimatter column is used to track possible conflicts between the updates and the rebuild (ie. delete of a row which was not yet copied).
See Online Index Operations. When the process is completed the table is locked for a brief period and the new index replaces the old index. If the index contains LOB columns, ONLINE operations are not supported in SQL Server 2005/2008/R2.
In OFFLINE mode the table is locked upfront for any read or write, and then the new index gets built from the old index, while holding a lock on the table.
No read or write operation is permitted on the table while the index is being rebuilt. Only when the operation is done is the lock on the table released and reads and writes are allowed again.
Bookmark Lookup VS RID Lookup
Clustered Index vs Non-Clustered Index
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
Having VS Where Clause
Both Having Clause and Where clause is used to filter the data coming from the Select statement, but still there are some differences between them. These difference are given below:-
Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.
We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.
For example, the sql query
select * from EmployeeDeptInfo where count(employeeid)>1
will not work but the query
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1)
will work fine
Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
CTE VS TEMP Table
CTEs... (creates tables being used in memory)/It just ad-hoc view
Are unindexable (but can use existing indexes on referenced objects)
Cannot have constraints
Are essentially disposable VIEWs
Persist only until the next query is run
Can be recursive
Do not have dedicated stats (rely on stats on the underlying objects)
#Temp Tables... (creates table being created tempdb databse/ on disk)
Are real materialized tables that exist in tempdb
Can be indexed
Can have constraints
Persist for the life of the current CONNECTION
Can be referenced by other queries or subprocedures
Have dedicated stats generated by the engine
As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.
Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them butspeeding up a query isn't really one of them
Rebuild index Vs Reorganize index
no
|
REBUILD
|
REORGANIZE
|
1
|
This process drops the existing Index and Recreates the index
|
This process doesn’t drop indexes; It physically reorganizes the leaf nodes of the index.
|
2
|
Syntax: ALTER INDEX ALL ON table_name REBUILD
|
Syntax: ALTER INDEX ALL ON table_name REORGANIZE
|
3
|
Index should be rebuild when index fragmentation is great than 30%
|
Index should be reorganized when index fragmentation is between 10% to 30%
|
4
|
Index rebuilding process uses more CPU and it locks the database resources
|
Index reorganize process uses less CPU and it doesn’t the database resources
|
5
|
Rebuilding an index can be executed online or offline
|
Reorganizing an index is always executed online
|
6
|
A nice thing about rebuilding an index is that you can change the setting for index properties like the fill factor and index padding
|
Index options cannot be specified when reorganizing an index
|
7
|
Requires ALTER permission on the table or view or User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles
|
Requires ALTER permission on the table or view or User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles
|
8
|
REBUILD locks the table for the whole operation period except in Enterprise Edition with ONLINE = ON
|
REORGANIZE doesn't lock the table.
|
9
|
It is changing the whole B-tree, recreating the index
|
It is a process of cleaning, organizing, and defragmenting of only "leaf level" of the B-tree
|
10
|
REBUILD will automatically rebuild all index-column statistics
|
Statistics are NOT updated with a reorganization
|
11
|
This reclaims disk space by compacting the pages in new index
|
It works in two phases – compaction and defrag
|
12
|
Rebuild does require extra space as same the size of index
|
Reorganize essentially just swaps one page with another and thus does not require free space
|
13
|
Rebuild can fix extent fragmentation as well
|
Reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation
|
14
|
Index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns).
|
Its always online operation.
|
15
|
If an index has multiple partitions, then you cannot rebuild a single partition online
|
You can reorganize a single index partition online
|
16
|
After rebuild statistics on non-indexed columns doesn’t get re-computed
|
Statistics are NOT updated with a reorganization
|
17
|
Rebuild is an atomic operation
|
Reorganize can be stopped while it is occurring and you won't lose the work it has already completed
|
18
|
Rebuild indexes always generates similar amount of t-log for same size index
|
For large, heavily fragmented indexes the reorganization operation will generate more t-log as compared to less fragmented index
|
19
|
REBUILD requires enough free space in the database to accommodate the new index
|
REORGANIZE only requires 8KB of additional space in the database
|
20
|
REBUILD can use multiple CPUs so the operation runs faster
|
REORGANIZE is always single-threaded
|
21
|
REBUILD can use minimal-logging to reduce transaction log growth
|
REORGANIZE is always fully logged, but doesn’t prevent transaction log clearing.
|
22
|
An index rebuild will always rebuild the entire index, regardless of the extent of fragmentation
|
An index reorganize will only take care of the existing fragmentation
|
23
|
SQL 2000 use DBCC DBREINDEX
|
SQL 2000 use DBCC INDEXDEFRAG
|
24
|
Even If the index spans multiple data files, it will be taken care
|
If the index spans multiple data files, reorganizing will only reorder pages within the same file, it won’t move pages between files
|
PAGE VS Extent
Extent Vs Page?
Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.
The size of the page is 8KB.
Eight consecutive pages will form an extent 8 * 8KB = 64KB. Thus I/O level operation will be happening at pages level.
The pages will hold a template information at the start of each page (header of the page). They are,
1. page number,
2. page type,
3. the amount of free space on the page,
4. the allocation unit ID of the object that owns the page.
Extents will be classifed into two types,
1. Uniform extents
2. Mixed extents
Uniform Extents:
It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.
Mixed Extents:
Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.
Property of SQL Server :
Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.
Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.
The size of the page is 8KB.
Eight consecutive pages will form an extent 8 * 8KB = 64KB. Thus I/O level operation will be happening at pages level.
The pages will hold a template information at the start of each page (header of the page). They are,
1. page number,
2. page type,
3. the amount of free space on the page,
4. the allocation unit ID of the object that owns the page.
Extents will be classifed into two types,
1. Uniform extents
2. Mixed extents
Uniform Extents:
It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.
Mixed Extents:
Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.
Property of SQL Server :
Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.
Dirty Read Vs Phantom
Dirty Read:-
Dirty read occurs when one transaction is changing the record, and the other transaction can read this record before the first transaction has been committed or rolled back. This is known as a dirty read scenario because there is always the possibility that the first transaction may rollback the change, resulting in the second transaction having read an invalid data.
Dirty Read Example:-
Transaction A begins.
UPDATE EMPLOYEE SET SALARY = 10000 WHERE EMP_ID= ‘123’;
UPDATE EMPLOYEE SET SALARY = 10000 WHERE EMP_ID= ‘123’;
Transaction B begins.
SELECT * FROM EMPLOYEE;
(Transaction B sees data which is updated by transaction A. But, those updates have not yet been committed.)
SELECT * FROM EMPLOYEE;
(Transaction B sees data which is updated by transaction A. But, those updates have not yet been committed.)
Non-Repeatable Read:-
Non Repeatable Reads happen when in a same transaction same query yields to a different result. This occurs when one transaction repeatedly retrieves the data, while a difference transactions alters the underlying data. This causes the different or non-repeatable results to be read by the first transaction.
Non-Repeatable Example:-
Transaction A begins.
SELECT * FROM EMPLOYEE WHERE EMP_ID= ‘123’;
SELECT * FROM EMPLOYEE WHERE EMP_ID= ‘123’;
Transaction B begins.
UPDATE EMPLOYEE SET SALARY = 20000 WHERE EMP_ID= ‘123’;
(Transaction B updates rows viewed by the transaction A before transaction A commits.) If Transaction A issues the same SELECT statement, the results will be different.
UPDATE EMPLOYEE SET SALARY = 20000 WHERE EMP_ID= ‘123’;
(Transaction B updates rows viewed by the transaction A before transaction A commits.) If Transaction A issues the same SELECT statement, the results will be different.
Phantom Read:-
Phantom read occurs where in a transaction execute same query more than once, and the second transaction result set includes rows that were not visible in the first result set. This is caused by another transaction inserting new rows between the execution of the two queries. This is similar to a non-repeatable read, except that the number of rows is changed either by insertion or by deletion.
Phantom Read Example:-
Transaction A begins.
SELECT * FROM EMPLOYEE WHERE SALARY > 10000 ;
SELECT * FROM EMPLOYEE WHERE SALARY > 10000 ;
Transaction B begins.
INSERT INTO EMPLOYEE (EMP_ID, FIRST_NAME, DEPT_ID, SALARY) VALUES (‘111′, ‘Jamie’, 10, 35000);
Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again.
INSERT INTO EMPLOYEE (EMP_ID, FIRST_NAME, DEPT_ID, SALARY) VALUES (‘111′, ‘Jamie’, 10, 35000);
Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again.
Differences Among CTE, Derived Table, Temp Table, Sub Query And Temp Variable:-
CTE:
The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.
The CTE is an abbreviation of “Common Table Expression.” CTE was introduced in SQL Server. It works as a temporary result set that is defined within the execution scope of a single select, insert, update, delete statements. CTE is typically the result of complex sub queries. Similar to temporary tables CTE doesn’t store as an object; the scope is limited to the current query. CTE improves readability and ease in maintenance of complex queries and sub-queries.
When to use CTE
- Use for recursive query.
- CTE is easy to implement compared to complex queries which involves several sub-queries.
Temp Variables:
Temp Variables are also used for holding the data fora temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.
Temp Variables are also used for holding the data fora temporary time just like Temp tables. Temp variables are created using “DECLARE” statements and are assigned values by using either a SET or SELECT command. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration. This acts like a variable and exists for a particular batch of query executions. It gets dropped once it comes out of batch. Temp variables are also created in the Tempdb database but not the memory.
Limitations Of Temp Variables
- Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter.
- The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.
- We cannot create a non-clustered index on a table variable, unless the index is a side effect of a PRIMARY KEY or UNIQUE constraint on the table. It can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement. Indexes cannot be added after the table has been created.
When to use Temp Variables:
- When you are required to use the current result set in next queries then store result into temp variables.
- When your data set is smaller, approximately less than 1000 records, then use temp variables , if your result set is larger then use temp table instead of temp variables.
Temp Table:
Temporary tables are tables that are available only to the session that created them.
These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table which helps the developers in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. In SQL Server all temporary tables are present in tempdbdatabase.
Types of Temporary Tables:
SQL Server contain two types of Temporary tables:
Temporary tables are tables that are available only to the session that created them.
These tables are automatically destroyed at the termination of the procedure or session that created them. SQL Server provides the concept of temporary table which helps the developers in a great way. These tables can be created at runtime and can do all kinds of operations that one normal table can do. In SQL Server all temporary tables are present in tempdbdatabase.
Types of Temporary Tables:
SQL Server contain two types of Temporary tables:
- Local Temporary Tables
- Global Temporary Tables
Local Temporary Tables:
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables start with(“#”) hash sign.
Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them. Local temp tables are only available to the current connection for the user. Names of Local temp tables start with(“#”) hash sign.
Global Temporary Tables:
Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign.
Global temporary tables are temporary tables that are available to all sessions and all users. Once this table has been created by a connection, like a permanent table, then it is available to any user by any connection. They are dropped automatically when the last session using the temporary table has completed. Names of Global temp tables start with (“##”) double hash sign.
When to Use Temporary Tables
- We required to hold data from further query.
- When we have a complex Join operation.
- In some cases it may be a replacement of cursors.
Derived Table:
Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.
Derived tables are the tables which are created on the fly with the help of the Select statement. Derived table expression appears in the FROM clause of a query. In derived table server create and populate the table in the memory, we can directly use it and we also don’t require to drop the table. But scope of derived table is limited to the outer Select query who created it. Derived table can’t be used further outside the scope of outer select query.
When to Use:
- When you are required to perform some complex query and result of inner query work as table for outer query.
Subquery:
A subquery is a SELECT statement that is nested within another statement. Subquery is also known as nested query. Result of subquery is used in the main query as a condition to further restrict the data to be retrieved. The subquery used in select, update, delete and insert command.
A subquery is a SELECT statement that is nested within another statement. Subquery is also known as nested query. Result of subquery is used in the main query as a condition to further restrict the data to be retrieved. The subquery used in select, update, delete and insert command.
Difference between subquery and derived table:
Subquery
|
Derived
|
Subqueries must be enclosed within parentheses.
|
Derived table must be enclosed within parentheses and table name must be provided.
|
Subquery can have only one column.
|
Derived table can have one or more column.
|
Subquery mainly use in where clause.
|
Derived table used in from clause.
|
Difference b/w Temp table and Temp variable:
Temp Table
|
TempVariable
|
Scope of Temp Table is wider the temp variables of. Local temporary tables are temporary tables that are available only to the session that created them and Global temporary tables are temporary tables that are available to all sessions and all users.
|
Scope of the Temp variables are limited up to current batch and current stored procedure.
|
Temp tables can be create using Create Table and Select Into commands
|
Temp variables only through Declare command can’t be created using select into command.
|
Temp tables can be drop through Drop Command.
|
We can not drop a Temp variables but we can use truncate command for temp variables.
|
Name of Temp table can have maximum 116 characters.
|
Name of a temp variables can have MAXIMUM 128 characters.
|
Temp table support foreign key concept.
|
Temp variable doesn’t support foreign key.
|
Temp tables support transaction management.
|
Temp variables doesn’t support transaction management. Rollback not work for temp variables.
|
Temp tables can easily handle large amount of data.
|
Temp variables are suitable with small amount of data.
|
Difference b/w CTE and Derived table:
CTE
|
Derived Table
|
A CTE can be referenced multiple times in the same query. So CTE can use in recursive query.
|
Derived table can’t referenced multiple times. Derived table can’t use in recursive queries.
|
CTE are better structured compare to Derived table.
|
Derived table’s structure is not good as CTE.
|
