Common Table Expressions (CTE) in SQL

A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE can only be referenced by the statement that immediately follows it. A CTE allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

A CTE can also be thought of a disposable view. No indexes or additional statistics are stored for a CTE, and it functions as a shorthand for a sub-query.

A CTE is not stored as an object and lasts only for the duration of the query. So, it is similar to a derived table. However, unlike a derived table, a CTE can be self-referencing (recursive CTE) and referenced multiple times in the same query.

A CTE can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

When could a Common Table Expression be used?

A CTE could be used to fulfill the following requirements:

• To create a recursive query.
• When the general use of a view is not required, a CTE can be substituted for a view; that is, you do not have to store the definition in metadata.
• A CTE can be used to enable grouping by a column that is derived from a scalar sub-select, or a function that is either not deterministic or has external access.
• A CTE can be used to reference the resulting table multiple times in the same statement.

Structure of a CTE

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

SELECT <column_list>

FROM expression_name;

Example

;WITH WebsiteDetails AS -- notice the semicolon here

(

SELECT ROW_NUMBER() OVER (ORDER BY in_website_id) AS [No.],

in_website_id, vc_website_domain_name

FROM dbo.website

WHERE bt_active = 1

)

SELECT * FROM WebsiteDetails

Note: The semicolon before WITH is used basically to terminate previous SQL statements to avoid errors.

SELECT statement – Order of Execution of SQL Clauses

SELECT statements retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables. They do not modify data in any way and are simply a method of looking at the data stored in a single table or in many related tables.

SELECT statements do not generate a transaction in Microsoft SQL Server either.

Syntax of the SELECT statement

SELECT [ ALL | DISTINCT ]

[TOP ( expression ) [PERCENT] [ WITH TIES ] ]

< select_list >

[ INTO new_table ]

