Thursday 17 November 2016

what are CTE? When and Why To Use CTE in SQL ?


What are CTE in SQL ?

CTE are Common Table Expressions first introduced in Sql Server 2005. It is basically a result set or result holder or separate plugin / extension that can hold some complex query separately from main query and supply the named result set or result holder name in place of that complex query in main query.

It really improves complexity of complex queries. It is specially used in queries that contains complex sub-queries in them. Using them makes our query more concise, improves readability of query and makes it easy for maintainable for long time for future use.

CTE always start with Semicolon preceeded by 'with' keyword and CTE are temporary result holders and are removed form memory after query executed its life is only during query is executing.

When To Use CTE 

CTE is helpful to be used in Below two Conditions -


  1.  In order to reduce complexity of query with complex subqueries that will make code easy to read and maintain.
  2. Very helpful to create Recursive query if required in some cases.


you can read more about recursive queries with CTE on link below -
Read More About How To Use Recursive Query With CTE

Why We use ; ( Semicolon ) in start of CTE

It is actually to ensure previous statement before with / CTE are terminated. Most of times
we use semicolon before with keyword in CTE as we don't know what is before CTE.

Example - Without semicolon may create problem in below code in some StoredProcedure

DECLARE @foo int
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Above query will create error in Stored Procedure as DECLARE @foo int is not ended with
semicolon and CTE's with keyword is not ended with ; ( Semicolon ). Better way will  to write this query in stored procedure or anywhere is below -


DECLARE @foo int
;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate from Orders;
)

Now in this case semicolon before WITH Keyword will terminate previous statement first and then execute CTE

Table Valued Functions in Sql


What are Table Valued Functions in Sql ?


There are two types of “User Defined” fucntions in sql server
  1. scalar function ( that returns single value in response to function call )
     2. inline table valued ( that returns function result in tabulated form but query can single lined e.g.

     3. return (select * from table_name).

1. Inline Table Valued Functions
If function is made to execute a select query with or without parameters and return result as table type it comes in category of Inline table valued functions.
Sql Code Example -


CREATE FUNCTION GetAuthorsByState( @state char(2) )
RETURNS table AS
RETURN (SELECT au_fname, au_lname FROM Authors WHERE state=@state)
GO


2. Multi-statement table valued Functions

Multi-statement table valued that returns function result in tabulated form but query may have multiple statements that can make changes to query and return the tabulated data.

If functions is made to execute multiple sql statements including insert, update statements etc and return result as table type then it comes in category of Multistatement table valued functions

Example

CREATE FUNCTION GetAuthorsByState( @state char(2) )
RETURNS
@AuthorsByState table (
au_id Varchar(11),
au_fname Varchar(20)
)
AS
BEGIN
INSERT INTO @AuthorsByState
SELECT au_id,au_fname FROM Authors WHERE state = @state
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO @AuthorsByState VALUES ('','No Authors Found')
END
RETURN
END
GO

Wednesday 16 November 2016

Google Joined .NET Foundation



Its great news by microsoft that google joined .net foundation. Recently Samsung also joined .Net foundation for Tizen platform. Apart from everything its great that all tech companies are working collectively to make good environment for innovation.

This Move of Big companies also gives inspiration to many developers working in different technologies to come and work together in a team. They have shown an example of working in a team that will empower and motivate many others in future and present.



Google is now a member of the .NET Foundation, where it joins the likes of Red Hat, Unity, Samsung  Jet-brains and (of course) Microsoft in the Technical Steering Group.

Google already allows developers on its Cloud Platform to deploy .NET applications

Samsung, too, is deepening its commitment to .NET by launching support for it on its Tizen platform. As Samsung’s Hong-Seok Kim told me, Samsung was looking for a framework in addition to the web framework and C API that Tizen developers currently use to write their applications.

SQL Server Public Preview available on Ubuntu





Microsoft has just announced its public preview of the next release of SQL Server, and Canonical is delighted to announce that this preview is available for Ubuntu.

Moreover, with SQL Server on Ubuntu, there are significant cost savings, performance improvements, and the ability to scale & deploy additional storage and compute resources easier without adding more hardware.

Read Complete Details Here -

Full Article details Sql Server Public Preview Available on Ubuntu

Download Sql Server on Linux 


Visual studio for mac is released



Very Nice Move from microsoft and cheers for Mac Users. Microsoft launched Visual studio preview for Mac .you can enjoy using dotnet development on mac platform now. Microsoft released Visual Studio for Mac it is currently in preview and still work is in progress for several tools. check download link below -

Tuesday 8 November 2016

No assembly found containing an OwinStartupAttribute



The Common scenario that one of student got was during creating signalr project from empty web project. so i would like to spread it online with others. you may have see Error Screen as shown in below Screenshots -

Error : No assembly found containing an OwinStartupAttribute.
           No assembly found containing a Startup or [AssemblyName].Starup class.

         
No Assembly found containing an OwinStartupAttribute Error


Solution For error : Simple straightforward solution is to Add Below code ( as shown in below screenshot ) in web.config under <configuration> tag as shown in image below. you error will be
gone

line of Code to Add is written below and also shown written in Screenshot below

<appSettings>
    <add key="owin:AutomaticAppStartup" value="false" />
  </appSettings>


₹500 and ₹100 Notes Banned In India : 25 Points You need to know


NDA Government has implemented smart move as slap to Black Money holders. Money of Black Money Holders will be of no cost if they kept it in their safe lockers. GPS chip based notes will be out in market soon and old notes must need to be deposited in banks as announced by Government of India. Black money holders must need to take their money out in banks otherwise they will be of now use. 

A Smart move By Government of India to out take thousands of crores of money from Black Money Holders in banks and charge them Taxes.


1) Why is this scheme?
The incidence of fake Indian currency notes in higher denomination has increased. For ordinary persons, the fake notes look similar to genuine notes, even though no security feature has been copied. The fake notes are used for antinational and illegal activities. High denomination notes have been misused by terrorists and for hoarding black money. India remains a cash based economy hence the circulation of Fake Indian Currency Notes continues to be a menace. In order to contain the rising incidence of fake notes and black money, the scheme to withdraw has been introduced.

