European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

European SQL 2016 Hosting - HostForLIFE.eu :: Drop Indexes In Views In SQL Server

clock July 19, 2018 08:56 by author Peter

In this article, I am going to tell about the dropping of indexes in View in SQL Server. Views are virtual tables in which data from one or more tables gives the result set as our SQL table does with rows and columns. A View doesn’t store the data permanently in the database and at the time of execution, only its result set gets determined. When a View contains a large amount of rows and has complex logic in it then we can create an index on a View to improve the query performance. A View consists of a Unique Clustered Index and it is stored in the database as the clustered index does. Now, let’s run a few scenarios to check when Clustered index which is created on a View gets dropped automatically.

First, I will create a table on which I will run those scenarios.
CREATE TABLE[dbo]. [Customer] 
    ( 
        [CustomerID][int] IDENTITY(1, 1) NOT NULL, [CustomerName][varchar](50) NOT NULL, [CEO][varchar](40) NULL, [Phone][varchar](20) NOT NULL PRIMARY KEY CLUSTERED( 
            [CustomerID] ASC)) 
GO 


Now, I will create a View which will use this Customer table.
-- Create view 
Create VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName, CEO 
  from dbo.Customer 
  GO 


Here in the definition of View, I have used WITH SCHEMABINDING which is necessary for creating an index on a View. This option simulates that we cannot delete any of the base table used in the View and in order to make any changes, first, we need to drop or alter the View.

Also, all the table references in a View should have two part naming convention (schemaname.tablename) as we have in vw_Customer view (dbo.Customer).

Now, I will create an index on our View.


Scenario 1

Create index IX_CustomerID  
 ON vw_customer (CustomerID); 
 GO 


Error returned: Cannot create index on view 'vw_customer'. It does not have a unique clustered index. On Views, the first index must be a unique clustered index, so this will throw the error.


So, the first index on View should be UNIQUE CLUSTERD INDEX, else it will throw an error.

Scenario 2
Create unique clustered index IX_CustomerID  
 ON vw_customer (CustomerID) 
 GO 

Now, our Indexed View is created having a clustered index on it. Now, I want to alter my View and add one more column GETDATE() as CurrentDate  in the View definition and alter the View.

Now, alter the View after Scenario 2.

Scenario 3
ALTER  VIEW vw_customer 
 WITH SCHEMABINDING  
AS  
 SELECT  CustomerID, CustomerName , GETDATE() as CurrentDate 
  from dbo.Customer 
GO 

Now, as I have altered the View, I want to create another index on column CustomerName which will be a NonClustered index.

Scenario 4
Create index IX_CustomerName  
 ON vw_customer (CustomerName); 
GO 


Again, I get an error: Cannot create index on view 'vw_customer'. It does not have a unique clustered index.

As we have already created Unique Clustered Index on View, still it gives an error. The interesting thing is that after updating the View, the index that was created in Scenario 2 is dropped and the code will throw the same error as in Scenario 1.

As mentioned here,
"ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view."
Hope you will like this post.

European 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.

 



European SQL 2016 Hosting - HostForLIFE.eu :: Using Window Ranking Function With UPDATE Statement

clock July 17, 2018 07:59 by author Peter

I was working on a scenario in which I needed to assign a unique value to a column. I was trying to update the column using window ranking function ROW_NUMBER() but I got an error that ‘Windowed function can only appear in SELECT or ORDER BY clause’:

Then I did some workaround and used the Windows function indirectly using a CTE (Common Table Expression) for this. I will show you step by step how I accomplished this using CTE.

Let’s first create a table with some test data,
    CREATE TABLE Test 
    ( 
        ID INT, 
        Value VARCHAR(10) NOT NULL 
    ) 
    GO 
     
    INSERT INTO Test (Value) VALUES('Sunday'),('Monday'),('Tuesday'),('Wednesday'),('Thursday'),('Friday'),('Saturday') 
    GO 

As we can see that in column ID NULL values get inserted as we didn’t specify any values for this column during INSERT statement. So, when I tried this UPDATE statement using ROW_NUMBER() with SET I got the following error,
    UPDATE Test 
    SET ID = ROW_NUMBER() OVER(ORDER BY Value) 
    GO 

