UPDATE ROWS FROM THE SAME TABLE

AS SALAMO ALAIKUM WA RAHMATULLAH

SELECT * FROM UPD_ROW

ID ITEMID ITEM QTY LOCATIONID
1 6 NOKIA-N30-B 2 5
2 7 NOKIA-N30-SL 1 5
3 8 NOKIA-N30-GL 10 5
4 9 IPH6-128-GL 10 5
5 10 IPH6-128-WH 1 5
6 11 IPH6-128-WH 10 135
7 11 IPH6-128-WH 5 5
8 9 IPH6-128-GL 11 135

9 8 NOKIA-N30-GL 20 135

UPDATE COMMAND
;WITH ITEM_QTY (ID,QT)
AS
(
SELECT ITEMID,QTY FROM UPD_ROW
WHERE LOCATIONID=5) 
UPDATE UPD_ROW
SET QTY=QTY+ITEM_QTY.QT
FROM ITEM_QTY

WHERE LOCATIONID=135 AND ITEM_QTY.ID=ITEMID
-- RESULT
ID ITEMID ITEM QTY LOCATIONID
1 6 NOKIA-N30-B 2 5
2 7 NOKIA-N30-SL 1 5
3 8 NOKIA-N30-GL 10 5
4 9 IPH6-128-GL 10 5
5 10 IPH6-128-WH 1 5
6 11 IPH6-128-WH 15 135
7 11 IPH6-128-WH 5 5
8 9 IPH6-128-GL 21 135

9 8 NOKIA-N30-GL 30 135

--
MA ASALAAM
PASSION 4 ORACLE

Formula to estimate content database storage

AS SALAMO ALAIKUM WA RAHMATULLAH

The following process describes how to approximately estimate the storage required for content databases, without considering log files:
Use the following formula to estimate the size of your content databases:
Database size = ((D × V) × S) + (10 KB × (L + (V × D)))
NoteNote:
The value, 10 KB, in the formula is a constant that approximately estimates the amount of metadata required by SharePoint Server 2013. If your system requires significant use of metadata, you may want to increase this constant.
Calculate the expected number of documents. This value is known as D in the formula.
How you calculate the number of documents will be determined by the features that you are using. For example, for My Sites or collaboration sites, we recommend that you calculate the expected number of documents per user and multiply by the number of users. For records management or content publishing sites, you may calculate the number of documents that are managed and generated by a process.
If you are migrating from a current system, it may be easier to extrapolate your current growth rate and usage. If you are creating a new system, review your existing file shares or other repositories and estimate based on that usage rate.
Estimate the average size of the documents that you'll be storing. This value is known as S in the formula. It may be worthwhile to estimate averages for different types or groups of sites. The average file size for My Sites, media repositories, and different department portals can vary significantly.
Estimate the number of list items in the environment. This value is known as L in the formula.
List items are more difficult to estimate than documents. We generally use an estimate of three times the number of documents (D), but this will vary based on how you expect to use your sites.
Determine the approximate number of versions. Estimate the average number of versions any document in a library will have. This value will usually be much lower than the maximum allowed number of versions. This value is known as V in the formula.
The value of V must be above zero.
As an example, use this formula and the characteristics in the following table to estimate the required storage space for data files in a content database for a collaboration environment. The result is that you need approximately 105 GB.

