SSIS Tutorial: SQL Server 2008 Integration Services Tutorial:
http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm
Tutorial: SQL Server Reporting Services (SSRS)
Create a SSRS Report based on a Cube and Deploy it
http://www.sqlservercurry.com/2009/11/create-ssrs-report-based-on-cube-and.html
http://www.sqlservercurry.com/2009/11/create-ssrs-report-based-on-cube-and.html
Tutorial: SQL Server Analysis Services (SSAS)
SSAS Tutorial: SQL Server 2005 Analysis Services Tutorial
http://www.accelebrate.com/sql_training/ssas_tutorial.htm
SSAS Tutorial: SQL Server 2008 Analysis Services Tutorial:
http://www.accelebrate.com/sql_training/ssas_2008_tutorial.htm
http://www.accelebrate.com/sql_training/ssas_tutorial.htm
SSAS Tutorial: SQL Server 2008 Analysis Services Tutorial:
http://www.accelebrate.com/sql_training/ssas_2008_tutorial.htm
Tutorial: C#
http://www.dotnetspider.com/tutorials/DotNet-Tutorial-271.aspx
ADO.NET is the data access model that comes with the .NET Framework. ADO.NET provides the classes required to communicate with any database source (including Oracle, Sybase, Microsoft Access, Xml, and even text files).
ADO.NET comes with few providers, including:
OleDb
SqlClient
There are other providers available, but we are not including them here as this tutorial is meant for beginners! When you want them, search for ADO.NET providers in Google or MSDN
Microsoft made the SQL Server. So they gave a separate provider, specifically made for SQL Server. We can use the OleDb provider for all other database sources including MS Access, Oracle, Sybase etc. There is a separate provider available for Oracle.
A DATA PROVIDER is a set of classes that can be used to access, retrieve and manipulate data from the databases.
Both OleDb and SqlClient has its own set of classes, but they have the same concepts. We would like to classify the classes into two broad categories (this is not a microsoft classification, anyway!)
Classes for communicate with database
Classes for holding/manipulating data
The job of first category of classes is to communicate with database and send or retrieve data from the database. The second category of the classes will be used as a carrier of data.
The Connection, Command, DataReader, and DataAdapter
objects are the core elements of the ADO.NET provider model.
Each provider may have classes equivalent to above objects. The name of the classes vary slightly to represent the provider type appropriately.
Depending on the type of database you work on, you will have to choose either OleDb or SqlClient (or, some other provider) objects. Since all our samples use MS Access database, we will be using OleDb objects in all the samples. If you like to use SqlServer, you just need to replace the OleDb objects with the equivalent SqlClient objects.
DataSet
DataTable
DataRow
A DataSet is an in-memory representation of the database.
DataSet contains DataTables (and more...)
DataTable represents a database table
DataTable contains DataRows (and more...)
A DataRow represents a record in a database table.
DataRow is a collection of all fields in a record.
We can use the DataAdapter or DataReader to populate data in DataSet. Once we populate data from database, we can loop through all Tables in the DataSet and through each record in each Table.
On the first look, this may look bit confusing, but once you understand the concept and get familiar with the Ado.NET classes, you will appreciate the power and flexibility of Ado.NET.
http://www.dotnetspider.com/tutorials/DotNet-Tutorial-281.aspx
ADO.NET
ADO.NET is the data access model that comes with the .NET Framework. ADO.NET provides the classes required to communicate with any database source (including Oracle, Sybase, Microsoft Access, Xml, and even text files).
DataAccess Providers in .NET
ADO.NET comes with few providers, including:
There are other providers available, but we are not including them here as this tutorial is meant for beginners! When you want them, search for ADO.NET providers in Google or MSDN
Microsoft made the SQL Server. So they gave a separate provider, specifically made for SQL Server. We can use the OleDb provider for all other database sources including MS Access, Oracle, Sybase etc. There is a separate provider available for Oracle.
A DATA PROVIDER is a set of classes that can be used to access, retrieve and manipulate data from the databases.
Both OleDb and SqlClient has its own set of classes, but they have the same concepts. We would like to classify the classes into two broad categories (this is not a microsoft classification, anyway!)
The job of first category of classes is to communicate with database and send or retrieve data from the database. The second category of the classes will be used as a carrier of data.
Classes for communicating with database
The Connection, Command, DataReader, and DataAdapter
objects are the core elements of the ADO.NET provider model.
Each provider may have classes equivalent to above objects. The name of the classes vary slightly to represent the provider type appropriately.
Depending on the type of database you work on, you will have to choose either OleDb or SqlClient (or, some other provider) objects. Since all our samples use MS Access database, we will be using OleDb objects in all the samples. If you like to use SqlServer, you just need to replace the OleDb objects with the equivalent SqlClient objects.
Classes for holding data
The following are the main classes used to hold data in Ado.NET:We can use the DataAdapter or DataReader to populate data in DataSet. Once we populate data from database, we can loop through all Tables in the DataSet and through each record in each Table.
On the first look, this may look bit confusing, but once you understand the concept and get familiar with the Ado.NET classes, you will appreciate the power and flexibility of Ado.NET.
http://www.dotnetspider.com/tutorials/DotNet-Tutorial-281.aspx
Q&A: Oracle Basic
How to make a procedure private?
Don't declare it in package specification.
What is ref cursor?
It is cursor variable like a pointer in C. can point to any query. Used to pass data set between program units.
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
When a ref cursor open, who use and close this ref cursor?
The program unit who receive the data set will use and close the cursor, it may be other packages or front end code or Reporting tools.
What is an autonomous transaction?
Autonomous transaction is the transaction initiated by another transaction. The key point is it allows the separate commit/rollback transactions.
What are two exceptions to be careful of when using a select INTO statement?
1. When records not return, it would generate NOT_DATA_FOUND Found error message.
2. TOO_NAMY_ROWS can be another problem.
What is %type? What are the advantages of using this over datatypes?
%type means matching the date type of original data source. It can make sure there would be no problem with date type matching issue. On other hand, if declare an own datatype, when source data by chance is different from this declaration, there would be a data type consistence issue.
In PL/SQL how can you test that an update statement updated no rows?
SQL%ROWCOUNT can be used to test for this purpose.
Give examples of collections in oracle and their differences?
# Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
# Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
# Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
Have you used Bulk Collection? If yes then:"Why is bulk collection more efficient"?
Without bulk collection, every single row would request PL/SQL server to call SQL server one by one. By using bulk collection, say 5000 in a patch, the PL/SQL server will call SQL server for every 5000 rows. This can be very efficient.
What is the difference between a function and a procedure?
1. Functions are normally used for computations where as procedures are normally used for executing business logic.
2. Function is mainly used in the case where it must return 1 value, which can be scalar value or table or table values. Where as a procedure may not return a value (except through out parameter). If procedure mistakenly called, is returns always integer zero.
3. Procedure can call in another project but function work in same project.
4. We can't have any DDL, DML and TLC command inside a function, if that function is called from a SQL query as a UDF(user defined function). But if the function is not called from SQL query then we can have all transactional statement(DDL, DML and TLC) inside a function. Procedure can not be called from the SQL statements.
When a procedure within a package (containting multiple procedures) is called, what will get loaded into memory, the whole package or just the procedure?
Whole package will be loaded into memory.
How many triggers exist?
Trigger Types
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points:
•Before the triggering statement executes
•After the triggering statement executes
•Before each row that the triggering statement affects
•After each row that the triggering statement affects
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.
How to know if index is being used?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: SELECT table_name, index_name, monitoring, used FROM v$object_usage;
Don't declare it in package specification.
What is ref cursor?
It is cursor variable like a pointer in C. can point to any query. Used to pass data set between program units.
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
When a ref cursor open, who use and close this ref cursor?
The program unit who receive the data set will use and close the cursor, it may be other packages or front end code or Reporting tools.
What is an autonomous transaction?
Autonomous transaction is the transaction initiated by another transaction. The key point is it allows the separate commit/rollback transactions.
What are two exceptions to be careful of when using a select INTO statement?
1. When records not return, it would generate NOT_DATA_FOUND Found error message.
2. TOO_NAMY_ROWS can be another problem.
What is %type? What are the advantages of using this over datatypes?
%type means matching the date type of original data source. It can make sure there would be no problem with date type matching issue. On other hand, if declare an own datatype, when source data by chance is different from this declaration, there would be a data type consistence issue.
In PL/SQL how can you test that an update statement updated no rows?
SQL%ROWCOUNT can be used to test for this purpose.
Give examples of collections in oracle and their differences?
# Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
# Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
# Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
Have you used Bulk Collection? If yes then:"Why is bulk collection more efficient"?
Without bulk collection, every single row would request PL/SQL server to call SQL server one by one. By using bulk collection, say 5000 in a patch, the PL/SQL server will call SQL server for every 5000 rows. This can be very efficient.
What is the difference between a function and a procedure?
1. Functions are normally used for computations where as procedures are normally used for executing business logic.
2. Function is mainly used in the case where it must return 1 value, which can be scalar value or table or table values. Where as a procedure may not return a value (except through out parameter). If procedure mistakenly called, is returns always integer zero.
3. Procedure can call in another project but function work in same project.
4. We can't have any DDL, DML and TLC command inside a function, if that function is called from a SQL query as a UDF(user defined function). But if the function is not called from SQL query then we can have all transactional statement(DDL, DML and TLC) inside a function. Procedure can not be called from the SQL statements.
When a procedure within a package (containting multiple procedures) is called, what will get loaded into memory, the whole package or just the procedure?
Whole package will be loaded into memory.
How many triggers exist?
Trigger Types
A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE trigger is fired by a DELETE statement, and so on. An INSTEAD OF trigger is a DML trigger that is defined on a view (not a table). The database fires the INSTEAD OF trigger instead of executing the triggering DML statement.
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.
A simple trigger can fire at exactly one of the following timing points:
•Before the triggering statement executes
•After the triggering statement executes
•Before each row that the triggering statement affects
•After each row that the triggering statement affects
A compound trigger can fire at more than one timing point. Compound triggers make it easier to program an approach where you want the actions you implement for the various timing points to share common data.
How to know if index is being used?
One can use the index monitoring feature to check if indexes are used by an application or not. When the MONITORING USAGE property is set for an index, one can query the v$object_usage to see if the index is being used or not. Here is an example: SELECT table_name, index_name, monitoring, used FROM v$object_usage;
Q&A: Database Basic
Normal DB is under which normal form?
3rd normal form, but sometimes for better performance, can be in 2nd normal form.
What is difference between TRUNCATE, DELETE and DROP commands?
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
What is difference between UNION and UNION ALL?
The Oracle SQL UNION differs from the Oracle UNION ALL primarily because it does not filter out duplicitous rows. The UNION SQL operator returns only the unique rows that appear in either result, while the UNION ALL operator returns all rows in both queries, including duplicate rows.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21547067945222
What is Cross Join?
CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. CROSS JOIN serves function as Inner Join if WHERE clause had been added. Vice versa, if INNER JOIN without WHERE clause, it is a cross join.
How to stop a process?
Using KILL command.
http://en.wikipedia.org/wiki/Kill_%28command%29
What is the difference between Inner Join and Outer Join?
3rd normal form, but sometimes for better performance, can be in 2nd normal form.
What is difference between TRUNCATE, DELETE and DROP commands?
DELETE
The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
TRUNCATE
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
DROP
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
What is difference between UNION and UNION ALL?
The Oracle SQL UNION differs from the Oracle UNION ALL primarily because it does not filter out duplicitous rows. The UNION SQL operator returns only the unique rows that appear in either result, while the UNION ALL operator returns all rows in both queries, including duplicate rows.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:21547067945222
What is Cross Join?
CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. CROSS JOIN serves function as Inner Join if WHERE clause had been added. Vice versa, if INNER JOIN without WHERE clause, it is a cross join.
How to stop a process?
Using KILL command.
http://en.wikipedia.org/wiki/Kill_%28command%29
What is the difference between Inner Join and Outer Join?
Q&A: Oracle Performane Tuning
Why using globe temp table (GTT)?
Faster, improve performance.
http://www.dba-oracle.com/t_temporary_tables_sql.htm
http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm
What is the difference between 'on commit preserve rows' and 'on commit delete rows' for GTT?
The ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay in this table until a logoff.
The ON COMMIT DELETE ROWS makes this a transaction based temp table. When you commit -- the rows disappear.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:48812348054
What is explain plan and how to read it?
It tells you how oracle processes the query. Read it form inner right to outer left.
http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php
http://www.orafaq.com/node/1420
Why use bulk collect?
Improve performance.
http://www.dba-oracle.com/t_oracle_bulk_collect.htm
Faster, improve performance.
http://www.dba-oracle.com/t_temporary_tables_sql.htm
http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm
What is the difference between 'on commit preserve rows' and 'on commit delete rows' for GTT?
The ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay in this table until a logoff.
The ON COMMIT DELETE ROWS makes this a transaction based temp table. When you commit -- the rows disappear.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:48812348054
What is explain plan and how to read it?
It tells you how oracle processes the query. Read it form inner right to outer left.
http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php
http://www.orafaq.com/node/1420
Why use bulk collect?
Improve performance.
http://www.dba-oracle.com/t_oracle_bulk_collect.htm
Subscribe to:
Posts (Atom)
Posts