Then I used CTE to update the ID values in table test in this manner,
    ;WITH CTE AS 
    ( 
        SELECT Value, ROW_NUMBER() OVER(ORDER BY Value) as RN 
        FROM Test 
    ) 
     
    UPDATE T 
    SET ID = RN 
    FROM CTE C JOIN Test T ON T.value = C.Value 


When I ran this SQL code I didn’t get any error and ID column values to get updated with unique values.

European 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.



European SQL 2016 Hosting - HostForLIFE.eu :: Multiple SQL Operation In Single Procedure

clock July 12, 2018 08:14 by author Peter

In this blog, I will show you how to write multiple SQL Statements in one procedure. Instead of writing separate procedures for Select, Insert, Delete and Update operations into the table, I am going to show you how to combine all operations into one single Stored Procedure.

This Table is called tblEmployee with the below structure:
We want to write a procedure for this table for Select, Insert, Delete and Update records.

Instead of writing separate a stored procedure for each operation we are going to write only one stored procedure to perform Select, Insert, Delete and Update records.

How To Achieve It?
Are you wondering how to accomplish this? It is simple -- just add a parameter to the stored procedure. Depending on this parameter we are going to execute the appropriate operations.

Here is the stored procedure:
Createprocedure [dbo].[USP_Employee] @empid asint=0, @empname asvarchar(50)=NULL, @age asint=0, @salary asint=0, @dob asvarchar(20)=NULL, @designation asvarchar(50)=NULL, @Reqtype asvarchar(10)=NULL AS  
BEGINIF @Reqtype='SELECT'   
BEGIN   
SELECT empid,   
       empname,   
       age,   
       salary,   
       dob,   
       designation   
FROM   tblemployee   
ENDIF @Reqtype='INSERT'   
BEGIN   
insertinto tblemployee VALUES(@empid,@empname,@age,@salary,@dob,@designation)   
ENDIF @Reqtype='DELETE'   
BEGIN   
deletefrom tblemployee WHERE [email protected]   
ENDIF @Reqtype='UPDATE'   
BEGIN   
UPDATE tblemployee   
SET    [email protected],   
       [email protected],   
       [email protected],   
       [email protected],   
       [email protected]   
WHERE  [email protected]   
ENDEND 

In the above example, based on the @Reqtype column the corresponding sql command will execute. For example, if the @Reqtype is select then select statement will execute. If the @Reqtype is inserted then Insert statement will execute.

In this blog, we have learned how to write multiple SQL operations into a single SQL procedure.

European 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.



European SQL 2016 Hosting - HostForLIFE.eu :: Alternative To SQL "In" Condition When Working With Many Values

clock July 10, 2018 06:58 by author Peter
In this post, we will be discussing an alternative to using a SQL “IN” condition, where many values need to be parsed to the SQL query through the use of a User Defined Data Type to get exact records back based on a list of ID's. There will probably be a very small number of cases where additional steps (as detailed in this post) will need to be carried out. Afterall, SQL Server has a very large limit on the number of values the “IN” condition can handle, based on the length of instruction (max 65k).

However, based on personal experience where there is a bigger need for dealing with large volumes of data, investing extra time in database architecture is always good.

The “IN” Condition

For those who are not familiar, the “IN" condition is probably one of the most flexible methods of returning specific records from a table based on multiple list of values. The “IN” condition is a nice shorthand way to carry out multiple OR conditions. A basic example of the condition in use is:

SELECT *  
FROM Products  
WHERE ID IN (1, 2, 3, 4)  

User Defined Data Type

First introduced in SQL Server 2008, User Defined Data Types defines a way to storing temporary data in a structure similar to a table. The User Defined Data Type can be populated through a Table-Valued parameter within a stored procedure. This will be the key to what we will be using in dealing with many values for our alternative to using the IN condition.

Scenario: Selecting List of Products

I encountered a scenario that required a flexible way to get back a number of specific records from my database when I received a list of product ID’s from an external API for cross-referencing. Normally, I would just grab all the ID’s and parse them directly into the IN condition. It was an unknown as to how many ID’s would received from the external API and a different approach needed to be considered to ensure the application would be scalable.

Create A New User Defined Data Type

