select into Temp table from dynamic sql

set @SQL=('SELECT * into #temp FROM TableName; SELECT * FROM #TEMP')
exec(@SQL)

or

set @SQL=('SELECT * into ##temp FROM TableName')
select * from ##temp
Drop Table  ##temp

or

CREATE TABLE #temp (iid int, strName varchar(100), strAge varchar(100), str varchar(100), strTitle varchar(100))

exec('insert into #temp SELECT * FROM tbl_XML_Test')

select * from #temp

drop table #temp

Note: we can, t use table variable  instead of temp table for above query.

Order By Day of Week

ORDER BY case [Day_Of_Week] when 'Monday' then 1
when 'Tuesday' then 2
when 'Wednesday' then 3
when 'Thursday' then 4
when 'Friday' then 5
when 'Saturday' then 6
when 'Sunday' then 7
else 8 end

SQL Server- View, temp table, variable table and Functions

View:

It is a virtual table.

Scenarios for Using Views:

Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.

To Focus on Specific Data:
Views allow users to focus on specific data that interests them and on the specific tasks for which they are responsible. Unnecessary data can be left out of the view. This also increases the security of the data because users can see only the data that is defined in the view and not the data in the underlying table

Views allow different users to see data in different ways, even when they are using the same data concurrently.

Advantages of views:

1. View the data without storing the data into the object.

2. Restict the view of a table i.e. can hide some of columns in the tables.

3. Join two or more tables and show it as one object to user.

4. Restict the access of a table so that nobody can insert the rows into the table.

5. We can join Temp table and view , physical table

Disadvatages:

1. Can not use DML operations on this. (we have used more then one table when creating view)

2. When table is dropped view becomes inactive.. it depends on the table objects.

3. It is an object, so it occupies space.

4. we can't  create clustrered index

5. we can't create nonclustered index without create unique clustred index

6. we can't create index without schema binding

6. we can't create view with schema binding, without using dbo.tablename instead of tablename.

7. we can't create constraints for view

8. we drop unique clustered index, non clustered index also droped.

------------------------------------------------------------------------------------------------------------

Variable table and Temp Table

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.

1. we can create primary key
2. we can create indentity column
3. we can't create non clustered index
4. Table variables are not transactional
5. we can join with temp table or normal table or view
6.Maximum 128 characters.
7. collation- String columns inherit collation from current database.
8. index- Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the    DECLARE statement.
9. Constraints - PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the        creation of the table in    the DECLARE statement. FOREIGN KEY not allowed.
10. Post-creation DDL (indexes, columns) - Statements are not allowed.
11. Data insertion- INSERT statement (SQL 2000: cannot use INSERT/EXEC).
12. Truncate table- Not allowed. Allowed.
13. Rollbacks- Not affected (Data not rolled back).
14. Dynamic SQL Must declare table variable inside the dynamic SQL.
15. Can't define globally

Temp Table

1. we can create all constraints include identity column
2. temp tables are transactional and do roll back.
3. we can join with temp table or normal table or view
4. Maximum 116 characters.
5. collation-String columns inherit collation from tempdb database.
6. index- Indexes can be added after the table has been created.
7. Constraints -RIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after    the table has been created. FOREIGN KEY not allowed.
8. Post-creation DDL (indexes, columns) - Statements are allowed.
9. Data insertion- INSERT statement, including INSERT/EXEC.SELECT INTO statement.
10. Truncate table  Allowed.
11. Rollbacks- Affected (Data is rolled back).
12. Dynamic SQL-Can use temporary tables created prior to calling the dynamic sql.
13. Can't define globally

Reference:

• http://www.codeproject.com/Articles/415184/Table-Variable-V-S-Temporary-Table
• http://www.sqlserver-training.com/why-to-use-temporary-table-over-table-variable/-
• http://www.dotnet-tricks.com/Tutorial/sqlserver/X517150913-Difference-between-CTE-and-Temp-Table-and-Table-Variable.html

------------------------------------------------------------------------------------------------------------
Function:

1. input  must be used
2. can't create temp table or normal table with in function
3. we can create table variable with in function
4. DDL and DML operation canot not supported
5. we must return any value.

Limitations:

1. We cannot use temporary tables inside the UDF.
2. We cannot use Print command inside the UDF.
3. We can't use the Insert, update and delete command inside the function. We can only use these commands on the table variable(not physical table) defined inside the function.
4. We cannot use the try catch statement inside the function for the debugging purpose which makes it difficult to debug.
5. We can't use transaction inside the function.
6. We can't call the stored procedure from inside the function.
7. We can't return multiple record sets from a function as we can do in case of Stored Procedure.
8. Also performance of a SQL Function is low as compared to a stored procedure.
9. So if we can get the same result through a stored procedure, we should write a stored procedure instead of SQL Function.

