Skip to main content

Featured Post

How to use Tabs in ASP.NET CORE

I want to show Components in a tabs , so first of all create few components. In this project we have three components, First View Component  public class AllViewComponent : ViewComponent     {         private readonly UserManager<ApplicationUser> _userManager;         public AllViewComponent(UserManager<ApplicationUser> userManager)         {             _userManager = userManager;         }         public async Task<IViewComponentResult> InvokeAsync()         {             List<StudentViewModel> allUsers = new List<StudentViewModel>();             var items = await _userManager.Users.ToListAsync();             foreach (var item in items)             {                 allUsers.Add(new StudentViewModel {Id=item.Id, EnrollmentNo = item.EnrollmentNo, FatherName = item.FatherName, Name = item.Name, Age = item.Age, Birthdate = item.Birthdate, Address = item.Address, Gender = item.Gender, Email = item.Email });             }            

Implementing Batches in SQL Server and Guidelines

As a database developer, you might need to execute more than one SQL statement to perform a task. For example, when a new employee joins AdventureWorks, Inc., you need to insert the employee details in the database. The details of the employees are stored in more than one table. Therefore, you need to execute an insert statement into store the details in each table. In such a case, you can send all the SQL statements together to the SQL Server to be executed as a unit. This helps in reducing the network traffic.

Creating Batches

A batch is a group of SQL statements submitted together to the SQL Server for execution. While executing batches, the SQL Server compiles the statements of a batch into a single executable unit called an execution plan. This helps in saving execution time.

Consider an example. You have to execute 10 statements and you are executing them one by one by sending 10 requests. This process takes time if your queries are in queue. All statements together in a batch, then the execution process becomes faster as all the statements are sent to the server together.

To create a batch, you can write multiple SQL statements followed by the keyword GO at the end, as shown in the following listing:
<T-SQL Statement1>
<T-SQL Statement2>
<T-SQL Statement3>
…. . .
GO

GO is a command that specifies the end of the batch and sends the SQL statements to an instance of the SQL Server.

Consider an example. If you want to store the details of new employees in the Adventure Works database, you can create the following batch:

INSERT INTO [AdventureWorks]. [Person]. [Contact]
VALUES (0, null, ‘Robert’, ‘J’ ‘Langdon’, NULL
,’rbl@adventure-works.com’, 0, ‘1 (11) 500 555-0172’
,’9E685955-ACD0-4218-AD7F-60DDF224C452’, ‘2a31OEw=’ , NULL
, newid( ), GETDATE ( ) )
INSERT INTO [AdventureWorks]. [HumanResources] . [Employee]
VALUES (‘AS01AS25R2E365W’, 19978, ‘robert1’, 16, ‘Tool Designer’,
‘1972-05-15’, ‘S’, ‘M’, ‘1996-07-31’, 0, 16, 20, 1, newid ( ) ,

When a batch is submitted to the SQL Server, it is compiled to create an execution plan. If any compilation error occurs, such as a syntax error, the execution plan is not created. Therefore, none of the statements in the batch is executed. However, after the execution plan is created, if a run-time error occurs, the execution of the batch stops. In such a case, the statements executed before the statement that encountered the run-time error are not affected.

Using Variables

While creating batches, you might need to save some values temporarily during the execution time. For example, you might need to store some intermediate values of calculations. To store the intermediate values, you can declare variables and assign values to them. You can declare a variable by using the DECLARE statement. The syntax of the DECLARE statement is:

DECLARE @variable_name data_type

Variables that are declare in a batch and can be used in any statement inside the batch are called local variables.
The following code declares a variable, @Rate, and assigns the maximum value of the Rate column from the EmployeePayHistory table to the variable:

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
GO
In the preceding example, the @Rate variable is declardd and used to store the maximum value of the Rate column. The max aggregate function is used to retrieve the maximum pay rate from the EmployeePayHistory table. The GO keyword is used to send all the statements together to the SQL Server.

Displaying User-Defined Messages

At times, you need to display user-defined messages or values of variables when the batch is executed. For this, you can use the PRINT statement, as shown in the following batch.
The following code displays the value of the rate variable by using the PRINT statement.

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
PRINT @Rate
GO
You can also use comment entries in batches to write a description of the code. This will help understand the purpose of the code. A comment entry can be written in two ways:

  • Multiple line comment entries enclosed within /* and */
  • Single line comment entry starting with – (double hyphens).

Guidelines to Create Batches

While crating batches, you need to consider the following guidelines:

  • You cannot combine statements, such as CREATE DEFAULT, CRATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW with other statements while creating a batch. Any statement that follows the create statement is interpreted as part of the definition.
  • You can use the EXECUTE statement in a batch when it is not the first statement of the batch, otherwise the EXECUTE statement works implicitly.
In addition, you need to consider the following restrictions:
  • You cannot bind rules and defaults to columns and use them in the same batch.
  • You cannot define and use the CHECK constraint in the same batch.
  • You cannot drop objects and recreate them in the same batch.
  • You cannot alter a table by adding a column and then refer to the new columns in the batch created earlier.


Comments

Popular Post

Polynomial representation using Linked List for Data Structure in 'C'

Polynomial representation using Linked List The linked list can be used to represent a polynomial of any degree. Simply the information field is changed according to the number of variables used in the polynomial. If a single variable is used in the polynomial the information field of the node contains two parts: one for coefficient of variable and the other for degree of variable. Let us consider an example to represent a polynomial using linked list as follows: Polynomial:      3x 3 -4x 2 +2x-9 Linked List: In the above linked list, the external pointer ‘ROOT’ point to the first node of the linked list. The first node of the linked list contains the information about the variable with the highest degree. The first node points to the next node with next lowest degree of the variable. Representation of a polynomial using the linked list is beneficial when the operations on the polynomial like addition and subtractions are performed. The resulting polynomial can also

How to use Tabs in ASP.NET CORE

I want to show Components in a tabs , so first of all create few components. In this project we have three components, First View Component  public class AllViewComponent : ViewComponent     {         private readonly UserManager<ApplicationUser> _userManager;         public AllViewComponent(UserManager<ApplicationUser> userManager)         {             _userManager = userManager;         }         public async Task<IViewComponentResult> InvokeAsync()         {             List<StudentViewModel> allUsers = new List<StudentViewModel>();             var items = await _userManager.Users.ToListAsync();             foreach (var item in items)             {                 allUsers.Add(new StudentViewModel {Id=item.Id, EnrollmentNo = item.EnrollmentNo, FatherName = item.FatherName, Name = item.Name, Age = item.Age, Birthdate = item.Birthdate, Address = item.Address, Gender = item.Gender, Email = item.Email });             }            

Memory representation of Linked List Data Structures in C Language

                                 Memory representation of Linked List              In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.                Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:       Suppose next node is allocated at an address 506, so the list becomes,   Suppose next node is allocated with an address with an address 10,s the list become, The other way to represent the linked list is as shown below:  In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of