The first thing we need to do is to create a User Defined Data Type that will allow us to pass the list of our product ID’s in a table structure. The structure itself is very simple and contains just one column called ProductID.
CREATE TYPE Type_ApiProductIDs AS TABLE    
(  
    ProductID int  

Create Stored Procedure

Next we create a stored procedure called “spGetAllProductsByIDs” that will contain a parameter based on the data type we created above. In this case, the parameter is @ProductsTVP. Note that when using a variable based on a data type in a stored procedure, it must be declared as READONLY.

CREATE PROCEDURE spGetAllProductsByIDs  
(  
    @ProductsTVP Type_ApiProductIDs READONLY  
)  
AS  
BEGIN  
    SELECT  
        ID,  
        ProductName,  
        ProductPrice,  
        ProductDescription  
    FROM   
        CMS_Products p  
    INNER JOIN @ProductsTVP AS tvp ON p.ID = tvp.ProductID   
END 
The stored procedure is simply getting back all products from CMS_Products table based on a values stored in @ProductsTVP parameter through a join. Now all that needs to be done is to use this stored procedure in code.

Passing User Defined Data Type In Code

A method called GetProductsBySearchTerm() gets a list of product ID’s based on search terms from a call to the SearchApiHelper class, which then transforms this output to the “prodIdsTable" DataTable structure similar to our Type_ApiProductIDs data type. To use this DataTable with our stored procedure, we’ll be using a SqlDbType.Structured SQL parameter.
 
In addition to a DataTable, a IEnumerable<SqlDataRecord> and DbDataReader can be used in conjunction with the SqlDbType.Structured parameter.  
public static void GetProductsBySearchTerm(string searchTerm) 

    #region Get Product IDs from API Based On Search Term 
      List<int> searchProductIds = SearchApiHelper.GetResults(searchTerm); // Get list of product ID's. 
      // Create a Data Table in the same structure to User Data-Type. 
    DataTable prodIdsTable = new DataTable(); 
    prodIdsTable.Columns.Add(new DataColumn("ProductID", Type.GetType("System.Int32"))); 
      // Populate "prodIdsTable" datatable with ID's from SearchApiHelper. 
    if (searchProductIds?.Count > 0) 
    { 
        foreach (int id in searchProductIds) 
        { 
            DataRow newRow = prodIdsTable.NewRow(); 
            newRow["ProductID"] = id; 
            prodIdsTable.Rows.Add(newRow); 
        } 
    }  
    #endregion 
      if (prodIdsTable?.Rows.Count > 0) 
    { 
        DataSet dsProducts = new DataSet(); 
          using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString())) 
        { 
            try 
            { 
                sqlConn.Open(); 
 
                using (SqlCommand sqlCmd = new SqlCommand("spGetAllProductsByIDs", sqlConn)) 
                { 
                    sqlCmd.CommandType = CommandType.StoredProcedure; 
 
                    SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@ProductsTVP", prodIdsTable); 
                    tvpParam.SqlDbType = SqlDbType.Structured; 
 
                    SqlDataAdapter da = new SqlDataAdapter(); 
                    da.SelectCommand = sqlCmd; 
                    da.Fill(dsProducts); 
                } 
 
                // Do something with the data returned from dsProducts DataTable... 
            } 
            catch (Exception e) 
            { 
                throw e; 
            } 
        } 
    } 
}

European 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.



SQL Server 2016 Hosting - HostForLIFE.eu :: Isolation Level In SQL Transaction

clock February 28, 2017 08:37 by author Peter

Isolation is one of the properties of SQL Transaction. Isolating / separating transactions from each other to maintain Data Integrity in Database is called Isolation.Before going to implementation part of isolation, we will understand why isolation is required in database ?

Why Is Isolation Level Required ?
While developing large enterprise/public networking kind of applications where a huge number of users access same Database, same Table and at the same Time, Data concurrency situation may occur. We will discuss this situation into 4 parts:

  • Loss of Data
  • Dirty Read
  • Phantom Read
  • Inconsistency Analysis

Loss of Data
Let's take an example - Suppose, there are 2 users accessing the same table, at the same moment, to update the same row. Each transaction is unaware of the other transaction. User A updates the row and then User B updates the same row. What happened here is the last transaction made my User B overwrites the updated record of User A and User A lost his/her data in the table.