-------------------------------------------------------------------------------

Basic Difference between Strored procedure and Functions

• Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
• Functions can have only input parameters for it whereas Procedures can have input/output parameters .
• Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..

• Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference between Strored procedure and Functions

• Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
• Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
• Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
• Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables
• Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
• Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
• We can go for Transaction Management in Procedure whereas we can't go in Function.

Get Client IP address using javascript

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>

    <script language="javascript" type="text/javascript">
    function GetClientIp()
        {
            var ip= '<%= Request.ServerVariables["REMOTE_ADDR"] %>';
            alert("Your IP Address is :"+ip+" ");
        }
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="GetIP" runat="server" Text="Get IP Address" OnClientClick="GetClientIp();" />
    </div>
    </form>
</body>
</html>

-----------------------------------------------------------

Server Variables collection:

Variable	Description
ALL_HTTP	Returns all HTTP headers sent by the client. Always prefixed with HTTP_ and capitalized
ALL_RAW	Returns all headers in raw form
APPL_MD_PATH	Returns the meta base path for the application for the ISAPI DLL
APPL_PHYSICAL_PATH	Returns the physical path corresponding to the meta base path
AUTH_PASSWORD	Returns the value entered in the client's authentication dialog
AUTH_TYPE	The authentication method that the server uses to validate users
AUTH_USER	Returns the raw authenticated user name
CERT_COOKIE	Returns the unique ID for client certificate as a string
CERT_FLAGS	bit0 is set to 1 if the client certificate is present and bit1 is set to 1 if the cCertification authority of the client certificate is not valid
CERT_ISSUER	Returns the issuer field of the client certificate
CERT_KEYSIZE	Returns the number of bits in Secure Sockets Layer connection key size
CERT_SECRETKEYSIZE	Returns the number of bits in server certificate private key
CERT_SERIALNUMBER	Returns the serial number field of the client certificate
CERT_SERVER_ISSUER	Returns the issuer field of the server certificate
CERT_SERVER_SUBJECT	Returns the subject field of the server certificate
CERT_SUBJECT	Returns the subject field of the client certificate
CONTENT_LENGTH	Returns the length of the content as sent by the client
CONTENT_TYPE	Returns the data type of the content
GATEWAY_INTERFACE	Returns the revision of the CGI specification used by the server
HTTP_<HeaderName>	Returns the value stored in the header HeaderName
HTTP_ACCEPT	Returns the value of the Accept header
HTTP_ACCEPT_LANGUAGE	Returns a string describing the language to use for displaying content
HTTP_COOKIE	Returns the cookie string included with the request
HTTP_REFERER	Returns a string containing the URL of the page that referred the request to the current page using an <a> tag. If the page is redirected, HTTP_REFERER is empty
HTTP_USER_AGENT	Returns a string describing the browser that sent the request
HTTPS	Returns ON if the request came in through secure channel or OFF if the request came in through a non-secure channel
HTTPS_KEYSIZE	Returns the number of bits in Secure Sockets Layer connection key size
HTTPS_SECRETKEYSIZE	Returns the number of bits in server certificate private key
HTTPS_SERVER_ISSUER	Returns the issuer field of the server certificate
HTTPS_SERVER_SUBJECT	Returns the subject field of the server certificate
INSTANCE_ID	The ID for the IIS instance in text format
INSTANCE_META_PATH	The meta base path for the instance of IIS that responds to the request
LOCAL_ADDR	Returns the server address on which the request came in
LOGON_USER	Returns the Windows account that the user is logged into
PATH_INFO	Returns extra path information as given by the client
PATH_TRANSLATED	A translated version of PATH_INFO that takes the path and performs any necessary virtual-to-physical mapping
QUERY_STRING	Returns the query information stored in the string following the question mark (?) in the HTTP request
REMOTE_ADDR	Returns the IP address of the remote host making the request
REMOTE_HOST	Returns the name of the host making the request
REMOTE_USER	Returns an unmapped user-name string sent in by the user
REQUEST_METHOD	Returns the method used to make the request
SCRIPT_NAME	Returns a virtual path to the script being executed
SERVER_NAME	Returns the server's host name, DNS alias, or IP address as it would appear in self-referencing URLs
SERVER_PORT	Returns the port number to which the request was sent
SERVER_PORT_SECURE	Returns a string that contains 0 or 1. If the request is being handled on the secure port, it will be 1. Otherwise, it will be 0
SERVER_PROTOCOL	Returns the name and revision of the request information protocol
SERVER_SOFTWARE	Returns the name and version of the server software that answers the request and runs the gateway
URL	Returns the base portion of the URL

