Checklist for Analyzing Slow-Running Queries?
# Use SQL Server Profiler to help identify the slow query or queries. For more information. Use the sys.dm_exec_query_stats and sys.dm_exec_requests dynamic management views to find similar queries that collectively consume a large number of resources.
# How do I analyze the performance of a slow-running query? After you have identified the slow-running query or queries, you can further analyze query performance by producing a Showplan, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. (You can produce a Showplan using Transact-SQL SET options, SQL Server Management Studio, or SQL Server Profiler.)
# Are suitable indexes available? Would adding one or more indexes improve query performance?
# Are there any data or index hot spots? Consider using disk striping. Disk striping can be implemented by using RAID (redundant array of independent disks) level 0, where data is distributed across multiple disk drives.
# Is the query optimizer provided with the best opportunity to optimize a complex query?
# If you have a large volume of data, do you need to partition it? Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance. For more information, see Understanding Partitioning and Tuning the Physical Database Design.
Q&A: Visual Basic 6
What is module?
variables and functions that are used throughout your program and on different forms can be stored in modules so that not to rewrite again each time the variables and functions when you need them in a form.
What is namespace?
A namespace is a collection of different classes. All VB applications are developed using classes from the .NET System namespace. The namespace with all the built-in VB functionality is the System namespace. All other namespaces are based on this System namespace. Namespaces organize the objects defined in an assembly. Assemblies can contain multiple namespaces, which can in turn contain other namespaces. Namespaces prevent ambiguity and simplify references when using large groups of objects such as class libraries.For example, the .NET Framework defines the ListBox class in the System.Windows.Forms namespace.
What is Lambda Expression?
A lambda expression is a function or subroutine without a name that can be used wherever a delegate is valid. Lambda expressions can be functions or subroutines and can be single-line or multi-line. You can pass values from the current scope to a lambda expression.
What is delegate?
A delegate is a form of object-oriented function pointer that allows a function to be invoked indirectly by way of a reference to the function. Delegates can be used to hook up event handlers and pass a procedure from one procedure to another. Delegates are objects that refer to methods. They are sometimes described as type-safe function pointers because they are similar to function pointers used in other programming languages. But unlike function pointers, Visual Basic delegates are a reference type based on the class System.Delegate. Delegates can reference both shared methods — methods that can be called without a specific instance of a class — and instance methods.
What is interface?
Interfaces define the properties, methods, and events that classes can implement. Interfaces allow you to define features as small groups of closely related properties, methods, and events; this reduces compatibility problems because you can develop enhanced implementations for your interfaces without jeopardizing existing code. You can add new features at any time by developing additional interfaces and implementations. There are several other reasons why you might want to use interfaces instead of class inheritance:
* Interfaces are better suited to situations in which your applications require many possibly unrelated object types to provide certain functionality.
* Interfaces are more flexible than base classes because you can define a single implementation that can implement multiple interfaces.
* Interfaces are better in situations in which you do not have to inherit implementation from a base class.
* Interfaces are useful when you cannot use class inheritance. For example, structures cannot inherit from classes, but they can implement interfaces.
What is assembly?
An assembly is the building block of a .NET application. It is a self describing collection of code, resources, and metadata (data about data, example, name, size, version of a file is metadata about that file). An Assembly is a complied and versioned collection of code and metadata that forms an atomic functional unit. Assemblies take the form of a dynamic link library (.dll) file or executable program file (.exe) but they differ as they contain the information found in a type library and the information about everything else needed to use an application or component. All .NET programs are constructed from these Assemblies. Assemblies are made of two parts: manifest, contains information about what is contained within the assembly and modules, internal files of IL code which are ready to run. When programming, we don't directly deal with assemblies as the CLR and the .NET framework takes care of that behind the scenes. The assembly file is visible in the Solution Explorer window of the project.
variables and functions that are used throughout your program and on different forms can be stored in modules so that not to rewrite again each time the variables and functions when you need them in a form.
What is namespace?
A namespace is a collection of different classes. All VB applications are developed using classes from the .NET System namespace. The namespace with all the built-in VB functionality is the System namespace. All other namespaces are based on this System namespace. Namespaces organize the objects defined in an assembly. Assemblies can contain multiple namespaces, which can in turn contain other namespaces. Namespaces prevent ambiguity and simplify references when using large groups of objects such as class libraries.For example, the .NET Framework defines the ListBox class in the System.Windows.Forms namespace.
What is Lambda Expression?
A lambda expression is a function or subroutine without a name that can be used wherever a delegate is valid. Lambda expressions can be functions or subroutines and can be single-line or multi-line. You can pass values from the current scope to a lambda expression.
What is delegate?
A delegate is a form of object-oriented function pointer that allows a function to be invoked indirectly by way of a reference to the function. Delegates can be used to hook up event handlers and pass a procedure from one procedure to another. Delegates are objects that refer to methods. They are sometimes described as type-safe function pointers because they are similar to function pointers used in other programming languages. But unlike function pointers, Visual Basic delegates are a reference type based on the class System.Delegate. Delegates can reference both shared methods — methods that can be called without a specific instance of a class — and instance methods.
What is interface?
Interfaces define the properties, methods, and events that classes can implement. Interfaces allow you to define features as small groups of closely related properties, methods, and events; this reduces compatibility problems because you can develop enhanced implementations for your interfaces without jeopardizing existing code. You can add new features at any time by developing additional interfaces and implementations. There are several other reasons why you might want to use interfaces instead of class inheritance:
* Interfaces are better suited to situations in which your applications require many possibly unrelated object types to provide certain functionality.
* Interfaces are more flexible than base classes because you can define a single implementation that can implement multiple interfaces.
* Interfaces are better in situations in which you do not have to inherit implementation from a base class.
* Interfaces are useful when you cannot use class inheritance. For example, structures cannot inherit from classes, but they can implement interfaces.
What is assembly?
An assembly is the building block of a .NET application. It is a self describing collection of code, resources, and metadata (data about data, example, name, size, version of a file is metadata about that file). An Assembly is a complied and versioned collection of code and metadata that forms an atomic functional unit. Assemblies take the form of a dynamic link library (.dll) file or executable program file (.exe) but they differ as they contain the information found in a type library and the information about everything else needed to use an application or component. All .NET programs are constructed from these Assemblies. Assemblies are made of two parts: manifest, contains information about what is contained within the assembly and modules, internal files of IL code which are ready to run. When programming, we don't directly deal with assemblies as the CLR and the .NET framework takes care of that behind the scenes. The assembly file is visible in the Solution Explorer window of the project.
Tutorial: SQL Server Integration Services (SSIS)
SSIS Tutorial: SQL Server 2008 Integration Services Tutorial:
http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm
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
Last Weekday n In T-SQL
T-SQL query:
SELECT DATEADD(D, n-DATEPART(DW, GETDATE()), DATEDIFF(DD, 0, GETDATE()))
can be used to get last weekday n in T-SQL. Following is the value of parameter n:
The result is that weekday at 0:00:00 timestamp.
However, if this is to be used as parameter in SSRS, it would be:
=DATEADD("D", n-WEEKDAY(TODAY())), TODAY())
This is because the syntax are different in parameter of SSRS and T-SQL.
Many people would prefer predict in T-SQL as follows:
WHERE PERIOD >= STARTDATE AND PERIOD < ENDDATE
However, in day-to-day life, people prefer both dates be to inclusive. In this case, the predict should be:
WHERE PERIOD >= STARTDATE AND PERIOD <= ENDDATE
Therefore, the n parameter should be changed accordingly.
http://weblogs.sqlteam.com/jhermiz/archive/2007/08/15/60289.aspx
SELECT DATEADD(D, n-DATEPART(DW, GETDATE()), DATEDIFF(DD, 0, GETDATE()))
can be used to get last weekday n in T-SQL. Following is the value of parameter n:
| At Least Hours Ago | Mon | Tue | Wed | Thu | Fri | Sat | Sun |
|---|---|---|---|---|---|---|---|
| 0 | 2 | 3 | -3 | -2 | -1 | 0 | 1 |
| 24 | 2 | -4 | -3 | -2 | -1 | 0 | 1 |
| 48 | -5 | -4 | -3 | -2 | -1 | 0 | 1 |
However, if this is to be used as parameter in SSRS, it would be:
=DATEADD("D", n-WEEKDAY(TODAY())), TODAY())
This is because the syntax are different in parameter of SSRS and T-SQL.
Many people would prefer predict in T-SQL as follows:
WHERE PERIOD >= STARTDATE AND PERIOD < ENDDATE
However, in day-to-day life, people prefer both dates be to inclusive. In this case, the predict should be:
WHERE PERIOD >= STARTDATE AND PERIOD <= ENDDATE
Therefore, the n parameter should be changed accordingly.
http://weblogs.sqlteam.com/jhermiz/archive/2007/08/15/60289.aspx
Schedule a Job in SQL Server Management Studio
To create and attach a schedule to a job
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
3. Select the Schedules page, and then click New.
4. In the Name box, type a name for the new schedule.
5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
6. For Schedule Type, select one of the following:
* Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
* Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
* Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
* Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.
To attach a schedule to a job
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
3. Select the Schedules page, and then click Pick.
4. Select the schedule that you want to attach, and then click OK.
5. In the Job Properties dialog box, double-click the attached schedule.
6. Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.
7. In the Job Properties dialog box, click OK.
Where is SQL Server Agent? By default, it is tuned off.
To start the SQL Server Agent service
1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2.In SQL Server Configuration Manager, expand Services, and then click SQL Agent.
3.In the results pane, right-click any instance, and then click Start. A green arrow on the icon next to the SQL Server Agent and on the toolbar indicates that SQL Server Agent started successfully.
4.Click OK.
Can't do it? You might be using express version. Only in full version you can turn it on.
http://technet.microsoft.com/en-us/library/ms191439.aspx
http://msdn.microsoft.com/en-us/library/ms191454.aspx
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click Properties.
3. Select the Schedules page, and then click New.
4. In the Name box, type a name for the new schedule.
5. Clear the Enabled check box if you do not want the schedule to take effect immediately following its creation.
6. For Schedule Type, select one of the following:
* Click Start automatically when SQL Server Agent starts to start the job when the SQL Server Agent service is started.
* Click Start whenever the CPUs become idle to start the job when the CPUs reach an idle condition.
* Click Recurring if you want a schedule to run repeatedly. To set the recurring schedule, complete the Frequency, Daily Frequency, and Duration groups on the dialog.
* Click One time if you want the schedule to run only once. To set the One time schedule, complete the One-time occurrence group on the dialog.
To attach a schedule to a job
1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job that you want to schedule, and click Properties.
3. Select the Schedules page, and then click Pick.
4. Select the schedule that you want to attach, and then click OK.
5. In the Job Properties dialog box, double-click the attached schedule.
6. Verify that Start date is set correctly. If it is not, set the date when you want for the schedule to start, and then click OK.
7. In the Job Properties dialog box, click OK.
Where is SQL Server Agent? By default, it is tuned off.
To start the SQL Server Agent service
1.On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.
2.In SQL Server Configuration Manager, expand Services, and then click SQL Agent.
3.In the results pane, right-click any instance, and then click Start. A green arrow on the icon next to the SQL Server Agent and on the toolbar indicates that SQL Server Agent started successfully.
4.Click OK.
Can't do it? You might be using express version. Only in full version you can turn it on.
http://technet.microsoft.com/en-us/library/ms191439.aspx
http://msdn.microsoft.com/en-us/library/ms191454.aspx
CAST Varchar To Integer in T-SQL
CAST(CASE WHEN ISNUMERIC(variab)=1 THEN variab ELSE 0 END AS INT)
This changes all non numeric string to 0 when deal with not so cleaned data.
This changes all non numeric string to 0 when deal with not so cleaned data.
In SQL Query, How to Compare Across Different Rows
In SQL query, it is easy to compare values in different columns for same row. However, when compare value at column_A of Row 1 to the values at column_B of Rows 2 to 3, it would be hard. Sure, you always can write the cursor to process rows one by one. Here is a quick way: INNER JOIN this table by itself, you would get following rows: New row 1 = old row 1 and 1 New row 2 = old row 1 and 2 New row 3 = old row 1 and 3 New row 4 = old row 2 and 1 New row 5 = old row 2 and 2 New row 6 = old row 2 and 3 New row 7 = old row 3 and 1 New row 8 = old row 3 and 2 New row 9 = old row 3 and 3 Now you will be able to compare column_A and column_B at new rows 2 to 3, which is what you want. Just remember to eliminate row 1 and row 4 to 9.
Set MS Access As An Interface to Access Other Database
MS Access can act as an stand alone database or an interface to access ther database, or a database consists of both local tables and linked tables. To set up such links after you have been granted the access to database from DBA, do follows:
1. In Windows' Administrative Tools, click Data Source (ODBC) to set up the link.
2. In MS Access, Click External Data menu.
3. In Import, click More to find out the ODBC Database.
4. Select all tables you need.
1. In Windows' Administrative Tools, click Data Source (ODBC) to set up the link.
2. In MS Access, Click External Data menu.
3. In Import, click More to find out the ODBC Database.
4. Select all tables you need.
SEO Tips
Put following scripts in HTML Head:
‹meta content='DESCRIPTION HERE' name='description'/>
‹meta content='KEYWORDS HERE' name='keywords'/>
‹meta content='AUTHOR NAME HERE' name='author'/>
Keywords need to be separated by comma.
Several people were filling with just the keywords in the description. DON'T DO THAT. Google and other search engines will ban your site from the search engines list if you did so. And also don't repeat the keyword more than 3 times.
http://www.bloggertricks.com/2007/12/adding-meta-tags-to-bloggerblogspot.html
‹meta content='DESCRIPTION HERE' name='description'/>
‹meta content='KEYWORDS HERE' name='keywords'/>
‹meta content='AUTHOR NAME HERE' name='author'/>
Keywords need to be separated by comma.
Several people were filling with just the keywords in the description. DON'T DO THAT. Google and other search engines will ban your site from the search engines list if you did so. And also don't repeat the keyword more than 3 times.
http://www.bloggertricks.com/2007/12/adding-meta-tags-to-bloggerblogspot.html
Update with Values from Fields in Another Table in Ms Access
Ms Access uses Jet engine, which is different in somewhere from T-SQL. Here is the way to update a field in Table_1 with the values from field in Tab_B:
UPDATE Table_1
INNER JOIN Tab_B ON Tab_B.Key_Field_In_B = Table_1.Key_Field_In_1
SET Table_1.Target_Field = Tab_B.From_Field
WHERE Tab_B.Condition_Field = 'Something'
Sometimes, you may still get error message: Operation must use an updateable query. Most likely, the join key of either table are not unique key, as a result, the join action does generate more rows than that in Table_1. Since this is an update action, this does caused the error.
http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html
UPDATE Table_1
INNER JOIN Tab_B ON Tab_B.Key_Field_In_B = Table_1.Key_Field_In_1
SET Table_1.Target_Field = Tab_B.From_Field
WHERE Tab_B.Condition_Field = 'Something'
Sometimes, you may still get error message: Operation must use an updateable query. Most likely, the join key of either table are not unique key, as a result, the join action does generate more rows than that in Table_1. Since this is an update action, this does caused the error.
http://www.fmsinc.com/microsoftaccess/query/snytax/update-query.html
Fast Redirect Method
Page redirection is used to redirect/forward a page visitor to another page, such as when a pageor
* JavaScript Redirect - preferred method
* Timed Redirect with JavaScript
* meta-tag - provided for reference but not the preferred method.
Hands on experiences show following method is the fast, if that is what you want:
‹script language="JavaScript"›
; window.onload = function()
{ ; window.location.replace("http://yourdomain.com/")
}
‹/script›
http://grizzlyweb.com/webmaster/javascripts/redirection.asp#version2
* JavaScript Redirect - preferred method
* Timed Redirect with JavaScript
* meta-tag - provided for reference but not the preferred method.
Hands on experiences show following method is the fast, if that is what you want:
‹script language="JavaScript"›
; window.onload = function()
{ ; window.location.replace("http://yourdomain.com/")
}
‹/script›
http://grizzlyweb.com/webmaster/javascripts/redirection.asp#version2
Add Sequential Number Column in SQL Query Result
To add a column with sequential number in SQL query can be done through add a sequence numbered table contains a link key or directly compose in query. The first method sounds unwise, but if the query itself is too complicated, it might not be a bad idea.
The second method is:
SELECT
(
SELECT COUNT(au_id)
FROM Mytable1 AS x
WHERE x.au_id <= y.au_id
) AS Sequence
, au_id
, something_else
FROM Mytable1 AS y
ORDER BY au_id
This constrains here are:
1. Field au_id must be number.
2. The query itself can’t be too complicated.
3. If Mytable is from another query, if won’t work properly in some systems, such as SQL Server.
http://www.databasejournal.com/features/mssql/article.php/3373861/Auto-Number-and-Cumulative-sum-in-SQL-Server-Query-results.htm
The second method is:
SELECT
(
SELECT COUNT(au_id)
FROM Mytable1 AS x
WHERE x.au_id <= y.au_id
) AS Sequence
, au_id
, something_else
FROM Mytable1 AS y
ORDER BY au_id
This constrains here are:
1. Field au_id must be number.
2. The query itself can’t be too complicated.
3. If Mytable is from another query, if won’t work properly in some systems, such as SQL Server.
http://www.databasejournal.com/features/mssql/article.php/3373861/Auto-Number-and-Cumulative-sum-in-SQL-Server-Query-results.htm
Object Oriented Programming in JavaScript
There are several ways to do this, such as using prototype and JavaScript object. However, simplest may be the best. Following method matches the orthodox structure of OO, which is put everything inside of a class file:
; var ObjectClass = function(inputPara)
{
; var privateAttribute_1 = inputPara
; this.publicAttribute_Temp = 200
; function privateMethod_SetInternally()
{ ; privateAttribute_1 = 100
}
; this.publicMethod_1 = function()
{ ; privateMethod_SetInternally()
; return 50*privateAttribute_1
}
; this.publicMethod_Get = function()
{ ; return privateAttribute_1
}
; this.publicMethod_SetExternally = function(valueSetLater)
{ ; privateAttribute_1 = this.publicMethod_1*valueSetLater*123456789
}
}
Please note, for private method, do not use Varibalized Function, which is also called Function Literal, Function Reference or Function Pointer, like this:
; var privateMethod_SetInternally = function () {…}
Indeed, private method privateMethod_SetInternally is useless here, because you can always set internally directly:
; privateAttribute_1 = newValue
The purpose of privateMethod_SetInternally here is to show how to call this method, as stated in publistMethod_1. There is no way for outsider to use this private method, but through publistMethod_1.
All attributes/method started with this. is the public accessible by outsider caller, or the object based on this Class.
To use this Class, an object needs to be created:
; var obj = New ObjectClass(300)
; var att1_inThere = obj.publicMethod_Get()
; obj.publicMethod_SetExternally(400)
Please note, internally, calling all public accessible attributes/methods would not need with (), while private methods need to include (). However, for outsider object, public methods need to include () as well, like above.
There is a problem. Since internally calling public methods could not be with (), there is no way to pass the parameter while calling. In here, publicMethod_SetExternally can be called and assigned with parameters externally with no problem, but can only be called without assigning parameter internally. In this case, parameter is essential, so it would generate “not defined” error.
To overcome this, publicMethod_SetExternally needs to be rewritten as follows:
; this.publicMethod_SetExternally = function()
{ ; privateAttribute_1 = this.publicMethod_1*this.publicAttribute_Temp*123456789
}
Internally, it can now be called directly:
; this.publicMethod_SetExternally
Externally, it would be called like this:
; obj.publicAttribute_Temp = 400
; obj.publicMethod_SetExternally()
; var ObjectClass = function(inputPara)
{
; var privateAttribute_1 = inputPara
; this.publicAttribute_Temp = 200
; function privateMethod_SetInternally()
{ ; privateAttribute_1 = 100
}
; this.publicMethod_1 = function()
{ ; privateMethod_SetInternally()
; return 50*privateAttribute_1
}
; this.publicMethod_Get = function()
{ ; return privateAttribute_1
}
; this.publicMethod_SetExternally = function(valueSetLater)
{ ; privateAttribute_1 = this.publicMethod_1*valueSetLater*123456789
}
}
Please note, for private method, do not use Varibalized Function, which is also called Function Literal, Function Reference or Function Pointer, like this:
; var privateMethod_SetInternally = function () {…}
Indeed, private method privateMethod_SetInternally is useless here, because you can always set internally directly:
; privateAttribute_1 = newValue
The purpose of privateMethod_SetInternally here is to show how to call this method, as stated in publistMethod_1. There is no way for outsider to use this private method, but through publistMethod_1.
All attributes/method started with this. is the public accessible by outsider caller, or the object based on this Class.
To use this Class, an object needs to be created:
; var obj = New ObjectClass(300)
; var att1_inThere = obj.publicMethod_Get()
; obj.publicMethod_SetExternally(400)
Please note, internally, calling all public accessible attributes/methods would not need with (), while private methods need to include (). However, for outsider object, public methods need to include () as well, like above.
There is a problem. Since internally calling public methods could not be with (), there is no way to pass the parameter while calling. In here, publicMethod_SetExternally can be called and assigned with parameters externally with no problem, but can only be called without assigning parameter internally. In this case, parameter is essential, so it would generate “not defined” error.
To overcome this, publicMethod_SetExternally needs to be rewritten as follows:
; this.publicMethod_SetExternally = function()
{ ; privateAttribute_1 = this.publicMethod_1*this.publicAttribute_Temp*123456789
}
Internally, it can now be called directly:
; this.publicMethod_SetExternally
Externally, it would be called like this:
; obj.publicAttribute_Temp = 400
; obj.publicMethod_SetExternally()
Annualized Projection Formula in Excel
Follow formula can be used in Excel when conduct the annual projection to avoid possible lack of sufficient data which may cause various format errors, such as divided by 0.
Assume annualized project is based on year to date information, in comparison to last year’s year to date number together with the last year’s summation, it can achieve the seasonal adjustment by using simple strait-line method:
A1-A12: Monthly numbers of last year
B1-B12: Monthly numbers of current year
C1: The current month number within a year, for instance, June is 6 in 12 months.
A13: Last year’s Year-to-date =SUM(A1:INDIRECT(ADDRESS(ROW(A1)+C1-1,COLUMN(A1))))
A14: Last year’s summation = SUM(A1:A12)
B13: Current year’s Year-to-date.
B14: Current year’s seasonal adjusted annualized projection = ISNUMBER(ROUNDUP(B13/A13*A14,-2),0).
The formula is: =MAX(ISNUMBER(ROUNDUP(A13/C1*12,-2),0),B14)
Can also use ROUND() or ROUNDDOWN() function.
Basically, it is to determine if seasonal adjustment should be used based on the validity of the data. The formula can guarantee a valid projection number will be generated.
Assume annualized project is based on year to date information, in comparison to last year’s year to date number together with the last year’s summation, it can achieve the seasonal adjustment by using simple strait-line method:
A1-A12: Monthly numbers of last year
B1-B12: Monthly numbers of current year
C1: The current month number within a year, for instance, June is 6 in 12 months.
A13: Last year’s Year-to-date =SUM(A1:INDIRECT(ADDRESS(ROW(A1)+C1-1,COLUMN(A1))))
A14: Last year’s summation = SUM(A1:A12)
B13: Current year’s Year-to-date.
B14: Current year’s seasonal adjusted annualized projection = ISNUMBER(ROUNDUP(B13/A13*A14,-2),0).
The formula is: =MAX(ISNUMBER(ROUNDUP(A13/C1*12,-2),0),B14)
Can also use ROUND() or ROUNDDOWN() function.
Basically, it is to determine if seasonal adjustment should be used based on the validity of the data. The formula can guarantee a valid projection number will be generated.
How Draw, Copy and Move a Circle in ArcEditor with Specific Radius
Article with ID 26558 in ArcGIS Resource Center has described the steps. Here is an improved version:
1. Add the Advanced Editing toolbar to ArcMap by clicking View > Toolbars > Advanced Editing.
2. If Drawing toolbar does not appear, click on the Editor menu and select Start Editing.
3. Zoom in to the right position and size in order to make precise pinpoint of the circle of the circle, in following steps.
4. Click on the Marker tool (a little dot) on the Drawing toolbar.
5. Click somewhere on the map to specify where the center of the circle will be to place the Marker. Make an adjustment to the size of this Marker.
6. Click on the Circle tool on the Drawing toolbar.
7. Click the Markers on the map which specifies where the center of the circle will be, and don't release the button on your mouse.
8. Move your mouse little bit while still hold your mouse button, hit the 'R' key on the keyboard.
9. Specify the radius of the circle in map units and hit the Enter key.
10. If you do not want the Marker to be seen, you can either remove it or color it into invisible. The latter is suggested because any future move and copy may rely on this Marker to be precised.
Done.
To move or copy a circle, you may regard the center of the circle as base point. In this case, use the Marker as mentioned above.
1. Zoom in to the right position and size in order to make precise pinpoint for both old and new position of the center of the circle.
2. Click "Select Elements" button, then click the circle.
3. Press Ctrl in keyboard while click the Marker at the center of the circle, to grouping it.
4. Ctrl C and Ctrl V to copy and paste. For move, just use your mouse to drag and drop.
5. Drag and drop to right position base on the Marker.
http://resources.arcgis.com/content/kbase?fa=articleShow&d=26558
1. Add the Advanced Editing toolbar to ArcMap by clicking View > Toolbars > Advanced Editing.
2. If Drawing toolbar does not appear, click on the Editor menu and select Start Editing.
3. Zoom in to the right position and size in order to make precise pinpoint of the circle of the circle, in following steps.
4. Click on the Marker tool (a little dot) on the Drawing toolbar.
5. Click somewhere on the map to specify where the center of the circle will be to place the Marker. Make an adjustment to the size of this Marker.
6. Click on the Circle tool on the Drawing toolbar.
7. Click the Markers on the map which specifies where the center of the circle will be, and don't release the button on your mouse.
8. Move your mouse little bit while still hold your mouse button, hit the 'R' key on the keyboard.
9. Specify the radius of the circle in map units and hit the Enter key.
10. If you do not want the Marker to be seen, you can either remove it or color it into invisible. The latter is suggested because any future move and copy may rely on this Marker to be precised.
Done.
To move or copy a circle, you may regard the center of the circle as base point. In this case, use the Marker as mentioned above.
1. Zoom in to the right position and size in order to make precise pinpoint for both old and new position of the center of the circle.
2. Click "Select Elements" button, then click the circle.
3. Press Ctrl in keyboard while click the Marker at the center of the circle, to grouping it.
4. Ctrl C and Ctrl V to copy and paste. For move, just use your mouse to drag and drop.
5. Drag and drop to right position base on the Marker.
http://resources.arcgis.com/content/kbase?fa=articleShow&d=26558
How to Upload Data to ArcEditor
Save data in Excel file. Open ArcCatalog, connect to that folder, find the table/worksheet in Excel file, right click it, click Create Feature Class, click From XY Table, a pop-up window Create Feature Class From XY Table will be shown. Select with XY columns and select right folder and file name for new SHP file destination, then click OK.
If it does not show on the folder, right click the folder to refresh it.
In ArcEditor, menu Windows, select Table of Contents. In the layer you want to add, right click it, click Add Data, and then upload the SHP files you have just created.
Done.
If it does not show on the folder, right click the folder to refresh it.
In ArcEditor, menu Windows, select Table of Contents. In the layer you want to add, right click it, click Add Data, and then upload the SHP files you have just created.
Done.
Javascript Error Message: is not a function
This error message appears when you use Varibalized Function (or called Function Literal, Function Reference, Function Pointer) method to declare function while you did not put into right order:
(Caller here)
; var func = function()
{
...
}
Solution 1:
; var func
(Caller here)
; func = function()
{
...
}
Solution 2:
(Caller here)
; function func()
{
...
}
http://www.dustindiaz.com/javascript-function-declaration-ambiguity/
Function Declaration in JavaScript
(Caller here)
; var func = function()
{
...
}
Solution 1:
; var func
(Caller here)
; func = function()
{
...
}
Solution 2:
(Caller here)
; function func()
{
...
}
http://www.dustindiaz.com/javascript-function-declaration-ambiguity/
Function Declaration in JavaScript
How to Create a New Report by Copy from Existing One in SSRS
First of all, if the Solution and Project for which the new report is supposed to be there is not exist yet, create the Solution and/or Project first.
1. In Solution Explorer, find existing report, right click and select COPY.
2. Click the Project where you want your new report be, right click and select PASTE.
Done.
1. In Solution Explorer, find existing report, right click and select COPY.
2. Click the Project where you want your new report be, right click and select PASTE.
Done.
Subscribe to:
Posts (Atom)
Posts
