In this post we are going to share the interview questions or the information which you must know as a programmer or a developer, especially if you are a Dot Net developer. I hope you will like this article.

Background
I am a dot net developer. As a dot net developer, there are so many things that I must be aware of. I am sharing those in the form of articles, you can always read my other interview questions here in the following links.

So shall we now discuss about SQL interview questions.

SQL Interview Questions
Question: What are the types of Joins in SQL. Explain?

INNER JOIN: Returns all rows when there is at least one match in BOTH the tables.
LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.
RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.
FULL JOIN: Returns all rows when there is a match in ONE of the table.

Question: What is the default join in SQL? Give an example query?
The default join is INNER JOIN.

Example

SELECT column_name(s)  
FROM table1  
INNER JOIN table2  
ON table1.column_name=table2.column_name;  

Question: Describe all the joins with examples in SQL?
SQL LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.
    SQL LEFT JOIN Syntax  
    SELECT column_name(s)  
    FROM table1  
    LEFT JOIN table2  
    ON table1.column_name=table2.column_name;  

SQL RIGHT JOIN
The right join returns all the rows in the right table i.e. table 2 with the matching ones on the left table (table1).
    SELECT column_name(s)  
    FROM table1  
    RIGHT JOIN table2  
    ON table1.column_name=table2.column_name;  

SQL FULL OUTER
The full join returns all rows from the left table (table1) and from the right table (table2).
    SELECT column_name(s)  
    FROM table1  
    FULL OUTER JOIN table2  
    ON table1.column_name=table2.column_name;  

Question: What is Union and Union All ? Explain the differences?
SQL UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

Note: The UNION operator selects only distinct values by default.
    SELECT column_name(s) FROM table1  
    UNION  
    SELECT column_name(s) FROM table2;  
SQL UNION ALL
    SQL UNION ALL Syntax  
    SELECT column_name(s) FROM table1  
    UNION ALL  
    SELECT column_name(s) FROM table2;  

Allows duplicate values.

Question: Differentiate Clustered and Non clustered Index in SQL?
A clustered index is one in which the index’s order is arranged according to the physical order of rows in the table. Due to this reason there can only be one clustered index per table, usually this is the primary key.

A non clustered index is one in which the order of index is not in accordance with the physical order of rows in the table.

Create Index Syntax
CREATE INDEX [ CLUSTERED | NONCLUSTERED ] PIndex ON Persons (LastName,FirstName)

Question: Explain the difference between Stored Procedure and User Defined Function?

Stored Procedure
Stored procedures are reusable code in database which is compiled for first time and its execution plan saved. The compiled code is executed when every time it is called.

Function
Function is a database object in SQL Server. Basically it is a set of SQL statements that accepts only input parameters, perform actions and return the result. Function can return only a single value or a table. We can’t use functions  to Insert, Update, Delete records in the database table(s). It is compiled every time it is invoked.

Basic Difference
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.

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

Advanced Differences
Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it,  whereas Function allows only SELECT statement in it. Procedures cannot 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.

The most important feature of stored procedures over function is retention and reusing the execution plan while in case of function it will be compiled every time.
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 use transactions in stored procedure but not in functions.

Conclusion
Did I miss anything that you may think which is needed? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.

In this blog, we will learn how we can check which mode of SSAS is installed on our machine. SSAS is available with three modes of installation.

• Tabular
• Multidimensional
• SharePoint

To check which mode of SSAS is installed on your machine, follow the below steps.

• Open SSMS.
• Right-click on SSAS Properties.

The Mode of Server Name is available in the property window. In our case, SSAS is installed with the Multidimensional mode, so it is showing “Multidimensional”.

In this article, we will see how to call a function from a stored procedure in SQL Server 2012. Here, I have written a scalar function named MultiplyofTwoNumber that accepts two parameters and returns one parameter. Now I want to call this from a stored procedure. So let's take a look at a practical example of how to call a function from a stored procedure in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.  There are some simple things to do that are described here.

There are two types of functions in SQL Server; they are:

•  System defined function
•  User defined function

User defined functions are three types in SQL Server. They are scalar, inline table-valued and multiple-statement table-valued.

Creating a User-Defined Scalar Function in SQL Server
Now create a function named MultiplyofTwoNumber with the two parameters number1 and number2 returning one parameter named result. Both parameters have the same type, int. The function looks as in the following:
    Create FUNCTION [dbo].[MultiplyofTwoNumber]  
    (  
           @Number1 int,  
           @Number2 int  
    )  
    RETURNS int  
    AS  
    BEGIN  
           -- Declare the return variable here  
           DECLARE @Result int  
           SELECT @Result = @Number1 * @Number2;  
           -- Return the result of the function  
           RETURN @Result  
    END  

