This article is about Auto Increment ID Column using nvarchar datatype in SQL Server 2016 . SQL server provides identity property to get auto-increment record values where the datatype used is “int”. This article explains a way to use nvarchar datatype column to get auto-increment record values.
e.g. S00001, E00001, INV00001. (Here there are prefix like S, E, INV used to denote the record ids.)

This article provides a solution to use alphanumeric record ids incremented automatically.

Solution
Create an Employee table as below,

Here “ID” column is for storing record id as similar to Serial No of any entry made. “EmployeeNo” column is used to store the alphanumeric auto increment id. “Name” stores the name of the Employee. Create a Windows Form with a TextBox and a Button control

The above figure shows the use of a textbox to enter name in the ”employee” table where the id is auto-incremented.
Code
    Import the NameSpace

Create 2 methods OpenConnection() and CloseConnection() to handle the SQL Server Connection object.
        private void OpenConnection(SqlConnection con) { 
            con.ConnectionString = "Data Source=.; Initial Catalog=demodb; Integrated Security=True"; 
            if (con.State == ConnectionState.Closed) { 
                con.Open(); 
            } 
        } 
        private void CloseConnection(SqlConnection con) { 
            con.Close(); 
        } 

On the Insert button click the id is auto-incremented and the relevant data is inserted in the employee table. (id, employeeno and name)
        SqlConnection con = new SqlConnection(); 
        OpenConnection(con); 
        string id = AutoIncrementID(); 
        int idLimit = 7; 
        string eno = "E" + ZeroAppend("0000000" + id, idLimit); 
        string query = "insert into employee values ('" + id + "', '" + eno + "', '" + txtName.Text + "')"; 
        SqlCommand cmd = new SqlCommand(query, con); 
        int i = cmd.ExecuteNonQuery(); 
        if (i > 0) { 
            MessageBox.Show("Data Added", "Add", MessageBoxButtons.OK, MessageBoxIcon.Information); 
        } 
        CloseConnection(con); 

Here a method AutoIncrementID() is used to do the task of auto-incrementing based on the last entry inserted in the table.
        private string AutoIncrementID() { 
            SqlConnection con = new SqlConnection(); 
            OpenConnection(con); 
            SqlCommand cmd = new SqlCommand("SELECT ISNULL(MAX(ID),0) + 1 from employee", con); 
            SqlDataReader dr = cmd.ExecuteReader(); 
            string id = null; 
            if (dr.Read()) { 
                id = dr[0].ToString(); 
            } 
            CloseConnection(con); 
            return id; 
        } 
        //The function ZeroAppend is used to append “0” after the prefix to the code. 
        public static string ZeroAppend(string data, int idLimit) { 
            return data.Substring(data.Length - idLimit); 
        } 

Output,

HostForLIFE.eu SQL Server 2016 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.

This is something interesting; i.e., how to enforce the compiler to use the inner join instead of the outer join. Let's take an example. We have two tables State_Tab and City_Tab. An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

State_Tab

City_Tab

Now, we write two queries, using outer join, and compare the execution plan of both the queries.

Query 1

Query 2

We can see that both the queries use the left outer join but the second query implements the inner join, instead of outer join, because for "On condition", we define the 1=1, which will be always true, so the query optimizer converts the left outer join to cross join due to the "Where condition". This cross join will convert into the inner join.

HostForLIFE.eu, a leading Windows web hosting provider with innovative technology solutions and a dedicated professional services team, today announced the support for Umbraco 7.5.7 hosting plan due to high demand of Umbraco users in Europe. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market.

HostForLIFE.eu hosts its servers in top class data centers that is located in Amsterdam, (NL), London, (UK), Washington, D.C. (US), Paris, (France), Frankfurt, (Germany), Chennai, (India), Milan, (Italy), Toronto, (Canada) and São Paulo, (Brazil) to guarantee 99.9% network uptime. All data centers feature redundancies in network connectivity, power, HVAC, security and fire suppression. All hosting plans from HostForLIFE.eu include 24×7 support and 30 days money back guarantee. HostForLIFE Umbraco hosting plan starts from just as low as €3.49/month only and this plan has supported ASP.NET Core 1.1, ASP.NET MVC 5/6 and SQL Server 2012/2014/2016.

Umbraco is a fully-featured open source content management system with the flexibility to run anything from small campaign or brochure sites right through to complex applications for Fortune 500's and some of the largest media sites in the world. Umbraco is strongly supported by both an active and welcoming community of users around the world, and backed up by a rock-solid commercial organization providing professional support and tools. Umbraco can be used in its free, open-source format with the additional option of professional tools and support if required.

Umbraco release that exemplifies our mission to continue to make Umbraco a bit simpler every day. The other change is that there's now a "ValidatingRequest" event you can hook into. This event allows you to "massage" any of the requests to ImageProcessor to your own liking. So if you'd want to never allow any requests to change BackgroundColor, you can cancel that from the event. Similarly if you have a predefined set of crops that are allowed, you could make sure that no other crop sizes will be processed than those ones you have defined ahead of time.