Session Management

The following are the available Session State Modes in ASP.NET:

• InProc
• StateServer
• SQLServer
• Custom
• Off

InProc Session State Mode

The InProc Session State Mode is the default Session State Mode. We can host multiple websites/web applications on a single IIS. Each application runs in a separate Application Domain. The InProc Session State Mode stores session data in a memory object in the application worker process (aspnet_wp.exe) in the application domain. It is usually the fastest, but more session data means more memory is used on the web server, and that can affect performance. 

The session data is stored in the application domain of the web server. When the server restarts then existing data is lost. If you modify the Global.asax file and the Web.Config file for an ASP.NET application then the application will be restarted and all the session data will be lost.

SP.NET provides two events that help you manage user sessions. These events are defined in the Global.asax file of the web application.

Sr. No	Event	Call
1	Session_Start()	This event occurs when a new session begins.
2	Session_End()	This event occurs when a session is abandoned or expires.

<configuration>
  <system.web>    
    <sessionState mode="InProc" timeout="25"></sessionState>
  </system.web></configuration>

The preceding session timeout setting keeps the session alive for 25 minutes. If you don't define a timeout attribute in the SessionState then the default value is 20 minutes.

Advantages of InProc Session State Mode

Here is a list of the advantages of the InProc Session State Mode.

1. It is easy to implement.
2. It stores the session data on the server so it is fast.
3. In this mode, it is not necessary to serialize and de-serialize to store and retrieve data in the session variables.

Disadvantages of InProc Session State Mode

Here is a list of the disadvantages of the InProc Session State Mode.

1. When the application domain or worker process recycles, the session data will be lost.
2. It is usually the fastest, but more session data means more memory is used on the web server, and that can affect performance
3. It won't work in web farm scenarios and web garden scenarios, because in these scenarios multiple "aspnet_wp.exe" processes will be running on the same machine.

Conclusion
The InProc Session State Mode is a very fast session storing mechanism but suitable only for small web applications. InProc session data would be lost if we restart the server or if the application domain is recycled.

------------------------------------------------------------------------------------------------------

ASP.NET Application and Page Life Cycle

Creation of ASP.NET Environment

Step 1: The user sends a request to IIS. IIS first checks which ISAPI extension can serve this request. Depending on file extension the request is processed. For instance, if the page is an ‘.ASPX page’, then it will be passed to ‘aspnet_isapi.dll’ for processing.

Step 2: If this is the first request to the website, then a class called as ‘ApplicationManager’ creates an application domain where the website can run. As we all know, the application domain creates isolation between two web applications hosted on the same IIS. So in case there is an issue in one app domain, it does not affect the other app domain.

Step 3: The newly created application domain creates hosting environment, i.e. the ‘HttpRuntime’ object. Once the hosting environment is created, the necessary core ASP.NET objects like ‘HttpContext’ , ‘HttpRequest’ and ‘HttpResponse’ objects are created.

Step 4: Once all the core ASP.NET objects are created, ‘HttpApplication’ object is created to serve the request. In case you have a ‘global.asax’ file in your system, then the object of the ‘global.asax’ file will be created. Please noteglobal.asax file inherits from ‘HttpApplication’ class.
Note: The first time an ASP.NET page is attached to an application, a new instance of ‘HttpApplication’ is created. Said and done to maximize performance, HttpApplication instances might be reused for multiple requests.

Step 5: The HttpApplication object is then assigned to the core ASP.NET objects to process the page.

Step 6: HttpApplication then starts processing the request by HTTP module events, handlers and page events. It fires the MHPM event for request processing.
Note: For more details, read this.

The below image explains how the internal object model looks like for an ASP.NET request. At the top level is the ASP.NET runtime which creates an ‘Appdomain’ which in turn has ‘HttpRuntime’ with ‘request’, ‘response’ and ‘context’ objects.

-------------------------------------------------------------------------------------------------------

You can read more about the differences from here.
Below is the logical flow of how the request is processed. There are 4 important steps MHPM as explained below:

Step 1(M: HttpModule): Client request processing starts. Before the ASP.NET engine goes and creates the ASP.NETHttpModule emits events which can be used to inject customized logic. There are 6 important events which you can utilize before your page object is created BeginRequest, AuthenticateRequest, AuthorizeRequest,ResolveRequestCache, AcquireRequestState and PreRequestHandlerExecute.

