From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it's integer (int) data type, then jump value is a thousand and if huge integer (bigint), then jump value is ten thousand. From our application point of view, this increment isn't acceptable for all the business cases especially once the value shows to the client. this is the special case/issue ships with only SQL Server 2012 and older versions don't have any such issue.

A few days ago, our QA Engineer claims that one of our table's identity column jumped ten thousand. Which means the last identity value of that table was 2200 now it's 12001. In our business logic is like that the value shows to the client and it will not be accepted by the client. So we must solve the issue.
Using the Code

The first time, we all are surprised and confused on how it's possible? we usually don't insert any value in identity column (insert value to identity column is possible). The identity value is maintained by SQL Server itself. one of our core team members started investigation the issue and found out the solution. Now, i want to elaborate the issue and solution that was found out by my colleague.

Now, you need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column with the following code:
create table MyTestTable(Id int Identity(1,1), Name varchar(255));

Now insert 2 rows there:
insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson');

You see the result:
SELECT Id, Name FROM MyTestTable;

The result is as expected. Now just restart your SQL Server service. There are various ways in which you can do it. We did it from SQL Server management studio.

Now, insert another 2 rows to the same table again:
insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');

Now, you can see the result:
SELECT Id, Name FROM MyTestTable;

Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000.

Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.
Solutions

If we are not interested in this so called feature, then we can do two things to stop that jump.
    Using Sequence
  Register -t272 to SQL Server Startup Parameter

Using Sequence
First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:
CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom');

   insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson');

How to Register -t272 to SQL Server Startup Parameter?
First, open the SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference as you can see on the following picture:

If too many tables contain identity column to your database and all contain existing values, then it is better to go for solution 2. Because it is a very simple solution and its scope is server wise. This means if you add SQL Server 2012 parameter -t272 there, then it will affect all your databases there. If you want to create a new database and you need auto generated number field, then you can use solution 1, that means use sequence value to a column instead of auto Identity value. There are so many articles you can find online about when you will use auto identity column when using sequence and advantages/disadvantages of each other. I hope you will read all those and take the appropriate decision.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn't trust you, you need to say "OK, I know what I'm doing, now let me do my work." This problem occurs when the Prevent saving changes that require the table re-creation option is enabled, and you make one or more of the following changes to the table:

• You change the Allow Nulls setting for a column.
• You reorder columns in the table.
• You change the column data type.
• You add a new column.

When you change a table so that you alter the metadata structure of the table, and then you save the table, the table must be re-created based on these changes. This may result in the loss of metadata and in a direct loss of data during the re-creation of the table. If you enable the Prevent saving changes that require the table re-creation option in the Designer section of the SQL Server Management Studio (SSMS) Options window, you receive the error message that is mentioned in the “Symptoms” section.

To change the Prevent saving changes that require the table re-creation option, follow these steps:

• Open SQL Server Management Studio (SSMS).
• On the Tools menu, click Options.
• In the navigation pane of the Options window, click Designers.
• Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.

Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.

Risk of turning off the “Prevent saving changes that require table re-creation” option

Although turning off this option can help you avoid re-creating a table, it can also lead to changes being lost. For example, suppose that you enable the Change Tracking feature in SQL Server to track changes to the table. When you perform an operation that causes the table to be re-created, you receive the error message that is mentioned in the “Symptoms” section. However, if you turn off this option, the existing change tracking information is deleted when the table is re-created. Therefore, we recommend that you do not work around this problem by turning off the option.

To determine whether the Change Tracking feature is enabled for a table, follow these steps:

• In SQL Server Management Studio, locate the table in Object Explorer.
• Right-click the table, and then click Properties.
• In the Table Properties dialog box, click Change Tracking.

If the value of the Change Tracking item is True, this option is enabled for the table. If the value is False, this option is disabled.

When the Change Tracking feature is enabled, use Transact-SQL statements to change the metadata structure of the table.

Steps to reproduce the problem

• In SQL Server Management Studio, create a table that contains a primary key in the Table Designer tool.
• Right-click the database that contains this table, and then click Properties.
• In the Database Properties dialog box, click Change Tracking.
• Set the value of the Change Tracking item to True, and then click OK.
• Right-click the table, and then click Properties.
• In the Table Properties dialog box, click Change Tracking.
• Set the value of the Change Tracking item to True, and then click OK.
• On the Tools menu, click Options.
• In the Options dialog box, click Designers.
• Click to select the Prevent saving changes that require table re-creation check box, and then click OK.
• In the Table Designer tool, change the Allow Nulls setting on an existing column.
• Try to save the change to the table.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