Dirty Read
This is otherwise known as Uncommitted Dependency. Let's take another example - Suppose, User A and User B are accessing a table row at the same time. User A wants to read and User B wants to update the row. In the friction of time difference, transactions are executed. So, when User B not yet updated the row (during the update process), User A reads that row and got the old record which may not be correct for his/her operation. This situation is known as Dirty Read.

Phantom Read
This is also known as Phantom Problem. Let's again take another example - Suppose User A is granted to insert a row but the same time User B inserted that row. Now, when User A tries to insert, he/she can't. Then, he/she will get angry and say- "Hey you committed that this is available for me to insert, but you cheated on me and granted someone else to do so !" . You may/might get this problem while the reservation of Train/Movie ticket.

Inconsistency Analysis
This is also known as Non-Repeatable Problem. Let's take the same example of User A and User B. Suppose, User A executes a transaction having three queries - a stored procedure or transaction or individual query with a batch. 1st query is to read a table row, the 2nd query is to update that, and the 3rd query is to read that again. By doing this, User A wants to generate the report. As we know User B is not a gentleman he always spoils the intention of User A, he accessed the table row in between the two Read queries of User A and did some operation like Delete ! Now, User A has already modified the data and when he wants to read it again, he is surprised ! He got inconsistency in data.

In the above-mentioned points, we understood that - as User A is a weak person, User B always plays with User A and forcefully does it's job by dominating him. But as a Database Administrator or Developer, we need to help the User A. Now, we have a weapon called "Isolation Level" by using which we can cooperate User A to maintain its integrity.

Isolation Levels
SQL Server provides 5 Isolation levels to implement with SQL Transaction to maintain data concurrency in the database.

Isolation level is nothing but locking the row while performing some task, so that other transaction can not access or will wait for the current transaction to finish its job.

Let's write a transaction without Isolation level.
BEGIN TRANSACTION MyTransaction 
BEGIN TRY 
UPDATE Account SET Debit=100 WHERE Name='John Cena' 
UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock' 
COMMIT TRANSACTION MyTransaction 
PRINT 'TRANSACTION SUCCESS' 
END TRY 
BEGIN CATCH 
ROLLBACK TRANSACTION MyTransaction 
PRINT 'TRANSACTION FAILED' 
END CATCH  


In the above code snippet, we have not implemented Isolation Level. Therefore, there are chances of data concurrency. Now, we will go through all the five Isolation levels and find the solution for our situation.

Read Uncommitted

When this level is set, the transaction can read uncommitted data resulting in the Dirty Read problem. With this isolation level, we allow a transaction to read the data which is being updated by other transaction and not yet committed. Suppose User A is trying to read a row which is being updated by User B. Here, we are allowing User A to read the un-updated/uncommitted data i.e old data.

Example
SET TRANSACTION ISOLATION LEVEL  
READ UNCOMMITTED 
BEGIN TRANSACTION MyTransaction 
BEGIN TRY 
UPDATE Account SET Debit=100 WHERE Name='John Cena' 
UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock' 
COMMIT TRANSACTION MyTransaction 
PRINT 'TRANSACTION SUCCESS' 
END TRY 
BEGIN CATCH 
ROLLBACK TRANSACTION MyTransaction 
PRINT 'TRANSACTION FAILED' 
END CATCH 
Read Committed

This prevents Dirty Read. When this level is set, the transaction can not read the data that is being modified by the current transaction. This will force user to wait for the current transaction to finish up its job. Suppose User A is trying to read a row which is being updated by User B. Here, we are asking User A to wait for the User B to finish its update task, and giving the updated/correct data to User A. But the problem with this level is - it can't resolve Phantom Read or Inconsistency Analysis i.e it asks User A to wait for Read but not for update or insert.

Example
SET TRANSACTION ISOLATION LEVEL  
READ COMMITTED 
BEGIN TRANSACTION MyTransaction 
BEGIN TRY 
UPDATE Account SET Debit=100 WHERE Name='John Cena' 
UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock' 
COMMIT TRANSACTION MyTransaction 
PRINT 'TRANSACTION SUCCESS' 
END TRY 
BEGIN CATCH 
ROLLBACK TRANSACTION MyTransaction 
PRINT 'TRANSACTION FAILED' 
END CATCH 
Repeatable Read

