Sunday, July 12, 2015

Difference between CTE and Temp Table and Table Variable - sql server

TEMP 
Temp Table or Table variable or CTE are commonly used for storing data temporarily in SQL Server. In this article, you will learn the differences among these three.

CTE
CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.
A sub query without CTE is given below :
SELECT * FROM (

 SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr

 Inner join Employee Emp on Emp.EID = Addr.EID) Temp

WHERE Temp.Age > 50

ORDER BY Temp.NAME


By using CTE above query can be re-written as follows 
;With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME
When to use CTE
This is used to store result of a complex sub query for further use.
This is also used to create a recursive query.
Temporary Tables
In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-
Local Temp Table
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.

The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from #LocalTemp

Global Temp Table
Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from ##GlobalTemp

temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.

Table Variable
This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.

Note
Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory

Remove duplicate records from table - sql server

CREATE TABLE dbo.Employee
(
   EmpID int IDENTITY(1,1) NOT NULL, Name varchar(55) NULL, Salary decimal(10, 2) NULL,      Designation varchar(20) NULL
)


WITH TempEmp
 (
    Name,duplicateCount) AS ( SELECT Name,ROW_NUMBER() OVER(PARTITION by Name,         Salary ORDER BY Name) AS duplicateCount FROM dbo.Employee
)

--PARTITION  will  gives you duplicate records count
 --Now Delete Duplicate Records
DELETE FROM TempEmp WHERE duplicateCount > 1

Thursday, March 5, 2015

Gmail : 5.5.0 Authentication Problem asp.net c#

Get to your Gmail account's security settings and set permissions for "Less secure apps" toEnabled. Worked for me.

1st login into your gmail account and then run below URL into browser

and select turn on option .

google.com/settings/security/lesssecureapps

Tuesday, February 10, 2015

HTML6 Features

The Spec That Brings Us Freedom

Section 1 - Introduction

HTML5 was a great leap forward for web developers. It gave us all kinds of hip new tags like <header>, <nav>and <footer>. It also gave us slick new JavaScript APIs like drag and drop, localStorage, and geolocation. Still, however, there is a void that HTML5 has yet to fill and that void is truly semantic markup.
Imagine being able to mark something up the way you want to mark it up. Imagine changing <div id="wrapper"> to <wrapper> or a better example, making a calendar like:
<calendar>
  <month name="January">
    <day>1</day>
    <day>2</day>
    <day>3</day>
    <!-- ...and so on -->
  </month>
</calendar>
Even better yet, how about finally adding support for new types of media by simply changing the media type rather than having to come up with whole new tags for it like <img>, <embed>, <video>, <audio>, and so on? For example, wouldn't it be nice to just simply do: <html:media src="my-audio-file.aac" type="aac"> and let the browser deal with how to render it?
The web is moving towards a giant app store and we need to embrace it. The markup we use shouldn't work against us, it should work for us. This spec is to do just that. To finally break free of fatuous rules and standards and to give us, developers, total freedom to code as we please bringing the web a more semantic, clean, and human readable markup.
Now, without further adieu, let me introduce you to HTML6.

Section 2 - The Concept

HTML6 is conceptually HTML with XML like namespaces. If you don't know XML, or don't know what XML namespaces are they're basically a way to allow you to use the same tag without it conflicting with a different tag. You've probably actually seen one before in the XHTML DOCTYPE:xmlns:xhtml="http://www.w3.org/1999/xhtml"
In HTML6 we take advantage of this ingenious concept by giving us freedom to use whatever tag we want by the W3C reserving namespaces and not tags. The W3C would basically reserve the right to all namespaces, and each namespace they reserve will trigger a different HTML API.
So, what does this look like? Below is an example of a full HTML6 document. We'll go over each tag and attributes in the API section.
<!DOCTYPE html>
<html:html>
    <html:head>
        <html:title>HTML6 Sample</html:title>
        <html:meta type="title" value="Page Title">
        <html:meta type="description" value="This is an example of HTML with namespaces">
        <html:link src="css/main.css" title="Main Styles" type="text/css">
        <html:link src="js/main.js" title="Main Script" type="text/javascript">
    </html:head>
    <html:body>
        <header>
            <logo>
                <html:media type="image" src="images/logo.png">
            </logo>
            <nav>
               <html:a href="/cats">Cats</a>
               <html:a href="/dogs">Dogs</a>
               <html:a href="/rain">Rain</a>
            </nav>
        </header>
        <content>
            <article>
                <h1>This is my main article head</h1>
                <h2>This is my sub head</h2>
                <p>[...]</p>
                <p>[...]</p>
            </article>
            <article>
                <h1>A cool video!</h1>
                <h2>Pay attetion to the media elements</h2>
                <p>[...]</p>
                <html:media type="video" src="vids/funny-cat.mp4" autostart controls>
                <p>Man, that was a stupid cat.</p>
            </article>
        </content>
        <footer>
            <copyright>This site is &copy; to Oscar Godson 2009</copyright>
        </footer>
    </html:body>