It is not smart to execute multiple db request for loading single page.  Review your database code to see if you have got request paths that go to the database quite once. each of these round-trips decreases the number of requests per second your application will serve. By returning multiple resultsets in a single database request, you can cut the total time spent communicating with the database.

In order to enhance performance you should execute single keep proc and bring multiple resultset in to single dB request. In this article i will explain you how to avoid multiple database request and how to bring multiple resultset into single dB request. Consider a scenario of loading a Product Page, which displays:
Product information
Product Review information.

In order to bring two information request in single dB request, your sql server keep proc ought to be declared as below.

SQL Server Stored Proc

CREATE PROCEDURE GetProductDetails
 @ProductId bigint,
AS
SET NOCOUNT ON

--Product Information
Select ProductId,
 ProductName,
 ProductImage,
 Description,
 Price
From Product
Where ProductId = @ProductId


--Product Review Information
Select  ReviewerName,
 ReviewDesc,
 ReviewDate
From ProductReview
Where ProductId = @ProductId

Asp.net, C# Code to bring multiple db request into single db request
Code Inside Data Access Class Library (DAL)
public DataSet GetProductDetails()
{
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = "GetProductDetails";
cmdToExecute.CommandType = CommandType.StoredProcedure;
DataSet dsResultSet = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(cmdToExecute);

try
{
    var conString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"];
    string strConnString = conString.ConnectionString;
    SqlConnection conn = new SqlConnection(strConnString);

    cmdToExecute.Connection = conn;

    cmdToExecute.Parameters.Add(new SqlParameter("@ ProductId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 19, 0, "", DataRowVersion.Proposed, _productId));

    //Open Connection
    conn.Open();

    // Assign proper name to multiple table
    adapter.TableMappings.Add("Table", "ProductInfo");
    adapter.TableMappings.Add("Table1", "ProductReviewInfo");
    adapter.Fill(dsResultSet);

    return dsResultSet;             
}
catch (Exception ex)
{
    // some error occured.
    throw new Exception("DB Request error.", ex);
}
finally
{
    conn.Close();
    cmdToExecute.Dispose();
    adapter.Dispose();
}
}

Code Inside Asp.net .aspx.cs page
protected void Page_Load(object sender, EventArgs e)
{
   if (Request.QueryString[ProductId] != null)
   {
      long ProductId = Convert.ToInt64(Request.QueryString[ProductId].ToString()); 
  
      DataSet dsData = new DataSet();

      //Assuming you have Product class in DAL
      ProductInfo objProduct = new ProductInfo();
      objProduct.ProductId = ProductId;
      dsData = objProduct.GetProductDetails();

      DataTable dtProductInfo = dsData.Tables["ProductInfo"];
      DataTable dtProductReviews = dsData.Tables["ProductReviewInfo"];

      //Now you have data table containing information
      //Make necessary assignment to controls
      .....
      .....
      .....
      .....
      ..... 

    }
}

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article I am going to explain how to save the youtube into database and display it in Gridview using Linq. I have got a requirement to save the youtube video URL into database and display them where user can play the videos. I have created a table Tb_videos which store the information video tile, description and URL as you can see on the following :

HTML Markup:
<table>  
<tr>
<td>Title :</td>
 <td>
     <asp:TextBox ID="txttitle" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Description :</td>
 <td><asp:TextBox ID="txtdescription" runat="server" TextMode="MultiLine" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Video URL :</td>
 <td><asp:TextBox ID="txturl" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td></td>
 <td>
     <asp:Button ID="btnsubmit" runat="server" Text="Submit" /></td>
 </tr>
</table>
<br />
    <asp:GridView ID="grdvideo" runat="server" AutoGenerateColumns="False"
        BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
        CellPadding="4" ForeColor="Black" GridLines="Vertical">
        <AlternatingRowStyle BackColor="White" />
    <Columns>
    <asp:BoundField DataField="Title" HeaderText="Title" />
    <asp:BoundField DataField="Description" HeaderText="Description"/>
   <asp:TemplateField HeaderText="Videos">
   <ItemTemplate>
   <object  width="480" height="385">
<embed src='<%#Eval("url") %>' type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="480" height="385">
</embed>
</object> 
   </ItemTemplate>
   </asp:TemplateField>
    </Columns>
        <FooterStyle BackColor="#CCCC99" />
        <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
        <RowStyle BackColor="#F7F7DE" />
        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#FBFBF2" />
        <SortedAscendingHeaderStyle BackColor="#848384" />
        <SortedDescendingCellStyle BackColor="#EAEAD3" />
        <SortedDescendingHeaderStyle BackColor="#575357" />
    </asp:GridView>

Import the namespace
C#:
using System.Text.RegularExpressions;

VB:
Imports System.Text.RegularExpressions

Create the object of DBML:
C#:
BlogDataContext db = new BlogDataContext();

VB:
Private db As New BlogDataContext()

Get the Youtube video id
Write a function to get the youtube video id from youtube video URL .

C# Code:
private string GetYouTubeVideoID(string youTubeVideoUrl)
{
    var regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*",
                       RegexOptions.IgnoreCase);
    if (regexMatch.Success)
    {
        return "http://www.youtube.com/v/" + regexMatch.Groups[1].Value +"&hl=en&fs=1";
    }
    return youTubeVideoUrl;
}

VB Code:
Private Function GetYouTubeVideoID(ByVal youTubeVideoUrl As String) As String
    Dim regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*", RegexOptions.IgnoreCase)
    If regexMatch.Success Then
        Return "http://www.youtube.com/v/" + regexMatch.Groups(1).Value + "&hl=en&fs=1"
    End If
    Return youTubeVideoUrl
End Function

Save the record to database
On button click write the below given code to insert the record into database table.

C# Code:
protected void btnsubmit_Click(object sender, EventArgs e)
{
    try
    {
        string ytFormattedVideoUrl = GetYouTubeVideoID(txturl.Text);
        Tb_Video tb = new Tb_Video();
        tb.Title = txttitle.Text;
        tb.Description = txtdescription.Text;
        tb.URL = ytFormattedVideoUrl;
        db.Tb_Videos.InsertOnSubmit(tb);
        db.SubmitChanges();
        Response.Write("<script>alert('Record Inserted Successfully');</script>");
        BindGrid();
        Clear();
    }
    catch (Exception ex)
    { }
}
public void Clear()
{
    txturl.Text = string.Empty;
    txtdescription.Text = string.Empty;
    txttitle.Text = string.Empty;
}

VB Code:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
    Try
        Dim ytFormattedVideoUrl As String = GetYouTubeVideoID(txturl.Text)
        Dim tb As New Tb_Video()
        tb.Title = txttitle.Text
        tb.Description = txtdescription.Text
        tb.URL = ytFormattedVideoUrl
        db.Tb_Videos.InsertOnSubmit(tb)
        db.SubmitChanges()
        Response.Write("<script>alert('Record Inserted Successfully');</script>")
        BindGrid()
        Clear()
    Catch ex As Exception
    End Try
End Sub
Public Sub Clear()
    txturl.Text = String.Empty
    txtdescription.Text = String.Empty
    txttitle.Text = String.Empty
End Sub

Fetch the record from database and display
Create a method to get the record from database table and display in Gridview data control.

C# Code:
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

public void BindGrid()
{
    try
    {
        var bind = from v in db.Tb_Videos
                   select v;
        grdvideo.DataSource = bind;
        grdvideo.DataBind();
    }
    catch (Exception ex)
    {
    }
}

VB Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindGrid()
    End If
End Sub

Public Sub BindGrid()
    Try
        Dim bind = From v In db.Tb_Videos
        grdvideo.DataSource = bind
        grdvideo.DataBind()
    Catch ex As Exception
    End Try
End Sub

Build the project and run. To test the application inserts a record into database.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article I am going to explain how to save the youtube into database and display it in Gridview using Linq. I have got a requirement to save the youtube video URL into database and display them where user can play the videos. I have created a table Tb_videos which store the information video tile, description and URL as you can see on the following :

HTML Markup:
<table>  
<tr>
<td>Title :</td>
 <td>
     <asp:TextBox ID="txttitle" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Description :</td>
 <td><asp:TextBox ID="txtdescription" runat="server" TextMode="MultiLine" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td>Video URL :</td>
 <td><asp:TextBox ID="txturl" runat="server" Width="450px"></asp:TextBox></td>
</tr>
<tr>
<td></td>
 <td></td>
</tr>
   <tr>
<td></td>
 <td>
     <asp:Button ID="btnsubmit" runat="server" Text="Submit" /></td>
 </tr>
</table>
<br />
    <asp:GridView ID="grdvideo" runat="server" AutoGenerateColumns="False"
        BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
        CellPadding="4" ForeColor="Black" GridLines="Vertical">
        <AlternatingRowStyle BackColor="White" />
    <Columns>
    <asp:BoundField DataField="Title" HeaderText="Title" />
    <asp:BoundField DataField="Description" HeaderText="Description"/>
   <asp:TemplateField HeaderText="Videos">
   <ItemTemplate>
   <object  width="480" height="385">
<embed src='<%#Eval("url") %>' type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" width="480" height="385">
</embed>
</object> 
   </ItemTemplate>
   </asp:TemplateField>
    </Columns>
        <FooterStyle BackColor="#CCCC99" />
        <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
        <RowStyle BackColor="#F7F7DE" />
        <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
        <SortedAscendingCellStyle BackColor="#FBFBF2" />
        <SortedAscendingHeaderStyle BackColor="#848384" />
        <SortedDescendingCellStyle BackColor="#EAEAD3" />
        <SortedDescendingHeaderStyle BackColor="#575357" />
    </asp:GridView>

Import the namespace
C#:
using System.Text.RegularExpressions;

VB:
Imports System.Text.RegularExpressions

Create the object of DBML:
C#:
BlogDataContext db = new BlogDataContext();

VB:
Private db As New BlogDataContext()

Get the Youtube video id
Write a function to get the youtube video id from youtube video URL .

C# Code:
private string GetYouTubeVideoID(string youTubeVideoUrl)
{
    var regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*",
                       RegexOptions.IgnoreCase);
    if (regexMatch.Success)
    {
        return "http://www.youtube.com/v/" + regexMatch.Groups[1].Value +"&hl=en&fs=1";
    }
    return youTubeVideoUrl;
}