Input Value
Number of documents (D)
200,000
Calculated by assuming 10,000 users times 20 documents
Average size of documents (S)
250 KB
List items (L)
600,000
Number of non-current versions (V)
2
Assuming that the maximum versions allowed is 10
Database size = (((200,000 x 2)) × 250) + ((10 KB × (600,000 + (200,000 x 2))) = 110,000,000 KB or 105 GB
NoteNote:

Efficient File I/O in SharePoint Server 2013 is a storage method in which a file is split into pieces that are stored and updated separately. These pieces are streamed together when a user requests the file. This increases the I/O performance but it normally does not increase the file size. However, small files can see a small increase in the disk storage that is required.

--
MA ASALAAM
Passion 4 Oracle

SET TRANSACTION ISOLATION LEVEL

AS SALAMO ALAIKUM WA RAHMATULLAH

This statement is used to set the isolation level for either a connection or a stored procedure. The most typical use I’ve seen is at the top of a stored procedure in order to avoid locking and deadlocks. This is a cleaner alternative to using WITH (NOLOCK) hints on tables. If you set the isolation level within a procedure, the isolation level will revert to its previous level once the procedure finishes.

The syntax is:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
The available options are:

READ UNCOMMITTED – Allows dirty reads, meaning data can be read from a transaction that is not yet complete.
READ COMMITTED – Only reads data from transactions that have already been committed.  Current transactions must wait for transactions referencing common resources to finish.
REPEATABLE READ – Data that is being read is exclusively locked until the transaction completes.
SNAPSHOT – The default for Oracle.  This level allows all data to be read without using locks by maintaining a snapshot of all the data being modified in “versioning” tables.  This is the only isolation level not lock based.
SERIALIZABLE – Data that is being read is exclusive locked and inserts are prevented within this range until the transaction completes.


Snapshot isolation is not available unless enabled at the database level. It’s recommended to know all implications prior to enabling.

---
Ma Asalaam
Passion 4 Oracle

Multi Organization in Oracle Apps R12

As salamo alaikum wa rahmatullah

What is Multi-Org?
It’s a feature used to store the data of multiple organization in a single instance by partitioning the data of human resource, financials, sales, purchases, assets and materials
Examples of Multi Org are Tata Group, Reliance Group, Mahindra Group etc.
Let us consider Tata Group to illustrate more on Multi Org
In the above illustration Tata Finance, TCS, Tata Motors are individual organizations.
NOTE: The complete organization data are maintained at one place.
Financial statements are to be created for individual organization separately this is achieved using E BUZ Suite.
Multi-Org has been categorized into:
1.     Business Group
2.     Ledger
3.     Legal Entities
4.     Operating Unit
5.     Inventory Org
 1.       Business Group:
Business Group is a top level in the org structure. At this level we secure work structures (Job Structure, Position structure, Grades structure) and remuneration policy (Pay roll Policy).
Structure for Jobs, Position, Grades may differ from organization to organization, say position structure in a organization may have clerk, senior clerk, manager, senior manager. Where as in another organization it may only have clerks and managers.
Based on the Work Structure and remuneration the business group are created. If all the 3 organization (Tata Finance, TCS, and Tata Motors) have different Work Structure and remuneration then we need to define 3 business groups else if they have the same Work Structure and remuneration then only one business group is defined.
Module involved is HR
 2.       Ledgers:
Ledgers are used to secure journals and ledger balances of the company. A ledger is a collection of currency, calendar, chart of accounts and sub ledger accounting (SLA) methods.
Currency, here we setup the local currency for the organization where its business is defined say INR (INDIAN Rupee) this is used only to report the balances but the transaction can be done in other foreign currency also including INR.
 Calendar, the financial year is been defined to control the transactions and secure them. We divide the financial year into PERIODS, periods may be defined daily, Monthly, Quarterly this is only to restrict the transaction as transaction can happen only for the period that’s open, say in July we can have transactions for the month of July and August as its open but we can restrict for September by keeping it closed.
 Chart of Accounts, here we define the accounts setup for an organization. For better understanding let us take an example where in we need to create an account when a rent has been paid. The account entry would be
 Rent A/c
To Cash
 Now to differentiate this based on the organization say if the rent is for Tata Finance or TCS or Tata Motors. The entries can be
So for this when the transaction is recorded the entry in the system would be 01.1001
If there are any further division (category) then a new segment is created but if an organization is not having any division then a default segment is also defined.
Now the account for tata motors having division for cars in a particular location say hyd and for payables as an account would be 01.001.0001.1002, if tata finance has no division and location but with rent account then the entry would be 02.000.0000.1001
Note: An account that is defined for an organization is a combination of Company code and Account code, that are defined if there are no segments (Division, Location etc) been defined. We should have minimum two segments and a maximum of 30 segments when defining an account.
Sub Ledger Accounting: (SLA), how to maintain the account is been defined in SLA, the accounts may be accrual based or cash based.
Module involved is GL
3.       Legal Entities:
This is a legal business or a registered firm; at this level we prepare income tax reports.
In our example we would be having 3 legal entities (Tata Motors, TCS, and Tata Finance)
Legal Entries are used to report the Tax Reporting
4.    Operating Unit: (OU)
 An operating unit is division of legal entity, at this level we secure the sales and purchase transactions.
Now we also need to keep in mind that an OU can be set at the division level or location level or branches based on the sectors.
Here in the above illustration Cars and Trucks are division, Hyd, Pune and Bangalore are location.  OU can be set at Division level or Location level or Branch Level.
If the OU is set at branch 1 then branch 2 cannot see the details (Sales and purchases) of branch 1 and vice versa as they are secured. Similarly if set at the location then it is secured with that location and restricted from other locations.
Module involved is AR, AP, PO, OM, CM etc
5.       Inventory Org:
This is used to secure the materials of an organization.
Note: Legal Entity can also be an Operating Unit as there are no further division in a organization.

Ma Asalaam
--
Passion 4 Oracle

R12 - Explanation about the Segment Value

As salamo alaikum wa rahmatullah

For All segment - Allow Budgeting and Allow Posting will default to NO for parent accounts, or YES for posting accounts.
These can be changed to meet your company's needs with one Emitted restriction:Parent accounts should never ALLOW POSTING.
For the Natural Account, ACCOUNT TYPE is available and has two main purpose: to classify accounts as Assets, Liabilities, Owner Equity, Revenue, or Expense (Budgetary CR and DR are available for encumbrances), as well as to determine which accounts will roll into prior year Retained Earnings at the end of Fiscal Year.
Third Party Control Account will need to be CUSTOMER, SUPPLIER, or YES to use the Third Party Balance feature.
This feature will prevent manual transactions from being allowed to use this segment, and any combinations created with it, only allowing journal entries created in the sub ledgers. This will prevent manual miss-postings to the account and make reconciliations easier between the sub ledger and the General Ledger.
Selecting CUSTOMER restricts journal to sub ledgers where the applications is identified as CUSTOMER, and the same goes for SUPPLIERS.
Selecting either CUSTOMER or SUPPLIER will restrict posting to these accounts and prevent miss-posting. For example, a common posting error is to create a manual journal entry to the payable liability account int the general ledger,which throws this account out of balance with the sub ledger.
Setting up the payable liability account to Suppliers will restrict entries to the account to only entries from Payable.

RECONCILE needs to be set to YES to use the feature of Reconciling Accounts, usually for VAT transaction or clearing accounts.

--
Ma Asalaam
Passion 4 Oracle

R12 Accounting Flex Field

As salamo alaikum wa rahmatullah

Segment Label: Identifies certain segments in your chart of accounts and assigns special functionality to those segments. The required segment labels are:
Balancing Segment: Ensures that all journals balance for each balancing segment value or combination of multiple balancing segment values to use in financial processes and reporting. The three balancing segment labels are: Primary balancing segment, Second balancing segment, and Third balancing segment. The Primary balancing segment label is required and must be the first segment in the Rapid Implementation spreadsheet.
Natural Account: Facilitates processes in the General Ledger application, such as retained earnings posting. For each child value, you must assign an Account Type. You can select from one of the general choices to mark the account value as an Asset, Liability, Owner's Equity, Revenue, or Expense account.
If the account is used by the rapid implementation solution to provide accounts for setup objects, select the appropriate Expanded Account Type value for the child account. Examples of expanded account types required for setup objects are:
Owner's Equity - Retained Earnings: To set up General Ledger ledgers.
Liability - Accounts Payable: To set up Payables common options.
Asset - Accounts Receivable: To set up Receivables receipt methods.
Accounts tagged with expanded account types are automatically assigned a financial category. You can override the default category in the Financial Category field, or leave it out.

Cost Center: Facilitates grouping of natural accounts by functional cost types, accommodating tracking of specific business expenses across natural accounts.

--

Ma ASalaam

Passion 4 Oracle

Primary Ledger Vs Secondary Ledger Vs Reporting Currency

As salamo alaikum wa rahmatullah


Primary Ledger Vs Secondary Ledger
Use secondary ledgers for supplementary purposes, such as consolidation, statutory reporting, or adjustments for one or more legal entities within the same accounting setup. For example, use a primary ledger for corporate accounting purposes that use the corporate chart of accounts and subledger accounting method, and use a secondary ledger for statutory reporting purposes that use the statutory chart of accounts and subledger accounting method. This allows you to maintain both a corporate and statutory representation of the same legal entity's transactions in parallel. 

Reporting Currency Vs Secondary Ledger
Reporting Currencies are not the same as secondary ledgers. Looking at the 4 C's that define a ledger, we have a chart of accounts, calendar, accounting method, and currency. If you only need multiple currencies to support your reporting requirements, use reporting currencies. If you need to account for your data using different calendars, charts of accounts, accounting methods in addition to currency, use a secondary ledger.

Ma Asalaam
--
Passion 4 Oracle

What is the cause of high transaction log file in SQL SERVER

As Salamo Alaikum wa Rahmatullah

You probably either have a long running transaction running (Index maintenance?  Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Fulland have not taken a log backup (or aren't taking them frequently enough).
If it is a recovery model issue, the simple answer could be Switch to Simple recovery mode if you do not need point in time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay in Full recovery.
There could be other reasons but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.

--
Ma Asalaam
Passion 4 Oracle

BE AWARE FOLLOWING THING WHILE WRITING SQL QUERY

AS SALAMO ALAIKUM WA RAHMATULLAH

Queries

Writing efficient queries in SQL Server is more an exercise in writing elegant relational queries than in knowing specific tricks and syntax tips. Generally, a well-written, relationally correct query written against a well-designed relationally correct database model that uses the correct indexes produces a system that performs fairly well and that is scalable. The following guidelines may help you create efficient queries:

• Know the performance and scalability characteristics of queries.
• Write correctly formed queries.
• Return only the rows and columns needed.
• Avoid expensive operators such as NOT LIKE.
• Avoid explicit or implicit functions in WHERE clauses.
• Use locking and isolation level hints to minimize locking.
• Use stored procedures or parameterized queries.
• Minimize cursor use.
• Avoid long actions in triggers.
• Use temporary tables and table variables appropriately.
• Limit query and index hints use.
• Fully qualify database objects

--
MA ASALAAM
PASSION 4 ORACE

FULLBACK DIFFERENTIAL BACKUP AND TRANSACTION LOG BACKUP WITH EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH

USE  AdventureWorks2012

Go

Begin

Declare @Filename Varchar(200);

Declare @FullPath Varchar(300);

Declare @FinalPath Varchar(300);

Set @FullPath='D:\ DB_Backup_Daily\DifferentialBackup\';

Set @Filename=' AdventureWorks2012_'+Convert(varchar(10),Getdate(),120)+'.bak';

Set @FinalPath=@FullPath + @Filename

print @FinalPath

BACKUP DATABASE AdventureWorks2012 TO DISK = @FinalPath WITH DIFFERENTIAL

End

USE AdventureWorks2012

GO

Begin

Declare @Filename Varchar(200);

Declare @FullPath Varchar(300);

Declare @FinalPath Varchar(300);

Set @FullPath='D:\DB_Backup_Daily\FullBackup\';

Set @Filename=' AdventureWorks2012_'+Convert(varchar(10),Getdate(),120)+'.bak';

Set @FinalPath=@FullPath + @Filename

print @FinalPath

BACKUP DATABASE AdventureWorks2012 TO DISK = @FinalPath WITH INIT

End

USE FahaheelLiveStore

go

Begin

Declare @Filename Varchar(200);

Declare @FullPath Varchar(300);

Declare @FinalPath Varchar(300);

Set @FullPath='D:\DB_Backup_Daily\TransactionBackup\';

Set @Filename=' AdventureWorks2012_'+Convert(varchar(10),Getdate(),120)+'.log';

Set @FinalPath=@FullPath + @Filename

print @FinalPath

BACKUP LOG AdventureWorks2012 TO  DISK = @FinalPath WITH STATS =1

END

--
MA ASALAAM
PASSION 4 ORACLE

DBCC COMMAND WITH EXAMPLE

AS SALAMO ALAIKUM WA RAHMATULLAH
SQL Server – DBCC Commands
DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and trouble shooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server 2005  on wards, with examples.
The DBCC Commands broadly falls into four categories:
Ø  Maintenance
Ø  Informational
Ø  Validation
Ø  Miscellaneous
v  Maintenance Commands
Performs maintenance tasks on a database, index, or filegroup.
1. CLEANTABLE – Reclaims space from the dropped variable-length columns in tables or index views.
DBCC CLEANTABLE (‘AdventureWorks’,'Person.Contact’,0)
2. DBREINDEX – Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead)
USE AdventureWorks
DBCC DBREINDEX (‘Person.Contact’,'PK_Contact_ContactID’,80)
3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.
DBCC DROPCLEANBUFFERS
4. FREEPROCCACHE – Removes all elements from the procedure cache
DBCC FREEPROCCACHE
5. INDEXDEFRAG – Defragments indexes of the specified table or view.
DBCC INDEXDEFRAG (‘AdventureWorks’, ‘Person.Address’, PK_Address_AddressID)
6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database
DBCC SHRINKDATABASE (‘AdventureWorks‘, 10)
7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.
USE AdventureWorks;
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1)
8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.
DBCC UPDATEUSAGE (AdventureWorks)Informational Commands
v  Informational Commands
              Performs tasks that gather and display various types of information.
1. CONCURRENCYVIOLATION – is maintained for backward compatibility. It runs but returns no data.
DBCC CONCURRENCYVIOLATION
2. INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.
DBCC INPUTBUFFER (52)
3. OPENTRAN – Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database.
DBCC OPENTRAN;
4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.
DBCC OUTPUTBUFFER (52)
5. PROCCACHE – Displays information in a table format about the procedure cache.
DBCC PROCCACHE
6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table
USE AdventureWorks
DBCC SHOW_STATISTICS (‘Person.Address’, AK_Address_rowguid)
7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.
USE AdventureWorks
DBCC SHOWCONTIG (‘HumanResources.Employee’);
8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.
DBCC SQLPERF(LOGSPACE)
9. TRACESTATUS – Displays the status of trace flags.
DBCC TRACESTATUS(-1)
10. USEROPTIONS – Returns the SET options active (set) for the current connection.
DBCC USEROPTIONS
v  Validation Commands
Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKALLOC (AdventureWorks)
2. CHECKCATALOG – Checks for catalog consistency within the specified database.
DBCC CHECKCATALOG (AdventureWorks)
3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database.
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.
DBCC CHECKDB (AdventureWorks)
5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
USE AdventureWorks
DBCC CHECKFILEGROUP
6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.
USE AdventureWorks;
DBCC CHECKIDENT (‘HumanResources.Employee’)
7. CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
USE AdventureWorks;
DBCC CHECKTABLE (‘HumanResources.Employee’)
v  Miscellaneous Commands
Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.
DBCC xp_sample (FREE)
2. TRACEOFF – Disables the specified trace flags.
DBCC TRACEOFF (3205)
3. HELP – Returns syntax information for the specified DBCC command.
– List all the DBCC commands
DBCC HELP (‘?’)
– Show the Syntax for a given DBCC commnad
DBCC HELP (‘checkcatalog’)
4. TRACEON – Enables the specified trace flags.

DBCC TRACEON (3205)
--
MA ASALAAM
PASSION 4 ORACLE

WHAT IS DMF AND DMV IN SQL SERVER?

AS SALAMO ALAIKUM WA RAHMATULLAH

There were many new DMVs added in SQL Server 2012, and some that have changed since SQL Server 2008 R2. This is a brief overview of the biggest changes and what they mean to you.
Introduction
Dynamic management views (DMVs) are immensely useful tools that are provided with all editions of SQL Server since 2005. Using them, you can quickly find out information about many aspects of SQL Server, everything from index usage, query cache contents, server process data, wait stats, and much, much more.
With each new version of SQL Server comes many new DMVs as well as changes to existing ones. What follows are the highlights of DMV changes in SQL Server 2012.  In addition, I have provided a spreadsheet with a list of all changes that I could identify between SQL Server 2008 R2 and SQL Server 2012. If you are upgrading, this list could be very useful to ensure that data collectors, debugging stored procedures, or other TSQL that you have written are modified as necessary to keep working, and that new views can be utilized to fill any existing gaps in monitoring.
The Details A list of DMVs can be generated in any version of SQL Server using the following query:
SELECT *FROM sys.system_objects
WHERE name LIKE 'dm_%'
AND type = 'V'
ORDER BY name
With no further delay, here are some of the more significant DMV changes in SQL Server 2012:
sys.dm_server_services
This contains details on each service that is running on your SQL Server, including the startup type, status, process ID, service account, startup command, and some cluster details. Being able to quickly return this data using T-SQL can be a big time saver, and could allow you to do some rudimentary monitoring on the service, or verify that all of its settings are what they should be. Here is a quick select of the contents of this DMV on my local test server.


Note that this DMV was available in SQL Server 2008R2 starting in service pack 1.
sys.dm_os_windows_info
This straightforward one-line DMV contains some basic version info on the OS that is running your SQL Server:

Note that this DMV was also available in SQL Server 2008R2 starting in service pack 1.
sys.dm_db_log_space_usage
This is another super-simple view that provides the size of your transaction log and the space used:

sys.dm_server_registry
Quick access to registry settings for your SQL server can be found right here!  This DMV can be invaluable for troubleshooting a settings issue or confirming that SQL Server is configured correctly:

Note that this DMV was also available in SQL Server 2008R2 starting in service pack 1.
sys.dm_db_uncontained_entities
If an entity in a database references anything outside of the current database, it is considered an uncontained entity and will be reported in this DMV. This can be especially handy if you are renaming, moving, or otherwise making changes to an object that could be referenced by another database. This view can be used to check for these unenforced dependencies and prepare changes as needed. The example below creates an entity such as this as an example:
CREATE DATABASE uncontained_entity_test
GO
USE uncontained_entity_test
GO

CREATE PROCEDURE uncontained_entity_test_proc AS
       SELECT
              *
       FROM master.sys.dm_os_wait_stats;
GO

SELECT
       SYS_SCHEMAS.name AS schemaname,
       SYS_OBJECTS.name AS objectname,
       SYS_OBJECTS.type_desc,
       SYS_OBJECTS.create_date,
       SYS_OBJECTS.modify_date,
       ENTITIES.class_desc,
       ENTITIES.statement_line_number,
       ENTITIES.statement_type,
       ENTITIES.feature_name,
       ENTITIES.feature_type_name
FROM sys.dm_db_uncontained_entities ENTITIES
INNER JOIN sys.objects SYS_OBJECTS
ON SYS_OBJECTS.object_id = ENTITIES.major_id
INNER JOIN sys.schemas SYS_SCHEMAS
ON SYS_SCHEMAS.schema_id = SYS_OBJECTS.schema_id
In this SQL we create a simple stored procedure that selects data from the master database, which is outside of the scope of our current database.  The query returns basic info on the stored proc, its schema, and the referenced object:


In this example, we can see our proc (dbo.uncontained_entity_test_proc) under objectname and the external resource (dm_os_wait_stats) we are referencing under feature_name.  As an added bonus, statement_line_number tells you on what line of the proc our reference occurs, which could be very handy when dealing with a large proc.
sys.dm_tcp_listener_states
This new DMV provides data on which TCP ports are being used by the TCP Listener, and does so for T-SQL, the Service Broker, and for mirroring.  This can be very handy for troubleshooting connectivity problems or verifying that a server is set up with the correct port & IP address.  A look at my local (and somewhat boring) server:

Note that the “::” is used for an IPv6 wildcard.  A separate row is returned if a listener has both an IPv4 and an IPv6 address.
sys.dm_server_memory_dumps
Another simple view with troubleshooting in mind.  It will return basic file data for any dump files that have been generated by SQL Server:

The file can be a minidump, full dump, or an all-thread dump.
sys.dm_os_cluster_properties
This DMV provides one row of data about the SQL Server cluster settings.  If your instance is stand-alone and not on a failover cluster, then no rows are returned.  This view is focused squarely on troubleshooting, and contains info on dump files, failure conditions, logging and the health check timeout.
sys.dm_os_server_diagnostics_log_configurations
One row is returned by this DMV, which contains the basic configuration info for the SQL Server failover cluster diagnostic log:

If you use AlwaysOn Availability Groups, then there are a dozen new DMVs that provide a plethora of information on the feature.  Since these views may not apply to everyone, I will leave out the details, but suffice it to say that troubleshooting AlwaysOn issues will be much easier with these than without:
• sys.dm_hadr_auto_page_repair
• sys.dm_hadr_availability_group_states
• sys.dm_hadr_availability_replica_cluster_nodes
• sys.dm_hadr_availability_replica_cluster_states
• sys.dm_hadr_availability_replica_states
• sys.dm_hadr_cluster
• sys.dm_hadr_cluster_members
• sys.dm_hadr_cluster_networks
• sys.dm_hadr_database_replica_cluster_states
• sys.dm_hadr_database_replica_states
• sys.dm_hadr_instance_node_map
• sys.dm_hadr_name_id_map
Conclusion
The changes above outline only a fraction of the DMVs that have been added or altered in SQL Server 2012.  There is quite a bit of new functionality that is addressed in these new views.  Many existing views have had columns added, removed, or renamed.  In addition, other new views have been added to address new functionality in the Resource Governor, Full-Text Search, and other features in SQL Server.

For details of all major changes and additions, please see the attached document, which contains a list of DMVs in SQL Server 2008 R2 and SQL Server 2012.  A row exists for each instance of the DMV, one in 2008R2 and one in 2012, along with brief details on what was changed.

--
MA ASALAAM
PASSION 4 ORACLE

ANNOTATION USED IN C#

AS SALAMO ALAIKUM WA RAHMATULLAH

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace WebApplication2.Models
{
    public class Products
    {
        [Display(Name = "Product Id")]
        public Int64 ID { get; set; }

        [Display(Name = "Product code")]
        [Required(ErrorMessage = "Product code required")]
        [StringLength(10, MinimumLength = 6, ErrorMessage = "Minimum length of product is 6")]
        public string Pcode { get; set; }

        [Display(Name = "Product name")]
        [Required(ErrorMessage = "Product name required")]
        [StringLength(50, MinimumLength = 1, ErrorMessage = "Minimum length of product is 1")]
        public string Pname { get; set; }

        [Display(Name="Price")]
        public decimal price { get; set; }
    }

}

--
MA ASALAAM
PASSION 4 ORACLE

CALCULATE THE SECOND

AS  SALAMO ALAIKUM WA RAHMATULLAH

create table TBL_PRODUCTS
(
ID bigint identity(1,1) primary key,
PRODUCTCODE VARCHAR(20),
PRODUCTNAME VARCHAR(200),
PRICE NUMERIC(18,3))
ALTER TABLE TBL_PRODUCTS ADD TRANSTIME TIME
ALTER TABLE TBL_PRODUCTS ADD TRANSTIMES bigint
ALTER PROC PROC_INSERT_PRODUCT
(
@PRODUCTCODE VARCHAR(10),
@PRODUCTNAME VARCHAR(300),
@PRICE NUMERIC(10,3)
)
AS
BEGIN

INSERT INTO TBL_PRODUCTS(PRODUCTCODE,PRODUCTNAME,PRICE,TRANSDATE,TRANSTIME,TRANSTIMES)
VALUES(@PRODUCTCODE,@PRODUCTNAME,@PRICE,GETDATE(),GETDATE(),((DATEPART(MINUTE,GETDATE())*60) + (DATEPart(HOUR,GetDATE())*3600) + DATEPart(SECOND,GetDATE())));
RETURN 1

END

note - CALCULATION OF TIME DONE HERE UNDER
select ((DATEPART(MINUTE,GETDATE())*60) + (DATEPart(HOUR,GetDATE())*3600) + DATEPart(SECOND,GetDATE()))

--

MA ASALAAM
PASSION 4 ORACLE

Complex Queries in SQL ( Oracle )

As Salamo alaikum wa rahmatullah

These questions are the most frequently asked in interviews.

To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
Find the 3rd MAX salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
Find the 3rd MIN salary in the emp table.
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
Select FIRST n records from a table.
select * from emp where rownum <= &n;
Select LAST n records from a table
select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
List dept no., Dept name for all the departments in which there are no employees in the department.
select * from dept where deptno not in (select deptno from emp);  
alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
How to get 3 Min salaries ?
select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
How to get nth max salaries ?
select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
Select DISTINCT RECORDS from emp table.
select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
Count of number of employees in  department  wise.
select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
 Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;

Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

Select  all record  from emp where job not in SALESMAN  or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');

Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

Select all records where ename may be any no of  character but it should end with ‘R’.

select * from emp where ename like'%R';

Count  MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

In emp table add comm+sal as total sal  .

select ename,(sal+nvl(comm,0)) as totalsal from emp;

Select  any salary <3000 from emp table. 

select * from emp  where sal> any(select sal from emp where sal<3000);

Select  all salary <3000 from emp table. 

select * from emp  where sal> all(select sal from emp where sal<3000);

Select all the employee  group by deptno and sal in descending order.

select ename,deptno,sal from emp order by deptno,sal desc;

How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;

How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And  sal<2000

Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where emp.deptno=dept.deptno)

If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)

How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)

 How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)