</html:html>
As you'll see, there are some weird <html:x> tags throughout this sample. Those are the namespaced elements that belong to the W3C and HTML6 spec. These elements trigger browser events. For example, the<html:media type="image"> element will make an image appear or, the <html:title> element makes the title bar of the browser change and so on.
All those other elements are just for you. None of those elements mean anything to the browser. They're simply hooks for CSS and JS and to make your code more semantic. The HTML elements you see in there like<p> or the <h1> tags are just because I like using those as ways to markup paragraphs or the most important header, but I could have used <paragraph>, <text>, or <helloworldanythingiwant>.
It's whatever makes sense to you and your application.

Section 3 - The APIs

Section 3A - HTML API

All of the following tags in this API have the namespace html like: <html:title>
<html:html>
This begins a HTML document. Equivelent to the current <html> tag.
Example:
<!DOCTYPE html>
<html:html>
  <!-- rest of HTML would go here -->
</html:html>
<html:head>
This begins an HTML's head. Equivelent to the current <html> tag. The tag contains data that isn't actually displayed (aside from the <html:title> which is displayed in the browser's windows). Rather, it's purpose is to get data and scripts that affect the display of the content in the <html:body>. These scripts and other sources include things like JavaScript, CSS, RSS feeds, etc.
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <!-- Head content here, like the <html:title> tag -->
  </html:head>
</html:html>
<html:title>
This is the title of the HTML document. Equivalent to the current <title> tag. Browsers will use this for the tab bar, favorites, etc. and search engines will use this as the title of their links
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
</html:html>
<html:meta>
This is a bit different then the current HTML version. Meta data in HTML6 can be anything. Unlike HTML now, there are no required or non-standard meta types. It's used to store content for you as a developer, or for other sites as a way to grab information such as a page description.
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
    <html:meta type="description" value="This is an example of HTML with namespaces">
  </html:head>
</html:html>
<html:link>
This links external documents and scripts such as CSS, JavaScript, RSS, favicons, etc. to the current document. Equivalent to the current <link> tag. This tag takes the following attributes:
  • charset: The character encoding such as "UTF-8".
  • href: The link to the source file.
  • media: The type of device the item should run on, for example, "mobile" or "tablet".
  • type: The MIME type of the document, for example, text/javascript.
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
    <html:link src="js/main.js" title="Main Script" type="text/javascript">
  </html:head>
</html:html>
<html:body>
This is the body of the HTML document. Equivalent to the current <body> tag. This is where you'd place most of the stuff that would be visible to the users like text, media, and so on.
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <!-- Your web page's content would go here -->
  </html:body>
</html:html>
<html:a>
This tag represents either an anchor on the page, or a link to another web page. Equivalent to the current <a>tag. The <html:a> tag takes one required attribute which is the href which directs the anchor or link where to go. For an anchor you'd use the syntax #id-of-element-to-link-to and for a link to another web page you'd simply insert the link like http://google.com.
Attributes available to the <a> tag are:
  • href
  • name
  • target (can be blank, parent, top or self)
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <html:a href="http://google.com">Go to google.com!</html:a>
  </html:body>
</html:html>
<html:button>
Similar to <button> or <input type="button"> in HTML<=5, the <html:button> tag allows you to create a button for user interaction on a page.
Attributes available to the <html:button> tag are:
  • disabled
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <html:button>Push me!</html:button>
  </html:body>
</html:html>
<html:media>
This tag encapsulates what we now have for media which are tags like <img>, <video>, <audio>, <embed>, and so on. Instead of a tag for each file type, the browser will just know how to run it by the type attribute, or will make a guess based on the file extension, or lastly, by the MIME type.
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <!-- Image -->
    <html:media src="images/logo.jpg" type="image">
    <!-- Video, shows you don't "need" a type -->
    <html:media src="videos/cute-cat.mov">
    <!-- Some made up format, browser will ignore if it doesn't know it -->
    <html:media src="misc/example.abc" type="abc">
  </html:body>
</html:html>

Section 3B - HTML Forms API

HTML Forms are separate from the HTML API to allow development on forms to not have to slow down for the entire HTML spec. Forms are constantly evolving with Sliders, color pickers, date and time pickers, progress bars and more. Forms really are sort of their own "thing" in HTML, so in HTML6 we've broken them into their own API.
<form:form>
This tag creates a new form. Has two attributes, method and action. As with current HTML forms, method can be POST or GET (they can be lowercase too) and will send the form with that as the HTTP header. More details on GET and POST can be found at W3.org. The action attribute tells the form where to send the data. By default the "method" is set to GET and the "action" is the current page.
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <form:form method="post" action="/sendmail">
      <!-- Form inputs and stuff go here -->
    </form:form>
  </html:body>
</html:html>
<form:input>
This tag creates a new form input. Any type of form input that you can enter text into would be an input. In HTML currently this includes everything from a plain old text input to a <textarea> and would also include HTML5 style for inputs like email and url. The full list of possible input types are:
  • text
  • email
  • url
  • tel
  • search
  • number
  • datetime
  • date
  • month
  • week
  • time
  • datetime-local
  • textarea
  • password
  • file - (multiple)
The possible attributes on an input are:
  • name
  • disabled
  • readonly
  • placeholder
  • autofocus
  • required
  • novalidate
The following are attributes that will work on any input except file inputs:
  • maxlength
  • autocomplete
  • pattern
  • spellcheck
  • match - This is new to HTML6, give it a name of a field you want it to require a match on.
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <form:form method="post" action="/sendmail">
      <!-- Simple input (defaults to text) -->
      <form:input>
      <!--  A new HTML6 match example -->
      <form:input type="password" name="user_password">
      <form:input type="password" match="user_password">
      <!-- Advanced example -->
      <form:input type="email" placeholder="user@site.com" autofocus required>
    </form:form>
  </html:body>
</html:html>
<form:select>
The <form:select> tag lets a user select from options rather than input anything. For example an HTML<=5<select> would be close to the same. Some others would be a calendar, color picker, and range because these are predefined values in which you choose from.
The possible input types follow along with attributes that are specific to it:
  • select - (multiple)
  • color
  • calendar - (range)
  • meter - (range, step)
Attributes that work for all select types are:
  • name
  • readonly
  • disabled
  • required
  • autofocus
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <form:form method="post" action="/scheduler">
      <!-- Normal select -->
      <html:select type="select" name="favorite_color">
      <!-- Calendar example -->
      <html:select type="calendar" name="the_calendar" range="10/10/10-10/10/11">
    </form:form>
  </html:body>
</html:html>
<form:status>
The <form:status> tag allows you to give feedback, or a "status" update to your users. Useful for an upload progress bar or steps in a multi-page form, for example. These are similar to the <progress> and <meter>elements in HTML5.
  • progress
  • meter
Attributes that work for all status types are:
  • min
  • max
  • value
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <form:form method="post" action="/upload">
      <!-- Example showing "steps" in a form -->
      <form:status type="meter" min="1" max="3" value="2">
      <message>You're currently on step 2 of 3</message>
      <!-- Example showing an upload progress bar -->
      <form:status type="progress" max="100" value="25">
    </form:form>
  </html:body>
</html:html>
<form:label>
The <form:label> tag allows you to label inputs for the user. It links text to an input and when click will focus on the connected input. It matches the label's for attribute to the id of any form element.
Attributes that work for the <form:label> tag are:
  • for
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <form:form method="post" action="/login">
      <form:label for="username">Username</form:label>
      <form:input id="username" name="username">
      <form:label for="password">Password</form:label>
      <form:input id="password" name="password" type="password">
    </form:form>
  </html:body>
</html:html>
<form:submit>
Just like <input type="submit"> in HTML<=5, <form:submit> will create a button which submits your form. If a submit button is present in a form, pressing enter while focused inside of a form will submit it.
Attributes that work for the <form:submit> tag are:
  • name
  • value
Example:
<!DOCTYPE html>
<html:html>
  <html:head>
    <html:title>HTML6 Spec Version 0.1</html:title>
  </html:head>
  <html:body>
    <form:form method="post" action="/login">
      <form:label>Login</form:label>
      <form:input name="username">
      <form:input name="password" type="password">
      <form:submit name="submit" value="submit">
    </form:form>
  </html:body>
</html:html>

Section 4 - Tag types

In HTML6, like in all previous HTML versions, there are two types of tags: single tags and double tags. Single tags can't have any text content, they only have attributes. This is an example of single tag (both elements are interpreted the same way):
<html:meta type="author" content="m93a">
<html:meta type="author" content="m93a" />
Unlike the double tag, the single tag doesn't need to be closed. Double tags usually have some text content so they are made of an opening and closing tag. If it has no text content, it can be shortened to the self-closing single variant. Examples:
<html:link href="./a.html">Text content</html:link>

<!-- This shortand... -->
<foo class="bar" />
<!-- ...means in fact this: -->
<foo class="bar"></foo>

Section 5 - Using HTML6 Now

Unfortunately you can't but I'm hard at work on a polyfill that will transform your HTML6 document into a normal HTML document with JS. There will be a front-end one (which I wouldn't use in production due to the processing time and because search engines won't understand what the document is) and a Node.js one which will transform it and give it to the browser as if it were HTML.
If you'd like it in another language submit it to the issue tracker or send a pull request.

Section 6 - Conclusion

This is simply an idea. It's an idea I've personally had for years, but it's in no way finished. There's still a lot missing and a lot I haven't yet thought about, but it's a start. I'd love to hear your thoughts in the issue trackeror better yet, send a pull request of what you think should be changed or added.

For more info refere below link
http://html6spec.com

Opps Part 1 : Abstraction

  Abstraction in C# is a fundamental concept of object-oriented programming (OOP) that allows developers t...