Creating a Stored Procedure in SQL Server
A function can be called in a select statement as well as in a stored procedure. Since a function call would return a value we need to store the return value in a variable. Now creating a stored procedure which calls a function named MultiplyofTwoNumber; see:
    Create PROCEDURE [dbo].[callingFunction]  
    (  
    @FirstNumber int,  
    @SecondNumber int  
    )  
    AS  
    begin  
    declare @setval int  
    select dbo.[MultiplyofTwoNumber](@FirstNumber, @SecondNumber)  
    end  

Now, we can execute the procedure with duplicate values to check how to call a function from a stored procedure; see:
    USE [registration]  
    GO  
    DECLARE  @return_value int  
    EXEC  @return_value = [dbo].[callingFunction]  
        @FirstNumber = 3,  
        @SecondNumber = 4  

Now press F5 to run the stored procedure.

A function can be called using a select statement:

    Select dbo.[MultiplyofTwoNumber](3, 4) as MultiplyOfNumbers  

Now press F5 to run the stored procedure.

Output

In this blog, I have shown the process of calling web services through a stored procedure in SQL Server database. Also, I have explained how to call a stored procedure with a SOAP Envelope.

Step 1
Create a stored procedure in your SQL Server.
  CREATE proc [dbo].[spHTTPRequest]    
        @URI varchar(2000) = 'http://localhost:55253/',         
        @methodName varchar(50) = 'Get',    
        @requestBody varchar(8000) = '',    
        @SoapAction varchar(255),    
        @UserName nvarchar(100), -- Domain\UserName or UserName    
        @Password nvarchar(100),    
        @responseText varchar(8000) output   
  as   
  SET NOCOUNT ON   
  IF    @methodName = ''   
  BEGIN   
        select FailPoint = 'Method Name must be set'   
        return   
  END   
  set   @responseText = 'FAILED'   
  DECLARE @objectID int   
  DECLARE @hResult int   
  DECLARE @source varchar(255), @desc varchar(255)    
  EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
                    source = @source,    
                    description = @desc,    
                    FailPoint = 'Create failed',    
                    MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- open the destination URI with Specified method    
  EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Open failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set request headers    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8'   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  -- set soap action    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @len int   
  set @len = len(@requestBody)    
  EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len    
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'SetRequestHeader failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  /*   
  -- if you have headers in a table called RequestHeader you can go through them with this   
  DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500)   
  DECLARE RequestHeader CURSOR  
  LOCAL FAST_FORWARD   
  FOR  
        SELECT      HeaderKey, HeaderValue   
        FROM RequestHeaders   
        WHERE       Method = @methodName   
  OPEN RequestHeader   
  FETCH NEXT FROM RequestHeader   
  INTO @HeaderKey, @HeaderValue   
  WHILE @@FETCH_STATUS = 0   
  BEGIN  
        --select @HeaderKey, @HeaderValue, @methodName   
        EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue   
        IF @hResult <> 0   
        BEGIN  
              EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT  
              SELECT      hResult = convert(varbinary(4), @hResult),   
                    source = @source,   
                    description = @desc,   
                    FailPoint = 'SetRequestHeader failed',   
                    MedthodName = @methodName   
              goto destroy   
              return  
        END  
        FETCH NEXT FROM RequestHeader   
        INTO @HeaderKey, @HeaderValue   
  END  
  CLOSE RequestHeader   
  DEALLOCATE RequestHeader   
  */    
  -- send the request    
  EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody    
  IF    @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'Send failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  declare @statusText varchar(1000), @status varchar(1000)    
  -- Get status text    
  exec sp_OAGetProperty @objectID, 'StatusText', @statusText out   
  exec sp_OAGetProperty @objectID, 'Status', @status out   
  select @status, @statusText, @methodName    
  -- Get response text    
  exec sp_OAGetProperty @objectID, 'responseText', @responseText out   
  IF @hResult <> 0    
  BEGIN   
        EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT   
        SELECT      hResult = convert(varbinary(4), @hResult),    
              source = @source,    
              description = @desc,    
              FailPoint = 'ResponseText failed',    
              MedthodName = @methodName    
        goto destroy    
        return   
  END   
  destroy:    
        exec sp_OADestroy @objectID    
  SET NOCOUNT OFF   
      
  GO   

The Stored Procedure takes the following parameters.
  @URI: the URI of the web service
  @MethodName: this would be ‘GET’ or ‘POST’
  @RequestBody: this is the SOAP xml that you want to send
  @SoapAction: this the operation that you want to call on your service
  @UserName: NT UserName if your web service requires authentication
  @Password: the password if using NT Authentication on the web service
  @ResponseText: this is an out parameter that contains the response from the web service