This level does every work that Read Committed does. but it has one additional benefit. User A will wait for the transaction being executed by User B to execute it's Update query as well, like Read Query. But Insert query doesn't wait, this also creates Phantom Read problem.

Example
SET TRANSACTION ISOLATION LEVEL  
REPEATABLE READ
Snapshot

This level takes a snapshot of current data. Every transaction works on its own copy of data. When User A tries to update or insert or read anything, we ask him to re-verify the table row once again from the starting time of its execution, so that he can work on fresh data. with this level. We are not giving full faith to User A that he is going to work on fresh data but giving high-level changes of data integrity.

Example
SET TRANSACTION ISOLATION LEVEL  
SNAPSHOT 
Serializable

This is the maximum level of Isolation level provided by SQL Server transaction. We can prevent Phantom Read problem by implementing this level of isolation. It asks User A to wait for the current transaction for any kind of operation he wants to perform.

Example
SET TRANSACTION ISOLATION LEVEL  
SERIALIZABLE

Isolation level also has a problem called "Dead Lock"- "Both the transactions lock the object and waits for each other to finish up the job". Dead Lock is very dangerous because it decreases the concurrency and availability of database and the database object. We will discuss Dead-Lock in the later part of this topic.

Hope, I covered the necessary points that can help many of us to implement Isolation level in the project with the identification of real situations. Believe me friends, it is a good practice to implement Transaction with "Isolation Level" in every type of projects.

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.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: FORMATMESSAGE

clock February 21, 2017 08:25 by author Peter

In this post, I will show you how to using bigint with FORMATMESSAGE. SQL Server 2016 added the FORMATMESSAGE function.  According to Books On Line, FORMATMESSAGE constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

So let's take a look at this new function, run the following
SELECT FORMATMESSAGE('Signed int %i, %i', 50, -50)
SELECT FORMATMESSAGE('Unsigned int %u, %u', 50, -50);


Here is the output if you run that

--------------------------------------------
Signed int 50, -50
Unsigned int 50, 4294967246

Here is what the type specifications that you can use are

Type specification         Represents
d or i                          Signed integer
o                                Unsigned octal
s                                String
u                               Unsigned integer
x or X                         Unsigned hexadecimal

We used i to denote a signed integer, we also used u to denote a unsigned integer

Let's look at another example, this time we are using a variable. The variable will be an integer and we  are using i as the type specification

DECLARE @Val int = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);


Here is the output
---------------------------------------
The value you supplied 1 is incorrect!

That worked without a problem. Now let's use a variable of the bigint data type, we are using the same type specification as before

DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %i is incorrect!', @Val);

Here is the output
---------------------------------------------------------------------------
Error: 50000, Severity: -1, State: 1. (Params:).


The error is printed in terse mode because there was error during formatting.
Tracing, ETW, notifications etc are skipped.

As you can see that did not work, so what can we do?
One thing we can do is converting the value to a varchar and then use s as the type specification
DECLARE @Val bigint = 1
SELECT FORMATMESSAGE('The value you supplied %s is incorrect!',
   CONVERT(VARCHAR(100),@Val));

You will again get this as output
---------------------------------------

So converting to varchar worked, but what if we want to use a bigint data type without converting to a varchar?

Another way is to use I64d as the type specification
DECLARE @Val bigint = 1<br />
SELECT FORMATMESSAGE('The value you supplied %I64d is incorrect!', @Val);


You will get this
---------------------------------------

The value you supplied 1 is incorrect!
So there you have it, if you want to use bigint with FORMATMESSGAE use I64d as the type specification, or convert to varchar and use s as the type specification.

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.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Auto Increment ID Column Using nvarchar DataType In SQL Server?

clock February 14, 2017 07:53 by author Peter

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.

 



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Implement Inner Join When Query Contains Outer Join?

clock February 7, 2017 08:50 by author Peter

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 Proudly Launches Umbraco 7.5.7 Hosting

clock January 27, 2017 07:48 by author Peter

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



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Get The Header Information Of A Table in SQL Server?

clock January 10, 2017 10:14 by author Peter

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.



About HostForLIFE.eu

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 offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in