Step 2 (H: ‘HttpHandler’): Once the above 6 events are fired, ASP.NET engine will invoke ProcessRequest event if you have implemented HttpHandler in your project.

Step 3 (P: ASP.NET page): Once the HttpHandler logic executes, the ASP.NET page object is created. While the ASP.NET page object is created, many events are fired which can help us to write our custom logic inside those page events. There are 6 important events which provides us placeholder to write logic inside ASP.NET pages Init, Load,validate, event, render and unload. You can remember the word SILVER to remember the events S – Start (does not signify anything as such just forms the word) , I – (Init) , L (Load) , V (Validate), E (Event) and R (Render).

Step4 (M: HttpModule): Once the page object is executed and unloaded from memory, HttpModule provides post page execution events which can be used to inject custom post-processing logic. There are 4 important post-processing events PostRequestHandlerExecute, ReleaserequestState, UpdateRequestCache and EndRequest.
The below figure shows the same in a pictorial format.

Using AJAX Textbox Extenders

1. Watermark Extender
2. Filtered TextBox
3. Masked Edit TextBox and Masked Edit Validator
4. Calendar Extender
5. Validator Callout Extender
6. Auto Complete Extender
7. Password Strength Extender

Reference:

http://www.c-sharpcorner.com/uploadfile/dpatra/using-ajax-textbox-extenders/

Ajax:MaskedEditExtender, Ajax:MaskedEditValidator

Ajax:MaskedEditExtender

<ajaxToolkit:MaskedEditExtender
    TargetControlID="TextBox2" 
    Mask="9,999,999.99"
    MessageValidatorTip="true" 
    OnFocusCssClass="MaskedEditFocus" 
    OnInvalidCssClass="MaskedEditError"
    MaskType="Number" 
    InputDirection="RightToLeft" 
    AcceptNegative="Left" 
    DisplayMoney="Left"
    ErrorTooltipEnabled="True"/>

• MaskType - Type of validation to perform:
  None - No validation
  Number - Number validation
  Date - Date validation
  Time - Time validation
  DateTime - Date and time validation
• Mask Characters and Delimiters
  9 - Only a numeric character
  L - Only a letter
  $ - Only a letter or a space
  C - Only a custom character (case sensitive)
  A - Only a letter or a custom character
  N - Only a numeric or custom character
  ? - Any character
  
  / - Date separator
  : - Time separator
  . - Decimal separator
  , - Thousand separator
  \ - Escape character
  { - Initial delimiter for repetition of masks
  } - Final delimiter for repetition of masks
  
  Examples:
  9999999 - Seven numeric characters
  99\/99 - Four numeric characters separated in the middle by a "/"
• AcceptAMPM - True to display an AM/PM symbol
• AcceptNegative - True if the negative sign (-) is allowed
  None - Do not show the negative sign
  Left - Show the negative sign on the left of the mask
  Right - Show the negative sign on the right of the mask
• AutoComplete - True to automatically fill in empty mask characters not specified by the user
  MaskType=Number - Empty mask characters will be filled with zeros
  MaskType=Time - Empty mask characters will be filled with the current time
  MaskType=Date - Empty mask characters will be filled with the current date
  MaskType=DateTime - Empty mask characters will be filled with the current date/time
• AutoCompleteValue - Default character to use when AutoComplete is enabled
• Century - Default century used when a date mask only has two digits for the year
• ClearMaskOnLostFocus - True to remove the mask when the TextBox loses focus
• ClearTextOnInvalid - True to clear the TextBox when invalid text is entered
• ClipboardEnabled- True to allow copy/paste with the clipboard
• ClipboardText - Prompt text to use when a clipboard paste is performed
• DisplayMoney - Specifies how the currency symbol is displayed
  None - Do not show the currency symbol
  Left - Show the currency symbol on the left of the mask
  Right - Show the currency symbol on the right of the mask
• ErrorTooltipCssClass - CSS class for the tooltip message
• ErrorTooltipEnabled - True to show a tooltip message when the mouse hovers over an invalid TextBox
• Filtered - Valid characters for mask type "C" (case-sensitive)
• InputDirection - Text input direction
  LeftToRight - Left to Right
  RightToLeft - Right to left