2) What is this scheme?
The legal tender character of the notes in denominations of ₹ 500 and ₹ 1000 stands withdrawn. In consequence thereof withdrawn old high denomination (OHD) notes cannot be used for transacting business and/or store of value for future usage. The OHD notes can be exchanged for value at any of the 19 offices of the Reserve Bank of India or at any of the bank branches or at any Head Post Office or Sub-Post Office.
3) How much value will I get?
You will get value for the entire volume of notes tendered at the bank branches / RBI offices.
4) Can I get all in cash?
No. You will get upto ₹4000 per person in cash irrespective of the size of tender and anything over and above that will be receivable by way of credit to bank account.
5) Why I cannot get the entire amount in cash when I have surrendered everything in cash?
The Scheme of withdrawal of old high denomination(OHD) notes does not provide for it, given its objectives.
6) ₹4000 cash is insufficient for my need. What to do?
You can use balances in bank accounts to pay for other requirements by cheque or through electronic means of payments such as Internet banking, mobile wallets, IMPS, credit/debit cards etc.
7) What if I don’t have any bank account?
You can always open a bank account by approaching a bank branch with necessary documents required for fulfilling the KYC requirements.
8) What if, if I have only JDY account?
A JDY account holder can avail the exchange facility subject to the caps and other laid down limits in accord with norms and procedures.
9) Where can I go to exchange the notes?
The exchange facility is available at all Issue Offices of RBI and branches of commercial banks/RRBS/UCBs/State Co-op banks or at any Head Post Office or Sub-Post Office.
10) Need I go to my bank branch only?
For exchange upto 4000 in cash you may go to any bank branch with valid identity proof. For exchange over 4000, which will be accorded through credit to Bank account only, you may go to the branch where you have an account or to any other branch of the same bank.
In case you want to go to a branch of any other bank where you are not maintaining an account, you will have to furnish valid identity proof and bank account details required for electronic fund transfer to your account.
11) Can I go to any branch of my bank?
Yes you can go to any branch of your bank.
12) Can I go to any branch of any other bank?
Yes, you can go to any branch of any other bank. In that case you have to furnish valid identity proof for exchange in cash; both valid identity proof and bank account details will be required for electronic fund transfer in case the amount to be exchanged exceeds ₹4000.
13) I have no account but my relative / friend has an account, can I get my notes exchanged into that account?
Yes, you can do that if the account holder relative/friend etc gives you permission in writing. While exchanging, you should provide to the bank, evidence of permission given by the account holder and your valid identity proof.
14) Should I go to bank personally or can I send the notes through my representative?
Personal visit to the branch is preferable. In case it is not possible for you to visit the branch you may send your representative with an express mandate i.e. a written authorisation. The representative should produce authority letter and his / her valid identity proof while tendering the notes.
15) Can I withdraw from ATM?
It may take a while for the banks to recalibrate their ATMs. Once the ATMs are functional, you can withdraw from ATMs upto a maximum of ₹2,000/- per card per day upto 18th November, 2016. The limit will be raised to ₹4000/- per day per card from 19th November 2016 onwards.
16) Can I withdraw cash against cheque?
Yes, you can withdraw cash against withdrawal slip or cheque subject to ceiling of ₹10,000/- in a day within an overall limit of ₹20,000/- in a week (including withdrawals from ATMs) for the first fortnight i.e. upto 24th November 2016.
17) Can I deposit withdrawn notes through ATMs, Cash Deposit Machine or cash Recycler?
Yes, OHD notes can be deposited in Cash Deposits machines / Cash Recyclers.
18) Can I make use of electronic (NEFT/RTGS /IMPS/ Internet Banking / Mobile banking etc.) mode?
You can use NEFT/RTGS/IMPS/Internet Banking/Mobile Banking or any other electronic/ non-cash mode of payment.
19) How much time do I have to exchange the notes?
The scheme closes on 30th December 2016. The OHD banknotes can be exchanged at branches of commercial banks, Regional Rural Banks, Urban Cooperative banks, State Cooperative Banks and RBI till 30th December 2016.
For those who are unable to exchange their Old High Denomination Banknotes on or before December 30, 2016, an opportunity will be given to them to do so at specified offices of the RBI, along with necessary documentation as may be specified by the Reserve Bank of India.
20) I am right now not in India, what should I do?
If you have OHD banknotes in India, you may authorise in writing enabling another person in India to deposit the notes into your bank account. The person so authorised has to come to the bank branch with the OHD banknotes, the authority letter given by you and a valid identity proof (Valid Identity proof is any of the following: Aadhaar Card, Driving License, Voter ID Card, Pass Port, NREGA Card, PAN Card, Identity Card Issued by Government Department, Public Sector Unit to its Staff)
21) I am an NRI and hold NRO account, can the exchange value be deposited in my account?
Yes, you can deposit the OHD banknotes to your NRO account.
22) I am a foreign tourist, I have these notes. What should I do?
You can purchase foreign exchange equivalent to ₹5000 using these OHD notes at airport exchange counters within 72 hours after the notification, provided you present proof of purchasing the OHD notes.
23) I have emergency needs of cash (hospitalisation, travel, life saving medicines) then what I should do?
You can use the OHD notes for paying for your hospitalisation charges at government hospitals, for purchasing bus tickets at government bus stands for travel by state government or state PSU buses, train tickets at railway stations, and air tickets at airports, within 72 hours after the notification.
24) What is proof of identity?
Valid Identity proof is any of the following: Aadhaar Card, Driving License, Voter ID Card, Pass Port, NREGA Card, PAN Card, Identity Card Issued by Government Department, Public Sector Unit to its Staff.
25) Where can I get more information on this scheme?
Further information is available at our website (www.rbi.org.in) and GoI website (www.rbi.org.in).