VB Code:
Private Function GetYouTubeVideoID(ByVal youTubeVideoUrl As String) As String
    Dim regexMatch = Regex.Match(youTubeVideoUrl, "^[^v]+v=(.{11}).*", RegexOptions.IgnoreCase)
    If regexMatch.Success Then
        Return "http://www.youtube.com/v/" + regexMatch.Groups(1).Value + "&hl=en&fs=1"
    End If
    Return youTubeVideoUrl
End Function

Save the record to database
On button click write the below given code to insert the record into database table.

C# Code:
protected void btnsubmit_Click(object sender, EventArgs e)
{
    try
    {
        string ytFormattedVideoUrl = GetYouTubeVideoID(txturl.Text);
        Tb_Video tb = new Tb_Video();
        tb.Title = txttitle.Text;
        tb.Description = txtdescription.Text;
        tb.URL = ytFormattedVideoUrl;
        db.Tb_Videos.InsertOnSubmit(tb);
        db.SubmitChanges();
        Response.Write("<script>alert('Record Inserted Successfully');</script>");
        BindGrid();
        Clear();
    }
    catch (Exception ex)
    { }
}
public void Clear()
{
    txturl.Text = string.Empty;
    txtdescription.Text = string.Empty;
    txttitle.Text = string.Empty;
}