Step 2
Make the setting in SQL for it.
  Use master 
  sp_configure 'show advanced options', 1  
   
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'Ole Automation Procedures', 1  
  GO  
  RECONFIGURE;  
  GO  
  sp_configure 'show advanced options', 1  
  GO  
  RECONFIGURE; 

Step 3
Call the stored procedure (Here is a sample call to my service).
  declare @xmlOut varchar(8000) 
  Declare @RequestText as varchar(8000); 
  set @RequestText= 
  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/"> 
     <soapenv:Header/> 
     <soapenv:Body> 
        <tem:CreateOrder> 
           <!--Optional:--> 
           <tem:OrderRequest> 
              <tem:OrderId>200</tem:OrderId> 
              <!--Optional:--> 
              <tem:OrderName>something</tem:OrderName> 
           </tem:OrderRequest> 
        </tem:CreateOrder> 
     </soapenv:Body> 
  </soapenv:Envelope>' 
  exec spHTTPRequest 
  'http://localhost/testwebservices/helloworldservice.asmx', 
  'POST', 
  @RequestText, 
  'http://tempuri.org/CreateOrderForMe',   -- this is your SOAPAction: 
  '', '', @xmlOut out 
  select @xmlOut  

Make sure your SOAP action is correct. Copy this action from your services. It will show up when your service is RUN.

Ever seen the below error? Until this week I hadn’t. So, I figured I’d take a little time and introduce it to those that had not.

Error Description: Length of LOB data (65754) to be replicated exceeds configured maximum 65536. Use the stored procedure sp_configure to increase the configured maximum value for max text repl size option, which defaults to 65536. A configured value of -1 indicates no limit

We ran into an issue with a customer this week. This error was flooding the error log. After a little digging, I found it had to do with transactional replication (also applies to Change Data Capture) they had set up which included LOB data.

Per MSDN,
The max text repl size option specifies the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default value is 65536 bytes.

In the error above you can see it plainly states that the column’s LOB data nvarchar(max), in this case, was 65754 bytes which was over the max default size of 65536. Which ironically is 64k.   64*1024 = 65536 (if you didn’t know). Adjusting the max text repl size for this server solved our issue. Below you can see the ways to change this value. For us changing it to the max value of 2147483647 bytes which is 2 GB was the way to go. If you don’t know the max value you can also set it to -1 which means no limit, the limit will be based on datatype limits. Previously, the limit was 2GB.

Script
GO  
EXEC sp_configure 'show advanced options', 1;   
RECONFIGURE ;   
GO  
EXEC sp_configure 'max text repl size',2147483647;   
GO  
RECONFIGURE;   
GO 

Using GUI
At the Server Level right click and go to Properties.
Click on Advanced. Under Miscellaneous, change the Max Text Replication Size option to the desired value.

What is a Key Lookup?
One of the easiest things to fix when performance tuning queries are Key Lookups or RID Lookups. The key lookup operator occurs when the query optimizer performs an index seek against a specific table and that index does not have all of the columns needed to fulfill the result set. SQL Server is forced to go back to the clustered index using the Primary Key and retrieve the remaining columns it needs to fulfill the request. A RID lookup is the same operation but is performed on a table with no clustered index, otherwise known as a heap. It uses a row id instead of a primary key to do the lookup.

As you can see these can be very expensive and can result in substantial performance hits in both I/O and CPU. Imagine a query that runs thousands of times per minute that includes one or more key look ups. This can result in tremendous overhead which is generated by these extra reads and it effects the overall engine performance.

Let’s look at an example.
    SELECT [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID], 
    [UnitPrice],[ModifiedDate]   
    FROM [AdventureWorks2014].[Sales].[SalesOrderDetail]   
    Where [ModifiedDate]> 2014/01/01  and [ProductID]=772
 

The cost of the key lookup operator is 99% of the query. You can see it did an Index Seek to the IX_SalesOrderDetail_ProductID which is very effective, but that index did not have all the columns needed to satisfy the query. The optimizer then went out to the clustered index PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID to retrieve the additional columns it needed. You can see what it got by hovering over the key lookup in the query plan window.

The good thing about Key and RID look ups is that they are super easy to fix. With a little modification to the non-clustered Index IX_SalesOrderDetail_ProductID we can change to query plan from an Index Seek and a Key Lookup to a very small index seek. All we have to do is recreate that index and add the Output List fields as Included columns on that index.
    CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_ProductID]  
    ON [Sales].[SalesOrderDetail]([ProductID] ASC) 
    INCLUDE ([CarrierTrackingNumber],[UnitPrice], [ModifiedDate], [OrderQty]) 
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
    ON [PRIMARY] 

