View:
It is a virtual table.
Scenarios for Using Views:
Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.
To Focus on Specific Data:
Views allow users to focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary data can be left out of the view. This also increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table
Views allow different users to see data in different ways, even when they are using the same data concurrently.
Advantages of views:
1. View the data without storing the data into the object.
2. Restict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restict the access of a table so that nobody can insert the rows into the table.
5. We can join Temp table and view , physical table
Disadvatages:
1. Can not use DML operations on this. (we have used more then one table when creating view)
2. When table is dropped view becomes inactive.. it depends on the table objects.
3. It is an object, so it occupies space.
4. we can't create clustrered index
5. we can't create nonclustered index without create unique clustred index
6. we can't create index without schema binding
6. we can't create view with schema binding, without using dbo.tablename instead of tablename.
7. we can't create constraints for view
8. we drop unique clustered index, non clustered index also droped.
------------------------------------------------------------------------------------------------------------
Variable table and Temp Table
Table Variable
This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.
1. we can create primary key
2. we can create indentity column
3. we can't create non clustered index
4. Table variables are not transactional
5. we can join with temp table or normal table or view
6.Maximum 128 characters.
7. collation- String columns inherit collation from current database.
8. index- Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.
9. Constraints - PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
10. Post-creation DDL (indexes, columns) - Statements are not allowed.
11. Data insertion- INSERT statement (SQL 2000: cannot use INSERT/EXEC).
12. Truncate table- Not allowed. Allowed.
13. Rollbacks- Not affected (Data not rolled back).
14. Dynamic SQL Must declare table variable inside the dynamic SQL.
15. Can't define globally
Temp Table
1. we can create all constraints include identity column
2. temp tables are transactional and do roll back.
3. we can join with temp table or normal table or view
4. Maximum 116 characters.
5. collation-String columns inherit collation from tempdb database.
6. index- Indexes can be added after the table has been created.
7. Constraints -RIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
8. Post-creation DDL (indexes, columns) - Statements are allowed.
9. Data insertion- INSERT statement, including INSERT/EXEC.SELECT INTO statement.
10. Truncate table Allowed.
11. Rollbacks- Affected (Data is rolled back).
12. Dynamic SQL-Can use temporary tables created prior to calling the dynamic sql.
13. Can't define globally
Reference:
------------------------------------------------------------------------------------------------------------
Function:
Limitations:
It is a virtual table.
Scenarios for Using Views:
Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.
To Focus on Specific Data:
Views allow users to focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary data can be left out of the view. This also increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table
Views allow different users to see data in different ways, even when they are using the same data concurrently.
Advantages of views:
1. View the data without storing the data into the object.
2. Restict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restict the access of a table so that nobody can insert the rows into the table.
5. We can join Temp table and view , physical table
Disadvatages:
1. Can not use DML operations on this. (we have used more then one table when creating view)
2. When table is dropped view becomes inactive.. it depends on the table objects.
3. It is an object, so it occupies space.
4. we can't create clustrered index
5. we can't create nonclustered index without create unique clustred index
6. we can't create index without schema binding
6. we can't create view with schema binding, without using dbo.tablename instead of tablename.
7. we can't create constraints for view
8. we drop unique clustered index, non clustered index also droped.
------------------------------------------------------------------------------------------------------------
Variable table and Temp Table
Table Variable
This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.
1. we can create primary key
2. we can create indentity column
3. we can't create non clustered index
4. Table variables are not transactional
5. we can join with temp table or normal table or view
6.Maximum 128 characters.
7. collation- String columns inherit collation from current database.
8. index- Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.
9. Constraints - PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.
10. Post-creation DDL (indexes, columns) - Statements are not allowed.
11. Data insertion- INSERT statement (SQL 2000: cannot use INSERT/EXEC).
12. Truncate table- Not allowed. Allowed.
13. Rollbacks- Not affected (Data not rolled back).
14. Dynamic SQL Must declare table variable inside the dynamic SQL.
15. Can't define globally
Temp Table
1. we can create all constraints include identity column
2. temp tables are transactional and do roll back.
3. we can join with temp table or normal table or view
4. Maximum 116 characters.
5. collation-String columns inherit collation from tempdb database.
6. index- Indexes can be added after the table has been created.
7. Constraints -RIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
8. Post-creation DDL (indexes, columns) - Statements are allowed.
9. Data insertion- INSERT statement, including INSERT/EXEC.SELECT INTO statement.
10. Truncate table Allowed.
11. Rollbacks- Affected (Data is rolled back).
12. Dynamic SQL-Can use temporary tables created prior to calling the dynamic sql.
13. Can't define globally
Reference:
- http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table
- http://www.sqlserver-training.com/why-to-use-temporary-table-over-table-variable/-
- http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html
------------------------------------------------------------------------------------------------------------
Function:
- input must be used
- can't create temp table or normal table with in function
- we can create table variable with in function
- DDL and DML operation canot not supported
- we must return any value.
Limitations:
- We cannot use temporary tables inside the UDF.
- We cannot use Print command inside the UDF.
- We can't use the Insert, update and delete command inside the function. We can only use these commands on the table variable(not physical table) defined inside the function.
- We cannot use the try catch statement inside the function for the debugging purpose which makes it difficult to debug.
- We can't use transaction inside the function.
- We can't call the stored procedure from inside the function.
- We can't return multiple record sets from a function as we can do in case of Stored Procedure.
- Also performance of a SQL Function is low as compared to a stored procedure.
- So if we can get the same result through a stored procedure, we should write a stored procedure instead of SQL Function.
-------------------------------------------------------------------------------
Basic Difference between Strored procedure and Functions
- 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 .
- Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..
- Functions can be called from Procedure whereas Procedures cannot be called from Function.
Advance Difference between Strored procedure and Functions
- 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.
- 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.
- 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.
- 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.