• MessageValidatorTip - Message displayed when editing in TextBox
• PromptChararacter - Prompt character for unspecified mask characters
• UserDateFormat - Custom date format
• UserTimeFormat - Custom time format
• OnFocusCssClass - CSS class used when the TextBox receives focus
• OnFocusCssNegative - CSS class used when the TextBox gets focus with a negative value
• OnBlurCssNegative - CSS class used when the TextBox loses focus with a negative value
• OnInvalidCssClass - CSS class used when the text is not valid.
• CultureName - Name of culture to use (overrides the default page culture)
• CultureAMPMPlaceholder - Culture override
• CultureCurrencySymbolPlaceholder - Culture override
• CultureDateFormat - Culture override
• CultureDatePlaceholder - Culture override
• CultureDecimalPlaceholder - Culture override
• CultureThousandsPlaceholder - Culture override
• CultureTimePlaceholder - Culture override

Ajax:MaskedEditValidator

<ajaxToolkit:MaskedEditValidator
    ControlExtender="MaskedEditExtender2"
    ControlToValidate="TextBox2" 
    IsValidEmpty="False" 
    MaximumValue="12000" 
    EmptyValueMessage="Number is required"
    InvalidValueMessage="Number is invalid"
    MaximumValueMessage="Number > 12000"
    MinimumValueMessage="Number < -100"
    MinimumValue="-100" 
    EmptyValueBlurredText="*" 
    InvalidValueBlurredMessage="*" 
    MaximumValueBlurredMessage="*" 
    MinimumValueBlurredText="*"
    Display="Dynamic" 
    TooltipMessage="Input a number: -100 up to 12.000"/>

• ControlToValidate - ID of the TextBox to validate
• ControlExtender - ID of the MaskedEditExtender attached to the TextBox
• AcceptAMPM - Whether or not AM/PM is accepted on times.
  The default value is false.
• ClientValidationFunction - Client script used for custom validation
• InitialValue - Initial value of the TextBox
• IsValidEmpty - True if the TextBox can be empty
• MaximumValue - Maximum value of the input
• MinimumValue - Minimum value of the input
• ValidationExpression - Regular expression used to validate the input
• TooltipMessage - Message displayed when the TextBox has focus with an empty value
• EmptyValueMessage - Message displayed when empty and TextBox has focus
• EmptyValueBlurredText - Message displayed when empty and TextBox does not have focus
• InvalidValueMessage - Message displayed when invalid and TextBox has focus
• InvalidValueBlurredMessage - Message displayed when invalid and TextBox does not have focus
• MaximumValueMessage - Message displayed when maximum value exceeded and TextBox has focus
• MaximumValueBlurredMessage - Message displayed when maximum value exceeded and TextBox does not have focus
• MinimumValueMessage - Message displayed when minimum value exceeded and TextBox has focus
• MinimumValueBlurredText - Message displayed when minimum value exceeded and TextBox does not have focus

Ajax:FilteredTextBoxExtender

 <table width="400">        <tr>            <td>                <asp:Label ID="label1" runat="server" Text="Only Numbers" Width="150"></asp:Label>                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>                <ajaxToolkit:FilteredTextBoxExtender ID="FilteredTextBoxExtender1" runat="server" TargetControlID="TextBox1" FilterType="Numbers" />            </td>        </tr>        <tr>            <td>                <asp:Label ID="label2" runat="server" Text="Uppercase Letter" Width="150"></asp:Label>                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>                <ajaxToolkit:FilteredTextBoxExtender ID="FilteredTextBoxExtender2" runat="server"TargetControlID="TextBox2" FilterType="UppercaseLetters" />            </td>        </tr>        <tr>            <td>                <asp:Label ID="label3" runat="server" Text="Lowercase Letters" Width="150"></asp:Label>                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>                <ajaxToolkit:FilteredTextBoxExtender ID="FilteredTextBoxExtender3" runat="server"TargetControlID="TextBox3" FilterType="lowercaseLetters" />            </td>        </tr>        <tr>            <td>                <asp:Label ID="label4" runat="server" Text="Custom numbers" Width="150"></asp:Label>                <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>                <ajaxToolkit:FilteredTextBoxExtender ID="FilteredTextBoxExtender4" runat="server"TargetControlID="TextBox4" FilterType="Custom, Numbers" ValidChars="+-=/*()." />            </td>        </tr>
         <tr>            <td>                <asp:Label ID="label4" runat="server" Text="Custom numbers" Width="150"></asp:Label>                <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>              <ajaxToolkit:FilteredTextBoxExtender ID="ftxtAcdD_MHTCET_Tot" runat="server" TargetControlID="txtbox1" FilterType="Custom" ValidChars="0123456789"; />            </td>        </tr>    </table>