And as you can see, we now have an Index Seek only and a more efficient plan.

Key Lookups can cause performance headaches, especially for queries that run many times a day. Do yourself and your environment a favor and start hunting these down and get them fixed.

Anyway, by now, you probably have seen documentation on Query Store and DBCC CloneDatabase. Query Store, introduced in SQL Server 2016, is considered the “black box” or “flight recorder” of queries. It retains a history of executed queries, including run-time statistics as well as execution plans from query store enabled databases. This flight recorder helps to diagnosis and pinpoint query performance issues. Another useful tool is DBCC CloneDatabase. This command was released in older versions via service packs before being fully released in 2016. This tool allows you to make a “schema” only copy of a database which will reside on the same server as the source database. The schema includes both user and system schema, as well as any corresponding statistical data.

Why is this useful?
Let’s say you have a 1 terabyte database and wanted to perform query tuning on it. Storage is usually a premium commodity in most organizations and there isn’t a spare terabyte of storage just laying around. DBCC CloneDatabase would allow you to make a schema only copy, including statistics. Once the new database has been created, you could move the new database onto another server without having the requirement of large amounts of storage. Since the database is really a schema-only copy, the footprint of the database is pretty small. After moving the database, queries ran against it would utilize the statistics contained within to execute the query. True, there isn’t any data in the database, but you can account for that when performing query performance analysis.

DBCC CLONEDATABASE (source_database_name, target_database_name) WITH [NO_STATISTICS],[NO_QUERYSTORE],[VERIFY_CLONEDB],[BACKUP_CLONEDB]

• NO_STATISTICS
  This option specifies that table/index statistics are excluded. Available with SQL Server 2014 SP2 CU3 & SQL Server 2016 SP1
  
  
• NO_QUERYSTORE
  This option specifies that query store data is excluded. Available with SQL Server 2016 SP1
  
  
• VERIFY_CLONEDB
  Verifies the consistency of the new cloned database. Available starting with SQL Server 2016 SP2
  
  
• BACKUP_CLONEDB
  Creates and verifies a backup of the newly cloned database. Available starting with SQL Server 2016 SP2

The command works in this order,

1. Creates a new destination database. Same file layout as the source database however with the default file sizes from Model.
2. Generates a snapshot of the source database.
3. Copies the system metadata from the source to the target database created in step 1.
4. All object schema is copied from the source to the target.
5. Index statistics are copied from the source to the target.

Fairly easy and straightforward. But wait! There’s more!

You will notice the “WITH NO_QUERYSTORE” option in the command. If you are running SQL Server 2016 SP1 or higher and utilizing the Query Store for the source database, DBCC CloneDatabase will also bring over the query store data! This is enabled by default so if you do not want the query store data, you have to explicitly define using NO_QUERYSTORE. This means, by default, if you clone a database you will get the flight recorder data from the query store as well.

These two tools, query store and DBCC CloneDatabase, are just another means to help troubleshoot performance issues.

In this post we will see how to create ASP.NET Registration form and save its data in MS SQL Database. It will be quite simple form with values like Employee Id, Employee name, Date of Birth, Country, State, City names and more. We will be also adding Profile Image of the user and saving it to ASP.NET Project’s folder to complete our Registration form data. So let’s see.

I’m also using AJAX to create this registration form in ASP.NET. You can add it to your project by using Visual Studio’s Nuget Manager Console.

First create a new ASP.NET web project in Visual studio. I’m using Visual studio 2017 to create this registration form for ASP.NET.

Now add a new ASPX form with name Registration.aspx and edit it as below:

Registration.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs"
Inherits="RegistrationForms.Registration" ValidateRequest="true"%>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<link href="css/bootstrap.min.css" rel="stylesheet" />
<style>
.borderless td, .borderless th {
border: none;
border-color: Red;
}

.table-condensed > thead > tr > th, .table-condensed > tbody > tr > th, .table-condensed > tfoot > tr > th, .table-condensed > thead > tr > td, .table-condensed > tbody > tr > td, .table-condensed > tfoot > tr > td {
padding: 3px;
}

input, select {
border-radius: 3px;
padding: 1px;
border: 1px solid darkgray;
}

.btnCoral {
background-color: crimson;
color: #fff;
}

