Author: Deependra Kushwah

Let’s understand the Transact-SQL

T-SQL or Transact-SQL is a proprietary SQL extension (design query language). Additional features include high performance, maximum functionality, and greater globalization support.

Transaction SQL is a Microsoft SQL Server process implemented. It is widely compatible with SQL-92, so you are familiar with the second SQL provider, probably feel at home with SQL. Transk-ss stored procedures are used to retrieve data or execute a question in the database.

Transact- SQL stored procedure

The stored procedure is a server-side routine. When you understand, they give you strength and give you a great advantage. Contrary to the user-defined functions (UDF), the stored procedure has allowed side effects. Because you can change the data in the table of actions and even change the appreciation of the object you can not worry about, it works. The protected procedure can be used as a security layer. To overcome access to the elements to perform something that can not be stored in the state.

SQL Server 2008 supports a variety of stored procedures: a user-defined system and augmentation. You can create a stored procedure by the user and TSS or CLR.

Below are some applications that can create transmission SQL

  • General office productivity applications.

  • Graphical user interface (GUI) applications that are used to select tables and user data to select tables and data.
  • Databases of databases, where the data is mobilized from SQL Server adopted in different databases or distribution problems.
  • Data storage in data obtained from online transaction processing systems (OLTP) and summary analysis to support decisions.

More completely defined, do, SQL programming, is a mixture of several components, although integrated, component. Suppose you want to create an application and want to start designing and analyzing the database. And he developed the database in which he uses an authorized SQL statement or a tool for visual tool modeling. Later, you can request a complete set of configuration business in the table in your database running through Encoding in SQL. From here you can create: module processing and the processing module that supports the user interface using the storage method encrypted in SQL.

As mentioned above, SQL Server 2008 developers can store and consume any type of data on any device, and make all those users who wish to create new applications: in SQL. One of the functions offered to the host so they can make relevant decisions closely related to the observation. These are the main characteristics:

  • Variants of variables
  • Union of workers
  • Change of modification
  • New types of date and time data
  • New date and time characteristics
  • Describe the statement
  • Classification of composition
  • A table with parameter values
  • Large types of users: Daratikon
  • Cost table of construction assistance
  • Type of organization of the type of data
  • DDL detects the repair
  • CLR large user-defined presentations

SQL databases are a popular topic these days. For example, the data in Convert to SQL is more complex and is optimized for complex and large-scale technological problems. In order to learn this technology, great problems of comprehension and integral storage of data and mining conditions are also included. Some of the beginners who have knowledge of RDBMS can help some new concepts improve their accessibility.

Advertisements

State design patterns, when to use, pros and cons

The state design scheme is used to model changes in the state or state of an object by delegating the rules for such changes to individual objects representing each possible state.

When we should use the state model?

The state model is a good candidate to apply when you have an object with a relatively complex set of possible states, with many different business rules on how state transitions occur and what should happen when the state changes.

If the object is merely a state-owned property, at any time, any state can be updated with at least special logic, the State Schema increases needless complication. However, for the objects representing the real world concepts, with a compound workflow, the state structure can be a decent option.

What are the Advantages of using this model?

The state model minimizes conditional complexity, eliminating the need for if and switch statements on objects that have different behavioral requirements that are unique to different state transitions. If you can represent the state of the object using a finite state machine diagram, it is easy enough to convert the diagram into the types and methods of the state design model.

What are the Disadvantages of using this model?

The state schema requires a large amount of code to be written. Depending on the number of different defined state transition methods and how many possible states an object can have, you can quickly write dozens or more different methods. For N states with M transition methods, the total number of methods required will be (N + 1) * M. In the previous example for an insurance policy, there are 5 different states, each of which must define 5 methods (ignoring for the moment the ListValidOperations method), for a total of 25 methods in the state types. Thus, the type of policy context must also define the 5 methods of state transition, for a total of 30 methods to be written.

Few Tips to Secure your Servers

Regarding your infrastructure, your primary concern is usually to get and manage your applications. However, apps are not going to work properly without fulfilling the security requirements of your infrastructure. There may be serious consequences in the future.

We will converse various simple security means that need setting or configuring for the top configuration setting.

Check site activity and archive records in a safe place.