Tuesday 1 November 2016

What are Instead of Triggers


Instead of triggers are used to reverse the default action of the query same as the word ‘insted of’ denotes. For example if we are going to execute query to delete record in Empoyee table and we want to first check whether id of employee passed Exist in database or not then we can place a “Insted of Trigger” in database on Delete Operation for that particular table and we will place code in trigger to first check whether employee with specified id exist in db . IF yes then delete that employe , otherwise abort the query execution.

In general language we use instead of triggers when we need to code - instead of doing xyz do abc . means we need to trigger the selection of operations in db depending on Values in database tables or other certain criteria.

INSTEAD OF TRIGGERS can be classified further into three types as:


  1. INSTEAD OF INSERT Trigger.
  2. INSTEAD OF UPDATE Trigger.
  3. INSTEAD OF DELETE Trigger.

EXAMPLE
Below trigger will prevent the deletion of records from the table where Emp_Sal > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed.


CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test] 
INSTEAD OF DELETE
AS
 declare @emp_id int;
 declare @emp_name varchar(100);
 declare @emp_sal int;
 
 select @emp_id=d.Emp_ID from deleted d;
 select @emp_name=d.Emp_Name from deleted d;
 select @emp_sal=d.Emp_Sal from deleted d;

 BEGIN
  if(@emp_sal>1200)
  begin
   RAISERROR('Cannot delete where salary > 1200',16,1);
   ROLLBACK;
  end
  else
  begin
   delete from Employee_Test where Emp_ID=@emp_id;
   COMMIT;
   insert into Employee_Test_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
   values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());
   PRINT 'Record Deleted -- Instead Of Delete Trigger.'
  end
 END
GO