body {
/* Permalink - use to edit and share this gradient: http://colorzilla.com/gradient-editor/#1e5799+0,2989d8+50,207cca+51,7db9e8+100;Blue+Gloss+Default */
background: #1e5799; /* Old browsers */
background: -moz-linear-gradient(top, #1e5799 0%, #2989d8 50%, #207cca 51%, #7db9e8 100%); /* FF3.6-15 */
background: -webkit-linear-gradient(top, #1e5799 0%,#2989d8 50%,#207cca 51%,#7db9e8 100%); /* Chrome10-25,Safari5.1-6 */
background: linear-gradient(to bottom, #1e5799 0%,#2989d8 50%,#207cca 51%,#7db9e8 100%); /* W3C, IE10+, FF16+, Chrome26+, Opera12+, Safari7+ */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#1e5799', endColorstr='#7db9e8',GradientType=0 ); /* IE6-9 */
}

.parent-container {
background-color: black;
width:70%;
}

.container {
background-color: white;
margin:2px;
width:auto;
}

</style>
<title>Registration Form</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager runat="server" />
<div class="parent-container">
<div class="container">
<table class="table-condensed borderless">
<tr>
<td align="center" colspan="2">
<h2 style="background-color: black; color: White; padding: 5px;">REGISTRATION FORM</h2>
</td>
</tr>
<tr>
<td align="right">EMP Id:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtEmpId" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtEmpId" ErrorMessage="*" ForeColor="Red"
ValidateRequestMode="Enabled"/>
</td>
</tr>
<tr>
<td align="right">Name:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtName" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtName" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Date of Birth:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtDOB" />
<ajax:CalendarExtender runat="server" Format="yyyy-MM-dd" TargetControlID="txtDOB" />

<asp:RequiredFieldValidator runat="server" ControlToValidate="txtDOB" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Address:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtAddress" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtAddress" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Country:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddCountry"
AutoPostBack="true" OnSelectedIndexChanged="ddCountry_SelectedIndexChanged" /></td>
</tr>
<tr>
<td align="right">State:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddState"
AutoPostBack="true" OnSelectedIndexChanged="ddState_SelectedIndexChanged" /></td>
</tr>
<tr>
<td align="right">City:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddCity" /></td>
</tr>
<tr>
<td align="right">Pincode:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtPincode" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtPincode" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Date of Joining:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtDOJ" />
<ajax:CalendarExtender runat="server" Format="yyyy-MM-dd" TargetControlID="txtDOJ" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtDOJ" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Department:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddDepartment" /></td>
</tr>
<tr>
<td align="right">Role:</td>
<td align="left">
<asp:DropDownList runat="server" ID="ddRole">

<asp:ListItem Text="USER" Value="USER" />
<asp:ListItem Text="ROLE" Value="ROLE" />

</asp:DropDownList></td>
</tr>
<tr>
<td align="right">Profile Pic:</td>
<td align="left" valign="middle">
<asp:FileUpload runat="server" ID="fileUpload" />
<asp:Button runat="server" Text="Upload" ID="btnUpload" OnClick="btnUpload_Click" />

<asp:Image ID="imgProfile" runat="server" Width="150px" Height="150px" />
</td>
</tr>

<tr>
<td align="right">Email ID:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtEmail" />
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtEmail" ErrorMessage="*" ForeColor="Red" />
<asp:RegularExpressionValidator runat="server" ValidationExpression="\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*" ControlToValidate="txtEmail"
ErrorMessage="Email address invalid" />
</td>
</tr>
<tr>
<td align="right">Password:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtpassword" TextMode="Password"/>
<asp:RequiredFieldValidator runat="server" ControlToValidate="txtpassword" ErrorMessage="*" ForeColor="Red" />
</td>
</tr>
<tr>
<td align="right">Confirm Password:</td>
<td align="left">
<asp:TextBox runat="server" ID="txtConfirmPassword" TextMode="Password"/></td>
</tr>
<tr>
<td colspan="20">
<asp:CompareValidator ID="comparePasswords"
runat="server"
ControlToCompare="txtpassword"
ControlToValidate="txtConfirmPassword"
ErrorMessage="Passwords do not match up."
ForeColor="Red" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<br />
<asp:Button runat="server" ID="btnSubmit" Text="Submit" OnClick="btnSubmit_Click"
CssClass="btn btnCoral" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label runat="server" ID="lblInfo" />
</td>
</tr>
</table>
</div>

</div>
</form>
</body>
</html>

Ajax’s Data picker is used to set Date of Birth and Date of Joining information of the employee to complete the registration process.

Profile picture will be saved inside “ProfileImages” folder.

And below is my code for the registration page:

using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web.UI;

namespace RegistrationForms
{
public partial class Registration : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
SqlDataAdapter adapter;
SqlDataReader reader;
DataSet ds;
DataTable dt;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadCountries();
LoadDepartment();
}
}

public void LoadDepartment()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblDepartments", con);
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["DeptId"] = 0;
dr["Department"] = "Please select Department";
dt.Rows.InsertAt(dr, 0);

ddDepartment.DataSource = dt;
ddDepartment.DataTextField = "Department";
ddDepartment.DataValueField = "DeptId";
ddDepartment.DataBind();
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblInfo.Text = ex.Message.ToString();
}
}
public void LoadCountries()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblCountries", con);
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["CountryId"] = 0;
dr["Country"] = "Please select Country";
dt.Rows.InsertAt(dr, 0);