[FROM {table_name | view_name}[(optimizer_hints)]

[[, {table_name2 | view_name2}[(optimizer_hints)]

[..., {table_name16 | view_name16}[(optimizer_hints)]]]

[WHERE <search_condition>]

[GROUP BY clause]

[HAVING <search_condition>]

[ORDER BY clause]

[COMPUTE clause]

[FOR BROWSE]

Logical Processing Order of the SELECT statement

The order execution of the clauses in the SELECT statement is significant. Given below is the order in which SQL clauses get executed:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Differentiate between local and global temporary tables in SQL

In SQL Server, temporary tables can be created in runtime and can do all sorts of things which can be achieved by a normal table. However, since these are temporary tables, their scope is limited. There are two types of temporary tables:

• Local temporary tables
• Global temporary tables

Both of these temporary tables are physical tables which are created within the Temporary Tables folder in tempdb database. Temporary tables are automatically dropped when they go out of scope, unless they are explicitly dropped using DROP TABLE.

• Local temporary tables are visible only in the current session, i.e; are only available to the current connection for the user.
• Local temporary tables are deleted or destroyed after the user disconnects from the instance of SQL Server.
• Local temporary tables are are automatically destroyed at the termination of the stored procedure or session that created them.
• Local temporary table name is prefixed with hash ("#") sign.
• Global temporary tables are visible to all sessions and all users.
• Global temporary tables are deleted or dropped when all users referencing the table disconnect from the instance of SQL Server.
• Global temporary tables are dropped automatically when the last session using the temporary table has completed.
• Global temporary table name is prefixed with a double hash ("##") sign.

Table creation statements

• Table variables (DECLARE @tmp_surgeon TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
• Local temporary tables (CREATE TABLE #tmp_surgeon) are visible only to the connection that creates it, and are deleted when the connection is closed.
• Global temporary tables (CREATE TABLE ##tmp_surgeon) are visible to everyone, and are deleted when all connections that have referenced them have closed.
• Tempdb permanent tables (USE tempdb CREATE TABLE tmp_surgeon) are visible to everyone, and are deleted when the server is restarted.

Local temporary table – Usage

CREATE TABLE #tmp_local (in_user_id int, vc_username varchar(50), vc_address varchar(150))

INSERT INTO #tmp_local VALUES (1, 'Rohan', 'India');

SELECT * FROM #tmp_local

After execution of all these statements, if you close the query window and again execute "INSERT" or "SELECT" Command, it will throw the following error:

Msg 208, Level 16, State 0, Line 1

Invalid object name '#tmp_local'.

Global temporary table – Usage

CREATE TABLE ##tmp_global (in_user_id int, vc_username varchar(50), vc_address varchar(150))

INSERT INTO ##tmp_global VALUES (1, 'Rohan', 'India');

SELECT * FROM ##tmp_global

Global temporary tables are visible to all SQL Server connections. Once you create a global temporary table, it becomes visible to all the users.

Count the number of occurrence of a character in a given string – SQL

Count the number of occurrence of a 'o' in the given string: Ram is a good boy.

DECLARE @vc_string varchar(40)

SET @vc_string = 'Ram is a good boy.'

SELECT LEN(@vc_string) - LEN(REPLACE(@vc_string, 'o', '')) AS in_count

Output: 3

Differentiate between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT in SQL

Identity columns, also known as AUTO INCREMENT column, is the column whose value is auto incremented by SQL Server on each insert. Data insertion in an IDENTITY column is automatic (not manual - we do not insert any data manually in the identity column, the data is inserted for that IDENTITY column automatically by SQL Server, depending upon the Identity Increment value).

When we need to retrieve the latest inserted row information through SQL Query, we can use SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY functions. All three functions return the last-generated identity values, however, these functions differ on the scope and session on which last is defined.

• @@IDENTITY – It returns the last identity value generated for any table in the current session, across all scopes. @@IDENTITY is not limited to a specific scope.
  Ex: Suppose we create an insert trigger on table which inserts a row in another table which generates an identity column. Then @@IDENTITY returns that identity record which is created by trigger.
• SCOPE_IDENTITY – It returns the last identity value generated for any table in the current session and the current scope.
  Ex: Suppose we create an insert trigger on table which inserts a row in another table which generates an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.
• IDENT_CURRENT – It returns the last identity value generated for a specific table in any session and any scope. It is not affected by scope and session and only depends on a particular table. IDENT_CURRENT returns the identity value for a specific table in any session or any scope.

Example: Lets assume that there are two tables, X and Y and both have identity columns. Define an INSERT trigger on X. When a row is inserted to X, the trigger fires and inserts a new row in Y. We can identify two scopes here:

1. INSERT on X
2. INSERT on Y by the trigger

@@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on X. @@IDENTITY will return the last identity column value inserted across any scope in the current session. So, this is the value inserted in Y.

SCOPE_IDENTITY() will return the IDENTITY value inserted in X. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Note:

1. Failed statements and transactions can change the current identity for a table and create gaps in the identity column values.
2. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Example:

CREATE TABLE X(id int IDENTITY);

CREATE TABLE Y(id int IDENTITY(100,1));

GO

CREATE TRIGGER XIns ON X FOR INSERT

AS

BEGIN

INSERT Y DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT id FROM X;

--id is empty

SELECT id FROM Y;

--ID is empty

--Do the following in Session 1

INSERT X DEFAULT VALUES;

SELECT @@IDENTITY;

/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();

/* Returns the value 1. This was inserted by the

INSERT statement - INSERT X DEFAULT VALUES;*/

SELECT IDENT_CURRENT('Y');

/* Returns value inserted into Y, that is in the trigger.*/

SELECT IDENT_CURRENT('X');

/* Returns value inserted into X.

This was the INSERT statement - INSERT X DEFAULT VALUES;*/

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action

up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action

up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Y');

/* Returns the last value inserted into Y.*/

What is AJAX?

AJAX is an acronym for Asynchronous JavaScript and XML. AJAX is a new technique for creating better, faster, and more interactive web applications with the help of XML, HTML, CSS and Java Script.

What does Asynchronous mean?

Asynchronous refers to events that happen in the background independent of the main application flow. These events do not disturb the flow of the application, allowing the continuation of it’s normal process.

A fairly good example of this happening is in your Facebook home page, when all of a sudden, without refreshing your browser window, you notice that there are new status feed updates from your friends. What happens there is, facebook sends your profile information (or your user id) to their servers. Their servers then look for your friends list, grab their newly added status, return the result to the browser and then add them to your wall so that you can see. All of that, without pressing that refresh button.

So AJAX allows you to update a web page asynchronously on the background by exchanging simple, and small amounts of data. Some more examples of pages using AJAX is: Youtube, Gmail, Google Maps, StackOverflow and many more on the web.

More Details about AJAX:

• Ajax uses XHTML for content and CSS for presentation, as well as the Document Object Model and JavaScript for dynamic content display.
• Conventional web application transmit information to and from the sever using synchronous requests. However, with AJAX when submit is pressed, JavaScript makes a request to the server, interprets the results and updates the current screen. In the purest sense, the user would never know that anything was even transmitted to the server (asynchronous request).
• XML is commonly used as the format for receiving server data, although any format, including plain text, can be used.
• AJAX is a web browser technology independent of web server software.
• A user can continue to use the application while the client program requests information from the server in the background.
• Intuitive and natural user interaction. No clicking required only Mouse movement is a sufficient event trigger.
• AJAX is data-driven as opposed to page-driven.
• AJAX, is based on internet standards. It uses a combination of the following to accomplish it’s goal:
  • XMLHttpRequest Object (Modern Broswers and IE7+)
  • ActiveXObject (IE6 and below)
  • JavaScript/DOM (Used to interact browser and server)
  • XML (Returned results)
  • JSON (Returned results)
  • HTML (Returned results)

These standards are browser based, making them platform independent.

• Another advantage using AJAX is a better user interactivity. AJAX simplifies the flow of an application, making it have quicker interaction between user and website since pages are not reloaded for content to be displayed.

Disadvantages of AJAX

• Even though the back and refresh button are not needed while navigating a website with AJAX, these two buttons can become useless. This is due to the fact that, AJAX ‘navigating’ does not change you URL, so if you were in the middle of a process, and have no direct URL to where you were, then this might be bad. In some cases, the use of Hijaxing is used, which is the use of hashing url (#) at the end.
• Another disadvantage would be that it is dependent on JavaScript. While it is OK to depend on it, since most modern (if not all) already use it, but in some cases, there are users who prefer disabling JavaScript. This makes AJAX worthless.
• The last disadvantage I want to point out would be the SEO factor. Since there are no SEO Friendly URL’s, then search engine tend to bypass your application, and it would appear as if that part of your site does not exist.

Differentiate between $() vs. querySelectorAll() in jQuery

The $() function is similar to the Document method querySelectorAll(): both take a CSS selector as their argument and return an array-like object that holds the elements that match the selector.

The jQuery implementation uses querySelectorAll() in browsers that support it, but there are good reasons to use $() instead of querySelectorAll() in your own code:

• querySelectorAll() has only recently been implemented by browser vendors, whereas $() works in older browsers as well as new ones.
• Because jQuery can perform selections “by hand”, the CSS3 selectors supported by $() work in all browsers, not just those browsers that support CSS3.
• The array-like object returned by $() (a jQuery object) is much more useful than the array-like object (a NodeList) returned by querySelectorAll().

Callback and Functions in jQuery

Unlike many other programming languages, JavaScript enables you to freely pass functions around to be executed at a later time. A callback is a function that is passed as an argument to another function and is executed after its parent function has completed.

Callbacks are special because they patiently wait to execute until their parent finishes. Meanwhile, the browser can be executing other functions or doing all sorts of other work.

Callbacks without Arguments

If a callback has no arguments, you can pass it as given below:

$.get( "myhtmlpage.html", myCallBack );

When $.get() finishes getting the page myhtmlpage.html, it executes the myCallBack() function.

Note: The second parameter the function name (but not as a string, and without parentheses).

Callback with Arguments

Executing callbacks with arguments can be slightly tricky as shown below:

WRONG Code

$.get( "myhtmlpage.html", myCallBack( param1, param2 ) );

This code will not work because the code executes myCallBack( param1, param2 ) immediately and then passes myCallBack()'s return value as the second parameter to $.get().

Our intention is to pass the function myCallBack(), not myCallBack( param1, param2 )'s return value (which might or might not be a function).

RIGHT Code

An anonymous function can be used as a wrapper to defer executing myCallBack() with its parameters.

Notice the use of function() {.

The anonymous function does exactly one thing: calls myCallBack(), with the values of param1 and param2.

$.get( "myhtmlpage.html", function() {
    myCallBack( param1, param2 );
});

When $.get() finishes getting the page myhtmlpage.html, it executes the anonymous function, which executes myCallBack( param1, param2 ).

Basics of jQuery

Create an HTML page:

<!doctype html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Demo</title>
</head>
<body>
    <a href="http://dotnetinterviewcracker.blogspot.com/">Dot Net Interview Cracker</a>
    <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script src="http://code.jquery.com/jquery-migrate-1.2.1.min.js"></script>
    <script>
 
    $( document ).ready(function() {
        $( "a" ).click(function( event ) {
            alert( "Thanks for visiting!" );
        });
    });
 
    </script>
</body>
</html>

The src attribute in the <script> element must point to a copy of jQuery. You can download a copy of jQuery from the Downloading jQuery page and store the jquery.js file in the same directory as your HTML file.

OR

You can use jQuery's CDN from MediaTemple:

<script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="http://code.jquery.com/jquery-migrate-1.2.1.min.js"></script>

Launching Code on Document Ready

To run code as soon as the document is ready to be manipulated, jQuery has a statement known as the ready event:

$( document ).ready(function() {
    // Your code here.
}); 

For example, inside the ready event, you can add a click handler to the link: 

$( document ).ready(function() {
    $( "a" ).click(function( event ) {
        alert( "Thanks for visiting!" );
    });
});

Save your HTML file and reload the test page in your browser. Clicking the link should now first display an alert pop-up, then continue with the default behavior of navigating to http://dotnetinterviewcracker.blogspot.com.
For click and most other events, the default behavior can be prevented by calling event.preventDefault() in the event handler:
 

$( document ).ready(function() {
    $( "a" ).click(function( event ) {
        alert( "As you can see, the link no longer took you to dotnetinterviewcracker.blogspot.com" );
        event.preventDefault();
    });
});

Complete Example:

The following example illustrates the click handling code discussed above, embedded directly in the HTML <body>.

Note: It is usually better approach to place your code in a separate JS file and load it on the page with a <script> element's src attribute.

<!doctype html>
<html>
<head>
    <meta charset="utf-8" />
    <title>Demo</title>
</head>
<body>
    <a href="http://dotnetinterviewcracker.blogspot.com/">Dot Net Interview Cracker</a>
    <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script src="http://code.jquery.com/jquery-migrate-1.2.1.min.js"></script>
    <script>
 
    $( document ).ready(function() {
        $( "a" ).click(function( event ) {
            alert( "As you can see, the link no longer took you to dotnetinterviewcracker.blogspot.com" );
            event.preventDefault();
        });
    });
 
    </script>
</body>
</html> 

Adding and Removing an HTML class
 

Note: You must place the remaining jQuery examples inside the ready event so that your code executes when the document is ready to be worked on.
 

Step 1: Add some style information into the <head> of the document:
 

<style>
    a.test {
        font-weight: bold;
    }
</style>

Step 2: Add the .addClass() call to the script: 

$( "a" ).addClass( "test" );

All <a> elements are now bold.

Step 3: To remove an existing class, use .removeClass():

$( "a" ).removeClass( "test" );

Introducing Special Effects

jQuery provides some handy special effects to help you make your web sites stand out. Ex.

$( "a" ).click(function( event ) {
    event.preventDefault();
    $( this ).hide( "slow" );
});

This effect slowly disappears the link when clicked. 

Mention the functionality which you can do with JavaScript

Given below are the things which you can do with JavaScript:

1. Give response to a user instantaneously – Suppose a user has just submitted a comment form on your website. It would be a nice idea to show a thank you message to the user instantaneously using JavaScript. The server can also do this but it might take a little while for your thank you message to appear to the user, depending on how busy the server is.
2. Make your web pages responsive to events – Since Web environments are dynamic, events happen all the time. With JavaScript, the web page can immediately react to these events the way you choose.
3. Detect visitors browser – JavaScript can be used to detect a visitor's browser. You can choose to load a page specifically tailored to that kind of browser, depending on the browser and its capabilities.
4. Creation of Cookies – JavaScript is quite efficient if you want to create cookies so that your visitors can enjoy a personalized experience the next time they visit your website.
5. Validation of Web Form data – JavaScript can be used to validate web-form data before the form is submitted to a server. This saves the server from extra processing.
6. There are tons of other functionality which can be implemented by JavaScript such as addition of cool animation effects to your web pages without using an external Flash plug-in, usage of the newest features of HTML5 such as canvas (to draw directly on your web page) and drag and drop capabilities, integrate your website with external web services such as Facebook, Twitter, etc.