VB Code:
Protected Sub btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsubmit.Click
    Try
        Dim ytFormattedVideoUrl As String = GetYouTubeVideoID(txturl.Text)
        Dim tb As New Tb_Video()
        tb.Title = txttitle.Text
        tb.Description = txtdescription.Text
        tb.URL = ytFormattedVideoUrl
        db.Tb_Videos.InsertOnSubmit(tb)
        db.SubmitChanges()
        Response.Write("<script>alert('Record Inserted Successfully');</script>")
        BindGrid()
        Clear()
    Catch ex As Exception
    End Try
End Sub
Public Sub Clear()
    txturl.Text = String.Empty
    txtdescription.Text = String.Empty
    txttitle.Text = String.Empty
End Sub

Fetch the record from database and display
Create a method to get the record from database table and display in Gridview data control.

C# Code:
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid();
    }
}

public void BindGrid()
{
    try
    {
        var bind = from v in db.Tb_Videos
                   select v;
        grdvideo.DataSource = bind;
        grdvideo.DataBind();
    }
    catch (Exception ex)
    {
    }
}

VB Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindGrid()
    End If
End Sub

Public Sub BindGrid()
    Try
        Dim bind = From v In db.Tb_Videos
        grdvideo.DataSource = bind
        grdvideo.DataBind()
    Catch ex As Exception
    End Try