ddCountry.DataSource = dt;
ddCountry.DataTextField = "Country";
ddCountry.DataValueField = "CountryId";
ddCountry.SelectedIndex = 0;
ddCountry.DataBind();
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
lblInfo.Text = ex.Message.ToString();
}

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
try
{
if (Page.IsValid)
{

con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_AddUsers";
cmd.Connection = con;
cmd.Parameters.AddWithValue("@UserId", txtEmpId.Text.ToString());
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@DOB", txtDOB.Text.ToString());
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.ToString());
cmd.Parameters.AddWithValue("@City", ddCity.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@State", ddState.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Country", ddCountry.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@Pincode", txtPincode.Text.ToString());
cmd.Parameters.AddWithValue("@Department", ddDepartment.SelectedValue.ToString());
cmd.Parameters.AddWithValue("@UserRole", ddRole.SelectedItem.Text.ToString());
cmd.Parameters.AddWithValue("@Passcode", txtpassword.Text.ToString());
cmd.Parameters.AddWithValue("@DOJ", txtDOJ.Text.ToString());
cmd.Parameters.AddWithValue("@ProfilePic", imgProfile.ImageUrl.ToString());
cmd.Parameters.AddWithValue("@EmailId", txtEmail.Text.ToString());
con.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
ShowAlert("Success", "Saved Successfully");
ClearFeilds();
}
else
ShowAlert("Error", "Please enter all fields");
}
catch (Exception ex)
{

}
}

public void ShowAlert(String header, String message)
{
ScriptManager.RegisterStartupScript(this, GetType(), header, "alert('" + message + "');", true);
}

public void ClearFeilds()
{
txtAddress.Text = "";
txtConfirmPassword.Text = "";
txtDOB.Text = "";
txtDOJ.Text = "";
txtEmail.Text = "";
txtEmpId.Text = "";
txtName.Text = "";
txtpassword.Text = "";
txtPincode.Text = "";
LoadCountries();
}
protected void ddCountry_SelectedIndexChanged(object sender, EventArgs e)
{
LoadState();
}

public void LoadState()
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblStates where CountryId=@id", con);
cmd.Parameters.AddWithValue("@id", ddCountry.SelectedValue.ToString());
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["StateId"] = 0;
dr["State"] = "Please select State";
dr["CountryId"] = "0";
dt.Rows.InsertAt(dr, 0);
ddState.Items.Clear();
ddState.ClearSelection();
ddState.DataSource = dt;
ddState.DataTextField = "State";
ddState.DataValueField = "StateId";
ddState.DataBind();
ddState.SelectedValue = null;
ddState.SelectedIndex = 0;
ddCity.Items.Clear();
ddCity.SelectedValue = null;
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
}
}

protected void ddState_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
con = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString);
cmd = new SqlCommand("Select * from tblCities where Stateid=@id", con);
cmd.Parameters.AddWithValue("@id", ddState.SelectedValue.ToString());
con.Open();
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);

if (dt.Rows.Count > 0)
{
DataRow dr = dt.NewRow();
dr["CityId"] = 0;
dr["City"] = "Please select City";
dr["StateId"] = "0";
dt.Rows.InsertAt(dr, 0);
ddCity.DataSource = null;
ddCity.DataSource = dt;
ddCity.DataTextField = "City";
ddCity.DataValueField = "CityId";
ddCity.DataBind();
ddCity.SelectedValue = null;
ddCity.SelectedIndex = 0;
}
adapter.Dispose();
cmd.Dispose();
con.Close();
}
catch (Exception ex)
{
}
}

protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
if (fileUpload.HasFile)
{
string fileName = Path.GetFileName(fileUpload.PostedFile.FileName);
fileUpload.PostedFile.SaveAs(Server.MapPath("~/ProfileImages/") + fileName);
imgProfile.ImageUrl = "~/ProfileImages/" + fileName;
}
}
catch (Exception ex)
{
lblInfo.Text = "Image upload: " + ex.Message.ToString();
}
}
}
}

Below is the Database script (MS SQL Database):

--Create database [ESource]
USE [ESource]

CREATE TABLE [dbo].[tblStates](
[StateId] [int] IDENTITY(1,1) NOT NULL,
[State] [nvarchar](50) NOT NULL,
[CountryId] [int] NOT NULL
)

CREATE TABLE [dbo].[tblCities](
[CityId] [int] IDENTITY(1,1) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[StateId] [int] NOT NULL
)

