Knowledge Sharing

In continuation with the topic 'Oracle Vs MS-SQL : Views' , I would like to say about indexes before taking you to the materialized views, because materilazied views use the Index. Index is not a new concept or strictly database concept. It is similar as book index which helps to find out a paricular topic instead of searching all the pages to find out the topic. Index in database is used to improve the performance of the query. Index concept is there in both oralce as well as  MS-SQL.  Indexes are most useful on larger tables, on columns that are likely to appear in 'where ' clauses. We can create index on multiple columns and it is better to create less number of index with multiple column instead of creating index on each columns. If we create more index on the table then the DML operation will take longer time to execute. There are two types of Index, first one is Btree and the other is Bitmap, but MS-SQL does not support Bitmap seraching. Btree A B-tree consist

In continuation with the discussion on Oracle vs. MSSQL, today I will address the views in two databases. There are two different types of views – simple views and materialized views. Views are created to hide the complexity of SQL statements to the end-applications and querying agents. Views can include queries to multiple tables, rules and transformations, and even aggregations. Simple views do not contain data, just SQL statements which use the underlying tables to fetch data while executing. Materialized views persist data in and queries to underlying tables are not made unless the view is refreshed explicitly. With simple views, it is also possible to perform an update statement into the base table. In such case the following operators should not be present in the SQL inside the view. Set Operators ( INTERSECT, MINUS|EXCEPT, UNION, UNION ALL ) DISTINCT GROUP BY ORDER BY JOIN INTO The updateable views should not contain subqueries as well. The syntax to create v

Sql set operations allows us to combine the results from multiple query into one. It is purely based on the mathematical set theory. Each sql statement can be treated as one set and we can combine this result by the operator. We should make note of the point that if you want to use these operators there should be equal number of columns selected in all the queries and also data type of each column should match with the same column of other queries. In case of oracle same data type in all the queries are not required since it will implicitly convert the data type but not in all the cases. For e.g handling null. Select 1 num, a name from dual Union Select null, ‘jithesh’ name from dual; ERROR at line 1: ORA-01790: expression must have same datatype as corresponding expression Operator ORACLE MS-SQL UNION Available Available UNION ALL Available Available INTERSECT Available Available MINUS Available except We will use the below ment