Security professionals know the importance of maintaining server activity logs. Since most web servers are for the public, it is important to perform this work for all internet-based services. These audits help detect and respond to the trail attacks and help resolve server performance issues. In high security environments, be sure to store files in a physically safe place – the safest (but the least easy) way to be the line printer, when the path is printed, the path is printed, and then its record Makes it The permanent card cannot be converted by Entrychrinter which has no physical access at workspace. You also want to consider using electronic equality, such as access to the secure host, which is encrypted with a digital signature to prevent entry and record.

Use the application scanner.

If it is accessible, you can enter the internal entry using the application scanner to enter the internal code. AppScan and Hacking tools can help ensure that the exploitation code is not to avoid the trees and the environment of production.

Set the frame for SSH Configuration

One of the most common services is almost SSH on almost all servers. While in the past, managers have sent everything using tools such as a telephone (including passwords!) Via simple text, SCH encryption connection between you and your server. Although it self-improvement in security, unfortunately, it is not enough. In this section, we have some basic SCH hardness techniques that should be used on all of your servers.

Disable Root Login

The easiest things you can do is disable the root record to save a layout setting. After this chapter, we will talk about how to prevent access to road access from Sud Utility (and some default system of viewpoint), in this case, we are as root, password, security keys Talking about limiting the ability to log in. Or any other way. Because of the root user’s power, it is safe to overcome the possibility that the invaders can get access to the administrator’s direct access. Instead, the administrators must log in as a common user and then use a local component like Sudo to become root.

SSL / TLS encryption

You can use an SSL certificate or TLS certificate to verify that the companies are different from each other. After verification, they can also be used to create encryption connections. Each server can be configured to trust a central certification authority. After that, any testimony in which the power signal can be collected. If applications and protocols are used to connect TLS encryption / SSL support, this system is encryption method without which the van tuning is maximum (often used indirectly). Goes)

 

Advantages of NoSQL Database over RDBMS

The NoSQL database is a data loading approach different from the relational database. NoSQL’s differences are considered as an additional chart and document, key-value, column, to name.

MongoDB is schematic of less.

  • It is a database of documents in which a collection contains different documents.
  • There may be a difference between the number of fields, the content and the size of the document from one to another.
  • The structure of a single object is clear in MongoDB.
  • There are no complex combinations in MongoDB.
  • MongoDB offers an in-depth query function because it supports a powerful dynamic query in documents.
  • It is very easy to climb.
  • Use internal memory to store work sets and this is the reason for quick access.
  • Load balancing and Sharding

If you have a lot of data or want to distribute your database traffic between multiple machines to load, there are many advantages of Mongo DB in traditional databases. In addition, shading, which has a unique approach to Mango D to meet data development needs, uses a horizontal scale and allows multiple machines to support database development.

What About Flexibility

All elements in their use do not require unified data structures in nature. It is much simpler than RDBMS. On the other hand, the constant stability of data is very important and, in general, it’s a good thing, so we recommend using a unified data structure.

What about Performance 

Since all figures are usually in one position, Mongo DB is very fast. However, it’s just when you’re working on a document. If you work in data dependency, the code should run multiple free queries to recover individual documents and slow down the RDBMS.

 

SQL interview questions part 1

What is SQL function?

SQL functions are methods which accept parameters and perform the action, such as a complex calculation and return the result of that action as a value.the return value can either be a single scalar value ,result set. We can’t use a function to Insert, Update, Delete records in the database table(s).

Available types of functions in SQL.

  1. Scalar Functions
  2. Table-Valued Functions
  3. System Function

What is the stored procedure?

Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved which executes whenever it is called.

Difference between SQL function and Stored procedure?

  1. A function must return a value but in Stored Procedure, it is optional( Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters.
  3. Functions can be called from Procedure whereas Procedures cannot be called from Function.
  4.  The procedure allows SELECT as well as Insert, Update, Delete statement where Function allows only SELECT statement.
  5. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  6. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  7. Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
  8. We can handle Exception by try-catch block in a Procedure but not in functions.
  9. We can perform Transaction Management in Procedure whereas we can’t go in Function.

Can we call the Stored procedure inside SQL function?

No, You cannot execute a stored procedure inside a function, because a function is not allowed to modify database state, and stored procedures are allowed to modify database state.

Can we call a function inside the Stored procedure?

Yes, We can call functions inside the stored procedures.

Is Row_Number generate duplicate Id?

No, ROW_NUMBER  Returns a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrarily assigned.

what is Scope identity in SQL?

Scope_Identity returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).

What is CTE in SQL?

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.