End Sub

Build the project and run. To test the application inserts a record into database.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this tutorial, I will explain how to use concat function in SQL 2016. CONCAT function is also known as T-SQL Function which was introduced in SQL Server 2012. This function allows us to concatenate two or more parameters values together and these parameters values should be separated by comma. Before release of Concat function, we used to use the “+” operator to combine or concatenate two or more string values. The most important feature of Concat function is that it also takes care of data type conversion and beautifully handles NULL on its own. In case of Concat function, we don't need to care about the null values in the parameters which are going to be used.

Interesting features

1. CONCAT takes a variable number of string arguments and concatenates them into a single string.

2. It requires at least two input values; otherwise, an error is raised.

3. All arguments are implicitly converted to string types and then concatenated.

4. Best part of this; Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.

5. The implicit conversion to strings follows the existing rules for data type conversions. 

6. If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

Examples:

Concatenate Space Characters with input variables

To understand the features of Concat function, we are showing the examples with Concat function and without the concat functions as given below:

In the above example, we are using the two variables to concatenating together. You can see that, we are getting the same outputs. 

Concatenate Space Characters with with NULL values

Now, we are going to make some interesting changes in the above example by setting the null value in the second variable as given below:

You can easily see that full name is showing NULL values in case of without Concat function but by using Concat function, full name is showing because of Null values are implicitly converted to an empty string.

Concatenate number and string together

If you want to concatenate string with numeric value then you should need to convert the numeric value into the string as given below:

If you want to concatenate two numeric values then there is no need to change their data types because of implicit conversion to strings follows the existing rules for data type conversions.

Conclusion

The CONCAT function appends one string to the end of another string and does not require ISNULL for converting NULL into an empty string. All arguments are implicitly converted to string types and then concatenated.

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

When reporting Services reports are shown with ASP.NET Report Viewer control, one of the common requirements for exporting facility is, limiting it to few output formats. By default Export drop-down contains 7 output formats. If we need to limit for 1-2 output formats, one way is, hide the ExportControl and implement it with our own code. Here is the way of implementing it;

Here could be a sample screen for a ASP.NET page with reporting Services report. Note that ExportControl is hidden in the toolbar and drop-down is added to show output formats for exporting.

Write the code below for Page_Load.

protected void Page_Load(object sender, EventArgs e)
{
    ReportViewer1.ShowExportControls = false;
    ReportViewer1.ProcessingMode = ProcessingMode.Remote;
    // this can be set with control itself.
    //ReportViewer1.ServerReport.ReportServerUrl = new Uri(@"http://localhost/reportserver");
    //ReportViewer1.ServerReport.ReportPath = @"/Report Project1/Report2";
    if (!IsPostBack)
    {
        DropDownList1.Items.Add(new ListItem("Word", "Word"));
        DropDownList1.Items.Add(new ListItem("Excel", "Excel"));
        DropDownList1.Items.Add(new ListItem("Acrobat (PDF) file", "PDF"));
    }

}

Now, write the following code for Button-Click.
protected void Button1_Click(object sender, EventArgs e)
{
    string mimeType;
    string encoding;
    string fileNameExtension;
    string[] streams;
    Warning[] warnings;
    byte[] bytes = ReportViewer1.ServerReport.Render(DropDownList1.SelectedValue, null, out mimeType, out encoding, out fileNameExtension, out streams, out warnings);
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ContentType = mimeType;
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=SalesReport." + fileNameExtension);
    HttpContext.Current.Response.BinaryWrite(bytes);
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();
}

HostForLIFE.eu ReportViewer Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, we will see about fixed database roles in SQL Server. As the name suggests, fixed database roles cannot be removed or modified and performs specific administrative tasks as it has pre-defined set of permissions. You need to be very careful while assigning these roles and assign these roles only when there is a serious requirements. You can execute the sp_helpdbfixedrole system procedure to get the list of fixed database roles.

EXEC sp_helpdbfixedrole
GO

• db_owner
• db_accessadmin
• db_securityadmin
• db_ddladmin
• db_backupoperator
• db_datareader
• db_datawriter
• db_denydatareader
• db_denydatawriter