Further information and the full range of features Umbraco 7.5.7 Hosting can be viewed here: http://hostforlife.eu/European-Umbraco-757-Hosting

Now, I will show you how to Get The Header Information Of A Table in SQL Server. If someone asks the question, "How do I get the header information of the table in SQL Server?" the first solution that arises in our mind will be "use any condition that never matches," like 1=0 or 2=0. However, in this case, we only get the header information of the table, and no result will return.

It is true that using the above solution, we will not get any data and the result will be the header information of our table.

But this is not the best solution because in this case, SQL Server reads all rows and compares the conditions each time. That increases the turnaround time and resource complexity.

For this, SQL Server provides the "SET FMTONLY ON" keyword. "SET FMTONLY ON" returns only metadata to the client. It can be used to test the format of the response without actually running the query. This method returns header information without performing any read operation.

HostForLIFE.eu SQL Server 2016 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.

Have you ever been in a situation where you've got to clear down some data within a database that's chock filled with constraints but don't desire to wipe out your precious relationships, indices and all that other jazz?

I found myself in a similar situation earlier this week, and required a clear-down script that might wipe out all of the data within a whole database, without being bothered by any existing constraints. Here it is.
    USE @YourTable;  
    EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"  
    EXEC sp_MSForEachTable "DELETE FROM ?"  
    EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"  
    GO  

The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.

Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables.

ALTER TABLE ? NOCHECK CONSTRAINT ALL
This will disable any constraint checking that is present on the table (so, operations like deleting a primary key or a related object won't trigger any errors).

DELETE FROM ?
This will delete every record within the table.

ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
This re-enables the constraint checking, bringing your table back to its original state, sans data.

It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss. While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).

HostForLIFE.eu SQL Server 2016 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, let me show you how to setup Node.js proxy configurations on Windows. It is often required to work with Node.JS and NPM package installer behind the proxy. When trying to install npm packages behind the proxy, you may come across the error, given below:

To resolve exceptions, shown above, http_proxy and https_proxy configurations needs to be done for NPM. The step by step guidance is given below to perform the configuration on Windows machine.

• Go to C:\User\{User} directory.
• If .npmrc file exists then open it in notepad editor else create .npmrcfile.
• Add the lines, given below to the file for your valid AD domain user. proxy=http://domain\\username:password@ip:port
• Add the lines, given below for Non AD user proxy=http://username:password@ip:port
• Save .npmrc file
• Try to use npm install now. It should work without any error.

The sample contents of .npmrc file are shown below:

In this post you will learn how to resolve dotnet not getting installed error in Windows Server 2012. For the first time when I faced this issue, it took a long time for me to troubleshoot it. So when you try to install .NET Framework 3.5 features using Add or Remove features in Server Manager of Windows Server 2012, You may get the following error.

Here are the steps to resolve this issue:

Firstly, mount DVD or ISO image of OS on Windows Server on which you are trying to install .NET Framework 3.5.

Open Server Manager Console, Go to Manage and Add or Remove features and select .NET Framework 3.5 and click next.

And now you will see the following screenshot and at bottom of the screen you can see the option which states “specify an alternate source path”.

After clicking on Specify Alternate Source Path, give the path of DVD or ISO like the following and make sure you give exact path and click OK.

For eg: If your DVD drive is D drive as mine then the path should be the following,

D:\sources\sxs

After clicking OK click install and your installation should get completed without any error.

European leading web hosting provider, HostForLIFE.eu announces the launch of Visual Studio 2017 Hosting

HostForLIFE.eu was established to cater to an underserved market in the hosting industry; web hosting for customers who want excellent service. HostForLIFE.eu - a cheap, constant uptime, excellent customer service, quality, and also reliable hosting provider in advanced Windows and ASP.NET technology. HostForLIFE.eu proudly announces the availability of the Visual Studio 2017 hosting in their entire servers environment.

The smallest install is just a few hundred megabytes, yet still contains basic code editing support for more than twenty languages along with source code control. Most users will want to install more, and so customer can add one or more 'workloads' that represent common frameworks, languages and platforms - covering everything from .NET desktop development to data science with R, Python and F#.

System administrators can now create an offline layout of Visual Studio that contains all of the content needed to install the product without requiring Internet access. To do so, run the bootstrapper executable associated with the product customer want to make available offline using the --layout [path] switch (e.g. vs_enterprise.exe --layout c:\mylayout). This will download the packages required to install offline. Optionally, customer can specify a locale code for the product languages customer want included (e.g. --lang en-us). If not specified, support for all localized languages will be downloaded.

