Temp Tables in SQL Server:
- Are real materialized tables that exists in tempdb
- can be indexed
- can have constraints
- Persists for the life of the current connection
- Can be referenced by the queries or sub procedures
- Have dedicated statistics generated by sql engine
- A table created on disk
- Use temp tables for longer or larger queries data.
- Table name looks like #Tablename
CTE - Common Table Expressions:
- CTE is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to derived table in that it is not stored as an object and lasts only for the duration of the query.
- Using CTE improves the readability and makes maintenance of complex queries easy
- The query can be divided in to separate, simple, logical buildings blocks which an be then used to build more complex CTE's until final result set is generated
- CTE can be defined in functions, SP's, Triggers and views
- After CTE is defined, it can be used as a table or a view and can select,Insert,Update or Delete
- No additional statistics stored in sql server engine
- No indexes
- Cannot have constraints
- Are essentially disposible views
- Persist only until the next query is run
- Can be recursive
- CTE's and Tbale Variables can be use for small dataset
- Can be substituted for a view
- Can reference itself multiple times
ReplyDeleteThrough this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
Msbi online training Hyderabad
Msbi online training India
Msbi online course