Count the totalsa  deptno wise where more than 2 employees exist.
SELECT  deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2


--
MA ASLAAM
Passion 4 Oracle

CHECK DATABASE LINK IS UP OR NOT IN SQL SERVER

AS SALAMO ALAIKUM WA RAHMATULLAH

Here under is code to check database link is up or not.

BEGIN TRY
    exec sp_testlinkedserver DB_TV
IF @@ERROR<> 53 BEGIN
                EXEC Prints_WithLink @ReceiptNo,@Type
print 'database is not up'
END
    
END TRY
BEGIN CATCH
print 'database is up'         
EXEC Prints_WithoutLink @ReceiptNo,@Type

END CATCH

--
Ma Asalaam
Passion 4 Oracle

SHRINK DATABASE IN SQL SERVER

AS SALAMO ALAIKUM WA RAHMATULLAH,

Hereunder is sample code for truncating Database log file or Database data file.

USE AdventureWorks2012;  
GO  
-- Truncate the log by changing the database 

recovery model to SIMPLE.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY SIMPLE;  
GO  
-- Shrink the truncated log file to 1 MB.  
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);  
GO  
-- Reset the database recovery model.  
ALTER DATABASE AdventureWorks2012  
SET RECOVERY FULL;  

GO 

--

USE AdventureWorks2012;  
GO  
-- Create a data file and assume it contains 

data.  
ALTER DATABASE AdventureWorks2012   
ADD FILE (  
    NAME = Test1data,  
    FILENAME = 'C:\t1data.ndf',  
    SIZE = 5MB  
    );  
GO  
-- Empty the data file.  
DBCC SHRINKFILE (Test1data, EMPTYFILE);  
GO  
-- Remove the data file from the database.  
ALTER DATABASE AdventureWorks2012  
REMOVE FILE Test1data;  

GO 

--
Ma Asalaam
Passion 4 Oracle