HostForLIFE.eu hosts its servers in top class data centers that is located in Amsterdam (NL), London (UK), Paris (FR), Frankfurt(DE) and Seattle (US) to guarantee 99.9% network uptime. All data center feature redundancies in network connectivity, power, HVAC, security, and fire suppression. All hosting plans from HostForLIFE.eu include 24×7 support and 30 days money back guarantee. The customers can start hosting their Visual Studio 2017 site on their environment from as just low €3.00/month only.

HostForLIFE.eu is a popular online ASP.NET based hosting service provider catering to those people who face such issues. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market.

HostForLIFE.eu offers the latest European Visual Studio 2017 hosting installation to all their new and existing customers. The customers can simply deploy their Visual Studio 2017 website via their world-class Control Panel or conventional FTP tool. HostForLIFE.eu is happy to be offering the most up to date Microsoft services and always had a great appreciation for the products that Microsoft offers.

Further information and the full range of features Visual Studio 2017 Hosting can be viewed here http://hostforlife.eu/European-Visual-Studio-2017-Hosting

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

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft. Their service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, they have also won several awards from reputable organizations in the hosting industry and the detail can be found on their official website.

In this post, I will tell you about the LAG and LEAD functions in SQL Server. These two functions are analytical functions in SQL Server. In actual scenarios we need to analyze the data, for example, comparing previous sales data.

The Lag and Lead functions support the window partitioning and ordering clauses in SQL Server. The Lag and Lead functions do not support the window frame clause.

LAG
The Lag function gives the previous column values based on ordering.

LEAD
The Lead function gives the next column values based on ordering.

Demo
    CREATE TABLE DBO.SALES 
    ( 
    PROD_ID INT , 
    SALES_YEAR INT, 
    SALES_AMOUNT INT  
    ) 

    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2009,10000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2010,9000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2011,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2012,7000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2013,14000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2014,18000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(1,2015,15000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2013,12000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2014,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(2,2015,16000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2012,7000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2013,8000) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2014,9700) 
    INSERT INTO DBO.SALES(PROD_ID,SALES_YEAR,SALES_AMOUNT) VALUES(3,2015,12500) 

    SELECT * FROM DBO.SALES

The following example shows the Previous Year Sales Amount.
SELECT *, LAG(SALES_AMOUNT) OVER(ORDER BY PROD_ID ,SALES_YEAR) [Prevoius Year Sales] FROM DBO.SALES

The following example shows the Next Year Sales Amount.
    SELECT * , 
    LEAD(SALES_AMOUNT) OVER(ORDER BY PROD_ID ,SALES_YEAR) [Next Year Sales]  
    FROM DBO.SALES 

The following example shows the Previous Year Next Year Sales Amount using the partition by clause.
    SELECT *, 
     
       LAG(SALES_AMOUNT) OVER(PARTITION BY PROD_ID ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT) OVER(PARTITION BY PROD_ID ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES     

The following example shows an offset other than 1.

The offset is by default 1. If we want an offset other than 1 then we need to provide 2 argument values in the Lag and Lead functions.
    SELECT * , 
       LAG(SALES_AMOUNT,2)  OVER(ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT,2)  OVER( ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES 

The following example shows replacing the null with various values:

    SELECT * , 
       LAG(SALES_AMOUNT,2,0)  OVER(ORDER BY PROD_ID ,SALES_YEAR) [PREVOIUS YEAR SALES] , 
       LEAD(SALES_AMOUNT,2,0)  OVER( ORDER BY PROD_ID ,SALES_YEAR) [NEXT YEAR SALES]  
    FROM DBO.SALES 

HostForLIFE.eu SQL Server 2016 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 post I will show you how to Clearing Down A Database Full Of Constraints In SQL Server. Have you ever been in a scenario where you have to clear down some data within a database that is chock full of constraints but don't want to wipe out your precious relationships, indices and all that other jazz?

I found myself in a similar situation earlier this week, and needed a clear-down script that would wipe out all of the data within an entire database, without being bothered by any existing constraints. Here it is.

    USE @YourTable;  
    EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"  
    EXEC sp_MSForEachTable "DELETE FROM ?"  
    EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"  
    GO  

What is this doing?
The script itself takes advantage of an undocumented stored procedure within SQL Server called sp_MSForEachTable that will actually iterate through all of the tables within a given database.

Now that we know we are going to be looping through each of the tables within the specified database, let's see what is going to happen to each of the tables.

    ALTER TABLE ? NOCHECK CONSTRAINT ALL
    This will disable any constraint checking that is present on the table (so, operations like deleting a primary key or a related object won't trigger any errors).

    DELETE FROM ?
    This will delete every record within the table.

    ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL
    This re-enables the constraint checking, bringing your table back to its original state, sans data.

Note
It is very important that you properly scope this query to the table that you are targeting to avoid any crazy data loss.

While I don't think that you could just leave that out and execute on master, I wouldn't want to even risk testing that out (although feel free to try it out and let me know if it nukes everything).

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.