You can navigate to Database Roles from Object Explorer –> Expand Databases Node –> Select Database –> Navigate to Security –> Roles –> Database Roles

db_owner

All the members of db_owner fixed database roles can perform all the maintenance activities and setting configurations. This role should not be given to regular users and should be assigned very carefully as it can perform almost all the operations in a database.

db_accessadmin

As the name suggests, all the members of db_accessadmin can handle access related issues. This role controls security, grants access, revokes access for logins to enter database. This role is rarely used as these operations are performed by DBAs as he has relevant fixed server role.

db_securityadmin

Members of db_securityadmin role manages all the permissions and security related activities. As database administrators usually manages security, permissions, role membership etc. so this role is hardly assigned and used. You should not assign this role to regular users.

db_ddladmin

If any member is assigned with db_ddladmin role then he can perform all DDL operations and can execute, create, drop and alter any objects. Normally this role is assigned to developers to perform the related operations in application. This role is usually not assigned to regular users as he can misuse the DDL operations.

db_backupoperator

db_backupoperator role can perform the database backup operations. This role is rarely used as backup activity is a role of database administrator and he has much higher permissions rather than using this specific fixed database role.

db_datareader

If any user is requesting for SELECT permission on database tables then you can tag him with db_datareader fixed database role as this role allows a member to perform SELECT operations on database tables and views and tagged member will not be able to modify any object. This role is mostly assigned to developers and regular users who need table access on production database.

db_datawriter

All the members of db_datawriter fixed database role can perform INSERT, UPDATE, DELETE operations on all tables and views in respective database. This role is basically assigned to developers to perform operations on QA servers. Testing applications sometimes require this role.

db_denydatareader

As the name suggests this role doesn’t allow to read data from tables in a database. So user will not be able to perform SELECT operations on a table or views. I haven’t used this role any time and also never seen members assigned with this role.

db_denydatawriter

This role is basically opposing db_datawriter role which means members with this role will not be able to perform INSERT, UPDATE or DELETE operations on tables and views. Again as a DBA I have not used this role yet and never seen anyone using this role.

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

In this tutorial, I will explain about how to make time converter. The time converter converts the most common time units, second, hour, minute, day, week, month, year and century. Second is the SI base unit of time and officially defined as the duration of 9 192 631 770 periods of the radiation corresponding to the transition between the two hyperfine levels of the ground state of the caesium 133 atom.

We have an integer number which represents seconds (secs) part of the time. We need to convert this integer number into year, month, days, hour, minute and second parts. Let me explain with the help of the examples to make it clear.

Examples:

100 Seconds – 0 year(s) 0 month(s) 0 day(s) 0 hour(s) 1 minute(s) 40 second(s)

3600 Seconds – 0 year(s) 0 month(s) 0 day(s) 1 hour(s) 0 minute(s) 0 second(s)

86400 Seconds – 0 year(s) 0 month(s) 1 day(s) 0 hour(s) 0 minute(s) 0 second(s)

2678400 Seconds – 0 year(s) 1 month(s) 0 day(s) 0 hour(s) 0 minute(s) 0 second(s)

35110011 Seconds – 1 year(s) 1 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

etc.

Convert number into year, month, day, hour, minute and second

Below is the code to convert the input integer number into year, month, day, hour, minute and second as above:

DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEPART(YEAR, @VARDT) - 1900 AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Or, we can use the below code.


DECLARE @VARDT DATETIME = DATEADD(SECOND, 35110011, 0)
SELECT CAST(DATEDIFF(YEAR, 0, @VARDT) AS VARCHAR(10)) + ' year(s) ' + CAST(DATEPART(MONTH, @VARDT) - 1 AS VARCHAR(2)) + ' month(s) '
 + CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

I have added the given integer value in second part of the default datetime value (‘1900-01-01 00:00:00.000’) of SQL Server to generate a datetime value. Once we have a datetime data type, we can easily apply the DATEPART function to extract the required parts from that. In this scenario SQL Server automatically takes care of the date time conversions. Finally, I have subtracted the respective date time parts from the generated datetime data type value from their respective parts in the final SELECT statement wherever required.

In second logic, I have just used a DATEDIFF function for the first step computation which can take care of all the conversions specially in below scenarios smoothly. For example, we don’t need to worry about to year to month, month to day and day to hour conversion. We just need to use DATEDIFF function for the first part.