CREATE TABLE [dbo].[tblCountries](
[CountryId] [int] IDENTITY(1,1) NOT NULL,
[Country] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[tblDepartments](
[DeptId] [int] IDENTITY(1,1) NOT NULL,
[Department] [nvarchar](50) NOT NULL
)

CREATE TABLE [dbo].[tblUserDetails](
[EId] [int] IDENTITY(1,1) NOT NULL,
[EmailId] [nvarchar](50) NOT NULL,
[UserId] [nvarchar](50) NULL,
[Passcode] [nvarchar](50) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[DOB] [datetime] NOT NULL,
[Address] [nvarchar](max) NOT NULL,
[City] [nvarchar](50) NOT NULL,
[Pincode] [int] NOT NULL,
[State] [nvarchar](50) NOT NULL,
[Country] [nvarchar](50) NOT NULL,
[Department] [nvarchar](50) NOT NULL,
[DOJ] [datetime] NOT NULL,
[ProfilePic] [nvarchar](max) NOT NULL,
[UserRole] [nvarchar](50)
)

CREATE procedure [dbo].[sp_AddUsers]
(
@UserId nvarchar(50),
@Passcode nvarchar(50),
@Name nvarchar(50),
@DOB datetime,
@Address nvarchar(max),
@City int,
@Pincode nvarchar(50),
@State int,
@Country int,
@Department int,
@DOJ datetime,
@ProfilePic nvarchar(max),
@UserRole nvarchar(50),
@EmailId nvarchar(50)
)
as begin
Insert into tblUserDetails
(UserId,Passcode,Name,DOB,Address,City,Pincode,State,Country,Department,DOJ,ProfilePic,UserRole,EmailId)
values
(@UserId,@Passcode,@Name,@DOB,@Address,@City,@Pincode,@State,@Country,@Department,@DOJ,@ProfilePic,@UserRole,@EmailId);
end
GO

Recently, I worked on a project to hide sensitive data. Basically, the client wanted sensitive data to be encrypted, then accessed and decrypted by the application only. In case a hacker or an employee or a DBA accesses data directly, they can't read the field. Some perfect examples are customer's credit card number, date of birth, social security, or even medical records.

SQL Server provides a feature that allows DBAs and data developers to encrypt and save encrypted data on a column level. Once a column is encrypted, it's not readable by humans.

In this blog, let's see how this can be acheieved.
In the below example, I have used the Credit Card Number column to be encrypted.

You need to write a stored procedure to execute a set of statements and queries. Though it is not a foolproof way to encrypt or decrypt at the database level, while working on this task, I learned some good techniques and features of SQL Server.

There are 3 major factors to encrypt data at the column level, as below.

• Master Key -  a key which is used to protect the keys of certificates and symmetric keys in the database
• Certificates - used to encrypt the data in the database
• Symmetric Key - can be encrypted by using many options, like certificate, password, symmetric key. There are different algorithms available for encrypting a key. The supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

So, let's start step by step and achieve the encryption and decryption.

Step 1
Create a Master Key first with the help of the below script.
use TestingDB; //This is the Test Database created.  
Create master key encryption by password ='abc123' 

Step 2
Once the Master Key is created, now it's time to create a Certificate.
Create certificate C1 with subject = 'Credit Card Data'

Step 3
Now with the help of certificate and master key create SYMMETRIC KEY.
Create symmetric key SK1 with algorithm = AES_256 encryption by certificate C1.
Once all these KEYs are created in the database, we can use those for encrypting and decrypting data.

Below is the script to encrypt the data in the column. Here I have created one TABLE named TestEncryption having 3 columns with its datatype as below. Note that the column in which we want to insert or update encrypted data should have VARBINARY as the datatype.

Id - INT
EncryptedCCNumber - varbinary (256)
CCNumber - Numeric(18,0)

Let's insert data in the column of the table,
Open symmetric key SK1
Decryption by certificate C1
insert into TestEncryption(Id, EncryptedCCNumber, CCNumber) values (1, ENCRYPTBYKEY(key_guid('SK1'),'5000'), '5000')

Close symmetric key SK1
Now it's time to check if the data is encrypted or not so when you execute a simple query you will get the data from the Table as it is.
select * from TestEncryption 

IF you want to DECRYPT the data you can use the below script
Open symmetric key SK1
Decryption by certificate C1
select *, convert(varchar, DECRYPTBYKEY(EncryptedCCNumber)) as 'Decrypted CC Number' from TestEncryption

Close symmetric key SK1
You will get an extra column named "Decrypted CC Number". This way we can insert/update and select the encrypted data from the table in SQL Server.

What a View is
A view is a virtual table in the database whose contents are defined by a query.
A view appears just like a real table, with a set of named columns and rows of data. Unlike a real table, a view does not exist in the database as a stored set of data values. Instead, the rows and columns of data are generated from the query results defined by the View.

Types of Views in SQL Server
System Views

• Information Schema View.
• Catalog view
• Dynamic Management View (DMV)

User Defined Views

• Simple View
• Complex View

create table Authors 
( 
AuthordId int, 
AuthorName varchar(Max), 
Article varchar (Max), 
AuthorRank int 
) 
-------Inserting Data into Authors Table---- 
Insert into Authors values (1,'Mahesh Chand','C# fundamentals',1) 
Insert into Authors values (2,'PraveenKumar','Wpf',20) 
Insert into Authors values (3,'Dhananjaykumar','windowsApplication',3) 
Insert into Authors values (4,'PinalDeve','SqlTrace',4) 
Insert into Authors values (5,'Abhinav','oops',2) 
Insert into Authors values (6,'Abhijit','WCF',5) 
Insert into Authors values (7,'Amit','DatabaseMirroring',7) 
Insert into Authors values (8,'Karthik','ssis',8) 
Insert into Authors values (9,'Divya','WebApi',9) 

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

System Views

System Views are predefined Views that already exist in the Master database of SQL Server. These System Views are used as template Views for all newly created databases. These system Views will be automatically created for any user defined database.

Information Schema View
The Information Schema Views are used to display information of a database, such as tables and columns. In SQL Server there are nearly twenty different Information Schema Views.
Example: To determine the complete information of an Authors table using an Information Schema View:
---To see the detail information of Authors Table 
Select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Authors' 

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME
C#Corner dbo Authors AuthordId
C#Corner dbo Authors AuthorName
C#Corner dbo Authors Article
C#Corner dbo Authors AuthorRank

Catalog view
Catalog Views are used to show database self-describing information.
Catalog views are also used to return information that is used by the SQL Database Engine like objects, logins permissions and so on.

Example:

Dynamic Management View (DMV)
DMVs are introduced in SQL Server 2005.
DMVs gives the database administration information about the current state of SQL Server machine on various aspects.
DMVs are easier to detect the health of SQL Server using these views.
DMVs replace many of the DBCC Commands.
All Dynamic Management Views (DMVs) exist in the sys schema and follow this naming convention dm_*.
There are two types of DMVs.

1. Server-scoped DMV
Server-scoped DMVs are Stored in Master Database.
Server-scoped DMVs are used to for the state of an entire SQL Server instance.
Server-scoped DMVs require VIEW SERVER STATE PERMISSION on the server.

2. Database-scoped DMV
Database-scoped DMVs are stored specific to each database.
Database-scoped DMVs require DATABASE STATE PERMISSION on the database.

The following are some of the Dynamic Management Views:
Sys.dm_exec_Cached_plans: Returns the information about query Execution Plans that are cached by SQL-SERVER for faster query execution.
Sys.dm_exec_Query_plan: Returns the show plan in XML format for a T-SQL batch.
Sys.dm_exec_Query_stats: Returns aggregate performance statistics for Cached Query Plans.
Sys.dm_exec_requests: Returns information about each request that is executing within SQL Server.

User Defined Views
These are the views that are defined by the user as per the their requirements.

Simple view
A simple view is one that can be addressed by DML statements as well as SELECT. As might be expected, simple views are based on relatively simple SELECT statements.
It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted data in the table the view was created with. It should also be noted that as data in the original table data changes.
A simple view can be created from a single table.
A simple view does not contain functions.
A simple view does not contain a group of data.

Complex View
We use Complex Views when we want to display data from two or more tables, using a group cluase or a grouping of aggregate functions.
A Complex View can be created from one or more table.
A Complex View contains functions.
A Complex View contains a group of data,

Creating Views
We can create views in 2 ways.

1. Through Query Designer.
2. Through Database

Through Query Designer
Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.
Syntax:
Create view view_Name
As
....Ur Query.....

Example:
Create view Authors Info

As
Select AuthorName,Article,Rank from Authors

Using SQL Server Management Studio

To create a view using the Query and View Designers:

1. In Object Explorer, expand the database where you want to create your new view.
2. Right-click the Views folder, then click New View.
3. In the Add Table dialog box, select the table that you want to include in your new view from one of the following tabs: Tables, Views, Functions, and Synonyms.
4. Click Add, then click Close.
5. In the Query Design Pane, select the columns or other elements to include in the new view.
6. In the Criteria Pane, select additional sort or filter criteria for the columns.
7. On the File menu, click Save view name.
8. In the Choose Name dialog box, enter a name for the new view and click OK.