Convert number into month, day, hour, minute and second

SELECT CAST((DATEDIFF(MONTH, 0, @VARDT)) AS VARCHAR(10)) + ' month(s) '
+ CAST(DATEPART(DD, @VARDT) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
+ CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

13 month(s) 10 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into day, hour, minute and second

SELECT CAST((DATEDIFF(DD, 0, @VARDT)) AS VARCHAR(10)) + ' day(s) '
 + CAST(DATEPART(HOUR, @VARDT) AS VARCHAR(2)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

406 day(s) 8 hour(s) 46 minute(s) 51 second(s)

Convert number into hour, minute and second

SELECT CAST(DATEDIFF(HOUR, 0, @VARDT) AS VARCHAR(20)) + ' hour(s) '
 + CAST(DATEPART(MINUTE, @VARDT) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

Output:

9752 hour(s) 46 minute(s) 51 second(s)

Convert number into minute and second

SELECT CAST(DATEDIFF(MINUTE, 0, @VARDT) AS VARCHAR(25)) + ' minute(s) ' + CAST(DATEPART(SECOND, @VARDT) AS VARCHAR(2)) + ' second(s)'

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In this tutorial I will show you Stored Procedure usage with step by step. First of All, you have good knowledge about SQL Server  after that, you will understand as well. Basically, Stored Procedure which is used in SQL Server, SQL Server is the product of Microsoft. Stored Procedure is mostly used with Insert, Update and Delete Data in Your Tables. If you make a table and insert update and delete record a records you have used insert update and delete query simply  again and again but Stored Procedure different. Stored Procedure you make a one time simply after that you don’t use long insert update and delete query. Stored Procedure is very helpful to make a Web Application or Desktop Application with the help of ASP.NET.

How to Make a Stored Procedure

First of All, you make a table, for example, I have made a Table namely Employee and I have inserted five columns ID_Number, Name_in_Full, Designation and Salary in my table you are see below with query and screenshot.
Make a Table in SQL Server
create table employee
(
ID_Number int primary key identity(1,1),
Name_in_Full varchar(50),
Employee_Number varchar(50),
Designation varchar(50),
Salary int
)

I have made a table  with you can see table query and display screen shot, after that I have inserted data in a table.
Insert into employee values
(‘Asif Ali’, ‘5050’, ‘Web Developer’, 250000),
(‘Aamir Ali’, ‘5051’, ‘Businessman’, 250000),
(‘Asadullah’, ‘5052’, ‘Network Engineer’, 150000),
(‘Sameer Ali’, ‘5053’, ‘Accountant’, 450000)

I have made the table and insert a record in the table.

Create Stored Procedure in SQL Server

I have made a Stored Procedure namely CreateProc and I have used same column name with I have already used in a table below you can see complete query and screenshot. 

create procedure createProc
(@Name_in_Full varchar(50),
@Employee_Number varchar(50),
@Designation varchar(50),
@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end

After that I have made complete Stored Procedure after that I have inserted a data in Table via Stored Procedure, complete query and screen shot as below.

Afer Run above query my data is inserted in my table with the help of SP

Alter Stored Procedure in SQL Server

After that I have changed with column data type I have used with Alter Query and change my column DataType, the complete query, and screen shot as below.

alter procedure createProc(@Name_in_Full varchar(50),@Employee_Number nvarchar(50),
@Designation varchar(50),@Salary int)
as
begin
insert into employee values(@Name_in_Full, @Employee_Number, @Designation, @Salary)
end

After that, I have made UpdateProcedure which is used with an update a record with your table with the help of SP, the query and screenshot as below.

Update Stored Procedure in SQL Server

create procedure updateProc
(@ID_Number int,
@Name_in_Full varchar(50),
@Employee_Number nvarchar(50),
@Designation varchar(50),
@Salary int)
as
begin
update employee set Name_in_Full=@Name_in_Full,
Employee_Number=@Employee_Number,
Designation=@Designation, Salary=@Salary where ID_Number=@ID_Number
end

After that, I have update a Record with Table with the help of SP, the complete query, and screenshot as below.

exec updateProc 5, ‘Sadam Hussain’, ‘50504’, ‘Chemical Engineer’, 250000

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to HostForLIFE's customers. They offer a highly redundant, carrier-class architecture, designed around the needs of shared hosting customers.